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

本文介绍了使用Google API和JSON检索股票信息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在研究dB，以便每天多次从Google财务中提取股票数据.起初，我只是提取数据并将其另存为CSV文件，如下所示:

I am working on a dB to pull stock data from Google finance multiple times a day. At first I was just pulling the data and saving as a CSV file as below

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格式.我在 https://json-csv.com/上发现了一个很棒的JSON格式化工具.

That script runs without a hitch. I then discovered the data being retrieved was in JSON format. I discovered a great JSON formatting tool at https://json-csv.com/.

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

After appending the shortcut and updating my code it looks like this:

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的新手，所以将不胜感激.

I now get error "Run-time error '-2147467259 (80004005)': Method 'open' of object 'IXMLHTTPRequest' failed". If I paste the request into Chrome, is also works fine. How would I alter this in order to get it to work? I am new with JSON and XMLHTTP, so any help would be appreciated.

推荐答案

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

It was VBA. The browser automatically adds the https://. I actually decided to try different angle and focus just on getting the data. by using the following which is working well.

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

这篇关于使用Google API和JSON检索股票信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！