p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Excel VBA (http://p2p.wrox.com/forumdisplay.php?f=79)
-   -   Function to convert CUSIP code to ISIN code ... cusipconv() (http://p2p.wrox.com/showthread.php?t=89498)

 amkh8857 March 6th, 2013 02:22 PM

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

 Shasur March 28th, 2013 02:37 AM

Hi

Please check the following code with the exact values. This is why the code exits:

Code:

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

 All times are GMT -4. The time now is 09:13 PM.