Import data in to Excel using Web Query

Microsoft Excel has many capabilities and one such is Web Query. Some times we may need to import data in to Excel from a website for analysis, for this normally people will go to that website and copy the required data and paste it to excel, remove unwanted formatting’s that copied along. This will be OK if the data is needed one time and the data is not going to change.

What if the data is dynamic and updated daily by the website?  Repeating the above process daily is cumbersome.This is where Web Query comes in to picture.

With Web Query, we can automatically import the data from the website with just on click.

How it works?
We create a web query in Excel and will define the website and what data needs to be imported and in which sheet to import. This is one time job, once done we can simply refresh the data as and when required.

Only HTML tables can be effectively imported.
The website must support cache
Access to the website must be open i.e. No login is required.
The data must be represented in HTML tables.

Here are the steps.

For this demo, we will import data from our website.

Step 1:
We created a new excel workbook with name ‘WebQuery-Demo’ and renamed a sheet to ‘BHAV’ (You name it anything)

Step 2:
Select cell ‘A1’

Go to Data tab –> Select ‘From Web’
This will open up a Web browser.

Step 3:
Enter the Url in the address bar and press Go.
The requested web page will be loaded in the browser with yellow arrow icon on the left side of the page.
If the web page has multiple tables/data, then you will find multiple arrow icons, you need to exactly select the required table. Do not select the arrow (the top most one) which says ‘Click to select entire page’, this will select the entire table/data of the webpage and unwanted things will be imported in to excel.

The Url which we loaded here has only one table, so you will find two arrow icons i.e. one for the entire page (top one) and one for the table (table arrow icon located at the end of the table, some page will have at the top of the table.)

Just select the arrow icon of the table and click on Import button, this will open up ‘Import Data’ dialog.

Step 4:
We need to define properties like name of the web query, scope etc.
Refer the below picture and define as exactly. The web query name must be unique for each web query.
Once done press OK to exit the ‘Properties’ dialog.
Press OK in the ‘Import Data’ dialog to complete the web query setup.
As soon as you press OK, data will be fetched from website.
Here after, whenever you need fresh data, just press ‘Refresh Data’ as show in the picture, this will fetch latest data from the website.

Like this you can create as many as web query  required.

That’s it…!



.Net Programmer. AFL Coder. Author of KiteDotNet & KiteXL.


Leave a Reply

Your email address will not be published.