Using Excel Vlookup to match data from datasets

Many people use the Excel Vlookup function to merge data from 1 or more sheets and have it presented in a single table for use later. This can be to create a single overview with all the data together from several downloads from 1 or more databases. Alternatively Vlookup can be used to match company identifiers from more than one database as a preparation for an event study. An example of the last situation could be as follows:

1) You may want to get the report dates for the quarterly statements of companies from the Fundamentals quarterly database of Compustat North America. You want to see what the price did within a certain event window before and immediately after in a small window around the report dates.

2) The first step would be to download your selection of companies and data from the Compustat North America Fundamentals quarterly database. In your download you automatically get the unique Global Company Key (= GVKEY) for each quarterly reported date (for the time period you choose). This means that you get (duplicate) GVKEYs for each quarter in the download.

3) As fiscal years and report dates differ a lot between many companies, you would have to do many searches in the Compustat North America Security Daily database to get data for all the (event) windows. There is an easier way to do this: use the CRSP tool Daily Extract with Time Window. Using ths tool all you need are the report dates for all the quarters. To use the tool, however, you need to match the company Identifiers for the Compustat north America dataset with the Permno‘s for CRSP. The event tool can only handle Permno’s. Luckily there is a CRSP/Compustat merged database.

4) Create a text file with all the GVKEYs from the Compustat download you did earlier. To speed things up you could use the Excel option remove duplicates to remove duplicate GVKEYS. I explained how to do this in an earlier post.

5) Upload the text file with GVKEYs in CRSP/Compustat merged database and download the necessary identifying data. The download should include the variable “Historical CRSP PERMNO Link to COMPUSTAT Record“.

6) Using the Compustat download and the CRSP/Compustat merged download you can now add the Permno identification codes you need for each quarter. You can find a short movie here that shows how you can get the Permno in the Compustat dataset using a dataset from the CRSP/Compustat database. It shows how to use the Excel Vlookup function:

7) The final step is easy: Copy the column with the report dates and the column with the Permno‘s to a new sheet and format them for use with the tool CRSP – Daily Extract with Time Window.
The text file that you can use should look as follows:
Permno Reportdate
29161 19761020
15763 19841229
10093 19860215
10161 19860821
10161 19860925
10341 19860925
65111 19880203


%d bloggers like this: