How to Create Excel Dynamic Web Query

In this section we will go through How to Create Dynamic Web Query in Excel.
Before staring this you should have knowledge about Excel web Query. You can refer to my previous post Excel Web Query.
There are 2 way to create dynamic web query:

1. By Macro: VBA program contains the logic to create dynamic web query and fetch from the internet. For more detail please refer to MicroSoft site Or ExcelForum.
2. By Parameters: Macro is not preferable for small task. If website permits simple query with parameter, It is always better to use this method.

I'm going to explain about How to Create Dynamic Web Query by Parameters means by Using values from cells.First we learn about how to Create Web Query (iqy) Files. You can also refer to Microsoft site for this. Or Follow following steps:

1. Open any text editor, such as notepad
2. Type "WEB" in first line
3. Type Version. example "1"
4. Type Website address without parameter. If full website address is 'http://finance.yahoo.com/q?s=MARUTI.NS' here parameter value 'MARUTI.NS'. so we type "'http://finance.yahoo.com/q?" in 3rd line
5. On last line we give name of parameter and text for popup dialogue as I've give 'stock1' so 4th line will be "s=["stock1","Please enter a stock symbol:"]"
6. Save this file with extension .iqy

I save as Stock.iqy and my file look like

WEB

1

http://finance.yahoo.com/q?

s=["stock1","Please enter a stock symbol:"]

Above stock.iqy  file will download given company quotes that can be used to create watch list or portfolio.

How to use this dynamic web query (Iqy) File:

For Excel 2007:
Open Data--> Connections

Workbook Connections will be Open. Click on 'Add' button and select your *.iqy file.

 Set connection properties by clicking on 'Properties' button.

Close this form.
Select Empty cell in Excel and Open Data--> Existing Connections.

Existing Connections form will be open as below. Select your iqy file and click on 'Open' button. Here you can select *.iqy file and open as well.

Select location. After selecting location another dialogue will be sown for parameter address. Point cell where are you like to enter you parameter value.

Now web query will fetch data as per this cell value.
In Excel 2003 these initial steps are little different. Iqy file can be selected from Data-->Import External Data--> Import Data

And select iqy file and rest steps are same as Excel 2007.
You can edit web query parameter as below

Parameters dialogue box will open here you can change text, parameter location.

I think I've provided steps to create dynamic web query with 1 parameter, similarly multiple parameter query can be created. You can download Excel Example from http://searchadres.com and Stock.iqy from Here

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