Related Topics
George (3)
It's often desirable to get live financial data and everyone knows. XML is the thing to use but actually writing programs that work takes a bit of trouble. Plus, once you've got the data you need to display it.
Popular Passages
New topic 2013-02-05 15:24:06 description
Exchange Quotes from XML using Excel (Google Finance)
Excel VBA function that returns stock information from Google Finance's XML stream.
Function GetQuote(Symbol As String, Optional GoogleParameter As String = "last") As String
' This is an Excel VBA function which returns data about a stock
' using Google Finance's XML stream
' On December 6, 2012 the Excel call =GetQuote("VTI") returned 72.55
' =GetQuote("VTI", "volume") returned 68676
' Since this function returns strings, I've included a String_to_Number function below
' I got the essential ideas from here:
' https://msdn.microsoft.com/en-us/library/aa163921%28office.10%29.aspx
' BUT before you start, read this:
' https://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
' Note: Google has threatened to discontinue this feed in the future
' see my GetQuoteYahoo function as an alternative in that case
' but until then the two streams have special features
' see https://www.philadelphia-reflections.com/blog/2392.htm
' "GoogleParameter" is one of the following node names, it defaults to "last":
' "URL" is also valid in case you want to look at the XML stream returned
'
'<xml_api_reply version="1">
' <finance module_id="0" tab_id="0" mobile_row="0" mobile_zipped="1" row="0" section="0">
' <symbol data="VTI"/>
' <pretty_symbol data="VTI"/>
' <symbol_lookup_url data="/finance?client=ig&q=VTI"/>
' <company data="Vanguard Total Stock Market ETF"/>
' <exchange data="NYSEARCA"/>
' <exchange_timezone data=""/>
' <exchange_utc_offset data=""/>
' <exchange_closing data=""/>
' <divisor data="2"/>
' <currency data="USD"/>
' <last data="72.55"/>
' <high data="72.69"/>
' <low data="72.45"/>
' <volume data="68676"/>
' <avg_volume data=""/>
' <market_cap data="22385.76"/>
' <open data="72.54"/>
' <y_close data="72.60"/>
' <change data="+0.03"/>
' <perc_change data="0.04"/>
' <delay data="0"/>
' <trade_timestamp data="1 minute ago"/>
' <trade_date_utc data="20121206"/>
' <trade_time_utc data="145144"/>
' <current_date_utc data="20121206"/>
' <current_time_utc data="145323"/>
' <symbol_url data="/finance?client=ig&q=VTI"/>
' <chart_url data="/finance/chart?q=NYSEARCA:VTI&tlf=12"/>
' <disclaimer_url data="/help/stock_disclaimer.html"/>
' <ecn_url data=""/>
' <isld_last data="72.55"/>
' <isld_trade_date_utc data="20121206"/>
' <isld_trade_time_utc data="142903"/>
' <brut_last data=""/>
' <brut_trade_date_utc data=""/>
' <brut_trade_time_utc data=""/>
' <daylight_savings data="false"/>
' </finance>
'</xml_api_reply>
Dim GoogleXMLstream As MSXML2.DOMDocument
Dim oChildren As MSXML2.IXMLDOMNodeList
Dim oChild As MSXML2.IXMLDOMNode
Dim fSuccess As Boolean
Dim URL As String
On Error GoTo HandleErr
' create the URL that requests the XML stream from Google Finance
URL = "https://www.google.com/ig/api?stock=" & Trim(Symbol)
' In case you want to look at the XML
If GoogleParameter = "URL" Then
GetQuote = URL
Exit Function
End If
' pull in the XML stream
Set GoogleXMLstream = New MSXML2.DOMDocument
GoogleXMLstream.async = False ' wait for completion
GoogleXMLstream.validateOnParse = False ' do not validate the XML stream
fSuccess = GoogleXMLstream.Load(URL) ' pull in the feed
If Not fSuccess Then ' quit on failure
MsgBox "error loading Google Finance XML stream"
Exit Function
End If
' iterate through the nodes looking for one with the name in GoogleParameter
GetQuote = GoogleParameter & " is not valid for GetQuote function"
Set oChildren = GoogleXMLstream.DocumentElement.LastChild.ChildNodes
For Each oChild In oChildren
If oChild.nodeName = GoogleParameter Then
GetQuote = oChild.Attributes.getNamedItem("data").Text
Exit Function
End If
Next oChild
' error handlers
ExitHere:
Exit Function
HandleErr:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitHere
Resume
End Function
Function String_to_Number(num_as_string As String) As Double
' The GetQuote function returns string values
' This function converts numbers in string format to double
String_to_Number = Val(num_as_string)
End Function
How to install an Excel macro
- In a spreadsheet press Alt+F11
- Insert > Module
- Copy the code on this page and paste it into your new module
- Tools > References > Microsoft XML v6 per the picture below
- Switch back to your spreadsheet (x out of the VBA screen if you want)
Thanks to declaring early bound msxml object throws an error in vba for both the clue and the picture
My thanks to Encode / Decode HTML Entities
Originally published: Tuesday, December 04, 2012; most-recently modified: Thursday, May 16, 2019
Posted by: Harrison Delfino | Jun 13, 2016 1:35 AM |
Posted by: SAMEER GUPTA | Feb 26, 2015 4:09 PM |
Posted by: Prathap | Nov 5, 2013 6:39 PM |
Posted by: Prathamesh Kini | Nov 5, 2013 5:29 AM |
Posted by: Steven_VC | Aug 6, 2013 5:10 AM |
Posted by: DJK | Jul 30, 2013 1:14 PM |
Posted by: Abhi | Jul 24, 2013 4:24 AM |
Posted by: HealthyB | Jul 15, 2013 11:44 AM |
Posted by: Schuy Monarch | Jun 30, 2013 4:43 PM |
Posted by: Scott | May 26, 2013 9:47 PM |
Posted by: George Fisher | Mar 13, 2013 9:24 AM |
Posted by: Steve Blank | Mar 13, 2013 9:21 AM |
Posted by: George Fisher | Mar 13, 2013 9:19 AM |
Posted by: steve | Mar 12, 2013 1:46 PM |
Posted by: Dave | Jan 31, 2013 1:29 PM |
Posted by: George Fisher | Dec 20, 2012 3:57 PM |
Posted by: Kunal Mittal | Dec 19, 2012 2:11 PM |