Convert 9-digit CUSIP codes into ISIN codes

Question

How do I convert 9-digit CUSIP codes into ISIN codes, preferably in Excel?

Solution 1

The CUSIP is entirely contained in the ISIN. The ISIN is then prefixed by 2 letters (in this case, either "US" or "CA" as CUSIP is a North American identifier) and a digit at the end.

To determine the digit at the end:

ISIN US0378331005, expanded from CUSIP 037833100. The main body of the ISIN is the original CUSIP, assigned in the 1970s. The country code "US" has been added on the front, and an additional check digit at the end. The country code indicates the country of issue. The check digit is calculated in this way.

Convert any letters to numbers:

U = 30, S = 28. US037833100 -> 3028037833100.

Collect odd and even characters:

3028037833100 = (3, 2, 0, 7, 3, 1, 0), (0, 8, 3, 8, 3, 0)

Multiply the group containing the rightmost character (which is the FIRST group) by 2:

(6, 4, 0, 14, 6, 2, 0)

Add up the individual digits:

(6 + 4 + 0 + (1 + 4) + 6 + 2 + 0) + (0 + 8 + 3 + 8 + 3 + 0) = 45

Take the 10s modulus of the sum:

45 mod 10 = 5

Subtract from 10:

10 - 5 = 5

Take the 10s modulus of the result (this final step is important in the instance where the modulus of the sum is 0, as the resulting check digit would be 10).

5 mod 10 = 5

So the ISIN check digit is five.

_{Source: http://en.wikipedia.org/wiki/International_Securities_Identification_Number}

Solution 2

Although there is a formulation to convert CUSIP to ISIN it may not always give you the correct ISIN for that instrument.

For Example:

CCL has CUSIP 143658300 and if you use the formula its ISIN should be US1436583009 however its ISIN is PA1436583006 (belongs to CVC1) for another reason.If a stock is listed outside the US and has an ISIN already its cusip is not derived from that ISIN so conversion will be wrong.

Solution 3

Here is a User Defined Function (UDF) for that extra digit:

Public Function CUSIPCONV(ByVal st As String) As Long

   'Using ideas from original by Jelle-Jeroen Lamkamp 28 Apr 2008

   Dim x As Integer, lTotal As Integer, stTemp As String, stNum As String
   st = UCase(Trim(st))
   If Len(st) <> 11 Or Mid(st, 1, 1) < "A" Or Mid(st, 1, 1) > "Z" Then Exit Function
   stNum = ""

   For x = 1 To 11
       stTemp = Mid(st, x, 1)
       If stTemp >= "0" And stTemp <= "9" Then
          stNum = stNum & stTemp
       ElseIf stTemp >= "A" And stTemp <= "Z" Then
          stNum = stNum & CStr(Asc(stTemp) - 55)
       Else
          Exit Function
       End If
   Next x

   stNum = StrReverse(stNum)
   lTotal = 0

   For x = 1 To Len(stNum)
       lTotal = lTotal + CInt(Mid(stNum, x, 1))
       If x Mod 2 = 1 Then
          lTotal = lTotal + CInt(Mid(stNum, x, 1))
          If CInt(Mid(stNum, x, 1)) > 4 Then
             lTotal = lTotal - 9
          End If
       End If
   Next x

 CUSIPCONV = (10 - (lTotal Mod 10)) Mod 10

End Function

Not my code, I found it Here

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=CUSIPCONV(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Some samples of usage:

Comments

[ZygD]

You commented on the structure of ISIN. But that does not answer the OP's question - how would one convert CUSIP > ISIN.

[LLaus]

ISIN = "US" + CUSIP + check digit (which is described in the link I posted).

[ZygD]

In Stack Overflow, that's not a good approach if your answer depends on a linked page (the page can change over time). You should post the main idea here and reference the source. On the other hand, you are not encouraged to answer questions where OP does not prove that he tried something before posting the question.

[LLaus]

Ok, check digit logic put in the answer directly

[ZygD]

CUSIP does not have "US" in front.

[Gary's Student]

@ZygD If the US is missing in column A, then the formula in column B must be modified.

[swie1992]

this works perfect, many thanks. I had tried some similar formula before, but it only gave me "0" as output (might have been because I forgot adding "US" though). In any case, this works, thank you so much.

[duckman]

While this post is old, I have a similar issue, but the opposite, i.e. converting ISIN to CUSIP. @LLaus 's answer is 95% correct, except there are occasions where this method does not work, mostly because the securities are listed elsewhere or its origin is overseas. i am just leaving a comment here for future users to be aware of this :D

[Marco Pagliaricci]

How do you apply this formula when there are ALPHABETIC characters into the CUSIP?! E.g. Slack, Inc. CUSIP is "83088V102".

[enharmonic]

@ZygD if you are using US CUSIPS that have no "US" in front, change the line in the VBA script to st = "US" & UCase(Trim(st)) and comment out the line that checks the length If Len(st)... and change the cell formula to ="US" & A2 & CUSIPCONV(A2)