0

I am using vba ( & internetexplorer object) to convert CSUIP to ISIN (these are financial security unique identification codes) from the following website http://www.isincodes.net/convert-cusip-to-isin.php.

I have so far created a vba code (in following order) that: 1. manipulates the US/CA dropdown box 2. inputs my CUSIP into the inputbox 3. clicks the "Covert"Button

Sub cusip_is2()

'declare variables
Set objie = CreateObject("internetExplorer.Application")

objie.Visible = True

'go to website
objie.navigate ("http://www.isincodes.net/convert-cusip-to-isin.php")
Do
DoEvents
Loop Until objie.readyState = 4

'input the CA in the country ID step1
objie.document.getelementbyID("country").Value = "CA"

'input 912796HQ5 in text box as desired CUSIP to convert
objie.document.getelementbyID("appendedInputButton").Value = "912796HQ5"


'press the button to convert
Set ElementCol = objie.document.getElementsByTagName("button")
 For Each btnInput In ElementCol
    btnInput.Click
 Next btnInput

'wait until the pages loads (not sure if I need this)
Do
DoEvents
Loop Until objie.readyState = 4

'added extra few seconds incase HMTL document is updating
Application.Wait Now + TimeSerial(0, 0, 1)

'created userform to look at the data to see if I can find the converted CUSIP - WHICH I CANNOT (HELPPPPP!!!!)
Userfrom_Web_Code.Textbox_Webform.Text = objie.body.innerText
Userfrom_Web_Code.Show


'clean up and try again for the next failure LOL
objie.Quit
Set objie = Nothing
End Sub

From my research I know that I keep getting the source code but I want the DOM, this is where I am having trouble. Please see EXTRACT of source code on the website once I have inputted "CA" for country and the CUSIP "912796HQ5", and note that the id tag with results contains nothing.

 <p>Enter a CUSIP and a correct ISIN will be calculated for you, if possible:</p>
                    <form class="form-inline">
                        <div class="input-append">
                            <select id="country" style="width:60px;">
                                <option value="CA">CA</option>
                                <option value="US" selected>US</option>
                            </select>
                            <input type="text" maxlength="9" class="span3" id="appendedInputButton">
                            <button type="button" class="btn">Convert</button>
                        </div>
                    </form>

                    <div id="results"></div>

                    <br><br>
                                    </div>

                <div class="span4">
                    <h4>What is a CUSIP?</h4>
                    <p>A CUSIP is a 9-character alphanumeric code which identifies a North American financial security.</p>
                    <p>
                        A CUSIP consists of three parts:<br>
                        &#8226; A six-character issuer code<br>
                        &#8226; A two-character issue number<br>
                        &#8226; A single check digit.<br><br>
                        For example: 780259206 (Royal Dutch Shell-A)
                    </p>

conversely when I check the inspect element which takes me to the "DOM" then I can see this under id results.

<div id="results" style="display: block;"><br>
<b>Strict Standards</b>:  Non-static method Validate_CUSIP::validate() should not be called statically in <b>/home/isincode/public_html/action/c.php</b> on line <b>15</b><br>
<p class="text-success"><strong>Correct ISIN: CA912796HQ58</strong></p></div>

Can anyone please help me solve this problem in vba.

Note that I know that you can create an algorithm yourself in vba/excel to do the conversion of CUSIP to ISIN however I want to do this project in VBA to learn parsing. I am also very new to coding and have had limited exposure to please be kind and explain everything you suggest.

1 Answer 1

1

Please see if the following edit will suit you:

....
....
'added extra few seconds incase HMTL document is updated
Application.Wait Now + TimeSerial(0, 0, 2)

Dim ISIN As String
ISIN = Right(objie.document.getelementbyID("results").innerText,12) 'the 12 rightmost characters
Debug.Print "ISIN you are looking for is: " & ISIN
....

Edit the code to do something usefull with the ISIN-string

Sign up to request clarification or add additional context in comments.

1 Comment

Hey Carmelid, seems to be working on my home computer, will try and on my work one which is the one I had trouble with. Can't believe it was just one second off LOL. Massive thanks for your time is also in order.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.