Import Web Data to Excel

MS Excel allows you to import content from almost any website you want to. Once you provide a link, it will look for tables inside that webpage and the ones selected by you are imported to the worksheet. This facility simplifies the complex process of parsing a web page via an application programming language to a few mouse clicks.

I am going to demonstrate a simple example to fetch historical data for Google Prices from Yahoo Finance. Following is the link of webpage for the same

http://finance.yahoo.com/q/hp?s=GOOG

Now to fetch this data to your Excel sheet, please follow the below steps

Go to Data Tab of ribbon and click on "From Web" option of top left hand side

The following pop-up will appear

Type the url from which you wish to fetch the data which for the purpose of demonstration is 

http://finance.yahoo.com/q/hp?s=GOOG, then click on "GO"

You will see that the webpage has opened in the small window. 

Now scroll down to locate the historical data which we wish to fetch here, click on  which will turn to   a , which means that only this table will be pulled to excel workheet. Click on Import to get the data

You will see a small pop-up with asking a location where you wish to copy te data. Click on "OK" to import the data.

You will see that the table of the webpage is imported successfully to the sheet.

At this point you might be thinking that the same could have been achieved by simple copy and paste. However this facility can prove very powerful when used with VBA Macros. If the data on the webpage is dynamic and you wish to refresh the data on sheet after an interval, you can do that following below steps

Right click on the data fetched and click on "Data Range Properties"

The following pop-up will appear with certain options with their default values.

You can check the "Refresh Every" box to specify an interval after which you want the data on the sheet to be refreshed.

This tutorial is a basic building block to make use of this facility in provide simple solution to complex issues which I will be covering in other tutorials.