CODEWITHSUNDEEP
jsonvbaexcelweb-scrapingxmlhttprequest
RockDoctor
RockDoctor

Reputation: 341

Retrieve webpage including AJAX content

I've been using VBA to retrieve stock prices from the ASX website (www.asx.com.au) for quite some time, however, my script no longer works as the website has been updated and now uses javascripts to build the content.

As a result, the script shown below now return the sections rather than the page content.

The VBA (pretty stock standard):

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", strURL, False
    .send
    http.body.innerHTML = .responseText
End With

And the .responseText contains things like:

<SCRIPT>
    var urlArray = window.location.hash.split('/');
    if (urlArray != null) {
      var var1 = urlArray[1];
      window.location = "http://www.asx.com.au/asx/research/companyInfo.do?by=asxCode&asxCode=" + var1;
    }
</SCRIPT>

How can I retrieve the webpage as one would view it in the browser? The only thing I've not tried is creating a browser object can grabbing the HTML from that.

Upvotes: 1

Views: 3317

Answers (1)

omegastripes
omegastripes

Reputation: 12612

The website http://www.asx.com.au has an API available. I opened a page in Chrome for one of the companies - AMC by the link http://www.asx.com.au/asx/share-price-research/company/AMC, then opened Developer Tools window (F12), Network tab, and examined XHRs in the list after the page was loaded after I clicked each section. I found several URLs which return data in JSON format:

To see a structure of the presented data the response contents could be copied and pasted to any JSON viewer (e. g. this online tool http://jsonviewer.stack.hu).

You may use the below VBA code to parse response from the URL https://www.asx.com.au/asx/1/share/AMC/prices and output result. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test_query_ASX()

    Const Transposed = False ' Output option

    Dim sCode As String
    Dim sInterval As String
    Dim sCount As String
    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim aRows()
    Dim aHeader()

    sCode = "AMC"
    sInterval = "daily"
    sCount = "10"

    ' Get JSON via API
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.asx.com.au/asx/1/share/" & sCode & "/prices?interval=" & sInterval & "&count=" & sCount, False
        .Send
        sJSONString = .ResponseText
    End With
    ' Parse JSON response
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then
        MsgBox "Invalid JSON"
        Exit Sub
    End If
    ' Pick core data
    vJSON = vJSON("data")
    ' Convert each data set to array
    JSON.ToArray vJSON, aRows, aHeader
    ' Output array to worksheet
    With ThisWorkbook.Sheets(1)
        .Cells.Delete
        If Transposed Then
            Output2DArray .Cells(1, 1), WorksheetFunction.Transpose(aHeader)
            Output2DArray .Cells(1, 2), WorksheetFunction.Transpose(aRows)
        Else
            OutputArray .Cells(1, 1), aHeader
            Output2DArray .Cells(2, 1), aRows
        End If
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Run Sub Test_query_ASX() to process data. The output on the Sheet1 for me is as follows:

output

Having that example you can extract the data you need from the JSON responses by the listed URLs. BTW, the similar approach applied in other answers.

UPDATE

After some changes on the web site it is necessary to use https://www.asx.com.au/asx/... instead of http://www.asx.com.au/b2c-api/..., so I fixed all of the above URLs.

Upvotes: 5

Related Questions