title: Using Google API and JSON to Retrieve Stock Info
body: <p>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</p>
  
  <pre><code>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&amp;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
  </code></pre>
  
  <p>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 <a href="https://json-csv.com/" rel="nofollow">https://json-csv.com/.</a></p>
  
  <p>After appending the shortcut and updating my code it looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>