Section 24.2. Performing Web Queries

24.2. Performing Web Queries

The Internet's greatest attraction (other than its ability to help Uncle Charlie auction off his Yankees Rule, Red Sox Drool t-shirts) is the fact that it provides a huge mass of continuously updated information. If you know where to look, you can find up-to-the-minute product prices, stock quotes, and sports scores.

And if you're like most Web surfers, the only way you can see this information is by using a Web browser. That approach works well most of the time, but it limits what you can actually do with the information you find. Because one of Excel's strengths is helping you analyze datawhether it's in your worksheet, in a relational database (see Chapter 22), or in an XML document (see Chapter 23)it probably won't surprise you to learn that Excel can also help you extract important information right off a live Web page. This feature is called a Web query .

When you perform a Web query in Excel, you suck the data out of one or more tables on a Web page and insert it into the cells in a worksheetwhere you can work on it just like any other data. Excel also stores the Web address you used and some information about the table you queried. That way, when you refresh a Web query, Excel returns to the Web page, grabs the new information from the same location, and replaces it in your worksheet. That definitely beats copying and pasting!

Interactive Web Pages

When does an interactive Web page make sense?

Interactive Web pages aren't intended to replace Excel. If you need to change the content or layout of a worksheet, you'll be much better off launching the full-fledged Excel application, rather than trying to use the limited options that are available in the online replica.

Interactive Web pages are intended for situations where people need to make changes on the fly in order to view your data in different ways.

For example, with an interactive Web page, the person viewing the page can:

  • Change the filter settings of a data list.

  • Change the fields in a pivot table.

  • Alter cell entries to see how they affect a linked chart.

Of course, there are a whole host of things that you can't accomplish with Excel's interactive Web pages, including inserting charts or graphics, or using anything that requires the Excel menu (which isn't shown in the scaled-down version). If you need to accomplish any of these tasks , you can click the handy Export to Microsoft Excel button to continue working with the data in a full-fledged Excel window outside your browser.

24.2.1. The Limitations of Web Queries

Excel Web queries seem downright miraculous . After all, who could pass up the ability to insert and update information from across the globe? But beware: Web queries are notoriously fragile. Due to the always changing nature of the Internet, a Web query that works one day might not work properly the next . Here are some of the pitfalls:

  • Web queries are tied to specific Web addresses . If the Web site owner moves the information somewhere else, your Web query won't be able to find it.

  • Web queries find information based on the structure of a Web page . Minor changes in that Web page (like adding a new table or even rearranging the order of elements) can throw Excel off, so it might download the wrong information or fail altogether.

  • Web queries rarely work on Web sites that require authentication . That's because you need to log in before you can retrieve the data. Excel can't tell that you haven't logged in, and its attempt to get the information fails.

Note: Most of these shortcomings are factors that limit the repeat use of Web queries. In other words, Web queries usually work great the first time you use them. Just don't assume that you'll be able to easily update the information in the future, unless you're using a Web site that never changes.

So what types of Web pages are good candidates for Web queries? Ideally, you'll use a page that has a simple, rigid structure, and puts the information you need in a table. Unfortunately, you often can't tell how complex a page is (or whether it uses tables) just by looking at it, so you may want to try a Web query to assess whether or not it will work. Also look for Web pages where the structure and formatting never change, unless you're willing to create a new Web query each time the Web page you're drawing from changes. Some examples you might want to try include getting a temperature from a city-specify weather Web page, reading a stock quote from a financial site, or getting the list price of a book on

24.2.2. Creating a Web Query

Now that you're forewarned, you're ready to create a Web query of your own. Just follow these steps:

  1. Decide where you want to go to get the data you want to import into Excel.

    Although you can actually browse different Web sites from within Excel (more on that in step 3), it's usually easiest to find the correct address using your Web browser before you create a Web query. That's because Excel's New Web Query window doesn't let you access your favorite Internet shortcuts.

    Excel finds a page based on its Web address. So once you've found the Web site you want using your Web browser, just copy all the text in the address bar. You'll be able to paste it right into Excel's New Web Query window.

  2. Open the worksheet you want to use, or create a new worksheet.

    There's no limit to how many Web queries you can place in a single worksheet, and you can freely mingle Web queries with ordinary data.

  3. Select Data Import External Data New Web Query.

    The New Web Query window appears, as shown in Figure 24-8. This window provides many of the conveniences of Internet Explorer, including an address bar and basic buttons for refreshing, stopping, and going forward and backward. The difference is that Excel automatically scans the page when you surf to it in the New Web Query window, looking for data that it can import. Every time it finds a table of information, it adds a tiny yellow arrow box next to the table. You click these arrow boxes to tell Excel what data you want to extract.

    Figure 24-8. This example shows a page on Microsoft's MSN Money site. This page is full of tables that organize text and links. The only table that's selected is the table in the center displaying the information about how the Dow Jones Index is performing today. You can tell it's been selected by the checkmark next to the table's name .

    Tip: The New Web Query window is resizable, so stretch away if all the information on your Web page isn't visible.
  4. Enter the Web address for the page you want to use in the Address box. Hit Enter or click Go to load the page.

    Optionally, you can enter the address for a search engine like Google (, perform a search, and browse to the page you want. But the quickest and most reliable approach, as discussed above, is to find the page in a standalone browser first (like Internet Explorer), and then copy that page's Web address to the New Web Query window.

  5. Select the table you want to extract.

    When the page appears in the New Web Query window, Excel adds yellow arrow boxes next to every table you can import. As you hover over each arrow box with the mouse, Excel draws a bold blue outline around the related table.

    Once you find the table you want to extract, click the arrow box (which then changes into a green checkmark). To deselect a table, just click it again.

  6. When you've finished selecting all the tables you want, click the Import button at the bottom of the New Web Query window.

    The Import Data window appears.

  7. Choose where you want your data to go, and click OK.

    If you want to insert the extracted information into your current worksheet, select the "Existing worksheet" option and enter a cell reference. For example, if you use the cell reference $A$1, Excel starts the first column of the first row in cell A1.

    If you want to insert the information into a new worksheet, select the "New worksheet" option.

    Once you click OK, Excel begins to fetch the information it needs. During this time, you'll see an information message appear in your worksheet. Excel then replaces this message with the downloaded data, as shown in Figure 24-9.

    Figure 24-9. These figures show a Web query in action. Top : Here's what happens immediately after you start the Web query. At this point, Excel is in the process of fetching the information it needs from the Web page.
    Bottom : A few seconds later, the data appears. You can see that Excel successfully converted a formatted table of HTML into a plain text-and- numbers -only table of data that you can analyze to your heart's content.

When you save a worksheet that uses a Web query, Excel stores all the data that's now in the cells of your worksheet, along with some information about the Web query's data source. You can grab the latest version of your information at any time by moving to any one of the linked cells and choosing Data Refresh Data.

Excel does give you some ability to respond to changes made on Web pages you're drawing data from. You can edit the data source at any time by moving into any one of the linked cells and choosing Data Import External Data Edit Query. The Edit Web Query window appears, which looks exactly the same as the New Web Query window. Using this window, you can choose a different table, or even enter a different Web page URL.

24.2.3. The Research Taska Web Query Alternative

One alternative to a Web query is the Research task, which is new in Excel 2003. While the Web query feature lets you get information from any Web page (and is very fragile), the Research task gets information from a small set of Web services that are designed explicitly for the purpose of sending real-time content to Excel over the Internet. To use the Research feature, select Tools Research from the menu. The Research task appears on the right of the Excel window (see Figure 24-10).

Figure 24-10. Top : The Research task provides two text boxes. You enter your search keyword in the first text box, and you choose the Research service you want to use in the second. Then, click the green arrow to start the search. When the results appear, you can read them in the result box. Depending on the service, you may also be able to copy some of the information into an Excel worksheet. Here, the Research task is used to find the meaning of a confounding word.
Bottom : In this example, the MSN Money service provides a recent (20-minute delayed) stock quote of Microsoft. If you click the Insert Price button, the price information is copied into the current cell, as shown here.

When you first install Excel, it's preconfigured with a set of Research services. These include a dictionary that lets you search for detailed definitions, a thesaurus that shows you synonyms, and the Encarta encyclopedia, which provides detailed information on a range of topics. You can also get stock market quotes from the MSN Money Web site.

Overall, the Research task is a nifty way to get some free information (regardless of whether you want to put it in a worksheet or not). Microsoft hopes that third-party companies will develop services that plug into the Research frameworkbut currently, only a few exist (and they're all fee-based). Click the "Get services on Office Marketplace" link to browse a catalog with more useful services online.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185 © 2008-2017.
If you may any questions please contact us: