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]

Below is a function for generating the Check digit and an example Sub that shows how the function could be called to generate Check Digits for the Cells in Range A1:A10.

Option Compare Text

Function Cusip_Check_Digit(cusip As String) As String
'---Input: an 8-character CUSIP
'---Output: the check digit for that CUSIP
    Dim sum&, i&, v&, p&, c$
    
    If Len(cusip) <> 8 Then
        Cusip_Check_Digit = "Error: cusip is not 8 characters"
        Exit Function
    End If
    For i = 1 To 8 Step 1
        c = Mid(cusip, i, 1)
        If IsNumeric(c) Then
            v = c
        ElseIf c Like "[a-Z]" Then
            v = 9 + Cells(1, c).Column
        Else
            Select Case c
                Case "*": v = 36
                Case "@": v = 37
                Case "#": v = 38
                Case Else
                    Cusip_Check_Digit = _
                      "Error: " & c & " is an invalid character"
                    Exit Function
            End Select
        End If
        If i Mod 2 = 0 Then
            v = v * 2
        End If
        sum = sum + Int(v / 10) + v Mod 10
    Next i
    Cusip_Check_Digit = (10 - (sum Mod 10)) Mod 10
End Function

Calling code example...

Sub Test_Cusip()
    Dim Cell As Range
    For Each Cell In [A1:A10]
       Cell.Offset(0, 1) = Cusip_Check_Digit(Cell.Value)
    Next Cell
End Sub

 

[kekstrom]

Thank you so much, the macro works AWESOME!

I really appreciate the help, this saves me a great deal of time each day.

The number of CUSIPS I have to check each time greatly varies... I am going to paste a list of 8 digit cusips into column A, is there a way I can make the macro run for however many cusips I paste instead of limiting it to 10?

I changed the value to 100, but when I am only doing 50 or so, I don't really want to wait for the last 50 to generate error messages. Anything I can change? THANKS


Sorry, 1 more thing. Sometime I paste in cusips that already have 9 digits and I get the error for 'not 8 digits', is there a way I can simply make it display the last digit in column B if the cusip is already 9 digits? THANKS

 

[Jerry Sullivan]

It's nice to hear this will save you some time each day.

Below is revised code for you to try. It moves the number of characters checking out of the function.
I thought it might be useful to double check the existing Check digit and flag if it is different than this functions calculates.

Option Compare Text
Function Cusip_Check_Digit(cusip As String) As String
'---Input: an 8-character CUSIP
'---Output: the check digit for that CUSIP
    Dim sum&, i&, v&, p&, c$
 
    For i = 1 To 8 Step 1
        c = Mid(cusip, i, 1)
        If IsNumeric(c) Then
            v = c
        ElseIf c Like "[a-Z]" Then
            v = 9 + Cells(1, c).Column
        Else
            Select Case c
                Case "*": v = 36
                Case "@": v = 37
                Case "#": v = 38
                Case Else
                    Cusip_Check_Digit = _
                      "Error: " & c & " is an invalid character"
                    Exit Function
            End Select
        End If
        If i Mod 2 = 0 Then
            v = v * 2
        End If
        sum = sum + Int(v / 10) + v Mod 10
    Next i
    Cusip_Check_Digit = (10 - (sum Mod 10)) Mod 10
End Function

Sub Test_Cusip()
    Dim lRow&, Cell As Range, sInput$, sRet$
    Application.ScreenUpdating = False
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each Cell In Range("A1", Cells(lRow, "A"))
        sInput = Cell.Value
        Select Case Len(sInput)
            Case 8
                sRet = Cusip_Check_Digit(sInput)
            Case 9
                sRet = Cusip_Check_Digit(Left(sInput, 8))
 
                If sRet <> Right(sInput, 1) Then
                    sRet = "Error: Existing Check Digit is incorrect"
                End If
            Case Else
                sRet = "Error: cusip is not 8 or 9 characters"
        End Select
        Cell.Offset(0, 1) = sRet
    Next Cell
End Sub

 

[kekstrom]

Thanks for the response, the only problem now is that CUSIP's that are entered that are already 9 digits are still producing the 9th digit in the B column.

The problem with this is that I am using a Concatenate formula to add column a to column b, and the cusips that already started with 9 digits are now 10 digits.

Easy fix?

 

[Jerry Sullivan]

The problem with this is that I am using a Concatenate formula to add column a to column b, and the cusips that already started with 9 digits are now 10 digits.

Easy fix?

Yes, just modify this part....

Case 9
    sRet = Cusip_Check_Digit(Left(sInput, 8))
    If sRet <> Right(sInput, 1) Then
        sRet = "Error: Existing Check Digit is incorrect"
    Else
        sRet = ""
    End If

 

[kekstrom]

Perfect! Thanks, much appreciated!

Any chance you could take a shot at this?

http://www.mrexcel.com/forum/showthread.php?t=568345

Have a good one!

 

[rub41]

Hi Jerry and others,

VB newbie here...what does the "&" and the "$" in dim do? where is p& being used?

It's nice to hear this will save you some time each day.

Below is revised code for you to try. It moves the number of characters checking out of the function.
I thought it might be useful to double check the existing Check digit and flag if it is different than this functions calculates.

Option Compare Text
 Function Cusip_Check_Digit(cusip As String) As String
'---Input: an 8-character CUSIP
'---Output: the check digit for that CUSIP
    Dim sum&, i&, v&, p&, c$
 
    For i = 1 To 8 Step 1
         c = Mid(cusip, i, 1)
        If IsNumeric(c) Then
            v = c
        ElseIf c Like "[a-Z]" Then
            v = 9 + Cells(1, c).Column
        Else
            Select Case c
                Case "*": v = 36
                Case "@": v = 37
                Case "#": v = 38
                Case Else
                    Cusip_Check_Digit = _
                      "Error: " & c & " is an invalid character"
                    Exit Function
            End Select
        End If
        If i Mod 2 = 0 Then
            v = v * 2
        End If
        sum = sum + Int(v / 10) + v Mod 10
    Next i
    Cusip_Check_Digit = (10 - (sum Mod 10)) Mod 10
End Function

Sub Test_Cusip()
     Dim lRow&, Cell As Range, sInput$, sRet$
    Application.ScreenUpdating = False
 
     lRow = Cells(Rows.Count, "A").End(xlUp).Row
     For Each Cell In Range("A1", Cells(lRow, "A"))
         sInput = Cell.Value
         Select Case Len(sInput)
            Case 8
                 sRet = Cusip_Check_Digit(sInput)
            Case 9
                 sRet = Cusip_Check_Digit(Left(sInput, 8))
 
                 If sRet <> Right(sInput, 1) Then
                     sRet = "Error: Existing Check Digit is incorrect"
                End If
            Case Else
                 sRet = "Error: cusip is not 8 or 9 characters"
        End Select
         Cell.Offset(0, 1) = sRet
    Next Cell
End Sub

 

[Jerry Sullivan]

Hi Jerry and others,

VB newbie here...what does the "&" and the "$" in dim do? where is p& being used?

Hi rub41, Those are Identifier Type Characters which are another way to declare data types.

This article describes their use...
Type Characters (Visual Basic)

I now prefer the clarity of writing out the data types instead of using these symbols.

It doesn't appear that the variable p& is used by the code. I presume that I used that variable while developing the code and at some point eliminated it's use prior to posting, but did not remove its declaration.

 

[ryantkelly2]

Jerry - will you kindly explain what this function is doing ?
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

I follow, in general the check digit algorithm but I'm not sure how the calculation is actually working in the VBA code here. If we look at this cusip for GOOG, 02079K107, how exactly is the code generating that?

I see the macro is effective, but I'm trying to recreate in SQL or just manually in excel via regular calculation and not using a macro.

Please advise.

Thanks,
****** id="cke_pastebin" style="position: absolute; top: 131.634px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 779"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: inherit; font-style: inherit; font-family: Arial, Verdana, sans-serif;">[TR]
[TD="align: left"]02079K107[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 779"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: inherit; font-style: inherit; font-family: Arial, Verdana, sans-serif;">[TR]
[TD="align: left"]02079K107[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 779"]
<tbody style="margin: 0px; padding: 0px; border: 0px; outline: 0px; font-weight: inherit; font-style: inherit; font-family: Arial, Verdana, sans-serif;">[TR]
[TD="align: left"]02079K107[/TD]
[/TR]
</tbody>[/TABLE]
</body>