![]() Note the use of the FindChildNodeName function to parse the result: The function takes a single argument: the stock symbol whose quote data you wish to retrieve. MsgBox " GetQuoteXmlFromWeb Error " & Err.Number & " : " & Err.Description Set GetQuoteXmlFromWeb = oChild ' Either the node or NOTHING ' error handlers MsgBox " error loading Yahoo Finance XML stream: cannot find 'results'" Exit Function End If ' If this works, we will have the XML quote data node - our target Set oChild = FindChildNodeName(oChild.ChildNodes, " quote") MsgBox " error loading Yahoo Finance XML stream: cannot find 'query'" Exit Function End If Set oChild = FindChildNodeName(oChild.ChildNodes, " results") MsgBox " error parsing Yahoo Finance XML stream" Exit Function End If ' Structure is: (3 children in) to get to our quote params Set oChild = FindChildNodeName(QuoteXMLstream.ChildNodes, " query") MsgBox " error loading Yahoo Finance XML stream" Exit Function End If ' Turn it into an XML document Set QuoteXMLstream = New MSXML2.DOMDocumentįSuccsss = QuoteXMLstream.LoadXML(QuoteXMLHttp.responseText) URL = URL & " &diagnostics=false&env=store%3A%2F%%2Falltableswithkeys" ' pull in the XML stream Set QuoteXMLHttp = New MSXML2.XMLHTTP60 ' create the URL that requests the XML stream from Yahoo Finance ' GO TO TOOLS/REFERENCES and include Microsoft XML (tested w/ v6) ' Returns the XML Node List (or Nothing on error/failure) Dim QuoteXMLstream As MSXML2.DOMDocumentĭim fSuccess As Boolean Dim URL As String On Error GoTo HandleErr ' This is an Excel VBA function which returns XML data on a stock from ' Yahoo Finance's XML stream. The heart of this VBA code is the function GetQuoteXmlFromWeb.įunction GetQuoteXmlFromWeb(stockSymbol As String) As MSXML2.IXMLDOMNode Important note: Before you can use this VBA code in Excel, make sure you've included Microsoft XML v6 (from within the Microsoft Visual Basic app, click on the Tools Menu, then click on References). Further disclosure: all work was also on Windows 7 圆4. Once more for emphasis: I've tested these macros on Excel 2007 (32-bit) only. The VBA code I developed is designed to be used in a spreadsheet where calls are made serially for each stock symbol, resulting in the return of multiple quotes with access to much more data than I'd ever found on web sites I could access with Excel's Web Queries. One good way to get a feel for what YQL can do is to play with the console here's a link that will, when you press the TEST button on the target page, show you the XML returned when quotes are requested for Yahoo, Apple, Google and Microsoft. ![]() With some trial and error, I came up with this set of VBA macros that I am now using in Excel 2007 (32-bit). There were other articles too, but I did not record all of the references (apologies). I found some code that made calls through YQL (the Yahoo Query Language) to download data directly, such as this article, that gave me some ideas. I found several references to Yahoo Finance, both for historical quote data and for downloads via CSV files (including this add-in), but nothing that would allow data downloads for multiple stocks (and without intermediate files). Research for other sites turned up the aforementioned, abandoned Google Finance API. In the last few months, the most recent website I used for multiple quotes made some change that rendered my spreadsheet and its web queries non-operational. As time went on, however, more and more websites made it more and more difficult to use Web Queries, at least for the sort of data I wanted for multiple stocks: Open Price, High Price, Low Price, Current/Closing Price, Volume, Time of last Trade.įor a while, Google's finance API was a favored alternative, however Google recently shut that down in favor of using built-in Google Docs functionality. It was subject to occasional hiccups when page design/layout changed, but was often easy enough to repair. Where before if you wanted live data, like stock quotes, you had to write your own "screen-scraper", now you could let Office do the heavy lifting. Backgroundīack when Office 97 was launched, Microsoft added a cool new feature: Web Queries. Provides a set of VBA macros and functions to download and parse live stock quotes from Yahoo Finance via the Yahoo Query Language. Download VBA export file with source code - 4.2 KB.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |