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.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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®ion=east
you can replace with
?name=["name","What product
name?"]®ion=["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.
|
|