Function to convert CUSIP code to ISIN code cusipconv()
 

I have the following code (Function: cusipconv) to convert CUSIP code to ISIN. Some people used it before and reported satisfactory results. In my case the function turns out 0 and not ISIN code. Can someone kindly look into the function to see what is the problem. I provide here with CUSIP code for 5 companies that you can use to test the function.

CUSIP
00371F206
04543P100
00081T108
002564102
000957100

Many thanks.



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

Function to convert CUSIP code to ISIN code cusipconv()
 

On Wed, 6 Mar 2013 18:01:45 +0000, Amir Kheirollah wrote:


I have the following code (Function: cusipconv) to convert CUSIP code to
ISIN. Some people used it before and reported satisfactory results. In
my case the function turns out 0 and not ISIN code. Can someone kindly
look into the function to see what is the problem. I provide here with
CUSIP code for 5 companies that you can use to test the function.

CUSIP
00371F206
04543P100
00081T108
002564102
000957100

Many thanks.



If Len(st) < 11 Or Mid(st, 1, 1) < "A" Or Mid(st, 1, 1) "Z" Then Exit Function

Since none of your codes have 11 characters, the function will exit at this line. Even if they had 11 characters, they would all fail the next test since they all start with "0" and "0" < "A"