Library Help: Teaching and learning

This post will help you to take a list of US or Canadian companies from Bloomberg, Datastream or (sometimes) Workspace, convert the codes, then use them in CRSP, Compustat or Eventus. If you have a list of 9-digit CUSIP codes, you can use Excel to convert them to 8-digit CUSIP codes. You could also convert to 6-digits.

• For example, if you are using the Equity Screening in Bloomberg, make sure to add a CUSIP column to the results.
• For example, if you are using the Deals Screener in Eikon, you might have the option of adding a 9-digit CUSIP data item, such as 'Issuer/Borrower 9-digit CUSIP'.
• If you are using Datastream, you can select the static datatype 'LOC' or 'Local Code' which, for US companies, is the letter 'U' followed by the 9-digit CUSIP code. Canadian companies begin with a 'Q' instead.
• Note, you will probably not achieve 100% coverage! 

Steps to add a conversion formula:

1. Using a computer with Excel, open a new Excel workbook.
2. Open your spreadsheet with a column of 9-digit CUSIP codes or Local codes. Let's say this is column C, with a header in row 1 and data starting in row 2.
3. In cell F2, paste the following formula: =LEFT(C2, 8)
   1. If you have LOCAL codes instead of pure CUSIP codes, use =MID(C2, 2, 8)
4. Press Enter and wait for the results to load.
5. Copy the formula down to all rows.
6. Remove the data in column F for any failed rows.
7. Copy and paste this new column 'As Values' to remove the formula and keep the data.

If we break the expression down:

• For pure CUSIP codes, this is a LEFT formula. The two parameters are the series (CUSIP code) and the number of characters to keep (8).
• For Local codes, this is a MID formula to extract the text from the second character then keep the next 8 characters.
• Watch out for any missing values, especially if they are for companies in other countries. They may appear as 'A' or 'NA' depending on your formula.
• If you want to get 6-digit CUSIP codes instead, change the 8 to a 6 in either formula.

After cleaning up any gaps or errors, you can now use the new column as 9-digit CUSIP codes.

See also: 

• How to convert from 6-digit to 8-digit CUSIP codes
• How to convert from ISIN or Datastream/DS code to CUSIP code
• CUSIP codes: how to avoid common problems when opening CSV files (My Learning Essentials)
• Convert 6 digit CUSIPs to 8 or 9 digit CUSIPs or vice versa? (Princeton University)
• WRDS: Identifiers and Linking Files (Stanford Graduate School of Business)