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

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