MSXML2.XMLHTTP Request does not return full HTML page

Question

I wan to pull Buy and sell quantities data from NSE website using XMLHTTP only as it can pull data very fast (The regular method of Set ie = CreateObject("InternetExplorer.Application")) takes very long time to pull datas for 100+ stocks.

Initially I tried with "MSXML2.XMLHTTP" method, I was not able to pull the data as the response text received is none. Later on after reading several trouble shooting, I found some solution from this site itself that first write the HTML document to text file then try to extract.

After using that method, some improvements made that, the response text received with all HTML elements except my required field. I was able to see my required getdocumentbyid yet I am not able to gets its inner text. The inner text received as "-".

Can any one please help?

Attaching my code here

Public Sub GetInfo()
    Dim sResponse As String, I As Long, html As New HTMLDocument, hTable As HTMLTable
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.nseindia.com/get-quotes/equity?symbol=VOLTAS", False
        .send
        Application.Wait Now + TimeValue("00:00:01")
        sResponse = StrConv(.responseBody, vbUnicode)
    End With
    sResponse = Mid$(sResponse, InStr(1, sResponse, "<!DOCTYPE "))
    WriteTxtFile sResponse
    With html
        .body.innerHTML = sResponse
        Set hTable = .getElementById("orderBuyTq")
        Debug.Print hTable.innerHTML 'It gives output as "-"
    End With
End Sub

 Public Sub WriteTxtFile(ByVal aString As String, Optional ByVal filePath As String = "E:\Share Market\Test.txt")
    Dim fso As Object, Fileout As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Fileout = fso.CreateTextFile(filePath, True, True)
    Fileout.Write aString
    Fileout.Close
End Sub

Answer 1

Well if you look at the generated text file you will find <span id="orderBuyTq" class="bold">-</span> so the actual content is - and your code works correct.

The same happens if I open your URL https://www.nseindia.com/get-quotes/equity?symbol=VOLTAS in a browser and view the HTML code to search for orderBuyTq. It's content is -.

So your code does exactly what it should.

The issue is that the value is loaded by some JavaScript since it doesn't run any JavaScript when you do a MSXML2.XMLHTTP request, the value is not there. You can test that by disabeling JavaScript in your browser.

I recommend to use the following URL instead
https://www.nseindia.com/api/quote-equity?symbol=VOLTAS&section=trade_info
to access the API directly, so you don't have to fiddle with the HTML at all. It gives you the following JSON response where you then have the "totalBuyQuantity":355 and "totalSellQuantity":0 in the "marketDeptOrderBook"

{
   "noBlockDeals":true,
   "bulkBlockDeals":[
      {
         "name":"Session I"
      },
      {
         "name":"Session II"
      }
   ],
   "marketDeptOrderBook":{
      "totalBuyQuantity":355,
      "totalSellQuantity":0,
      "bid":[
         {
            "price":1048.7,
            "quantity":355
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         }
      ],
      "ask":[
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         },
         {
            "price":0,
            "quantity":0
         }
      ],
      "tradeInfo":{
         "totalTradedVolume":3011002,
         "totalTradedValue":31321.35,
         "totalMarketCap":3475778.75,
         "ffmc":2428991.787866,
         "impactCost":0.03
      },
      "valueAtRisk":{
         "securityVar":14.13,
         "indexVar":0,
         "varMargin":14.13,
         "extremeLossMargin":3.5,
         "adhocMargin":0,
         "applicableMargin":17.63
      }
   },
   "securityWiseDP":{
      "quantityTraded":3011002,
      "deliveryQuantity":720105,
      "deliveryToTradedQuantity":23.92,
      "seriesRemarks":null,
      "secWiseDelPosDate":"25-FEB-2021 EOD"
   }
}

Make sure that you don't violate the therms of that website when you process it automatically. Usually terms of use disallow automated scripts.