VBA: Get data from HTML

Question

The objetive is to extract the XBT/USD value from this website: https://www.bitmex.com/ I got this code from another question and tried to edit it for the need but it returns the QTUMU17 value.

Option Explicit

Sub BitMEX_BTC_USD()

     'Open website
        Dim IE As New SHDocVw.InternetExplorer
        IE.Visible = True
        IE.Navigate "https://www.bitmex.com/"
        Do While IE.ReadyState <> READYSTATE_COMPLETE
        Loop

    'Extract USD value
        Dim kfc As String
        Dim oHTML_Element As IHTMLElement

            For Each oHTML_Element In IE.Document.getElementsByTagName("span")
                If oHTML_Element.className = "PlusTick" Then
                    kfc = oHTML_Element.innerText
                End If
            Next

    'Value
        Debug.Print kfc

    End Sub

HTML code according to inspect element:

<i class="price">2765.3</i>

Thanks in advance for your valuable help.

Tehscript · Accepted Answer · 2017-07-31 20:36:15Z

2

You should be focusing on class names here. Each currency is under ticker-item class. XBT/USD is the first currency so you should go for (0):

IE.document.getElementsByClassName("ticker-item")(0).innerText

Since you want to extract just the price, you need to dig into that. price element is the first occurrence again so you need to get the price with (0):

IE.document.getElementsByClassName("ticker-item")(0).getElementsByClassName("price")(0).innerText

answered Jul 31, 2017 at 20:36

Tehscript

2,5562 gold badges15 silver badges24 bronze badges

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

Comments

APW · Accepted Answer · 2017-07-31 20:35:31Z

1

I see a couple of problems. Your For each statement is searching through every classname that is equal "PlusTick" and the last statement that meets this criteria on the website happens to be the QTUMU17 value. The other problem I see is that if you search by the class name PlusTick you will only pick up the value if it was a "PlusTick" but from looking at the website it appears there will be instances in which your XBT/USD could be a minus tick. I'm not very experienced in html but I hope this helps you further understand your current output.

answered Jul 31, 2017 at 20:35

APW

4712 bronze badges

Collectives™ on Stack Overflow

VBA: Get data from HTML

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related