Software

Creating dynamic spreadsheets in Excel

Consultants who need to provide Web-based data to a client's users can use Excel's Web query functions to create dynamic spreadsheets. We'll show you how to do it in both the old and new versions of Excel.

As a consultant, framing your work in ways that are familiar to your client can really help to ensure your client’s satisfaction. Corporate users, for example, are used to being able to do analysis on the data that they receive, with many users getting their reports in Excel spreadsheets.

Excel 97 and above lets users make use of a Web query to acquire information from remote sources and load it into a spreadsheet. This can be used, for example, as a way to keep users up to date on Web pages that feature levels of inventory, sales, or product performance.

In this article, we’ll take a look at how it’s done in Excel 97, 2000, and XP. We’ll also examine how dynamic spreadsheets are formatted.

A closer look
Although Excel 97 does not have a built-in way to create Web queries, there have been improvements in the way Excel handles Web queries in each successive version. To create a Web query in Excel 2000 and XP, go to the Data menu and select the Import External Data/New Web Query option (Figure A).

Figure A


Excel 2000 presents you with this dialog box (Figure B) to select the Web address and formatting options. You have to view the source in your Web browser to select the tables that you wish to import; the table number reflects the order in which the table appears in the HTML code.

Figure B


Excel XP, on the other hand, presents you with a browser window in the dialog box, so you can select the tables you want to import by clicking the arrows next to those tables (Figure C). The formatting options are controlled in a pop-up dialog box.

Figure C


After clicking the OK button in 2000 (the Import button in XP) you will get a dialog box asking for the location to place the imported data (Figure D).

Figure D


Figure E shows the resulting spreadsheet:

Figure E


How Web queries work
Essentially, a Web query is just a plain text file written with specific formatting that has the ".iqy" file extension. This is an excellent opportunity for consultants to create text files on the fly for the user to retrieve over the Web and get instant ad hoc reporting.

Here is the text of the CNET_Example.iqy file. You can cut and paste the text and save it into a notepad. When you want to view the results, you can double-click on the file to launch Excel. Here’s what it would look like in your notepad file:
WEB
1
http://quote.bloomberg.com/analytics/quote.cgi?
view=extmult&version=quick_quote&ticker=cnet
Selection=5
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


Formatting Web queries
Here is the how the file is formatted:
  • The first line specifies the type, which right now can only be "Web."
  • The second line specifies the version, which right now can only be one ("1").
  • The third line specifies the URL for the page being used as the data provider.
  • If the page requires "Get" form input in order to provide results, the query string is appended to the URL.
  • If the page requires "post" form input, the post information is written on the fourth line.

Here is an example of a "Get" form query:
WEB
1
http://moneycentral.msn.com/investor/external/
excel/quotes.asp?
symbol=$INDU,$COMP,$TRAN,$UTIL,$DAX,$FTSE,
$HSI,$IIX.X,$COMPX,
$NI225,$CAC,$SOX.X,$IUX,$OEX,$INX,$STI,
$AOI,$TI300


and Figure F shows the spreadsheet that this query produced.

Figure F


You can even prompt the user for the parameter values of the form input fields as illustrated in the following example that asks for stock symbols. After this query…
1
http://moneycentral.msn.com/investor/
external/excel/quotes.asp?
SYMBOL=["QUOTE","Enter stock, fund or
other MSN MoneyCentral
Investor symbols separated by commas."]


…the user prompt is then listed in the parameter entry box as shown in Figure G below.

Figure G


Figure H shows the spreadsheet that this query would produce:

Figure H


Doing more with Web queries
There are additional options that can be added to control the content derived from the Web page. Here’s what the formatting would look like if, for example, you wanted to grab an entire page:
Selection=EntirePage

If, on the other hand, you just wanted to grab the first table from a Web page, you would use the following formatting:

Selection=1(The number specifies the table to get the information from, as listed in the order in which they appear in the HTML.)

The formatting specifies whether or not to use the HTML formatting from the Web page. If, for example, you specifiedFormatting=None, it would appear as a regular Excel spreadsheet.

Want to learn more?
For more information on using Excel with Web queries, see the following articles on the Microsoft Office site:
About retrieving data from a Web page Getting Data From the Web in Excel 2002


 
0 comments

Editor's Picks