mindoff@microsoft.com

he 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://msdn.microsoft.com/library/officedev/odeopg/deconquerybuildingtools.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 your Web query pulls data from a static source.       To create your first Web query, open Notepad and type:

 http://www.microsoft.com/excel/Webquery/samples.htm 

 WEB
 1
 http://Webservices.pcquote.com/cgi-bin/excelget.exe?TICKER=["TICKER",
 "Enter your stock symbol for a detailed quote."]

Figure: Initial Launch of the Web Query

Figure 1 shows the initial launch of the Web query before it stores its returnset in the worksheet. This query is dynamic because users can input a stock symbol to determine the returnset. For instance, entering "msft" returns financial data for Microsoft. Inserting this value in the dialog assigns it to the TICKER parameter that the query passes to excelget.exe. The second entry in brackets determines the text for the prompt on the Web query dialog.       A weakness of the preceding Web query is that it only accepts one stock symbol at a time. What if you want to compare several? Figure 2 shows an HTML form at the PC Quote site that addresses this need. It invites users to input up to 20 symbols. The address bar shows that the form relies on excel.exe to process the request. Figure 3 shows an excerpt from the underlying HTML for the page in Figure 2. It adds emphasis to the form method (Post), and the name of the input box for storing the stock symbols that a user types (QUOTE0). Since the form uses the Post method, the corresponding Web query requires the fourth line of the basic syntax.

Figure 2: Getting Multiple Stock Quotes

The following two-line Web query reproduces the outcome of the form in Figure 2.

http://Webservices.pcquote.com/cgi-bin/excel.exe QUOTE0=["QUOTE0", "Enter up to 20 symbols separated by spaces."]

The first line references the location of excel.exe at the PC Quote Web site. The second line prompts for up to 20 stock symbols. This query references a dynamic data source with a dynamic Web query.       You can modify the same basic design so that it always returns information for a fixed basket of stocks. This approach uses a static Web query with a dynamic data source. The following Web query always returns data for just four stocks: Microsoft, Netscape, Novell, and Oracle: http://Webservices.pcquote.com/cgi-bin/excel.exe QUOTE0=msft+nscp+novl+orcl       The Web query syntax is flexible enough to permit a mixed static-dynamic Web query. This kind of Web query always pulls some specific data, but it also lets the user input parameter values to customize the returnset. The following Web query regularly includes financial data for Microsoft and up to 19 other stock symbols that a user enters:

http://Webservices.pcquote.com/cgi-bin/excel.exe QUOTE0=msft+["QUOTE0", "Enter up to 19 symbols separated by spaces."]

There is an option in the Web query syntax to accommodate the input of values for multiple parameters. When you run a Web query with multiple parameters, a separate dialog box prompts for each parameter. See the CNNfn Mortgage Calculator Web query for an example. When you try using this query, you'll see the problem with it. My opinion is that the Web queries best serve for one dialog, or at the very most a short list of dialogs. With a long list of dialogs, it is easy to forget your replies to previous prompts. Also, there is no way to go back and change your replies to a previous prompt. Two Custom Web Queries       The most obvious use for Web queries is to populate a worksheet with the most recent data available from some source. Figure 4 shows a Profit and Loss statement. It has the virtue of being dynamic and formatted. However, users must enter the sales quantity manually. As this number grows over a year or becomes more refined with the arrival of data from sales branches, the actual sales quantity can change. Web queries can automatically update a workbook with the most recent sales information every time a user opens it.

Figure 4: Profit and Loss Statement

Figure 5 shows an adaptation of the worksheet in Figure 4. It draws Sales from cell B1 in the WQData worksheet. Each time a user opens the workbook, the file automatically runs the Web query. This captures the most recent values of Sales in cell B1 of the WQData worksheet. The PandL- Styles worksheet recomputes the Profit and Loss statement with this value.

Figure 5: The Data Range Properties dialog

The panel on the left in Figure 5 shows the setting that permits the Web query to run automatically. Notice the check in the "Refresh data on file open" checkbox. Microsoft Excel does not turn this option on by default. You can open the dialog in one of two ways. First, when creating a Web query, click the Properties button on the dialog that lets you set the top-left corner of the Web query in a worksheet. Second, right-click in the cell range of a previously designed Web query and choose the Data Range Properties command.       The Web query merely references a text file with an .html extension. LatestSales.html contains a single number, the most recent sales data. The Web query calls the MyAsps folder of the Cabxp5 Web server to retrieve the most recent sales estimate. A single line in the LatestSales.iqy Web query accomplishes this cool result.

http://Cabxp5/MyAsps/LatestSales.html

Figure 6 contains the HTML for a form that calls the ASP file in Figure 7. These two files together allow a user to input all or part of a last name and retrieve all matching last names with their telephone extensions. The example works with the Northwind database, but you can easily revise it for any database by changing the database name in the connection code at the top of Figure 7.       The action attribute in Figure 6 points to the file listed in Figure 7. This is an important part of the Web query. The method specifies how the parameter setting in the LastName box merges with the http reference.       The actual Web query appears as a single line. It starts with the Web server name, and then the folder holding the ASP file in Figure 7. This file actually does the lookup and display of last names and telephone extensions. LastName is the parameter that the HTML code in Figure 6 passes to qryTelExt_ 13.asp. Then the Web query sets the prompt for LastName and stores the user's reply in it.

http://cabxp5/myasps/qryTelExt_13.asp?LastName=["LastName", "Please enter the last name for the extension that you want."]

Figure 8 shows the returnset when a user enters the letter D into the dialog and clicks OK. Notice that the Web query's second argument in square brackets sets the prompt for the dialog. Since two employees have last names beginning with the letter D, the returnset shows two names and telephone extensions. If the dialog in Figure 8 contained the letters Da instead of just D, then the returnset would show the telephone number for the sole matching employee—Davolio.

Figure 8: Last Name Lookup

Microsoft Excel 97 Does the Web       Web queries are a powerful means of enriching spreadsheets with Web content. They make it easy to add content from any Web site directly into a spreadsheet for further analysis and archiving. Web queries can work with CGI programs as well as ASP and IDC/HTX files to bring dynamic data from an intranet or the Internet into a spreadsheet. Despite the power of Web queries, they are easy to program. A Web query with a single line can bring dynamic, user-specified content into a worksheet. You can even set Web queries so that they launch automatically whenever a workbook opens. Finally, they have an object model with methods and properties. The article "Corner Office: You Can Hit the Web Running with Microsoft Access 97," (MIND, September 1997) includes an example that demonstrates how to dynamically create a Web query and update its contents on a worksheet.

From the February 1998 issue of Microsoft Interactive Developer.