Research Databases

University of Arizona Department of Finance Research Databases By: Andrew Zhang

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

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.aspx

Database Resources • Compustat • CRSP Stocks • Others • TAQ • CRSP Mutual Fund • ExecuComp • IBES • Berkeley Options • SDC M&A data • FISD fixed income database • Thompson Mutual Fund data • Global Insight

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

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

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

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

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

Standard & Poor’s 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

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/ibesdocs.html • Password needed • /fa/finacct/passwords.txt

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

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

SDC M&A 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

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

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

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

Getting familiar with data • Go to finance database website • http://finance.eller.arizona.edu/research/dataresources.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

Where to get help? • WRDS: http://wrds.wharton.upenn.edu/support/dataoverview.shtml • CRSP, Compustat and IBES documentation • UA: www.ic.arizona.edu/ic/trombley/dbdocs/docs.html • WRDS: wrds.wharton.upenn.edu/support/docs/manual.shtml • 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

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

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

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

Some Common Data—Nowhere to Get • CRSP / Compustat Merged Database • Thomson Financial Institutional Ownership and Insider Trading Data • Compustat Bank and Utilities Data

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++

Database in WRDS—Contd. • 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

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

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

PC SAS—Remote SubmitSample Codes • %let wrds=wrds.wharton.upenn.edu 4016;options comamid=TCP remote=WRDS;signon username=_prompt_;rsubmit;libname comp '/wrds/compustat/sasdata';data mydata;set comp.compann;where yeara=2002;proc print data=mydata (obs=30);run;endrsubmit;

PC SAS—Remote 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

Sample Codes—RLS • %let wrds = wrds.wharton.upenn.edu 4016; • options comamid=TCP remote=WRDS; • signon username=_prompt_; • options linesize=72 nocenter nodate; • libname crsplib remote '/wrds/crsp/sasdata' server=wrds; • libname locallib 'c:\sas'; • data a; • set crsplib.msi; • where '01JAN2000'd <= date <= '31DEC2000'd; • run; • data b; • set locallib.msi_local; • where '01JAN2001'd <= date <= '31DEC2001'd; • run; • data c; • set a b; • run; • proc print data=c; • var date vwretd ewretd; • run; • signoff;

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

Sample SAS—in 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

Research Databases