Databases

1
University of Arizona Department of Finance
Research Databases
By Andrew Zhang
2
What is Covered?

• Database Introduction
• What is it?
• Where is it?
• How to access it?
• Where to get data documentation?
• Where to get help?
• Online help and support files
• How to get sample codes?
• SAS, Fortran, and C?
• Unix Introduction
• WRDS Data Retrieving Techniques

3
What is NOT covered

• How to pull data?
• How to program SAS or Fortran 77 or 95?
• How to compile program in Unix?
• It is time for you to decide whether you need
  continue to stay here or NOT
• ??? Or !!!
• finance.eller.arizona.edu/research/dataresources.a
  spx

4
Database Resources

• Compustat
• CRSP Stocks
• Others
• TAQ
• CRSP Mutual Fund
• ExecuComp
• IBES
• Berkeley Options
• SDC MA data
• FISD fixed income database
• Thompson Mutual Fund data
• Global Insight

5
Compustat

• Annual, Quarterly, and Segment Financial
  Statement Data
• Active and Inactive Files
• North American Public Firms
• Updated annually
• moving window of 20 years
• 40 years data on WRDS

6
How to Access Compustat

• Z drive
• 2000-2004
• Unix of UA Fortran or SAS
• No update available any longer
• WRDS web querying
• Unix of WRDS Fortran or SAS

7
CRSP Stock

• Provides stock market data for NYSE, AMEX, and
  Nasdaq firms
• Prices, Return, etc.
• Monthly and Daily
• How to Access
• ts-print
• Unix of UA Fortran or SAS
• No updating available any longer
• WRDS web querying
• Unix of WRDS Fortran or SAS

8
NYSE Trade and Quoting (TAQ) Data

• Intraday transactions data for all securities
  listed on NYSE, AMEX, and Nasdaq.
• Data available 1/93 1/04
• Access through TAQ2.exe
• No subscription in WRDS

9
CRSP Mutual Fund

• Provides open-ended mutual fund data
• 1962 to 2005
• Equity Funds
• Bond and hybrid funds
• Money market funds
• Tax free funds
• How to access
• Z drive
• SAS or Microsoft Access

10
Standard Poors ExecuComp

• Executive and Director Compensation
• Including option and stock holding by insiders
• Dates of coverage 1992 2004
• Updated annually
• How to Access
• Z drive with MSFT Access
• WRDS
• Web Querying
• Unix of WRDS

11
Institutional Brokers Estimates System (IBES)

• Monthly analyst forecast on earnings
• 1987 to 2004
• Maintained by Accounting dept
• US companies and International companies
• It is free subscription but working paper
  submitted to Thompson
• Documentation
• www.ic.arizona.edu/ic/trombley/dbdocs/ibesdocs/ibe
  sdocs.html
• Password needed
• /fa/finacct/passwords.txt

12
How to Access IBES

• Unix of UA Finacct user group
• Where is data?
• /fa/databases/ibes/2004/us/detail
• /fa/databases/ibes/2004/us/summary
• SAS program to access it
• Where is sample SAS program?
• /fa/finacct/ibes_summary_us.sas
• /fa/finacct/ibes_detail_us.sas
• Intl vs. us
• Copy it to your own directory and sas it
• cp file /fa/fa4/andrew/myfile.sas

13
Berkeley Options Data

• Derived from the CBOE's Market Data Retrieval
  tapes
• Data begins August 1976 1994.
• Historical record of trades and quotes,
  time-stamped to the nearest second, for
  standardized contracts traded on the CBOE.
• How to Access
• Z drive
• Database format

14
SDC MA data

• Our subscription permission
• Merge and Acquisition Only
• 1962-present
• How to access
• Install SDC setup file
• Access online only
• Txt and Excel format available

15
FISD database

• Fixed Income Transaction Data
• Real bond buy and sell transaction
• Not quote or matrix price
• Issuer and Issue specific information
• 1994-2004
• How to access
• Z drive
• Access or SAS

16
Thompson Mutual Fund Data

• Mutual funds holding data
• 1992-2002
• Annually, semi-annually, or quarterly
• Stock holdings only
• Quantity and amount of stocks held by mutual
  funds with 13f SEC filings
• Maybe incomplete
• How to Access
• Z drive
• Txt or Excel format

17
Global Insight

• Macroeconomics Datasets
• provides data on national income accounts,
  balance of payments, foreign debt, exchange
  rates, money supply and employment - among other
  national level categories. Also provides data on
  the IMF series and the OCDE series.
• XLS format
• Up to September 2004

18
Getting familiar with data

• Go to finance database website
• http//finance.eller.arizona.edu/research/datareso
  urces.aspx
• http//www.ic.arizona.edu/ic/trombley/
• http//www.ic.arizona.edu/ic/trombley/dbdocs/ibes.
  html
• Go to finance server Z drive
• Get database manual

19
Where to get help?

• WRDS http//wrds.wharton.upenn.edu/support/dataov
  erview.shtml
• CRSP, Compustat and IBES documentation
• UA www.ic.arizona.edu/ic/trombley/dbdocs/docs.htm
  l
• WRDS wrds.wharton.upenn.edu/support/docs/manual.s
  html
• Unix Programming
• UA www.u.arizona.edu
• WRDS wrds.wharton.upenn.edu/support/index.shtml
• SAS programming
• support.sas.com/onlinedoc/913/docMainpage.jsp
• Fortran 77 or 95 programming
• http//docs.sun.com/app/docs/doc/802-2998

20
Unix of UA and WRDS

• How to get account
• finacct group
• Email Mark at accounting department
• Each account 12 MB
• WRDS same username and password
• Each account 750 MB
• Where to download SSH
• http//www.u.arizona.edu/ssh/
• Help for programming in Unix
• http//www.u.arizona.edu
• http//wrds.wharton.upenn.edu/support/index.shtml

21
WRDS

• Request account
• wrds.wharton.upenn.edu/wrdsauth/account_req.shtml
• Our subscription
• http//wrds.wharton.upenn.edu/mywrds/dslist_desc.s
  html
• CRSP
• Compustat
• ExeuComp
• A lot of free data Fama French factors, etc.

22
Data Not Available Through WRDS

• Finance server Z drive
• Trade and Quote (TAQ) Data
• Fixed Income Securities Database (FISD)
• Global Insight (monthly and quarterly files)
• SDC Mergers and Acquisitions Data
• Unix of UA
• IBES

23
Some Common DataNowhere to Get

• CRSP / Compustat Merged Database
• Thomson Financial Institutional Ownership and
  Insider Trading Data
• Compustat Bank and Utilities Data

24
Database in WRDS

• How to access?
• Web Querying?
• If files are too large, you may have to split
• Unix of WRDS
• Where is the data in Unix of WRDS
• /wrds/crsp/sasdata (fortran C etc)
• How to access data in Unix?
• Download directly
• SSH file transfer
• Program to retrieve data
• SAS
• Fortran and C

25
Database in WRDSContd.

• Where is sample program codes?
• wrds.wharton.upenn.edu/support/dslist/dslist.shtml
  
• Unix of WRDS
• /wrds/compustat/samples
• Free SAS viewer
• www.sas.com

26
How to SAS in WRDS?

• Three Ways
• PC SAS
• Remote Submit
• Remote Library Services (RLS)
• SAS in Unix of WRDS
• Where to get help?
• wrds.wharton.upenn.edu/support/index.shtml

27
PC SAS Remote Submit

• Where to get help?
• wrds.wharton.upenn.edu/support/docs/wrdsSASConnect
  .shtml
• Use WRDS server to remote execute SAS
• Good for stupid old computers
• Easily get disconnected
• Not always so fast

28
PC SASRemote SubmitSample Codes

• let wrdswrds.wharton.upenn.edu 4016options
  comamidTCP remoteWRDSsignon
  username_prompt_rsubmitlibname comp
  '/wrds/compustat/sasdata'data mydataset
  comp.compannwhere yeara2002proc print
  datamydata (obs30)runendrsubmit

29
PC SASRemote Library Services (RLS)

• To use local libraries and remote libraries in
  the same time
• SAS dataset in WRDS server and the dataset in
  your own computer
• Your computer cannot be too old
• It is in fact using your computer CPU and memory
  to execute program

30
Sample CodesRLS

• let wrds wrds.wharton.upenn.edu 4016
• options comamidTCP remoteWRDS
• signon username_prompt_
• options linesize72 nocenter nodate
• libname crsplib remote '/wrds/crsp/sasdata'
  serverwrds
• libname locallib 'c\sas'
• data a
• set crsplib.msi
• where '01JAN2000'd lt date lt '31DEC2000'd
• run
• data b
• set locallib.msi_local
• where '01JAN2001'd lt date lt '31DEC2001'd
• run
• data c
• set a b
• run
• proc print datac

31
SAS in Unix of WRDS

• Write program and compile it
• Use WRDS server, and is faster than UA Unix
• Personal account 750 MB
• Nearly unlimited temporary capacity
• /wrds/sastemp01-09
• Only 48 hours or even shorter

32
Sample SASin Unix of WRDS

• cp /wrds/crsp/samples/ina.sas my_ina.sas
• sas my_ina.sas
• How to read the results?
• SAS viewer
• File transfer to download to your local PC