Geeks With Blogs

Lance's TextBox » About Me » My Resume »Twitter

Here is an example of a dynamic Excel spreadsheet that populates itself with data from an RSS feed of stock quote details. Each time you open the document, it will automatically reload the data from the feed. Also the feed, while open, will even auto-refresh periodically (whether or not this happens and the interval at which it happens are all configurable inside Excel). The stock quote feed itself is generated using the RSSBus yahooStockQuotes operation (pictured below) which consumes a Yahoo service.

yahooStockQuotes RSSBus operation

You can create a dynamic excel sheet that populates itself based on data coming from a web query. To do this, you just create an .iqy file (what Excel calls a "Web Query File"), which needs at least three lines:

WEB
1
[a URL to get data from]

RSSBus feed formats This is what RSSBus uses to generate dynamic spreadsheets of data coming from feeds. We simply use the URL of an RSSBus script.

You can also have excel prompt for input parameters. To do this, modify the url like so:
http://server/file.xml?parameter=["title","prompt"]

After you double-click the iqy it will prompt you for the parameters prior to populating the data. You could take this another step by providing the input parameters in a cell by using the Query Parameters dialog of the External Data toolbar of Excel.

Technorati : excel, iqy, rssbus

Posted on Monday, May 7, 2007 2:05 PM General | Back to top


Comments on this post: Excel's external data capabilities (web query files)

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Lance Robinson | Powered by: GeeksWithBlogs.net