The components of a CUSIP code

CUSIP codes: where it could go wrong

Note: this post has been replaced, please go to:

CUSIP codes: where it could go wrong

Researchers who need U.S. and Canadian company financial information need a unique identifier one of which is CUSIP codes.

CUSIP stands for Committee on Uniform Securities Identification Procedures. A CUSIP number identifies most financial instruments, including: stocks of all registered U.S. and Canadian companies, commercial papers, and U.S. government and municipal bonds.

This post highlights possible areas CUSIP codes may lead to hiccups during data analysis, and how to prevent these problems from occurring.

This post outlines:

1. What problems can occur with CUSIP codes?
2. How to prevent these problems

1. What problems can occur with CUSIP codes?

1.1 Different lengths of CUSIP codes

CUSIP codes can either be 6, 8 or 9 digits in length.

Some example CUSIP codes of different lengths.

• The first six digits are the issuer code (the company).
• The next two digits are the issue code (the stock within the company, where the primary stock is usually ‘10’).
• The final digit is a check code (read more on Investopedia).

Different databases use CUSIP codes of different lengths. For example, in Eventus, you must provide 8 or 9-digit versions. Also, there maybe need to link two databases that use different lengths. In both cases, it is necessary to convert to a shorter or longer length. You can get a longer CUSIP code by using the WRDS CUSIP Converter; you can make a shorted one by trimming digits from the right.

1.2 Lost “leading zeroes” or accidental scientific notation

What went wrong with some of these CUSIP codes?

Given the format CUSIP codes may come as; a mixture of numbers letters (such as Dr Pepper ‘26138E109’) or a leading zero (such as Apple Inc ‘037833100’). Inputting these into Excel could lead to it been converted to 2.61E+113 or automatic removal of the leading zero. It is therefore important that care is taken when opening a .csv file in Excel.

^back to content

2. How to prevent these problems

2.1 Using Excel’s Text Import Wizard

Workaround

• Launch Excel, create a new workbook.
• Click on ‘Data’ > ‘Get External Data’ > ‘From Text’ > select .csv file of interest > ‘Next’.
• Change delimiter to comma. Click ‘Next’.
• Hold the shift button and select all CUSIP related columns, change the column data format from ‘General’ to ‘Text’.
• Click on ‘Finish’ and save the workbook.

2.2 Import via SPSS

Using SPSS

Step 1: Import to SPSS

• Launch SPSS Statistics.
• Click on ‘File’ > ‘Open’ > ‘DATA’.
• Go to file location, ensure file type is Excel.
• Open the dataset, click on variable view, change CUSIP columns ‘Type’ to ‘String’.

Step 2: Export to Excel

• Click on ‘File’ > ‘Save As’.
• Change ‘Save as type’ to ‘Excel’.

^back to content