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 (Yahoo Finance)

Excel VBA function that returns stock information from Yahoo Finance's XML streams.

Function GetQuoteYahoo(Symbol As String, _
         Optional YahooParameter As String = "LastTradePriceOnly", _
         Optional YahooFeed As String = "a") As String

    ' This is an Excel VBA function which returns data about a stock
    ' using Yahoo Finance's various XML streams
    
    ' On December 8, 2012 =GetQuoteYahoo("VTI")                   returned 73.04
    '                     =GetQuoteYahoo("VTI", "Dividend_Yield") returned 1.89
    '                     =GetQuoteYahoo("MS",  "Industry", "e")  returned Investment Brokerage - National
    
    ' This function returns a string;
    ' the Str_to_Num function included below will convert a string-number to a double
    
    ' Yahoo Finance offers several XML streams
    ' You should look at each stream and figure out which one you want and what the node names are
    ' Stream description
    '   "a" is from the CSV data
    '   "b" is from yahoo.finance.quotes
    '   "c" and "d" are from yahoo.finance.quant and quant2
    '   "e" is from yahoo.finance.stocks
    '
    ' =GetQuoteYahoo("GOOG","URL","a") will give you the URL for stream "a" to look at for Google, etc.
    ' The default is stream "a" and node "LastTradePriceOnly"
    
    ' The full list (as of Dec 2012) of yahoo.finance data tables was
    '   yahoo.finance.historicaldata
    '   yahoo.finance.industry
    '   yahoo.finance.isin
    '   yahoo.finance.onvista
    '   yahoo.finance.option_contracts
    '   yahoo.finance.options
    '   yahoo.finance.quant
    '   yahoo.finance.quant2
    '   yahoo.finance.quotes
    '   yahoo.finance.quoteslist
    '   yahoo.finance.sectors
    '   yahoo.finance.stock
    '   yahoo.finance.stocks
    '   yahoo.finance.xchange
    
    ' In case you want even more variety, I have a GetQuote function which uses the XML
    ' stream from Google Finance but they are threatening to discontinue it
    ' see http://www.philadelphia-reflections.com/blog/2385.htm
    
    ' My thanks to http://vikku.info/codetrash/Yahoo_Finance_Stock_Quote_API
    ' and http://developer.yahoo.com/yql/console/
    
    ' Before you start, read this:
    ' http://stackoverflow.com/questions/11245733/declaring-early-bound-msxml-object-throws-an-error-in-vba
    
    ' --------- code follows ---------
    
    Dim YahooXMLstream As MSXML2.DOMDocument
    
    Dim oChildren As MSXML2.IXMLDOMNodeList
    Dim oChild As MSXML2.IXMLDOMNode
    
    Dim fSuccess As Boolean
    Dim URL As String, _
        url_part1 As String, _
        url_part2 As String, _
        url_part3 As String, _
        url_part4 As String, _
        url_part5 As String
    
    On Error GoTo HandleErr
    
    ' create the URL that requests the XML stream from Yahoo Finance
    If LCase(YahooFeed) = "a" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20csv%20where%20url%3D'http%3A%2F%2Fdownload.finance.yahoo.com%2Fd%2Fquotes.csv%3Fs%3D"
        url_part2 = "%26f%3Dsnll1d1t1cc1p2t7va2ibb6aa5pomwj5j6k4k5ers7r1qdyj1t8e7e8e9r6r7r5b4p6p5j4m3m7m8m4m5m6k1b3b2i5x"
        url_part3 = "%26e%3D.csv'%20and%20columns%3D"
        url_part4 = "'Symbol%2CName%2CLastTradeWithTime%2CLastTradePriceOnly%2CLastTradeDate%2CLastTradeTime%2CChange%20PercentChange%2CChange%2CChangeinPercent%2CTickerTrend%2CVolume%2CAverageDailyVolume%2CMoreInfo%2CBid%2CBidSize%2CAsk%2CAskSize%2CPreviousClose%2COpen%2CDayRange%2CFiftyTwoWeekRange%2CChangeFromFiftyTwoWeekLow%2CPercentChangeFromFiftyTwoWeekLow%2CChangeFromFiftyTwoWeekHigh%2CPercentChangeFromFiftyTwoWeekHigh%2CEarningsPerShare%2CPE%20Ratio%2CShortRatio%2CDividendPayDate%2CExDividendDate%2CDividendPerShare%2CDividend%20Yield%2CMarketCapitalization%2COneYearTargetPrice%2CEPS%20Est%20Current%20Yr%2CEPS%20Est%20Next%20Year%2CEPS%20Est%20Next%20Quarter%2CPrice%20per%20EPS%20Est%20Current%20Yr%2CPrice%20per%20EPS%20Est%20Next%20Yr%2CPEG%20Ratio%2CBook%20Value%2CPrice%20to%20Book%2CPrice%20to%20Sales%2CEBITDA"
        url_part5 = "%2CFiftyDayMovingAverage%2CChangeFromFiftyDayMovingAverage%2CPercentChangeFromFiftyDayMovingAverage%2CTwoHundredDayMovingAverage%2CChangeFromTwoHundredDayMovingAverage%2CPercentChangeFromTwoHundredDayMovingAverage%2CLastTrade%20(Real-time)%20with%20Time%2CBid%20(Real-time)%2CAsk%20(Real-time)%2COrderBook%20(Real-time)%2CStockExchange'"
    
        URL = url_part1 & Trim(Symbol) & url_part2 & url_part3 & url_part4 & url_part5
    ElseIf LCase(YahooFeed) = "b" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quotes%20where%20symbol%20in%20%28%22"
        url_part2 = "%22%29&diagnostics=false&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "c" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "d" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.quant2%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    ElseIf LCase(YahooFeed) = "e" Then
        url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.stocks%20where%20symbol%20in%20(%22"
        url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
        
        URL = url_part1 & Trim(Symbol) & url_part2
    Else
        ' return error message if YahooFeed isn't recognized
        GetQuoteYahoo = YahooFeed & " is an invalid YahooFeed parameter supplied to GetQuoteYahoo function"
        Exit Function
    End If
    
    ' In case you want to look at the XML
    If YahooParameter = "URL" Then
        GetQuoteYahoo = URL
        Exit Function
    End If
    
    ' pull in the XML stream
    Set YahooXMLstream = New MSXML2.DOMDocument
    YahooXMLstream.async = False                 ' wait for completion
    YahooXMLstream.validateOnParse = False       ' do not validate the XML stream
    
    fSuccess = YahooXMLstream.Load(URL)          ' pull in the feed
    
    If Not fSuccess Then                         ' quit on failure
      MsgBox "error loading Yahoo Finance XML stream"
      Exit Function
    End If
    
    ' iterate through the nodes looking for one with the name in YahooParameter
    GetQuoteYahoo = YahooParameter & " is not valid for GetQuoteYahoo function with YahooFeed " & YahooFeed
    
    Set oChildren = YahooXMLstream.DocumentElement.LastChild.LastChild.ChildNodes
    
    For Each oChild In oChildren

        If oChild.nodeName = YahooParameter Then
        
            GetQuoteYahoo = oChild.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 Str_to_Num(num_as_string As String) As Double

    ' The GetQuoteYahoo function returns string values
    ' This function converts numbers in string format to double

    Str_to_Num = 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

(2392)

This is not working for NSE stocks, Is there anyother work around? Please respond.
Posted by: Prathap   |   Nov 5, 2013 6:46 PM
George, Thanks for these functions and your blog. They have saved me many hours of trying to figure how to get quotes into my spreadsheet on my own. iGoogle is now down so it appears that we have to rely on the Yahoo apis. Unfortunately, it appears that Yahoo's api is buggy. For example, on my spreadsheet: =GetQuoteYahoo("YHOO") returns the error: "LastTradePriceOnly is not valid for GetQuoteYahoo function with YahooFeed a" however, =GetQuoteYahoo("YHOO","AskRealtime","b") return the correct value. Any insight would be appreciated. Hopefully we can find another xml stock quote feed. All the best in today's election. I grew up in Philly, but live in Northern Virginia now. I tell everyone it was the best place to grow up from all the other areas I have seen. Regards, Bob/
Posted by: Bob   |   Nov 5, 2013 6:37 PM
Hi George, Thanks for the help! Can the same (yahoo macro) be used for international stock quotes? It is not working for quotes on the NSE (National Stock Exchange India)- example ("NSE:WIPRO") Thanks
Posted by: Saboo   |   Nov 1, 2013 12:52 PM
Well done! Thank you for the work. I had to tweak it to fit what I needed, and had to bone up on xml - which was bound to happen anyway. Saved me a ton of hours of work. Here is a list of additional tables - at: github.com/yql/yql-tables/tree/master/yahoo/finance Most of them are the same, but it looks like there have been some adds since this was posted.
Posted by: mechd2119   |   Oct 18, 2013 8:22 PM
Awesome function!, just a weird thing, does anyone had problems retreiving CSCO's stock price?. On my case I'm getting the "error loading Yahoo Finance XML Stream" error. 10x, David
Posted by: David   |   Sep 4, 2013 6:46 PM
hi, what's YahooParameter should i use to get day's high,day's low, adj rice? i have been searching for hours and managed to guess the right YahooParameter for open price. i really appreciate your help. Thanks a lot. I hope you can see my question.
Posted by: Yuandong   |   May 28, 2013 12:07 PM
Jim, Good question but I'm afraid I do not use Macs for my analytical work. I would suggest searching the Stack Overflow blog, they are a great source of this sort of information. Other than that, however, I do not know. If you find out the answer please post it so that others will know. George Fisher
Posted by: George Fisher   |   Jan 15, 2013 8:16 AM
The XML libraries don't show up in Office 11 For Apple's Mac. Any ideas how to get a function working with only the libaries in Office 11? The function looks great.
Posted by: jim   |   Jan 14, 2013 6:06 PM
It looks like the problem lies in exercising the Yahoo server too hard. If you have more than one quote to look up, it's best to call this function in a controlled loop with at least a 1 sec delay. I changed my spreadsheet to do that and it worked just fine. Thanks again.
Posted by: Duke of Earle   |   Dec 31, 2012 10:28 AM
I too am seeing some buggy behavior. It seems a number of symbol inputs generate a bad url and the following error message: http://www.yahooapis.com/v1/base.rng" yahoo:lang="en-US"> Error Retrieving Data from External Service Most of them seem to be for Nasdaq stocks such as ADBE and AAPL, but some are NYSE such as TXN. I haven't found the bug yet, but will keep looking. In the meantime, I'll use the Google Finance script also. Thanks for all your good work and generosity George!
Posted by: Duke of Earle   |   Dec 28, 2012 12:09 PM
Lumute,

Thank you for all your work. By now, you know this function much better than I do myself. I must say that I have never had and problems in my daily use but I will research your information. Please do keep me posted on your work going forward.

George Fisher
Posted by: George Fisher   |   Dec 27, 2012 10:40 PM
Bad news, it seems this Yahoo API if very buggy and returns commans in some fields (like volumes that come with comma as the thousand separator and it is not quoted) that completely messes the output as the parser separates this as two different columns... Yahoo answer as you can see on the URL below is that this API is not supported, so back to google until they take theirs down in Nov 2013 and then to look for another solution... :-( http://developer.yahoo.com/forum/General-Discussion-at-YDN/Stock-Quote-API-returning-commas-in/1234765072000-6036c128-a7e0-3aa5-9e72-1af1871e1b41
Posted by: Lumute   |   Dec 26, 2012 10:35 PM
Awesome! Thank you, this is very useful. I added some code to get currency exchange rates and works beautiful! very cool to use the same function to calculate everything when trading on different currencies, etc... Just added this to the case before the last else:
ElseIf LCase(YahooFeed) = "x" Then url_part1 = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22" url_part2 = "%22)&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys" URL = url_part1 & Trim(Symbol) & url_part2
=GetQuoteYahoo(CAD,"Rate","x") returns the exchange rate for USD to CAD =GetQuoteYahoo(CADEUR,"Rate","x") returns the exchange rate for CAD to EUR
Posted by: Lumute   |   Dec 26, 2012 12:50 AM

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.