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.
Most of our users who need North American company financial information need to link it to another data source, often with a different type of company identifier. A tool has appeared within Compustat via WRDS (which may have been there for some time) allowing you to convert either 8 or 9-digit CUSIP codes into 6, 8 or 9-digit ones. This is a bit of a surprise to me, as Compustat works best with GVKEY codes, but the different length of CUSIP codes help it to interface with other databases as follows.
- Bloomberg (9-digit CUSIP)
- Capital IQ (GVKEY has better coverage)
- CRSP (8-digit CUSIP)
- Datastream (via Local Code)
- Eventus (8-digit CUSIP)
- SDC Platinum (6-digit CUSIP)
- ThomsonONE.com (9-digit CUSIP when using the Excel add-in)
The databases you join with may not have the full coverage of CUSIP codes, so take care. There will almost always be gaps when going from one database to another.
Take scientific care of CUSIPs in Excel
A final tip when using Excel. Some CUSIP codes can be interpretted as numbers in scientific format (when the code contains numerals and the letter ‘E’, such as Genuity Inc 37248E202 read as 3.72 x 10206). Some CUSIP codes begin with leading zeroes (Apple Inc is ‘037833AL4‘) which can get lost and change meaning.
You must always make sure that a column of CUSIP codes is formatted as Text not General before you paste them in. When opening from a CSV file, consider using the Data > Get External Data > From Text menu command and explicitly set the CUSIP column as Text format.
See also our earlier post on the CRSP converter tool:
Using CUSIP codes can cause headaches in Excel. They can be confused with numbers expressed in scientific format or leading zeroes can be lost. The blog post below, written by EDSC, offers some assistance on the subject.
When you download data from WRDS to an Excel spreadsheet and include CUSIPs in your file, you often have CUSIPs that that look weird. See this example from CompuStat North America Index Constituents, the column Company Cusip should contain 9-digit CUSIPs:
The E’s in the CUSIPs of Dun & Bradstreet and Dr Pepper are recognized by Excel as a mathematical constant (2,718) and the CUSIPs of Amazon.com and Apple are incomplete (they start with zero’s, but these are not visible). This can very unhandy if you use these codes as input in other databases.
You can solve this by downloading the data from WRDS in another output format (comma-delimited text or tab-delimited text) and then:
- Open a new Excel sheet
- Open the ribbon Data
- Choose From Text
- Browse to the CSV or txt file and click Import
- In the Text Import Wizard, mark Delimited and click Next
- In step 2…
View original post 98 more words