Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert code name | Excel Programming | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
Convert VBA code to VB.NET | Excel Programming |