CUSIP Check digit generator

[kekstrom]

The text below is from wikipedia, I wasn't able to convert that into a macro so I am hoping to get some help.

What I want to be able to do is paste in a list of 8 digit cusips into a column, and then have a macro/formula that will generate the 9th digit and place that digit in the column next to it.


Description

The first six characters are known as the base (or CUSIP-6), and uniquely identify the issuer. Issuer codes are assigned alphabetically from a series that includes deliberate built-in "gaps" for future expansion. The 7th and 8th digit identify the exact issue. The 9th digit is an automatically generated checksum (some clearing bodies ignore or truncate the last digit). The last three characters of the issuer code can be letters, in order to provide more room for expansion.
Issuer numbers 990 to 999 and 99A to 99Z in each group of 1,000 numbers are reserved for internal use. This permits a user to assign an issuer number to any issuer which might be relevant to his holdings but which does not qualify for coverage under the CUSIP numbering system. Other issuer numbers (990000 to 999999 and 99000A to 99999Z) are also reserved for the user so that they may be assigned to non-security assets or to number miscellaneous internal assets.
The 7th and 8th digit identify the exact issue, the format being dependent on the type of security. In general, numbers are used for equities and letters are used for fixed income. For commercial paper the first issue character is generated by taking the letter code of the maturity month, the second issue character is the day of the maturity date, with letters used for numbers over 9. The first security issued by any particular issuer is numbered "10". Newer issues are numbered by adding ten to the last used number up to 80, at which point the next issue is "88" and then goes down by tens. The issue number "01" is used to label all options on equities from that issuer.
Fixed income issues are labeled using a similar fashion, but due to there being so many of them they use letters instead of digits. The first issue is labeled "AA", the next "A2", then "2A" and onto "A3". To avoid confusion, the letters I and O are not used since they might be mistaken for the digits 1 and 0.
The 9th digit is an automatically generated check digit using the "Modulus 10 Double Add Double" technique.[2] To calculate the check digit every second digit is multiplied by two. Letters are converted to numbers based on their ordinal position in the alphabet.
[edit]Check digit pseudocode

algorithm Cusip-Check-Digit(cusip) is
Input: an 8-character CUSIP
Output: the check digit for that CUSIP
sum := 0
for 1 ≤ i ≤ 8 do
c := the ith character of cusip
if c is a digit then
v := numeric value of the digit c
else if c is a letter then
p := ordinal position of c in the alphabet (A=1, B=2...)
v := p + 9
else if c = "*" then
v := 36
else if c = "@" then
v := 37
else if c = "#" then
v := 38
end if
if i is even then
v := v × 2
end if
sum := sum + v div 10 + v mod 10
repeat
return (10 - (sum mod 10)) mod 10
end function

 

[Jerry Sullivan]

Hi Ryan,

Is it just the part of the algorithm that you pasted that would you like explained?

 

[ryantkelly2]

Apologies - this is not the algorithm ****** id="cke_pastebin" style="position: absolute; top: 131.634px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">, copy and paste error.

 

[ryantkelly2]

If you can explain in english on how this works that would be great, I understand pretty much up until the algorithm from vba that I put in the original thread but beyond that, it's not clear on the mod function. I have to do this in excel and sql but it would be very useful to see how the calculation actually works in the raw format - for instance if we took the sample cusip and did the algorithm on it manually or even without the vba, just in a spreadsheet.

 

[Jerry Sullivan]

Ryan, Here's an attempt to explain the parts you noted from the algorithm excerpted from Wikipedia in the original post.

if i is even then
v := v × 2
end if
sum := sum + v div 10 + v mod 10
repeat
return (10 - (sum mod 10)) mod 10

if i is even then
v := v × 2
end if

Explanation: The algorithm works by stepping through each of the 8 characters and processing each one. The variable "i" is used to store the position of the character being processed. For a CUSIP of 912828NB, i=1 represents the first character, "9", i = 8 represents the eight character "B". For even values of "i" (positions 2,4,6 and 8), the value of v is multiplied by 2.


sum := sum + v div 10 + v mod 10
repeat

Explanation: The variable "sum" is used to store a running sum total of the values resulting from the processing of each character. Before each "repeat" loop, the value of sum is updated by adding the result of the expression "v div 10 + v mod 10" for that character. Taking that in two parts:
"v div 10" divides v by 10 then rounds down to the integer. So if v=38 then "v div 10" = 3.8 rounded down to "3".
"v mod 10" takes the modulus or remainder after v is divided by 10. So if v=38 then "v mod 10" = 8. For our base 10 number system, v mod 10 will always be the last digit of v.
If v=38 then "v div 10 + v mod 10" = 3+8=11.

return (10 - (sum mod 10)) mod 10

Explanation: After processing all 8 characters the resulting value stored in "sum" needs to be transformed into a single digit that can be used as the ninth checksum character of the CUSIP. This is done using the expression: "(10 - (sum mod 10)) mod 10". As noted above, "sum mod 10" evaluates to the last digit in sum. This value is subtracted from 10 and the last digit of that subtraction is finally returned as the checksum. Some examples
if sum=383 then the checksum digit returned is (10-3) mod 10 = 7
if sum=380 then the checksum digit returned is (10-0) mod 10 = 0

I hope this helps. Please let me know if any of that is unclear.