|
PureBytes Links
Trading Reference Links
|
from
http://www.microsoft.com/mind/0298/corner0298.htm
- 0 -
mindoff@xxxxxxxxxxxxx
Rick Dobson
Web Queries in Microsoft Excel
The Web is a treasure trove of data; Microsoft® Excel is a data-analyzing
machine. It's a natural fit. Microsoft Excel 97 comes equipped with special
tools to fulfill its Web mission: a hyperlinks collection and a Web toolbar
to ease jumping back and forth between its workbooks and the Web. This
month, I'll focus on a specific area of Microsoft Excel 97 Web connectivity:
Web queries. Web queries are a very rich tool for bringing intranet and
Internet data into Microsoft Excel worksheets.
Web Queries and How to Get Them
A Web query is an extremely flexible and easy method of importing data into
a worksheet. You can use Web queries to pull data from Web forms that
reference CGI programs, static data from a Web site, and dynamic data from
either IDC/HTX or ASP files.
Web queries are short text files (at most four lines long) that reference a
Web site and a program or file at the site. You can write a useful query in
a single line. If you can read HTML, you can also construct a Web query that
prompts for the information on the form.
Microsoft Excel 97 ships with pcquote.xls. This workbook references a Web
query named Detailed Stock Quote by PC Quote, Inc.iqy in the \Program
Files\Microsoft Office\ Queries directory. The .iqy extension is the default
for Web queries, and the Queries directory is the default location for
storing them. To get a stock quote, enter a stock's ticker symbol in a
worksheet cell, then launch the Web query by clicking a button on the sheet.
The worksheet pulls the most recently available quote for any single stock
from the PC Quote Web site.
You can find a large selection of sample queries to play around with at
http://www.microsoft.com/MSExcel/WebQuery/Samples.htm.
Web queries are available for downloading from leading data vendors,
including PC Quote, CNNfn, Accutrade, and Yahoo. They allow you to look up
stock quotes, obtain currency exchange rates, list money market rates, and
even compute mortgage payments.
There are actually two distinct procedures for running a Web query. The
first time you use a query, move to a blank worksheet cell with enough empty
space below and to the right to accept the returnset from the Web site. I
recommend importing the data into its own worksheet tab. The rest of your
application can reference the data from the Imported Data tab. The Choose
Data | Get External Data | Run Web Query command opens a dialog with all the
Web queries that you currently have saved in the \Queries folder. Select a
Web query and click Get Data. If you choose the Detailed Stock Quote by PC
Quote, Inc., Web query, you can then type in a stock symbol to get the last
transaction for that company and related information.
To run the Web query the next time, just move to the point in the worksheet
where the Web query deposits its data, then invoke the Data | Refresh Data
command. While you cannot use the Choose Data | Get External Data | Run Web
Query command in the cell space of a previously run Web query, you can move
to a new blank spot and launch the command. This allows you to track quotes
successively over time or get detailed stock quote information on more than
one company—several sample Web queries let you pull data on multiple stocks.
Web Query Syntax and Type
There are two basic types of Web query: static and dynamic. A static Web
query always asks for the same data. A dynamic Web query allows the user to
set some parameters and modify the data that it returns. You can also design
Web queries that always pull some static data, but let users specify
additional data to come down as well. There are a maximum of four elements
in a Web query, each entered on a separate line in the query file: type of
query (optional), version of query (optional), URL (required), and POST
Parameters (required when referencing forms with the Post method).
You can use your favorite text editor to create Web queries; remember to
save them with an .iqy extension. One convenient storage location is the
\Program Files\Microsoft Office\Queries folder, but you can use alternate
folders if you want to bunch a large collection of related Web queries in a
folder of their own.
The type of query and version of query parameters don't mean much right now
because the only supported values are WEB and 1, respectively. If you choose
to input the version, then your Web query must also specify the type. (As
with any value in a query file, follow type and version lines with a
carriage return.) Since these lines are optional and they currently have
only one possible value, I strongly advise that you don't use them.
The third line specifies the URL of the source from which your Web query
pulls data, whether it's static or dynamic. A static source can be a simple
HTML file. A dynamic source can be an ASP file or CGI script. Do not confuse
the type of source with the type of Web query. A static source always
returns the same data, but a dynamic source can return different resultsets
depending on its input. You can use either a static or dynamic Web query
with a dynamic source, but a static source requires a static Web query.
Web queries require a fourth line only when you reference an HTML form with
a Post method like a submitted form. Use this line to designate the
parameter values that the form passes to its action attribute. When sending
parameter values to a form with a Get method, you do not need this line.
This line is also not required when...
Continued in the February 1998 issue of Microsoft Interactive Developer. Get
it at your local newsstand, or better yet, subscribe.
http://www.microsoft.com/workshop/author/default.asp and
http://www.microsoft.com/sitebuilder/archive/starters/starts0220.htm.
----------------------------------------------------------------------------
----
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.
|