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.


Compustat Execucomp output and Excel

One of the few databases that covers compensation data (on CEO’s and others) for companies is the Compustat Execucomp database. This database has data fro S&P 1500 companies from 1992 to now. Most of these companies are American companies. When you download CEO data using a list of companies (Tickers or CIK codes) you may have to filter the result in Excel to limit the data from the database to just CEO’s.

Even when you have limited the download in Excel to just CEO’s it still is important to check the data using the Execucomp variables for Date Became CEO & Date left as CEO against the Fiscal Year date for the reported remuneration. Using Excel you can use simple formulas to check for outliers or weird values.

First you can do a check on whether the CEO started before or in the fiscal year for which we have data. In this case we need to check the year of the starting date against the value of the column with the fiscal year: =IF(YEAR(I2)<=H2,TRUE,FALSE)
See example:

Next you do a check on whether the CEO has left before the fiscal year for which we have data. This time the formula is a bit more complicated as we only need to check cells that have an ending date value. When the End date is NOT zero (= if there is a date) we need to check if the year when the CEO left the company (as CEO) is before the Fiscal Year. This can be done using a nested conditional formula as follows: =IF(LEN(J2)=0,TRUE,IF(YEAR(J2)>=H2,TRUE,FALSE))

LEN() = Length of the string of characters in a Cell
IF() = Condition
YEAR() = Gets the Year value for the date in a Cell

The first condition checks whether the field has an End date or is empty, which means that the CEO is still the current CEO. If there is a date, the second condition checks if the year of the End Date lies before the (current) fiscal year.

See example:

NB: If you quickly need to scan the check columns for the FALSE rows (indicating outliers and such) you may use the Excel Conditional formatting option to colour the FALSE values. See for the option at: