json - 使用 VBA 抓取 AJAX 页面

Question

我一直在尝试抓取整个 HTML 正文并将其分配为字符串变量，然后再操作该字符串以填充 excel 文件 - 这将在一个循环中完成，以每 5 分钟间隔更新一次日期。

这些页面是 AJAX 页面，所以运行看起来像 JavaScript 的东西(虽然我一点也不熟悉 JS)。

我试过使用 XMLHttpRequest对象(下面的代码)但 t 返回 JS 调用:

Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", "https://www.google.co.uk/finance?ei=bQ_iWLnjOoS_UeWcqsgE", False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send
Debug.Print XMLHTTP.ResponseText

我尝试创建一个 IE使用以下代码的对象，但同样的问题:

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.navigate "https://www.google.co.uk/finance?ei=bQ_iWLnjOoS_UeWcqsgE"
While IE.Busy Or IE.ReadyState <> 4: DoEvents: Wend
Set HTMLdoc = IE.Document
Debug.Print = HTMLdoc.Body.innerHTML

当我按下 F12 并进入检查器选项卡(即下面黄色部分中的全部文本)时，我想要的正是文本可用 - 如果我能得到这个(完全扩展)，我可以从那里工作。任何帮助将不胜感激。



在上面的例子(谷歌金融)中，指数价格是异步更新的——我想在我分配字符串的时候捕获这些。

Answer 1

对于任何动态加载的数据，您只需检查网页所做的 XHR，找到包含相关数据的数据，制作相同的 XHR(站点是否提供 API)并解析响应，或者在 IE 自动化的情况下添加额外的等待循环，直到目标元素变得可访问，然后从 DOM 中检索它。

在这种情况下，您可以通过 Google Finance API 获取数据。

方法一。

要提出请求，您必须知道股票代码，这可以在网页 HTML 内容中轻松找到，或者 e. G。如果您点击 CAC 40，在打开的页面中会有一个标题 CAC 40 (INDEXEURO:PX1)。

该页面的世界市场表中有以下股票和证券交易所代码:

Shanghai            SHA:000001
S&P 500             INDEXSP:.INX
Nikkei 225          INDEXNIKKEI:NI225
Hang Seng Index     INDEXHANGSENG:HSI
TSEC                TPE:TAIEX
EURO STOXX 50       INDEXSTOXX:SX5E
CAC 40              INDEXEURO:PX1
S&P TSX             INDEXTSI:OSPTX
S&P/ASX 200         INDEXASX:XJO
BSE Sensex          INDEXBOM:SENSEX
SMI                 INDEXSWX:SMI
ATX                 INDEXVIE:ATX
IBOVESPA            INDEXBVMF:IBOV
SET                 INDEXBKK:SET
BIST100             INDEXIST:XU100
IBEX                INDEXBME:IB
WIG                 WSE:WIG
TASI                TADAWUL:TASI
MERVAL              BCBA:IAR
IPC                 INDEXBMV:ME
IDX Composite       IDX:COMPOSITE

将它们放入 URL:

http://finance.google.com/finance/info?q=SHA:000001,INDEXSP:.INX,INDEXNIKKEI:NI225,INDEXHANGSENG:HSI,TPE:TAIEX,INDEXSTOXX:SX5E,INDEXEURO:PX1,INDEXTSI:OSPTX,INDEXASX:XJO,INDEXBOM:SENSEX,INDEXSWX:SMI,INDEXVIE:ATX,INDEXBVMF:IBOV,INDEXBKK:SET,INDEXIST:XU100,INDEXBME:IB,WSE:WIG,TADAWUL:TASI,BCBA:IAR,INDEXBMV:ME,IDX:COMPOSITE

响应包含 JSON 数据，如下所示:

[
    {
        "id": "7521596",
        "t": "000001",
        "e": "SHA",
        "l": "3,222.51",
        "l_fix": "3222.51",
        "l_cur": "CN¥3,222.51",
        "s": "0",
        "ltt": "3:01PM GMT+8",
        "lt": "Mar 31, 3:01PM GMT+8",
        "lt_dts": "2017-03-31T15:01:15Z",
        "c": "+12.28",
        "c_fix": "12.28",
        "cp": "0.38",
        "cp_fix": "0.38",
        "ccol": "chg",
        "pcls_fix": "3210.2368"
    },
    ...
]

您可以使用下面的 VBA 代码来解析响应和输出结果。它需要 JSON.bas要导入到 VBA 项目以进行 JSON 处理的模块。

Sub GoogleFinanceData()

    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim aData()
    Dim aHeader()

    ' Retrieve Google Finance data
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "http://finance.google.com/finance/info?q=SHA:000001,INDEXSP:.INX,INDEXNIKKEI:NI225,INDEXHANGSENG:HSI,TPE:TAIEX,INDEXSTOXX:SX5E,INDEXEURO:PX1,INDEXTSI:OSPTX,INDEXASX:XJO,INDEXBOM:SENSEX,INDEXSWX:SMI,INDEXVIE:ATX,INDEXBVMF:IBOV,INDEXBKK:SET,INDEXIST:XU100,INDEXBME:IB,WSE:WIG,TADAWUL:TASI,BCBA:IAR,INDEXBMV:ME,IDX:COMPOSITE", False
        .Send
        If .Status <> 200 Then Exit Sub
        sJSONString = .responseText
    End With
    ' Trim extraneous chars
    sJSONString = Mid(sJSONString, InStr(sJSONString, "["))
    ' Parse JSON string
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then Exit Sub
    ' Convert to table format
    JSON.ToArray vJSON, aData, aHeader
    ' Results output
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        If UBound(aHeader) >= 0 Then OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

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

因此，您需要的数据位于 l_fix , c_fix , cp_fix列。

方法2。

您也可以通过类似 CAC 40 的 URL 来制作 XHR:

https://www.google.co.uk/finance/getprices?q=PX1&x=INDEXEURO&i=120&p=20m&f=d,c,v,o,h,l

特别是该 URL 用于 PX1 股票和 INDEXEURO 股票交易所符号，120 秒间隔，20 分钟周期，响应数据 d,c,v,o,h,l 用于 DATE (UNIX TimeStamp), CLOSE, VOLUME, OPEN, HIGH,低的。

响应格式如下:

EXCHANGE%3DINDEXEURO
MARKET_OPEN_MINUTE=540
MARKET_CLOSE_MINUTE=1050
INTERVAL=120
COLUMNS=DATE,CLOSE,HIGH,LOW,OPEN,VOLUME
DATA=
TIMEZONE_OFFSET=120
a1491405000,5098.75,5099.92,5098.75,5099.92,0
1,5100.51,5100.51,5098.09,5098.09,0
2,5099.63,5101.2,5099.29,5100.68,0
3,5099.83,5100.04,5099.07,5099.28,0
4,5098.19,5098.9,5097.71,5098.9,0
5,5098.56,5099.24,5097.99,5099.24,0
6,5097.34,5098.2,5096.14,5098.2,0
7,5096.52,5097.38,5095.66,5097.38,0
8,5093.27,5095.39,5093.27,5095.39,0
9,5094.43,5094.43,5092.07,5093.17,0
10,5088.18,5092.72,5087.68,5092.72,0

应该对列表中的每个股票代码进行 XHR，然后将结果合并到表格中。