CRSP and Compustat via WRDS 20190901

BLA New Business Librarians Training Day

Supporting materials

• Slides/materials: onedrive

Practicalities

• Group: Up to 20
• Length: 40 minutes
• Room: any computer lab
• Discipline: new business librarians
• Level: any

Learning outcomes

After engaging with this support, you will be able to:

• Be able to select an appropriate database for use in your studies and dissertation
• Be able to search databases effectively to obtain data/information to support the analysis of companies, markets and countries

Suggested online resources

• Using specialist business databases

Session content

Outline of the session

1. Choose relevant databases
2. Database demos
3. Good advice and common pitfalls
4. What comes next?

Choose relevant databases

Of the many specialist business and financial databases available, you can be guided to make an appropriate selection by following our online support or by instructions from your course tutors and previous research.

You may wish to conduct similar research to that published in a paper. In many circumstances, you can use the same database platform. However, if you modify the study to look at a different part of the world, for example, you may have to use a different database.

Some resources are available online for use anywhere in the world with your University login… others require you to come into the Library to use.

These tools are often the same ones used in industry, they may assume that their users have extensive subject knowledge. Some are very old pieces of software that assume the user will read through an instruction manual before getting started! We will help you to get started with the features you need to know.

More on this later.

Company databases

• CRSP has price data for US-listed companies.
• Compustat has accounts data in a North America database and a Global database.
• There is a CRSP-Compustat Merged database called CCM.
• WRDS databases are mostly North America, some are global or for other markets.

Search strategy: what, where, how?

A bit of thought at the planning stage will save you a lot of time throughout your studies at University. Everybody does things their own way but I like to try and keep it simple — Three key questions need to be addressed when finding suitable information to support and contrast with your own opinions / arguments

Database demos (build up)

• Look at Compustat with one company, then with a few companies.
• Look at CRSP with a list of many companies, then all companies that satisfy a condition.

What is Wharton Research Data Services?

Wharton Research Data Services (WRDS)

• Developed by researchers for researchers
• Widely used and well respected

Several databases (subscribe individually)

• Standard web query interface
• Online documentation and help (FAQ for each product)
• Excellent support for bulk download.

How to access WRDS

Show method for University of Manchester, via Subject Guides

WRDS approach

• Access upon request (each user applies, we administer)
• Students may have a class account!
• Choose dates, companies, variables (common principle across databases)
• Download in various formats (Excel, CSV, SAS, SPSS, more)

Databases vary by institution, may include:

• CRSP, Compustat, also CRSP-Compustat Merged (CCM)
• London Share Price Database (LSPD)
• Bureau van Dijk Amadeus
• Eventus
• I/B/E/S
• BoardEx

Database demo 1: Compustat, one company

Download Apple’s total revenue 2012–2017

1. Log into WRDS, find Compustat — Capital IQ, Compustat North America Daily, Fundamentals Annual.
2. Choose date range, CUSIP code type, search for Apple by name, search for total revenue, save as CSV, download.
3. (Why CSV and not Excel? More on that later)

Save file as compustat apple.csv. Open by double-clicking. Don’t worry about CUSIP errors yet.

Multiple companies, several ways

Explain that there are different ways, we will see all:

• Select each company one-by-one
• Upload a list of many companies
• Search entire database, except conditions

Database demo 2: Compustat, a few companies

Download total revenue 2012–2017 for Apple Inc, Dr Pepper Snapple Group Inc, Microsoft Corp.

1. Log into WRDS, find Compustat North America, Annual Fundamentals, Daily Update.
2. Choose date range, CUSIP code type, search for each by name, search for total revenue, save as CSV, download.
3. (Why CSV and not Excel? More on that later)

Save file as compustat apple drpepper microsoft.csv

Open CSV files carefully

Open in Excel, say that some codes come in wrong, we will explain it later. For now:

1. Download CSV files from WRDS, not Excel
2. In Excel, ‘Get External Data From Text’
3. Make sure CUSIP or GVKEY columns are formatted as Text, not General or Number
4. Save as compustat.xlsx

Create list of company codes

Copy the codes to new sheet, sort, remove duplicates, save as text file cusips.txt

Database demo 3: CRSP, list of many companies

CRSP: download price of the same three companies, daily for January 2017

What exchange are they trading on?

1. Log into WRDS, find CRSP, Annual Update, Stock/Security Files, Daily Stock File.
2. Choose date range, CUSIP code type, upload list, select price and exchange code, save as CSV, download.
3. (Why CSV and not Excel? More on that later)
4. NASDAQ exchange code is 3

Save file as crsp apple drpepper microsoft.csv

Database demo 4: CRSP, all companies matching a filter

CRSP: download price of NASDAQ constituents, daily for January 2017

Hint: search entire database, filter to only include rows where exchange code is NASDAQ

1. Log into WRDS, find CRSP, Annual Update, Stock/Security Files, Daily Stock File.
2. Choose date range, CUSIP code type, search entire database, search price and exchange code, filter exchange code = 3, save as CSV, download.
3. (Why CSV and not Excel? More on that later)

Save file as crsp nasdaq.csv

Good advice and common pitfalls

1. Data questions
2. CUSIP codes
   (Open CSV files carefully)
3. Company identifier codes
4. Fuzzy name matching

Data questions

Is there real data available?

• Yes — plenty
• Deals and companies given — Datatypes/variables to be decided

How much data?

• WRDS excellent for handling large data volumes

What format?

• Analysis — Excel, Stata, Eviews, …

Data Quality

• IDs permno — linking deals with IDs for use in WRDS has already been done

How long will collecting data take?

• WRDS is quick (once you have decided what you need)

CUSIP codes

US/Canada security issue codes (stocks/bonds). Used by CRSP database and others.

• Issuer number is who the security belongs to (the company).
• Issue number is which stock class or bond within that issuer (primary issue is usually 10).
• Check digit is calculated by an algorithm using the other 8 digits, can point out if there is an error (like credit card numbers).

You can guess an 8-digit CUSIP from the 6-digit one followed by 10.

For example, Alphabet Inc. is 02079KAB3

Question

If you type these three CUSIP codes into Excel, which codes get stored correctly, and why? Apple 037833100; Dr Pepper 26138E109; Microsoft 594918104

Is Apple stored correctly?

• No, it has lost the leading zero, it now has 8 digits. If you were to take the first 6 digits you would get the wrong issuer number.

Is Dr Pepper stored correctly?

• No, it had an E in the code so got interpreted as scientific notation (2.61 times 10 to the power of 113).

Is Microsoft stored correctly?

• Yes, it just has numbers, does not start with zeros, so is fine.
• (If it had had letters other than E it would have been fine too.)

Company identifier codes

• Ticker format for company and exchange are not the same with different data suppliers.
• They also change with time (as do some of the other codes).
• Name can change too, and not a perfect match
• Compustat prefers CUSIP9 (North America) or ISIN (Global). Also has GVKEY
• CRSP prefers CUSIP8, also has PERMNO
• Some conversions can be done via WRDS, Datastream or Excel
• (see chart)

Downloading data

• Bulk download essential for empirical studies
• Check database historical coverage
• Create lists/portfolios to save time later
• Choose identifiers wisely

What comes next?

• Students take their data away
• Reshape/format
• Join with other data sets
• Perform analysis such as regression

Tools such as SAS, Stata, Excel, SPSS, EViews, MATLAB, Python, R.