Home	Case Studies	MS Office Tips and Tricks	Sample Code	Helpful Videos	Custom Development	Custom Training	Join Us	Contact Us
Search Our Site:

Advanced Web Queries

Last issue we looked at generating web queries using the Excel 2003 user interface.  The interface is easy to use (if you have 2003!) but it's a little bit simple.

If you willing to try a little bit of coding, you can make your queries a lot more powerful with the introduction of parameters. And, you can use web queries in the prior versions of Excel.

When you make an internet web query using the user interface, a small text file is created on your machine with the extension .iqy. You can use Explorer or Search My Computer to find these files. Don't double-click to open them, though! They'll open up in Excel. You'll want to open them using Notepad or Wordpad or your own favorite text editor.

Create a new web query using the same tool as in the last edition or just copy the code below. Use Yahoo! stocks as your source data. Look up the last year of Microsoft (MSFT) stock data.

The URL is http://finance.yahoo.com/q/hp?s=MSFT.Before you do the final step of importing, click on the icon to save the query and put the file on your desktop where it will be easy to find. The file contains the following:

WEB
1
http://finance.yahoo.com/q/hp?s=MSFT

Selection=22
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

We can leave most of the file alone. What we're interested in is the third line. Notice that the ticker symbol for Microsoft is embedded in our web query. It would be a lot more powerful if the query would ask us each time what ticker what we wanted data for, rather than having to make a different query for every ticker symbol! In other words, we need to replace the literal MSFT with a parameter.

A Word From Our Sponsor

Computer Books

It's pretty easy to do. We need the parameter name, and we need a polite message asking us for the information to use. The parameter name is always just on the other side of the equals sign from the literal; here, it's s, presumably for stock. Our polite message will be "What ticker symbol to use?" We wrap these two strings in square brackets and replace the literal with them.

The file above becomes:

WEB
1
http://finance.yahoo.com/q/hp?s=["s","What ticker symbol to use?"]

Selection=22
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

You can substitute any number of literal values with parameters as above. Whatever you see in the URL that looks like

?name=gadget&region=east

you can replace with

?name=["name","What product name?"]&region=["region","What sales region?"]

When you run or refresh the query in Excel, you will be asked to supply each value in turn and your query will return data based on only those values.