How to create Excel Web Query

Excel is a great tool and its web query feature is excellent. This is quite amazing that  I open my Excel and I get latest data downloaded from internet. In one Excel workbook I can have data from multiple web sites. No need to open browser and search for desired data. It might be you are watching your stock watch list in Office at refresh rate of  1 minute without opening a browser. It has some limitation about quality of data. Some time it may happen that desire data can't be downloaded into excel. In this blog I'm going to talk about

1. How to Create Web Query
2. How to Create Dynamic Web Query

-------------------------------------------------------------------------------------------------

How to Create Web Query

To start, First open Excel. Select Data --> From Web

  If you are using Excel 2003 then Select Data--> Import External Data--> Web Query

It will open a blank internet browser or with your default home page as:

 Now you can type here your desired site. I recommend to first open your site in separate internet browser or go to correct page on web site and copy URL and paste in Address bar. This will be the address of your web query. Here I have opened http://finance.yahoo.com/ and typed BSE and click on 'Get Quotes' , Then copied and pasted this URL to address bar. After clicking on Go button, I can see same page in New Web Query Form.  

 In this Form you can see some arrow mark in yellow box, These are data tables. Select your desired table(s). Data table will be selected and marked as below.

Now click on Import button. It will pop-up a dialog for location.

Set the desired location. You can set properties here or later. Clicking on properties will pop-up Data Range Properties

Here you can set refresh behavior of web query data. If you want to refresh data on regular interval, select Refresh every and select your interval time. You can also select to refresh data on opening of spread sheet.  Click OK to save and close this form. Click OK on Import Data. Data will be downloaded in sheet as below:

This is data downloaded without any forma. you can select format options present on New Web Query form. Options dialog as below:

Here you can select formatting styles and other options. Or you can do your own formatting as I did

It you may require to modify web query or refresh data, you can do by right clicking on data range or through tool bar menu items. Right click context menus are:

•  Edit Query: It will open Edit Web Query form similar to New Web Query form. You can change address or change data selection. You can also open Option dialogue from here.
• Data Range Properties: You can open Data Range Properties as shown above.
• Refresh : It will refresh data means it download fresh data from internet. Selected web query or All Web query can be refreshed from menu also.

Once data is downloaded in excel you can manipulate or refer to other work sheet.

I hope this will help in using Excel web query. Your comments are welcome. Please visit to my Website.

Thanks,

  Citizen