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!
FREQUENTLY ASKED QUESTION 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:
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. |
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.
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 Amazon.com.
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.
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.
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.
|
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.
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 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.
|
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.
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).
|
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.