Scrape Realtime data using VBA

Viewed 125

I want to scrape realtime data from https://iboard.ssi.com.vn/bang-gia/hose using VBA.

My code is as follows; but it does not return the html file with data contained inside. I looked for but could not find the link to JSON data either.

Sub Get_ssi_data()
       
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    
    xmlhttp.Open "GET", "https://iboard.ssi.com.vn/bang-gia/hose"
    xmlhttp.send
    
    Debug.Print xmlhttp.responseText
      
End Sub

Could you please suggest what to do to be able to get the real time data?

1 Answers

It is unclear which part of the data are you looking for but I assume that you want the main table.

Multiple APIs are called to the endpoint https://gateway-iboard.ssi.com.vn/graphql , with different request body to get the specific information.

From the DevTools, the request body for the main table is as below:

{"operationName":"stockRealtimes","variables":{"exchange":"hose"},"query":"query stockRealtimes($exchange: String) {\n  stockRealtimes(exchange: $exchange) {\n    stockNo\n    ceiling\n    floor\n    refPrice\n    stockSymbol\n    stockType\n    exchange\n    matchedPrice\n    matchedVolume\n    priceChange\n    priceChangePercent\n    highest\n    avgPrice\n    lowest\n    nmTotalTradedQty\n    best1Bid\n    best1BidVol\n    best2Bid\n    best2BidVol\n    best3Bid\n    best3BidVol\n    best4Bid\n    best4BidVol\n    best5Bid\n    best5BidVol\n    best6Bid\n    best6BidVol\n    best7Bid\n    best7BidVol\n    best8Bid\n    best8BidVol\n    best9Bid\n    best9BidVol\n    best10Bid\n    best10BidVol\n    best1Offer\n    best1OfferVol\n    best2Offer\n    best2OfferVol\n    best3Offer\n    best3OfferVol\n    best4Offer\n    best4OfferVol\n    best5Offer\n    best5OfferVol\n    best6Offer\n    best6OfferVol\n    best7Offer\n    best7OfferVol\n    best8Offer\n    best8OfferVol\n    best9Offer\n    best9OfferVol\n    best10Offer\n    best10OfferVol\n    buyForeignQtty\n    buyForeignValue\n    sellForeignQtty\n    sellForeignValue\n    caStatus\n    tradingStatus\n    currentBidQty\n    currentOfferQty\n    remainForeignQtty\n    session\n    __typename\n  }\n}\n"}

So you need to send the above request body to the endpoint which will gives you a JSON response containing the raw data of the main table which you will then need to process them on your own to get what you want.

Private Sub Get_ssi_data()
           
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    
    Dim reqBody As String
    reqBody = "{""operationName"":""stockRealtimes"",""variables"":{""exchange"":""hose""},""query"":""query stockRealtimes($exchange: String) {\n  stockRealtimes(exchange: $exchange) {\n    stockNo\n    ceiling\n    floor\n    refPrice\n    stockSymbol\n    stockType\n    exchange\n    matchedPrice\n    matchedVolume\n    priceChange\n    priceChangePercent\n    highest\n    avgPrice\n    lowest\n    nmTotalTradedQty\n    best1Bid\n    best1BidVol\n    best2Bid\n    best2BidVol\n    best3Bid\n    " & _
                "best3BidVol\n    best4Bid\n    best4BidVol\n    best5Bid\n    best5BidVol\n    best6Bid\n    best6BidVol\n    best7Bid\n    best7BidVol\n    best8Bid\n    best8BidVol\n    best9Bid\n    best9BidVol\n    best10Bid\n    best10BidVol\n    best1Offer\n    best1OfferVol\n    best2Offer\n    best2OfferVol\n    best3Offer\n    best3OfferVol\n    best4Offer\n    best4OfferVol\n    best5Offer\n    best5OfferVol\n    best6Offer\n    best6OfferVol\n    best7Offer\n    best7OfferVol\n    best8Offer\n    best8OfferVol\n    best9Offer\n    best9OfferVol\n   best10Offer\n    best10OfferVol\n    buyForeignQtty\n    buyForeignValue\n    sellForeignQtty\n    sellForeignValue\n    caStatus\n    tradingStatus\n    currentBidQty\n    currentOfferQty\n    remainForeignQtty\n    session\n    __typename\n  }\n}\n""}"
    
    xmlhttp.Open "POST", "https://gateway-iboard.ssi.com.vn/graphql", False
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    xmlhttp.send reqBody
    'xmlhttp.send (reqBody) 'For Excel 2013 (Credit to SIM in comment)

    Debug.Print xmlhttp.responseText

End Sub

EDIT - On finding the request body

Under Network tab in Chrome Devtools, you will see that the website made several requests to graphql (Filter by Fetch/XHR to get a smaller result in the list)

enter image description here

Because they all call to the same endpoint which makes it difficult to identify the correct table, I click onto each request and look at its Response tab (that is the JSON string).

Below you can see that I found the request that returns the main table's data (the JSON string is usually in 1 long line, you can click Pretty Print (circled in red) to make it easier to read)

enter image description here

Once you found the request that you want, go back to Headers tab and look at the end of the tab, you will find Request Payload section which is the request body. You can click view source to get a plain text version which you use to copy-paste.

enter image description here

Related