Docstoc

Databases

Document Sample
Databases Powered By Docstoc
					  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/datares
  ources.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/ibes
    docs.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/datar
    esources.aspx
  – http://www.ic.arizona.edu/ic/trombley/
  – http://www.ic.arizona.edu/ic/trombley/dbdocs/i
    bes.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.sh
         tml
• 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/wrdsS
    ASConnect.shtml


  – Use WRDS server to remote execute SAS
    • Good for stupid old computers
    • Easily get disconnected
    • Not always so fast
       PC SAS—Remote Submit
           Sample 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

				
DOCUMENT INFO