how to get data from webpage using getElementsByClassName in excel vba

Question

I am trying to get data from https://in.finance.yahoo.com/quotes/ADANIENT.BO in excel vba using following code but it doesn't seem to work.

Private Sub mysub()
'Use References> Microsoft Internet Controls and Microsoft HTML Object Library

Dim IE As InternetExplorer, doc As HTMLDocument, quote As String
Dim URL As String
Set IE = CreateObject("internetExplorer.application")

URL = "https://in.finance.yahoo.com/quotes/ADANIENT.BO"
IE.navigate (URL)
Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Set doc = IE.document

      quote = doc.getElementById("JB3wv").getElementsByClassName("-fsw9 _16zJc")(0).getElementsByClassName("_3Bucv")(0).innerText
      'quote = doc.getElementById("JB3wv").getElementsByTagName("table")(0).getElementsByTagName("tr")(0).getElementsByTagName("td")(1).getElementsByClassName("_3Bucv").innerText
Debug.Print quote
IE.Application.Quit
End Sub

you can goto the URL https://in.finance.yahoo.com/quotes/ADANIENT.BO and check inspect element for Last price

    <div class="JB3wv"><table class="-fsw9 _16zJc" data-test="contentTable">      <tbody><tr data-index="0" data-key="ADANIENT.BO" data-test-key="ADANIENT.BO" class=""><td class="_2VvFs"><span><label class="_120DQ _2z7ql"><input name="rowToggle" value="on" data-rapid_p="14" data-v9y="1" type="checkbox"><i></i></label><a class="_61PYt " title="ADANIENT.BO" href="/quote/ADANIENT.BO" data-rapid_p="15" data-v9y="1">ADANIENT.BO</a></span></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">121.60</span></td><td style="font-weight: 700;"><span class="_3Bucv _2ZN-S" style="font-weight: 700;">+1.50</span></td><td style="font-weight: 700;"><span class="_3Bucv _2ZN-S" style="font-weight: 700;">+1.25%</span></td><td style="text-align: left;">INR</td><td><span>3:56 PM IST</span></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">1.98m</span></td><td>-</td><td>1.45m</td><td style="text-align: left;"><canvas style="width: 140px; height: 23px;" width="140" height="23"></canvas></td><td style="text-align: left;"><canvas style="width: 140px; height: 23px;" width="140" height="23"></canvas></td><td style="text-align: left;"><canvas style="width: 70px; height: 25px;" width="70" height="25"></canvas></td><td style="font-weight: 700;"><span class="_3Bucv" style="font-weight: 700;">0</span></td></tr></tbody></table></div>

Answer 1

Seems after recent changes on finance.yahoo.com the correct URL is

https://finance.yahoo.com/quote/ADANIENT.BO

To retrieve the value you need from the webpage DOM via IE automation you can use the following code:

Option Explicit

Sub GetLastPriceIE()

    Dim sURL As String
    Dim sHeader As String
    Dim sQuote As String

    sURL = "https://in.finance.yahoo.com/quote/ADANIENT.BO"
    ' Open IE
    With CreateObject("InternetExplorer.Application")
        ' Navigate URL
        .Visible = True
        .Navigate sURL
        ' Wait IE
        Do While .readyState < 3 Or .Busy
            DoEvents
        Loop
        ' Wait document
        Do While .document.readyState <> "complete"
            DoEvents
        Loop
        ' Wait target element
        Do While IsNull(.document.getElementById("quote-header-info"))
            DoEvents
        Loop
        ' Retrieve quote header info inner text
        sHeader = .document.getElementById("quote-header-info").innerText
        .Quit
    End With
    ' Create RegEx
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        ' Set pattern to match values like "121.55+1.15 (+0.96 %)"
        .Pattern = "^\s*(\d+\.\d+)\s*[+-]\d+\.\d+\s*\(\s*[\+-]\d+\.\d+\s*%\s*\)\s*$"
        With .Execute(sHeader)
            If .Count = 1 Then
                sQuote = .Item(0).SubMatches(0)
            Else
                sQuote = "N/A"
            End If
        End With
    End With
    Debug.Print sQuote

End Sub

You can get the last price via API by the URL like https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=ADANIENT.BO, here is the example code:

Option Explicit

Sub GetLastPricesXHR()

    Dim aSymbols
    Dim aPrices
    Dim i As Long

    ' Put symbols into array
    aSymbols = Array("ADANIENT.BO", "NTPC.BO", "BHEL.BO")
    ' Retrieve prices
    aPrices = ParseLastPricesXHR(aSymbols)
    ' Output
    For i = 0 To UBound(aSymbols)
        Debug.Print aSymbols(i), aPrices(i)
    Next

End Sub

Function ParseLastPricesXHR(aSymbols)

    Dim sResp As String
    Dim aChunks
    Dim i As Long
    Dim sChunk As String
    Dim aPrices
    Dim sPrice As String

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&fields=regularMarketPrice&symbols=" & Join(aSymbols, "%2C"), False
        .Send
        sResp = .ResponseText
    End With
    aChunks = Split(sResp, """regularMarketPrice"":", UBound(aSymbols) + 2)
    If UBound(aChunks) <> UBound(aSymbols) + 1 Then
        MsgBox "Wrong response"
        End
    End If
    ReDim aPrices(UBound(aSymbols))
    For i = 0 To UBound(aSymbols)
        sChunk = aChunks(i + 1)
        sPrice = Split(sChunk, ",", 2)(0)
        aPrices(i) = sPrice
    Next
    ParseLastPricesXHR = aPrices

End Function

Short description. Navigate the URL https://in.finance.yahoo.com/quotes/ADANIENT.BO in Chrome. Open Developer Tools window (F12), Network tab, and examine logged XHRs after the page loaded. You can find one of XHRs containing the relevant data on Previev / Response tabs (searched by the value 121.6 from the page https://in.finance.yahoo.com/quotes/ADANIENT.BO/view/v1):

Take a look at Headers tab: you can find here the URL in General parameters, and detailed Query String Parameters:

Some of parameters seems to be optional, so I omitted them in the above code. Notwithstanding the response actually is JSON, parse using Split is simple and efficient for one value.

For retrieving more data from Yahoo Finance via API and JSON parsing - check this answer, via parsing HTML content - this answer.

Answer 2

Sub getLastPrice()

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True

ie.navigate "https://in.finance.yahoo.com/quotes/ADANIENT.BO"

Do While ie.busy Or ie.readystate <> 4
DoEvents
Loop

Application.Wait Now + TimeValue("00:00:02") '~~> give another 2 seconds

Set tbls = ie.document.getElementsByTagName("table")

Dim k As Integer
k = 0

For Each tbl In tbls '~~> looping in order to find the exact table

'~~> Be aware that the table's class value has one white space at the end.

If tbl.getAttribute("class") = "_2VeNv " Then Set tbl = tbls(k): Exit For

k = k + 1

Next

last_Price = tbl.Rows(1).Cells(1).Children(0).innertext 

debug.print last_Price '~~> result value = 195.10

'Explain ~~> table(k) second row(=second tr) second cell(=second td) first tag(span) innerText

End Sub