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:-

WEB
1
http://www.amazon.com


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.

sub geturl()
workbooks.open filename:="http://www.amazon.com"
end sub

​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama