Going from CRSP & Compustat North America to Datastream

Making a selection of companies/data in different databases can be tricky. In some cases it helps if the owners of a database already did some work for you. In the case of CRSP it is easy to go from this database to Compustat (and the reverse) using the CRSP/Compustat Merged database. Also, in some cases it can be handy to use it to go from a selection in CRSP or Compustat North America and look up data in Datastream.

The important thing is, as usual, the company or listing identification codes. The import codes that you can work with in these databases are the following: GVKEY, PERMNO, PERMCO, CUSIP, Ticker, CIK.

GVKEY = unique company code from Compustat databases
PERMNO = unique listing code from CRSP databases
PERMCO = unique company level code from CRSP databases
CUSIP = unique code for listed companies from the CUSIP bureau
Ticker = Ticker Symbol for listed companies at exchanges
CIK = Central Index Key = unique code for companies registered at the SEC.

The merged database consists of different part databases that you can use to match one of these codes to the other. If you are interested in Company level information the part database you can use from the merged database is the Fundamentals Annual database.

You can start from a selection in Compustat and use a GVKEY list to get the PERMNO or PERMCO codes.
You can also start from a CRSP selection with PERMNO codes and get GVKEY codes.
If you need to go from CRSP to Datastream the merged database is a good way to get CUSIP 9 codes that allow you to search for data in Datastream.

In the WRDS search screen you also try getting a closer match using the Linking options available and the options at “Fiscal Period and Link Date Requirements“. You only need to work with this if the amount of codes in the result is significantly different from the input list. Also, if the original list that you use to search, is an older (or historical list) you may need these options to get matches. See screenshot:

At step three in WRDS you can select the codes you need in your output:

Once you have made your selection (maybe already including financial data) it is a good idea to use the download option at step 4, called: “tab-delimited text (*.txt)“. The downloaded text file can still be used fine in Excel and this ensures that you are in control where company codes are concerned. These codes need to stay in tact if you need to search in another database like Datastream with CUSIP 9 codes. Check another post about working with text files in Excel if you need more information on this.
To use CUSIP 9 codes in Datastream you have to add the letter “U” in front of each code: to make it a “local code” that Datastream understands better Alternative: use the text “WS:”). You can use the Concatenate() (= Tekst.Samenvoegen) function of Excel to do this:

If your starting point from CRSP is not only a list of PERMNO codes but dates as well, then you will have to use the VLOOKUP() (= Verticaal.Zoeken) to match original PERMNO codes with dates with the right CUSIP 9 codes. If your starting point is a file like this:

Using the list I removed duplicate PERMNO codes and then did a search in the merged database (Fundamentals Annual part database) to get the CUSIP 9 codes:

As the final step I got the CUSIP 9 codes from that download in Excel with the original list of PERMNO codes + dates using the Vlookup() function (example of this function):

When this was finished I could use the original dates with the needed CUSIP codes (after adding the letter U) in a Request Table search in Datastream to get data from there.

N.B.: You will probably lose some companies from the original file as the PERMNO may not always be matched perfectly using the merged database.


%d bloggers like this: