bydesigner.blogg.se

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.