Unable to retrieve value and text with HTML elements

Question

I need help in returning data from a webpage.

Code is as follows:

Option Explicit

Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLButtons As MSHTML.IHTMLElementCollection
Dim HTMLbutton As MSHTML.IHTMLElement
Dim HTMLTables As MSHTML.IHTMLElementCollection

Sub GetData1()
    Dim URL As String
    URL = "https://www.nseindia.com/get-quotes/equity?symbol=POONAWALLA"

    With XMLPage
        .Open "GET", URL, False
        .send
    End With
    HTMLDoc.body.innerHTML = XMLPage.responseText
    
    Dim StockName As Object, StockSymbol As Object, StockLTP As Object, StockPrevClose As Object
    
    Set StockName = HTMLDoc.getElementById("quoteName")
    Set StockSymbol = HTMLDoc.getElementById("equityInfo")
    Set StockLTP = HTMLDoc.getElementById("quoteLtp")
    Set StockPrevClose = HTMLDoc.getElementById("priceInfoTable")

    Debug.Print StockName.innerText         'Need Text1 here as "Poonawalla Fincorp Limited"
    Debug.Print StockSymbol.innerText       'Need Text2 here as "POONAWALLA"
    Debug.Print StockLTP.Value              'Need Value1 here as "324.60"
    Debug.Print StockPrevClose.Value        'Need Value2 here as "321.60"
  
End Sub

I have copied each element from HTML inspect of webpage which is as follows:

Text1 HTML
Element:
<h2 id="quoteName">Poonawalla Fincorp Limited</h2>
Selector:
#quoteName
JSPath:
document.querySelector("#quoteName")
XPath:
//*[@id="quoteName"]
FulXpath:
/html/body/div[9]/div/div/section/div/div/div/div[1]/div/div[1]/div/div/div[1]/h2

Text2 HTML
Element:
<td>POONAWALLA</td>
Selector:
#equityInfo > tbody > tr > td:nth-child(2)
JSPath:
document.querySelector("#equityInfo > tbody > tr > td:nth-child(2)")
XPath:
//*[@id="equityInfo"]/tbody/tr/td[2]
FullXPath:
/html/body/div[9]/div/div/section/div/div/div/div[1]/div/div[2]/div/div/div/div[1]/div/div[2]/div[1]/div/table/tbody/tr/td[2]

Value1 HTML
Element:
<span id="quoteLtp">324.60</span>
Selector:
#quoteLtp
JSPath:
document.querySelector("#quoteLtp")
XPath:
//*[@id="quoteLtp"]
FullXPath:
/html/body/div[9]/div/div/section/div/div/div/div[1]/div/div[2]/div/section/div/div/div/aside[1]/div[1]/span[1]

Value2 HTML
Element:
<td>321.60</td>
Selector:
#priceInfoTable > tbody > tr > td:nth-child(1)
JSPath:
document.querySelector("#priceInfoTable > tbody > tr > td:nth-child(1)")
XPath:
//*[@id="priceInfoTable"]/tbody/tr/td[1]
FullXPath:
/html/body/div[9]/div/div/section/div/div/div/div[1]/div/div[2]/div/section/div/div/div/aside[2]/div/div/table/tbody/tr/td[1]

Kindly help me to return the 2 values and 2 texts using Excel VBA.

Answer 1

The content of that site is dynamic and generated via an external url https://www.nseindia.com/api/quote-equity?symbol=POONAWALLA. When you issue an xmlhttp request using that url, it will produce a json response. You can opt for a VBA JSON converter to fetch the required fields from JSON. However, a quick and dirty solution would be something like the following.

Sub GrabInfo()
    Const link$ = "https://www.nseindia.com/get-quotes/equity?symbol=POONAWALLA"
    Const Url$ = "https://www.nseindia.com/api/quote-equity?symbol=POONAWALLA"
    Dim oHttp As Object, sResp$, prevClose$
    Dim StockName$, lastPrice$, StockSymbol$

    Set oHttp = CreateObject("MSXML2.XMLHTTP")
    
    With oHttp
        .Open "GET", link, True
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36"
        .send
        While .readyState < 4: DoEvents: Wend
        .Open "GET", Url, True
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36"
        .setRequestHeader "Referer", "https://www.nseindia.com/get-quotes/equity?symbol=POONAWALLA"
        .setRequestHeader "accept", "*/*"
        .setRequestHeader "accept-encoding", "gzip, deflate, br"
        .setRequestHeader "accept-language", "en-US,en;q=0.9,bn;q=0.8"
        .send
        While .readyState < 4: DoEvents: Wend
        sResp = .responseText
    End With
    
    StockName = Split(Split(sResp, "companyName"":""")(1), """")(0)
    lastPrice = Split(Split(sResp, "lastPrice"":")(1), ",""")(0)
    StockSymbol = Split(Split(sResp, "symbol"":""")(1), """")(0)
    prevClose = Split(Split(sResp, "previousClose"":")(1), ",""")(0)
    
    Debug.Print StockName, StockSymbol, lastPrice, prevClose
End Sub

Output in the immediate window:

Poonawalla Fincorp Limited POONAWALLA 324.6  321.6