Library Help: Teaching and learning

This post will help you to take a list of US or Canadian companies from CRSP or Compustat, convert the codes, then use them in Bloomberg, Eikon/Workspace, Datastream or Capital IQ Excel add-ins. If you have a list of GVKEY codes or PERMNO codes, you can use CRSP/Compustat Merged to convert them 9-digit CUSIP codes.

This post will also describe how to use Stata to clean the intermediate data file if it is too large for Excel. It will show you how to use VLOOKUP to map the CUSIP codes into your original data set with GKVEY or PERMNO codes, so that you can use Excel formula builder tools from Datastream or similar databases. The process has been tested with GKVEY codes more thoroughly than PERMNO codes.

Outline of the process:

1. Start with GVKEY, end up with CUSIP, so you can supplement existing data table with Bloomberg, Datastream, Workspace or Capital IQ Excel add-in formulas.
2. Take a list of unique GKVEY codes as a text file, use this in a CRSP/Compustat Merged request (fundamentals annual) to return GVKEY and CUSIP only.
3. Save this intermediate data file. You will get too many columns (name and report type) and too many results (one row for each company each year) so you need to drop extraneous columns and remove duplicates.
4. If the intermediate file is too big for Excel, you can use Stata. Import a CSV file, use the drop columns and remove duplicates commands then export a CSV file.
5. After you clean up the intermediate file, you can merge the CUSIP codes into your original dataset with VLOOKUP in Excel.

See the attached file below for details.