A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Retrieving a web page into EXCEL
Here are a couple of ways to retrieve web based data into an excel spreadsheet. The first uses a little known method called the web query document while the second uses standard VBA macro. Both methods assume that you have a connection to the web.
1) Web Query Document
A web query document is a just a plain text file - like you'd create with notepad - with an extension of .iqy which typically contains 3 lines of text as in the example below:-
As far as I'm aware the first two lines are the same in all web query documents while the third is obviously the URL of the web-site your interested in retrieving. Note that this retrieves all the data on the web page and is in a fairly messy format. Its likely that you will need a second sheet in your spreadsheet which will refer only to those data you're interested in and generally tidy things up a little.
To run the web query first open up notepad or other text editor and type in and save your web query document. Next open up excel and click Data->Get External Data->Run Web Query. You will then be presented with the usual File Choose dialog box from which you can pick your web query document. That's it.
2) VBA Macro
I'm not sure which version of EXCEL this could first be used, but here's a snippet of code to show how easy its done.