Excel 2007 : Converting CUSIP to ISIN's using Excel

[lumbous]

Hi,

I need help converting CUSIP numbers to ISIN numbers using Excel. According to wikipedia here is how: "The procedure for calculating ISIN check digits is similar to the "Modulus 10 Double Add Double" technique used in CUSIPs. To calculate the check digit, first convert any letters to numbers by adding their ordinal position in the alphabet to 9, such that A = 10 and M = 22. Starting with the right most digit, every other digit is multiplied by two. (For CUSIP check digits, these two steps are reversed.) The resulting string of digits (numbers greater than 9 becoming two separate digits) are added up. Subtract this sum from the smallest number ending with zero that is greater than or equal to it: this gives the check digit which is also known as the ten's complement of the sum modulo 10. That is, the resulting sum, including the check-digit, is a multiple of 10."

An example:TREASURY CORP VICTORIA 5 3/4% 2005-2016: ISIN AU0000XVGZA3

Convert any letters to numbers:

A = 10, G = 16, U = 30, V = 31, X = 33, Z = 35. AU0000XVGZA -> 103000003331163510.

Collect odd and even characters:

103000003331163510 = (1, 3, 0, 0, 3, 3, 1, 3, 1), (0, 0, 0, 0, 3, 1, 6, 5, 0)

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

(0, 0, 0, 0, 6, 2, 12, 10, 0)

Add up the individual digits:

(1 + 3 + 0 + 0 + 3 + 3 + 1 + 3 + 1) + (0 + 0 + 0 + 0 + 6 + 2 + (1 + 2) + (1 + 0) + 0) = 27

Take the 10s modulus of the sum:

27 mod 10 = 7

Subtract from 10:

10 - 7 = 3

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).

3 mod 10 = 3

So the ISIN check digit is three.

I have attached a worksheet with the CUSIP numbers and their correct ISIN's, but I do not know how to do it formulaically on Excel.

Thanks for the help.

[Richard Buttrey]

Hi,

Try the following user defined function. i.e. with the CUSIP code in A1 enter =CUSIPCONV(A1)

Please Login or Register  to view this content.

[Staveley]

This works well. Just what I needed

[Sheilaf]

When i use the function -- it just results in 0. ANy idea what I could be doing wrong?

[arlu1201]

Sheilaf,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.