Web pages often contain information in tabular format that is perfect for analysis in excel. There are 2 ways to import such tabular data from webpage to excel.
- Copy and Paste the tabular data to excel
- Fetch tabular data using Excel's inbuilt feature
The earlier approach of copy/pasting seems easy to us but it's not that much effective when you need the data in excel sheet to be synced with the web page continuously. Here comes the latter approach of using excel's inbuilt feature in picture.
This post covers the second approach - Fetching data from web page to excel. Excel has inbuilt support to parse and import the tabular information from web pages to excel sheet. It is highly effective way to keep data synced between web page and local excel sheet. Based on your needs, you can configure your sheet to be synced -
- At specific time intervals
- When the sheet opens
1. Create a new excel sheet. Go to Data tab and click on From Web button.
 |
| Image 1 |
2. Follow the below points
- Paste the web address in the address box
- Click the Go button
- Wait until page gets loaded completely and then select the table that needs to be fetched by clicking on yellowish arrow.
- You can specify formatting options. See the 4th optional step in the following image.
- Click the import button.
 |
| Image 2 - Importing data from web page |
- By clicking upon the Import button you will be prompted cell reference to put the data. Input your preferred cell location into location box and [Optionally, you can set refresh interval to sync the excel sheet data with the web page data automatically - click on the Properties button shown in Image 3 will pop-up a new window for the advanced settings - see the Image 4] click Ok.
 |
| Image 3 |
 |
| Image 4 - Optional settings |
That's it! The excel will start fetching the data from the web page. The process completion may take some time based on the size of data needs to be fetched. The following Image 5 shows example of imported data from Stock Exchange website.
 |
| Image 5 |
You can refresh/sync the excel sheet at any point of time by clicking the Refresh button (see the Image 1) from the menu Ribbon. Just make sure that you are connected to internet before updating the sheet!