0

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?

Veck Hsiao
  • 591
  • 2
  • 8
  • 20

1 Answers1

1

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
Community
  • 1
  • 1
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • 1
    Thanks Axel. With MSXML2.XMLHTTP, I successfully get the JSON back to my worksheet. ScriptControl failed to be execute (Error: 429). I think the problem is that my Windows is 64-bits. Any way, thanks a lot. – Veck Hsiao Oct 26 '15 at 16:45
  • @Veck Hsiao: Yes `ScriptControl` is only a 32-bit ActiveX component. Try using one of the available libraries for parsing JSON wit VBA. – Axel Richter Oct 26 '15 at 16:47
  • Trying the routine above I get a 404 not found answer. Does anyone knows if this interface still works? – Ultra Junkie May 26 '23 at 06:42
  • Addendum: removing the "/info" in the URL (according to another source I found), I get "access denied"... – Ultra Junkie May 26 '23 at 06:53
  • This answer from 2015 is outdated. Seems Google finance does not allow that kind of request anymore. – Axel Richter May 26 '23 at 09:07