Wrox Programmer Forums Function to convert CUSIP code to ISIN code ... cusipconv()
 Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersâ€™ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
March 6th, 2013, 02:22 PM
Registered User
 Points: 5, Level: 1
 Activity: 0%

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
Friend of Wrox
 Points: 3,060, Level: 23
 Activity: 0%

Join Date: Sep 2005
Location: , , .
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)

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off Forum Rules

 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

All times are GMT -4. The time now is 10:00 AM.