PHILADELPHIA REFLECTIONS
Musings of a Philadelphia Physician who has served the community for six decades

Return to Home

Related Topics

Using XML to Retrieve Financial Data
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:
    ' http://msdn.microsoft.com/en-us/library/aa163921%28office.10%29.aspx
    
    ' BUT before you start, read this:
    ' http://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 http://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 = "http://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

  1. In a spreadsheet press Alt+F11
  2. Insert > Module
  3. Copy the code on this page and paste it into your new module
  4. Tools > References > Microsoft XML v6 per the picture below
  5. Switch back to your spreadsheet (x out of the VBA screen if you want)
{Microsoft XML v6 installation}

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

(2385)

I have been using MarketXLS for this, it's been working Great so far
Posted by: Harrison Delfino   |   Jun 13, 2016 1:35 AM
i am getting error error loading google finance XML Stream in excel 10 and windows 7
Posted by: SAMEER GUPTA   |   Feb 26, 2015 4:09 PM
Igoogle has retired and this is not working anymore. Is there any work around?
Posted by: Prathap   |   Nov 5, 2013 6:39 PM
Hello, It seems with iGoogle shutting down, it does not seem to be working anymore. Is there any work around to the same? Thanks in Advance. With Warm Regards, Prathamesh
Posted by: Prathamesh Kini   |   Nov 5, 2013 5:29 AM
For the people who have trouble using a quote from the right stock exchange. This has to do with your stock symbol. Google Finance will automatically select a US-stock exchange if you just put in the stock symbol. If you want it to select a stock from a different exchange, then go to google Finance, search the stock you want, and you will see that the stock exchange is also included in the stock symbol. For example, the callsign RY gives you the share info of the Royal Canadian Bank, but the callsign 'TSE:RY' will give you the same share, but noted on the Canadian stock exchange. To find the right callsign, search Google Finance manually. Hope this was of any benefit
Posted by: Steven_VC   |   Aug 6, 2013 5:10 AM
Hi There, The above works like a charm, except for one thing - where a code is listed on more than one exchange it defaults to US exchange whereas I want Canada, or at least to be able to specifiy Canada Try getquote("PZA") as an example - you get the US one, where I want the CA one. ANy idea how can I specify to only look at the Canada exchange? Kind Regards Darin
Posted by: DJK   |   Jul 30, 2013 1:14 PM
Hello, Thank for the absolutely simplified way of doing this. Need help on a couple thing though. 1. Is there a way to change the script to refresh the data periodically without manual intervention. 2. I have tried in Excel 2007 and Excel 2010 with file saved in XLSM but it does not refresh the price automatically. Can you please help? Regards, Abhi
Posted by: Abhi   |   Jul 24, 2013 4:24 AM
G'Day George, I am based in Australia and I really like this solution thank you. It's great for US stocks but I don't seem to be able to access ASX (Australian Stock Exchange) Quotes. This is the same problem that Schuy Monarch reported. i.e. ASX:NEA (NEARMAP) works in the google finance web page but not in the VBA macro you wrote. Do you have any idea of a work around for this issue? Thanks in advance.
Posted by: HealthyB   |   Jul 15, 2013 11:44 AM
George - Thanks for your work on this. I have a database with 1500 stocks going back more than 12 years. For all this time, I have used Excel's MSN Stock quotes with a macro that made it easy enough to import all the prices and other info I needed. Recently, MSN has limited an import to 50 stocks at a time (I had previously been doing 150 at a time with the macro) which now makes the whole process too tedious. I like Google Finance but Google docs limits a spreadsheet to 1000 symbols - so I'd like to stay with Excel for the time being. I like the use of functions and your program works fine except for stocks listed on foreign exchanges (I probably have > 200). I have tried every variation I can think of (e.g. TSE:ABT, TSX:ABT, ABT.TO, etc) but they all are rejected, although Google Finance shows the symbol as TSE:ABT. In some cases there isn't a problem because it's a unique symbol and Google returns the correct price. But in the case of ABT as an example, I want Absolute Software, not Abbott Labs. Suggestions?
Posted by: Schuy Monarch   |   Jun 30, 2013 4:43 PM
This is awesome, I've been trying to figure out how to put real time stock data into my excel sheets. Thank you for sharing this!
Posted by: Scott   |   May 26, 2013 9:47 PM

Steve,
Iíve built both of the functions you mention. Browse around
www.philadelphia-reflections.com/topic/230.htm
itís a concatenation of a number of pieces Iíve written on this general topic. The list on the right-hand side gives a summary of all the articles contained in it. My documentation is a little sparse since it presumes a familiarity with both programming and XML but if you stare at the code and the XML feeds a little bit Iím sure youíll get the idea.

Posted by: George Fisher   |   Mar 13, 2013 9:24 AM
Hi George, Thank you for your prompt response! I went back into the file and was able to pull the current price of Apple using a =GetQuote("AAPL") formula in any random cell, so that works great, thank you very much! The reason I asked is basically I'm just trying to make a simple "Price Movers" file...for example I've got a list of anywhere from 20 to 100 stocks that I try to monitor... I'd like to be able just open my file and quickly be able to glance at the daily, week-to-date, month-to-date, and possibly year-to-date returns. Would you know if there is a quick formula where you could pull data from a certain day automatically? I know the excel formulas for the specific day and change in price calculations, I'm just not sure of what (or even if it exists) of a specific price formula for a certain day would be (for example,, =GetQuote("AAPL", 2/28/13)). Would you happen to have any idea offhand? I've researched quite a few sites but it seems it might be a lot more complicated than a simple formula. Just figured I'd ask if you knew of any quick formula offhand before I start downloading massive amounts of data. Thanks again for all your help, I appreciate it! Regards, Steve Blank
Posted by: Steve Blank   |   Mar 13, 2013 9:21 AM
Steve,
  1. You are right to copy from
    "Function GetQuote ..."
    to the second "End Function"
  2. After you finish the XML bit you should be able to go to your spreadsheet and start typing "=Get" (no quotes)
    1. A drop down menu should appear with a list of all the functions beginning with "Get", one of which will be this one

Posted by: George Fisher   |   Mar 13, 2013 9:19 AM
Hi, Sorry I don't know a thing about VBA but I've been looking everywhere for a file that does just this, I just haven't had luck inputting this into Excel though. I have a few questions if anyone has a sec: 1) Does the entire 'VBA function' start at the top of this page with "Function GetQuote(Symbol As String, Optional GoogleParameter As String = "last") As String", and then ends at the bottom where it says, "End Function"? 2) So basically, I open up a new Excel sheet, click Alt + F11, Insert / Modue, then I copy and paste this entire code (again starting with "Function GetQuote" and ending with "End Function" into my 'Module', click 'Microsoft XML v6' and then click OK... but then what? When I click back to my Excel sheet, it is still blank. Do I need to click Run or anything? Is there any sort of text/data that is supposed to appear on my Excel sheet where I can input a stock ticker, and then it imports a price? I am confused. Thanks for anyone's help! Sorry again- I'm a bit of an idiot. (I saved down in XLSM).
Posted by: steve   |   Mar 12, 2013 1:46 PM
THANKS!! I just implemented this in Excel 2010 in less than 10 minutes and I have little to no recent VBA/Macro experience. Extremely helpful.
Posted by: Dave   |   Jan 31, 2013 1:29 PM

To: Kunal Mittal

In Excel 2010, if you save the spreadsheet in XLSM format the VBA will be there automatically when you open it again. For earlier versions I'm not sure but I am sure the process is similar.

Posted by: George Fisher   |   Dec 20, 2012 3:57 PM
How do I run this function automatically on opening the XLS
Posted by: Kunal Mittal   |   Dec 19, 2012 2:11 PM

Please Let Us Know What You Think


(HTML tags provide better formatting)

Because of robot spam we ask you to confirm your comment: we will send you an email containing a link to click. We apologize for this inconvenience but this ensures the quality of the comments. (Your email will not be displayed.)
Thank you.