How do I convert 9-digit CUSIP codes into ISIN codes, preferably in Excel?
Best Answer
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) = 45Take the 10s modulus of the sum:
45 mod 10 = 5Subtract from 10:
10 - 5 = 5Take 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 = 5So the ISIN check digit is five.
Source: http://en.wikipedia.org/wiki/International_Securities_Identification_Number
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.
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 2008Dim x As Integer, lTotal As Integer, stTemp As String, stNum As Stringst = UCase(Trim(st))If Len(st) <> 11 Or Mid(st, 1, 1) < "A" Or Mid(st, 1, 1) > "Z" Then Exit FunctionstNum = ""For x = 1 To 11stTemp = Mid(st, x, 1)If stTemp >= "0" And stTemp <= "9" ThenstNum = stNum & stTempElseIf stTemp >= "A" And stTemp <= "Z" ThenstNum = stNum & CStr(Asc(stTemp) - 55)ElseExit FunctionEnd IfNext xstNum = StrReverse(stNum)lTotal = 0For x = 1 To Len(stNum)lTotal = lTotal + CInt(Mid(stNum, x, 1))If x Mod 2 = 1 ThenlTotal = lTotal + CInt(Mid(stNum, x, 1))If CInt(Mid(stNum, x, 1)) > 4 ThenlTotal = lTotal - 9End IfEnd IfNext xCUSIPCONV = (10 - (lTotal Mod 10)) Mod 10End FunctionNot my code, I found it Here
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
- ALT-IALT-M opens a fresh module
- 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 savethe file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- 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:
