From ISIN codes to Compustat (via Capital IQ using GVKEY, CUSIP and Name)
Given a list of North American company ISIN codes from the Markit credit default swap database (via a lookup database), you may want to join in quarterly company fundamentals from Compustat in WRDS. Compustat does not accept ISIN codes; you have to use an intermediate source such as Capital IQ and one or more intermediate data types (GVKEY, CUSIP and Company Name).
Note, you must be using a PC with the Capital IQ Excel add-in set up. This is in the Library Finance Zone.
- Use Capital IQ Office Plug-in (CIQ) with a column of ISIN codes in Excel. Put these in column A.
- In the next column, B, get the values of column A with a prefix of “I_” so CIQ can interpret them as ISINs. Cell B2 contains =”I_”&A2
- Next get the GVKEY code in column C with a CIQ formula. Cell C2 contains =CIQ(B2, “IQ_GVKEY”) — the output will need cleaning up to remove the “GV_” prefix and any multiple matches.
- Then the CUSIP codes in column D. Cell D2 contains =CIQ(B2, “IQ_CUSIP”) — this may not work well, I used the Compustat helpdesk here to get these.
- Finally from CIQ, the company names. Column E. Cell E2 contains =CIQ(B2, “IQ_COMPANY_NAME”)
- Create a new plain text document with the values of column C (GVKEY) and upload this to Compustat in the usual way.
- For the gaps, repeat with the values of column D (CUSIP).
- For any final gaps, use the Manually enter company codes > Code Lookup tool in Compustat, typing in the first part of the names to obtain any extra GVKEY codes one by one and repeat step 6.
- Stitch together the various Compustat output files and check that all the companies are present without duplicates.
Consider that CUSIP codes can change with time, so use them with caution. Remember to keep any column of CUSIP codes with Text format, never General, Number or Scientific.