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