This documentation is archived and is not being maintained.

Well-kept Secret

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.


Aa155714.OfficePro04(en-us,office.10).gif

Tactic

Excel 2002 | Web Queries

Excel's Web Queries Enable You to Populate Worksheets from Web Sites

When I talk to people about Excel Web queries, I feel like the character Cliff Clavin from the television show Cheers. He would preface his obscure tidbits of wisdom with the phrase, "It's a little-known fact ..." That applies in a discussion about Microsoft Excel's Web queries. The big difference between Cliff's nuggets of knowledge and a conversation about Web queries, though, is the usefulness of the information. Cliff always seemed to be fumbling for some proverbial relevance, but Web queries, on the other hand, are more than relevant for anyone wanting to use data from the Web in Excel.

Three versions of Excel have been released since Web queries' introduction, and yet they remain one of the best-kept secrets in all of Office. As I talk with users, enterprise developers, conference attendees, and even writers, I'm shocked to find they don't use Web queries. Many people don't even know Web queries exist. Perhaps a look at what they are and how they work will help people understand why they should care, and, finally, the secret will be out.

Data Everywhere

As you know, Excel is an excellent tool for analyzing data. With data in Excel, you can chart, sort, apply AutoFilters or Advanced Filters, implement grouping with outlining, use PivotTables reports, build scenarios with a wizard, and more. Basically, you can manipulate data in so many ways with Excel that you might not even recognize that data when you are done.

There are many ways to get data into Excel. Of course, there's the manual way in which you just type or cut and paste from other sources. For a little more sophistication, you can use Excel's built-in data-form feature or add your own UserForms with Visual Basic for Applications (VBA) to get data into cells. If the data is available in other Excel workbooks, you can build links to them to get current data. You can import files from other applications and you can get data via OLAP cubes or ODBC data sources. In short, there are myriad options for getting data into a worksheet.

Generally, though, people tend to overlook the option of using the Web as a data source for Excel, be that source the Internet, an intranet, an extranet, or a Web Service. But they shouldn't. Web queries are an easy, yet remarkably flexible and predictable way of bringing data into Microsoft Excel from anywhere on the Web. You can point a Web query at any HTML document that resides on any Web server - or even on a file server, for that matter - and pull part or all of the contents back into your spreadsheet. Web queries are not limited just to htm files and will work with any file that can be rendered as HTML. So, you can use a Web query to pull back XML documents as data to crunch in Excel. Or, you can create your own Active Server Pages to offer dynamic data your Web queries will target. When you start using Excel's Web queries, you will realize they are almost as limitless as the Web is.

You may be thinking that, in addition to using Web queries to pull documents over HTTP into Excel, you could do the same thing from Excel by going to File | Open. It's true you can open Web documents into Excel, but Web queries offer a lot more flexibility than a traditional File | Open command. For starters, a Web query allows you to add data into an existing workbook directly where you want it. File | Open leaves you with an entirely separate workbook. Other advantages of Web queries are that you control features, such as formatting options, refresh intervals, and the ability to execute parameterized queries.

Short & Sweet

Although Web queries have been around since 1997, they have changed tremendously. I'm using Excel 2002, but if you're using an earlier version than that, don't worry. Quite a bit of this information applies to prior releases, but there are definitely some exceptions and limitations that apply to the older queries.

To create a Web query in a worksheet, select Data | Import External Data from Excel's menu. Then, select Import Data to use an existing Web query or select New Web Query to build a new one. If you select Import Data for an Office installation right out of the box, you may select one of the three existing MSN Web queries Microsoft ships with the product. As you can see in FIGURE 1, the Web queries are the ones with the iqy file type and that have the little icon that looks like a globe over two documents. If you select one of them and accept the defaults on the trailing dialog boxes, you quickly will have refreshable financial data straight from Microsoft's MSN Web site.

Aa155714.vba200206cm_f_image002(en-us,office.10).jpg
FIGURE 1: Web queries have the file type iqy and have an icon that looks like a globe over two documents.

My personal favorite is the MSN MoneyCentral Investor Stock Quotes.iqy Web query. If you own common stock or just like to follow along, this Web query lets you stay up-to-date with current pricing and some basic fundamental data about individual stocks.

What this, in turn, allows you to do is build a lightweight but inexpensive, nearly real-time portfolio model right inside Excel. I say nearly real-time because most vendors that offer market data apply a 15- or 20-minute delay on the information. That includes the vendors that offer pre-built Web queries, such as MSN or PCQuote. But, remember, the data is free and requires no code to include it in your solutions. FIGURE 2 shows an application I built.

Aa155714.vba200206cm_f_image004(en-us,office.10).jpg
FIGURE 2: You can use a Web query provided by Microsoft to track your stocks in near real-time.

It turns the underlying Web query data into a user-friendly report that allows you to manage your stock portfolio, even if it's just a pretend one. The Web query actually is being returned onto a hidden worksheet behind the glossy sheet and uses standard Excel formulas, not code, to present it to the user. I simply added a couple of formulas around the data to let you enter the number of shares held and follow your daily gains or losses. (See the end of the article for details about how you can download this model.)

Roll Your Own

If you have never created a Web query, you are going to be shocked at how easy and flexible it is. Once again, starting from Data | Import External Data on the Excel menu, select New Web Query. You will be presented with the New Web Query dialog box FIGURE 3 shows.

Aa155714.vba200206cm_f_image006(en-us,office.10).jpg
FIGURE 3: Although this looks like a browser page, it's actually the Web Query dialog box.

This resizable dialog box, which was enhanced greatly in Excel 2002, offers a browser experience for users to navigate Web pages and pull out pieces of a given page. All you have to do is enter the URL of the page in the address bar, hit the Go button or force a refresh, and the dialog will navigate to the page. Once the page draws, the next thing you will notice is little, yellow arrows that appear throughout the view window of the page. These arrows point to the independent tables inside the HTML of the page, and you can select any combination of the tables you want returned into your spreadsheet by clicking the corresponding arrows.

I selected http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&Symbol=msft&SUBMIT1=Go as the URL in FIGURE 3, and I've checked the table-marker arrow next to Quotes delayed 15 minutes. Clicking that arrow turns it green, and the table region it represents is shaded in a purple hue to designate its selection. The great thing about this arrow-selection feature is that you can choose from a page only the content you really need. For example, you can return just data and avoid navigation menus or advertising. This brings up an important issue: Allowing this much flexibility also means that if the structure of the target Web page changes, your query likely will need to be changed, also. If you want to avoid that uncertainty, select the uppermost, left-hand arrow on the page to return the entire page. Then, if you want specific data, you'll have to parse the results.

Once you have decided on the content to import, Excel prompts you to decide where you want to place the results.

Format Formalities

Your choices range from the currently active cell to having the query retrieved into an entirely new workbook. Web queries also offer many other options. For example, while you are in the New Web Query dialog box, at the very end of the menu bar, you can pull up the Web Query Options dialog box (see FIGURE 4) and manage some of the import properties of the query.

Aa155714.vba200206cm_f_image008(en-us,office.10).jpg
FIGURE 4: The Web Query Options dialog box enables you to specify the data's format and other settings.

These include whether or not you want the source to be brought in as plain text; rich, formatted text; or the full, HTML formatted version. Usually, but not always, you should choose one of the first two options. If you're just after data - and I'm talking about Excel - the Full HTML Formatting option can be disruptive to your spreadsheets because it applies merged cells in Excel to represent commonly used nested tables in HTML. So, unless you really need and want those pictures and patterns, take one of the lighter formatting options. You also can manage some advanced settings. These might include disabling Excel's built-in numeric parsing for numbers to avoid confusion around dates - for instance, 2/7 is imported as text, not February 7th.

There are also optional settings that are specific to the way Excel will interact with a Web query. The External Data Range Properties dialog box is available by clicking the Properties button from the Import Data dialog box when you are positioning the Web query.

The dialog allows you to manage the operational aspects of your new query. This dialog box contains settings that range from giving your query a name to determining what your query does if it finds existing data. Generally, it is not necessary to give a Web query a specific name. Excel will provide a default name based upon the URL string of the source page. However, if you need to manage many queries in one workbook or you need to control Web queries programmatically, you might want to get in the habit of naming them.

The default is to save your Web query, but you can opt not to save it. If you don't save it, though, you won't be able to refresh it or edit it. Just a quick note about saving Web queries: In this instance, it means to save it as part of the workbook. I don't mean the independent iqy files to which I referred earlier. Instead, this means saving its definition as a property of the workbook and, more specifically, the worksheet. If you want to save a Web query outside a workbook, such as for use in other workbooks, to share with other users, or to keep a backup separate from your models, then you want to save it as an independent iqy file. You can do this with the Save Query button located next to the Options button in the New Web Query dialog box (see FIGURE 3 again).

When you save the query definition with the worksheet, you'll notice that the definition isn't anything you can touch. The saved Web query definition is built into Excel and is not something you can modify on your own. Web query definitions are even hard to find sometimes. The only real giveaway is when you move the cursor into a cell where a Web query has been defined, and the data query menu items, such as Refresh Data, become active.

Keep Up to Date

Another property you can control from the External Data Range Properties dialog box is the refresh setting of a Web query. For starters, you can control whether your query runs asynchronously via the Enable Background Refresh option. This is useful in controlling how your application interacts with the refresh process. For example, if you have an Excel application that produces reports, and those reports contain results of formulas dependent on values returned by your Web, you will want to make sure that data is returned before you make those reports available. Any time you are using Web queries with automation, you will need to pay special attention to this setting. The general rule of thumb is if other formulas or events are dependent on your Web query data, set Enable Background Refresh to False.

You also can control the intervals in which your Web queries are updated. Because Web queries are doing an HTTP GET, the server cannot refresh your query automatically or remind you to update when the data has changed. And, of course, the Excel workbook must be open to initiate a refresh - no out-of-process magic here. But you can control a lot about how refreshes happen if they happen at all. You can control whether a Web query refreshes automatically when a workbook opens. This is useful if you want to ensure you have the latest data available and if your data source is relatively static. If you are connecting to a continuously refreshing data source, such as a real-time data feed, you can have your Web query fire a refresh at stated intervals. Allowable intervals are by minutes, with one minute being the smallest interval available. If you need absolute, live data feeds, the Web query probably won't be the best solution. In other words, start coding.

Under Data Formatting and Layout in the External Data Range Properties dialog box, you can control how Excel treats your Web query's results. You can force your query to honor your existing Excel formatting by setting Preserve Cell Formatting to True. This is useful if you don't want the Web query to change the current appearance of the target spreadsheet. You also can set Adjust Column Width to False if you want column widths to remain the way they are and do not want them resized to fit the incoming data. These are important considerations when you want your Web query to co-exist within an existing model or application, from a look-and-feel standpoint. However, controlling how the data is introduced physically into your worksheet is perhaps even more important than how it looks.

As you know, data often changes its dimensions as elements or records are added. Therefore, a Web query may return a varying number of rows or columns of data. If you are refreshing the same data repeatedly, this might not be a problem because you probably will want Excel to overwrite the existing data. However, you don't want to leave outdated data behind or, worse, overwrite useful data or formulas. You can control these settings depending on your needs by using the options under Data Formatting and Layout pertaining to layout. Finally, by selecting Fill down formulas in columns adjacent to data, your Web query can reproduce existing formulas that correspond with the incoming data. This is valuable if you need to support a varying number of records returning from your query.

Skinning a Query

You've seen the straightforward approach to creating Web queries, which is through the user interface. Typically that's the way you will build them. However, there are some cool alternatives. My favorite is if you are browsing in Internet Explorer, and you find a nugget of data you would like to play with in Excel, simply copy it in Internet Explorer. Then, from inside an Excel workbook, select Edit | Paste Special and choose HTML as the option. Once the data is inside the sheet, you will notice a Smart Tag icon pop up at the lower, right-hand corner of the pasted data. If you activate the smart tag and choose the bottom option, Create Refreshable Web Query, the New Web Query dialog box will pop up with your page loaded into the address bar already. Unfortunately, Excel can't interpret which specific tables you have copied from Internet Explorer, but using the browser to find data for Excel is quicker and more natural.

Another way to create a Web query is by using the smart tags that ship with Office. With the smart tag named Smart tag list (MSN Money Financial Symbols) enabled (check under Tools | AutoCorrect Options on the SmartTag tab to make sure this is installed and operational), type in a valid ticker, such as the Microsoft stock ticker (MSFT). Once Excel recognizes this symbol as a smart tag, you can choose the smart-tag action Insert refreshable stock price from MSN and instantly have a Web query with stock data. Because it's kind of a basic query, you may want to edit it.

One of the nice things about editing a previously built Web query is that all the options you had available to you when you created it are available in exactly the same dialog boxes in which you created them. Of course, you must have saved the Web query definition within the worksheet to be able to edit it. As I mentioned, no saved query definition means no more query. But, if you want to edit that saved query, look under the Data | Import External Data menu once you have selected the query, and you can the edit the query and its data-range properties from there.

One thing is peculiar when you're trying to edit a query, and that is actually locating the query. Because there is no user interface specifically for finding or navigating queries in Excel, you have to know where they are located. That's easy if there is an abundance of data or if you have the area well defined. But, if the resulting set is small or blank, look out. You may need a search-and-rescue team to find the little culprits. Although you could write code to navigate queries by manipulating the QueryTables collection, here's a tip that doesn't involve writing code:

  1. Set Excel's External Data toolbar to visible.
  2. Then, move the cursor around the area where you suspect the Web query to be.
  3. When you enter the range of the Web query, several of the External Data toolbar buttons will become enabled. The most logical to watch is the Refresh Data button (shaped like an exclamation point) as it turns from gray to red. When it lights up, you've found a query and can edit away.

Solution Scenarios

Creating Web queries is a slick process from Excel's user interface. It's easy and offers a lot of potential. Take this common IT scenario, for instance: Data resides in a database on a server, and users want to manipulate that data. Excel is commonly the tool of choice for those users. You can write code in Excel that directly attaches to that database, perhaps using ADO, and draws the data into the client from the server. But there are some limitations. Your code ships with the Excel workbook. If the data changes, you need to update that code on every desktop your application reaches. You also are relying on a closed connection. In other words, the client needs access to that database, typically inside the firewall, on the domain. Your client machine is doing some of the processing of the data, which is what those expensive servers are supposed to do. Finally, the data is only available to your Excel application because that is where you have invested your business logic.

Consider the Web query alternative. You distribute an Excel application, but it contains no data-specific code. Of course, other logic will be in there, but the data layer is abstracted because it is moved out of Excel and centralized onto the Web, whether it is your intranet or Internet or whatever. Your database is behind the firewall still, but you can serve any non-confidential data over HTTP so it can be accessed freely from anywhere. (You actually can use Web queries in a secure environment, but that's another topic.) Instead of offering a direct connection to all your clients, the data server simply is crunching the data with its high processing power and then is serving the data as HTML, perhaps driven by Active Server Pages from an application server. Maybe it's as simple as enabling SQL via XML over HTTP. Either way, the results are hosted by any Web server. What you have then is a nice side result: a bunch of HTML-enabled reports that can be used by other applications, not just your Excel application. The advantages of relying on Web queries and HTTP are numerous.

Charles Maxson is an independent consultant who has been using Microsoft Office to build solutions for many years. Charles evangelizes about the power of Office as a development platform, whether it is part of a solution or the entire solution. He frequently writes for magazines and speaks at conferences, such as Microsoft TechEd and the Microsoft Office Solutions Conference to share his experiences with others. Readers may contact him at mailto:Charles@OfficeZealot.com.

Tell us what you think! Please send any comments about this article to mailto:feedback@msOfficeMag.net. Please include the article title and author.

Overcoming Limitations

These are some of Web queries' known limitations:

  • They have no support for client-side scripting.
  • You can import data from only one URL per query. There's no frames support.
  • The Office Web Spreadsheet Component cannot create or run Web queries.
  • For parameterized queries, you must be prepared to create or edit an iqy file.
  • Sites requiring authentication and passwords provide additional challenges. They may require coded workarounds or may be unsolvable.

Here are some suggestions:

  • The key to using Web queries effectively is relying on solid Web-page design. If you are building the data sources (the Web pages), make sure they are Web query friendly.
  • Use unique IDs on the tables of your Web pages if you can.
  • Consider using page redirects to get back a pure data set from a Web page that otherwise has undesired content or formatting.
  • When importing from XML pages, Excel 2002's Spreadsheet XML is the best source to pull from.
  • Everything you can do through the Excel user interface (and more) you can do programmatically by manipulating the QueryTable collection. Be prepared to create dynamic queries from code, but also be smart about how you use cell formulas to set parameters and URL string properties. No hard coding!

Show: