Search code examples
excelvbagoogle-finance-api

Call Google Finance API in Excel

I want to fetch stock price via Google Finance API in Excel.

Resource: https://finance.google.com/finance/info

Method: GET

Parameters:

  • client = ig
  • q = <exchange code>:<stock code>

example:

https://finance.google.com/finance/info?client=ig&q=TPE:2330

this request will get the stock price of Taiwan Semiconductor.

Is there a solution, no matter doing some setting of worksheet or writing some VBA code, to reach my goal?

Solution

  • Yes. The methods are XMLHTTPRequest, https://msdn.microsoft.com/en-us/library/ms759148%28v=vs.85%29.aspx for getting the response. And parsing JSON with VBA for parsing the response since it is JSON. Simplest method for parsing JSON with VBA is described here Parsing JSON in Excel VBA but there are much more comfortable libraries available with search keywords Excel VBA parse JSON.

    Simple example for your use case:

    Sub testGetJSON()

 sExchangeCode = "TPE"
 sStockCode = "2330"
 sURL = "https://finance.google.com/finance/info?client=ig&q=" & sExchangeCode & ":" & sStockCode

 Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
 oXMLHTTP.Open "GET", sURL, False
 oXMLHTTP.send

 sJSONResp = oXMLHTTP.responseText

 sJSONResp = Mid(sJSONResp, 4, Len(sJSONResp))

 Set oScript = CreateObject("ScriptControl")
 oScript.Language = "jscript"
 oScript.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "

 Set oJSObj = oScript.eval("(" & sJSONResp & ")")

 Set oProp0 = oScript.Run("getProperty", oJSObj, "0")

 sLCur = oProp0.l_cur
 sLT = oProp0.lt

 MsgBox sLT & " : " & sLCur

End Sub