Excel Web Query Secrets Revealed

Excel Web Query - What in the world is that? If you are like the other 99.9% of MS Excel users, you probably have never heard of microsoft excel web queries (note: statistic made up).

Excel web queries are powerful! Web queries are basically like having a browser built into Excel, which strips web pages of useless stuff and gives you access to the essential information. You can then use Excel formulas (like =A1/B2) to work directly with the data you've downloaded. And you don't have to know anything about perl, cgi, php, javascript, etc.

To the normal every-day Excel user, a web query is nothing more than a cool trick. But to creative-minded Excel users, with some HTML knowledge behind them, web queries can lead to a drastic increase in productivity.

Getting Started with Sample Excel Web Queries

To get started, take a jog over to Microsoft's page that explains the basics of web queries: "Getting Data From the Web In Excel".

The example web queries that come with Excel are usually for importing stock quote data into Excel. For rapidly changing data like stock quotes, the web queries can be set to automatically refresh every 5,7,21,40,... minutes.

For more information about using web queries to download Stock Quotes into Excel, take a look at the Excel Stock Quotes Template.

Although importing stock quotes into Excel happened to be the reason I came across Excel web queries, I have since found many other ways to use them.

It is not necessarily the web query itself that is THE big secret that this article is about. Instead, it's the ability to make a DYNAMIC Web Query!

By dynamic, I'm not talking about using "refresh" to update the data. What I mean is the ability to change the parameters of the web query, such as typing in a new stock symbol into a cell and having the table update automatically.

If you find yourself using the internet to gather data by filling out forms and copying and pasting data over and over, an excel web query might be the solution to your monotonous woes. It's not always going to be better, and it's not always going to work, but it's worth a try, especially since a simple web query could drastically boost your productivity!

Excel Web Query ".iqy" Files

The real key to creating a dynamic excel web query is to work directly with a ".iqy" file. In it's basic form, the ".iqy" file is simply a TEXT file with three main lines:

You can create the file using a simple text editor! You don't even need to know what the first two lines are for. I knew once, but I've forgotten. I just keep putting them there out of habit.

The second line is the important one! It's simply the URL. Notice that is contains two parameters. If you don't know what parameters are for, just browse the web for a while and pay attention to what shows up in the address bar of your browser. Go to Google.com for instance, and look up "excel web query".

Parameter name / value pairs are listed after the "?" in the URL and are separated by an "&".

Make the Web Query Dynamic

To make the query dynamic, simply replace the value of each parameter in the web query file (queryname.iqy) with:

["paramname","Enter the value for paramname:"]

Want to see how this would apply to a Google search? The form that I used above consists of HTML code that looks like this:

Notice that "q" is the name of the parameter, and the action tells you what the URL should be. The dynamic web query file for a simple google search would look like this:

Let's Create a Web Query

1. Open up a text editor and copy the Google example. Save the file as GoogleSearch.iqy
2. Open up Excel and enter a search term in Cell B3
3. Open the web query file you just saved (GoogleSearch.iqy)
     Excel 2000: Go to Data --> Get External Data --> Run Saved Query...
     Excel XP: Go to Data --> Import External Data --> Import Data ...
4. When prompted for the search term, enter: =B3
     If that doesn't work right off, you can edit your web query later, using the External Data Toolbar (Tools --> Customize ...)

Play around with the web query options and properties (using the External Data Toolbar). You will find that you need to select a cell inside of the web query before you can click on the toolbar buttons.

For the Google Search example, try selecting just the portion of the page that tells how many sites matched your search. If you need a little help, or are just lazy, click here.

When you have the query looking just the way you want it, save the Excel web query (an icon or button for saving the web query is in the "Edit Query" window. When you save the new query, your ".iqy" file will include the options you have selected.

Another Example:

Want to try another interesting web query? How about a link to Overture's keyword suggestion tool? It tells you the popularity of various keywords (in terms of searches per month). Click Here for the IQY File.

After a little formatting, you can do some fun stuff with the query. The following link is an example Excel spreadsheet (.xls file) containing the dynamic web query:
Overture Keyword Suggestion Tool Example

Become a Web Query Wiz

Granted, web queries are obviously not the solution to every problem, but when they DO work, it sure is great fun!

Take a few minutes to think about what mundane or repetitive tasks YOU do on the internet, particularly cases where you go to the same sites over and over and fill out the same on-line form again and again. If you find yourself copying and pasting or re-typing information into Excel so that you can perform calculations with the data, you may have found an ideal use for an excel web query.

There are two main things that you need in order to become a Web Query Wiz.

1. A working knowledge of Excel formulas. By this, I mean the ability to use text-manipulation and other formulas. A GREAT book for both learning and reference (and the one that I use all the time) is John Walkenbach's "Excel Formulas". Go get it now! If you are an avid Excel user, it will probably be the best investment you will make for a long time! The only problem I have with the book is that I need TWO copies - one for work and one for home.
2. A working knowledge of HTML. This is the hard part unless you are a web designer or programmer. The articles listed in the side bar will help to some extent, but ultimately this issue was the reason why I didn't write a more detailed tutorial. Something that will be a great help is to learn about how <form> tags work. Here is my favorite reference.

Web Query Solutions for Webmasters

If you have developed a free on-line tool (particularly tools which display tables of information or statistics), then you may or may not want people to use web queries to access it.

Please click here for more information.

Share the Joy

Everybody likes a good Excel tip now and then.

Do you have friends or work associates that use Excel? (Who doesn't?) Send them a link to this page, and share your new-found Excel skills.

Happy Excelling!

Jon Wittwer, President
Vertex42, LLC