26.2. Performing Web Queries
The Internet's greatest attraction (other than its ability to help Uncle Charlie auction off his The Y2K Bug Will End Life As We Know It t-shirts) is 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, you can see this information only 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, a relational database, or an XML document (see Chapter 25)you'll be glad to know that Excel can also help 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 worksheet, where 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!
26.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 may 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 can't 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 may 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 it'll 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 may want to try include getting a temperature from a city-specific weather Web page, reading a stock quote from a financial site, or getting the list price of a book on Amazon.com.
26.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:
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.
Open the worksheet you want to use, or create a new worksheet .
You can place as many Web queries as you want in a single worksheet, and you can freely mingle Web queries with ordinary data.
Choose Data Get External Data From Web .
The New Web Query window appears, as shown in Figure 26-4. 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.
Tip: The New Web Query window is resizable, so stretch away if all the information on your Web page isn't visible.
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 (www.google.com), 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.
| || |
Figure 26-4. A page from Microsoft's MSN Money Web site. Each arrow-in-a-box represents a table of info that Excel's capable of importing. To give Excel the go-ahead, click any arrow to transform it into a checkmark, like the one shown here next to the table of data about Microsoft's stock performance.
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.
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.
Choose where you want your data to go, and then click OK .
If you want to insert the extracted information into your current worksheet, select the "Existing worksheet" option and enter a cell reference. 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 26-5.
| || |
Figure 26-5. These figures show a Web query in action.
Top: Here's what happens immediately after you start the Web query. At this point, Excel's 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 Connections Refresh All.
Note: When you open a workbook that contains a Web query, Excel disables the Refresh All command just to be safe. (This is the same watchdog behavior you learned about in Section 24.1.3 with database queries.) It's up to you whether you want to turn on your Web query each time you open the workbook (by clicking Options to show the Microsoft Office Security Options dialog box, and then clicking "Enable this content"); or you can place the file in a trusted location so Excel always turns on your Web query (as described in Section 27.3.2).
Excel gives you some ability to respond to changes made on Web pages from which you're drawing data. You can edit the data source at any time by following these steps:
Choose Data Connections Connections .
The Workbook Connections window appears. It lists all the connections that link to outside data, include databases, XML files, and Web queries.
Find your Web query connection in the list (which is probably the only connection you've created so far), and then click Properties .
The Connection Properties dialog box appears. Here you can give your connection a better name and description, and set it to refresh your data periodically (either every time the file is opened or after a certain number of minutes). Section 24.1.4 has more information about this feature.
Click the Definitions tab, and then click the Edit Query button .
This action opens an Edit Web Query window, 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.
When you're finished, click Import. Then, click OK to close the Connection Properties dialog box, and OK again to close the Workbook connections window and return to your worksheet .
26.2.3. The Research Panea Web Query Alternative
The Research feature is one alternative to a Web query. While the Web query tool lets you get information from any Web page (and is very fragile), the Research pane 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, choose Review Proofing Research. The Research pane appears on the right of the Excel window (see Figure 26-6).
When you first install Excel, it's preconfigured with a set of Research services. These services 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.
| || |
Figure 26-6. Excel's Research pane. Enter your search keyword in the first text box, choose the Research service you want from the pull-down menu below it, and then click the green arrow (circled) to start the search. When the results appear, you can read them in the results box. Depending on the service, you may also be able to copy some of the information into an Excel worksheet by clicking a button. In this example, the Encarta dictionary provides the definition for a confounding word.
Overall, the Research task is a nifty way to get some free information (regardless of whether you want to put it in a worksheet). 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.