使用 Google API 和 JSON 检索股票信息

我正在开发一个 dB，每天多次从 Google 财经中提取股票数据。起初我只是提取数据并保存为 CSV 文件，如下所示

Public Sub GrabQuotes()



Dim oXMLHTTP
Dim oStream


Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")


oXMLHTTP.Open "GET", "http://finance.google.com/finance/info?client=ig&q=CVX,XOM,HP,SLB,PBA,ATR,NVZMY,MON,MMM,CNI,EMR,UTX,ROK,XYL,IPGP,DE,JCI,TGT,HD,CVS,NSRGY,PG,PEP,STKL,UNFI,VZ,NGG,POR,ABT,JNJ,NVS,PRGO,RHHBY,ALNY,MDT,ILMN,ISIS,LH,NVO,AFL,CYN,AAPL,ADP,CSCO,EMC,FISV,GOOGL,MA,XLNX,QCOM,INTC,MSFT,NXPI,ORCL", False
oXMLHTTP.Send

If oXMLHTTP.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write oXMLHTTP.responseBody
    oStream.SaveToFile "\\HBFSBOS\APPS\TDID\StockQuotes\All.csv", 2
    oStream.Close
End If


End Sub

该脚本运行顺利。然后我发现检索到的数据是 JSON 格式。我发现了一个很棒的 JSON 格式化工具https://json-csv.com/。 https://json-csv.com/

添加快捷方式并更新我的代码后，它看起来像这样：

Public Sub GrabQuotes()



Dim oXMLHTTP
Dim oStream


Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.3.0")

'Site address has to be encoded. Go to "http://meyerweb.com/eric/tools/dencoder/" to encode/decode


oXMLHTTP.Open "GET", "json-csv.com/?u=http%3A%2F%2Ffinance.google.com%2Ffinance%2Finfo%3Fclient%3Dig%26q%3DCVX%2CXOM%2CHP%2CSLB%2CPBA%2CATR%2CNVZMY%2CMON%2CMMM%2CCNI%2CEMR%2CUTX%2CROK%2CXYL%2CIPGP%2CDE%2CJCI%2CTGT%2CHD%2CCVS%2CNSRGY%2CPG%2CPEP%2CSTKL%2CUNFI%2CVZ%2CNGG%2CPOR%2CABT%2CJNJ%2CNVS%2CPRGO%2CRHHBY%2CALNY%2CMDT%2CILMN%2CISIS%2CLH%2CNVO%2CAFL%2CCYN%2CAAPL%2CADP%2CCSCO%2CEMC%2CFISV%2CGOOGL%2CMA%2CXLNX%2CQCOM%2CINTC%2CMSFT%2CNXPI%2CORCL", False
oXMLHTTP.Send

If oXMLHTTP.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write oXMLHTTP.responseBody
    oStream.SaveToFile "\\HBFSBOS\APPS\TDID\StockQuotes\All.csv", 2
    oStream.Close
End If


End Sub

我现在收到错误“运行时错误‘-2147467259 (80004005)’：对象‘IXMLHTTPRequest’的方法‘open’失败”。如果我将请求粘贴到 Chrome 中，也可以正常工作。我该如何改变它才能使其正常工作？我对 JSON 和 XMLHTTP 很陌生，因此我们将不胜感激。

这是VBA。浏览器会自动添加 https://。实际上我决定尝试不同的角度并专注于获取数据。通过使用以下效果很好的方法。

Option Compare Database

Public Sub RunJSON()

Dim browser As Object 'defines browser as object


Set browser = CreateObject("InternetExplorer.Application") 'creates the object
browser.navigate "json-csv.com/?u=http%3A%2F%2Ffinance.google.com%2Ffinance%2Finfo%3Fclient%3Dig%26q%3DCVX%2CXOM%2CHP%2CSLB%2CPBA%2CATR%2CNVZMY%2CMON%2CMMM%2CCNI%2CEMR%2CUTX%2CROK%2CXYL%2CIPGP%2CDE%2CJCI%2CTGT%2CHD%2CCVS%2CNSRGY%2CPG%2CPEP%2CSTKL%2CUNFI%2CVZ%2CNGG%2CPOR%2CABT%2CJNJ%2CNVS%2CPRGO%2CRHHBY%2CALNY%2CMDT%2CILMN%2CISIS%2CLH%2CNVO%2CAFL%2CCYN%2CAAPL%2CADP%2CCSCO%2CEMC%2CFISV%2CGOOGL%2CMA%2CXLNX%2CQCOM%2CINTC%2CMSFT%2CNXPI%2CORCL"
Set browser = Nothing 'nullifies the object

End Sub