Function to convert CUSIP code to ISIN code ... cusipconv()
Dear,
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 only ziros and no ISIN code. Can you 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
|
