Last Updated: 28 Oct 2024 Views: 719 (After reading, tell us below if this answer was helpful)

How to convert from 6-digit to 8-digit CUSIP codes


This post will help you to take a list of US or Canadian companies from Refinitiv, SDC Platinum or Capital IQ, convert the codes, then use them in CRSP, Compustat or Eventus. If you have a list of 6-digit CUSIP codes, you can use Excel to convert them to 8-digit CUSIP codes.

Convert CUSIP6 to CUSIP8 graphic

  • For example, if you are using the Company Screening in Capital IQ, make sure to add Company CUSIP as a Display Column.
  • For example, if you are using the Deals Screener in Eikon, make sure to add a column for the CUSIP code of the company (target or acquiror). Similar in SDC Platinum.
  • 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 6-digit CUSIP codes. Let's say this is column E, with a header in row 1 and data starting in row 2.
  3. In cell F2, paste the following formula: =E2 & "10"
  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.
  8. If any of the new codes fail, try an alternative formula: =E2 & "20"

If we break the expression down:

  • This is a concatenation of the 6-digit code (company identifier) followed by "10".  
  • For most companies, "10" represents their common stock.
  • If "10" does not work, try "20" instead, then "30", and so on.
  • You may need to look up some companies by name instead.

Screenshot of formula in Excel

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

See also: 

FAQ Actions