asx

asx

关注
发信
关注(28)粉丝(399)

检索包含AJAX内容的网页

本文介绍了检索包含AJAX内容的网页的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

一段时间以来，我一直在使用VBA从ASX网站(www.asx.com.au)检索股票价格，但是，由于该网站已更新，我的脚本不再起作用，现在使用JavaScript进行构建内容.

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.

VBA(库存标准):

The VBA (pretty stock standard):

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

.responseText包含以下内容:

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>

如何检索网页，就像在浏览器中查看该网页一样?我唯一没有尝试过的事情就是创建一个浏览器对象，可以从中获取HTML.

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.

推荐答案

网站 http://www.asx .com.au 有一个可用的API.我在Chrome中为其中一家公司-AMC打开了一个页面，链接为 http://www.asx.com.au/asx/share-price-research/company/AMC ，然后打开开发人员工具"窗口()，网络"标签和单击每个部分后，在加载页面后检查列表中的XHR.我找到了几个以JSON格式返回数据的URL:

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 (), 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:

  • https://www.asx.com.au/asx/1/company/AMC?fields=primary_share,latest_annual_reports,last_dividend,primary_share.indices
  • https://www.asx.com.au/asx/1/company/AMC/similar?compare=marketcap
  • https://www.asx.com.au/asx/1/company/AMC/announcements?count=10&market_sensitive=false
  • https://www.asx.com.au/asx/1/chart/highcharts?asx_code=AMC&years=10
  • https://www.asx.com.au/asx/1/share/AMC/prices?interval=daily&count=10
  • https://www.asx.com.au/asx/1/company/AMC/dividends/history?years=5
  • https://www.asx.com.au/asx/1/company/AMC/dividends
  • https://www.asx.com.au/asx/1/company/AMC/options?count=5000
  • https://www.asx.com.au/asx/1/company/AMC/warrants?count=5000
  • https://www.asx.com.au/asx/1/company/AMC/people

要查看所呈现数据的结构，可以将响应内容复制并粘贴到任何JSON查看器中(例如，此在线工具 http://jsonviewer.stack.hu ).

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).

您可以使用下面的VBA代码来解析URL https://www.asx.com.au/asx/1/share/AMC/prices 并输出结果. 将 JSON.bas 模块导入VBA项目中以进行JSON处理.

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

运行Sub Test_query_ASX()以处理数据.对我来说，Sheet1上的输出如下:

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

在该示例中，您可以通过列出的URL从JSON响应中提取所需的数据.顺便说一句，此和答案.

Having that example you can extract the data you need from the JSON responses by the listed URLs. BTW, the same approach used in this and this answers.

更新

在网站上进行了一些更改之后，有必要使用https://www.asx.com.au/asx/...而不是http://www.asx.com.au/b2c-api/...，因此我修复了所有上述URL.

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.

这篇关于检索包含AJAX内容的网页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-20 23:28
Powered by LMLPHP ©2025 asx 0.004671