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

How to convert from ISIN or Datastream/DS code to CUSIP code


This post will help you to take a list of US or Canadian companies from Datastream, Workspace, SDC Platinum, Bloomberg or Capital IQ, convert the codes, then use them in CRSP, Compustat or Eventus. If you have a list of ISIN codes or Datastream/DS codes, you can use Datastream to convert them to 8 or 9-digit CUSIP codes.

Graphic to show conversion options

  • For example, if you are using the Company Screening in Capital IQ, make sure to add Primary ISIN as a Display Column.
  • For example, if you are using the Deals Screener in Workspace, make sure to add a column for the Datastream code of the company (target or acquiror). Similar in SDC Platinum.
  • If you have already used Datastream to create a list of companies, you will likely have the more friendly 'mnemonic' code to identify the companies. This is not the same as the 6-character Datastream code but it will work just as well here.
  • Note, you will probably not achieve 100% coverage! 

Steps to add a conversion formula:

  1. Using a computer with Workspace and Datastream, make sure you are logged onto the Datastream Excel add-in.
  2. Open your spreadsheet with a column of Datastream codes or mnemonics. 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: =MID(DSGRID(E2, "LOC"), 2, 9)
    • If you want 8-digit CUSIP codes instead, replace the 9 with an 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 after you log out of Datastream.

If we break the expression down:

  • This is a Datastream static request using the DSGRID formula. The two parameters of DSGRID are the series (ISIN or Datastream code) and the static variable 'LOC' which is the 'Local Code', or a company identifier using the standard code for the country where the company is registered.
  • For the United States, Local Code is the letter 'U' followed by the 9-digit CUSIP code. (It is the letter 'Q' for Canada.)
  • We then use the MID formula to extract the text from the second character to the end. Use '9' if you want 9-digit codes or '8' if you want 8-digit codes. Eventus requires 8-digit CUSIP codes.
  • 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.

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