Wrox Programmer Forums Function to convert CUSIP code to ISIN code ... cusipconv()
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

March 6th, 2013, 02:22 PM
 amkh8857 Registered User Join Date: Mar 2013 Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts
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

March 28th, 2013, 02:37 AM
 Shasur Friend of Wrox Join Date: Sep 2005 Posts: 812 Thanks: 1 Thanked 53 Times in 49 Posts

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

 Similar Threads Thread Thread Starter Forum Replies Last Post How To convert code aliusmankhan VB How-To 1 July 21st, 2008 12:56 PM Convert to C# Code sanjaymannnet ASP.NET 1.0 and 1.1 Basics 1 September 16th, 2006 10:47 AM Convert C++ Code to C# skin C# 2 May 30th, 2006 05:34 AM Convert VB to C# code skdp VS.NET 2002/2003 2 March 1st, 2004 11:45 PM