Capturing Information for Spreadsheet with Web Queries
You can use Excel's Web Query feature to extract text or tables (or a combination of the two) from Web pages on the World Wide Web and bring their data into an Excel worksheet. Doing a Web query is a lot like performing an external database query except that instead of extracting data from an external database, you're taking it out of a Web page on the Internet.
The key to being able to do a Web query is having the URL of the Web site whose data you want to query. (You know, the http://-type address that appears on the Address bar of your Web browser when you visit a site.) You must have this address handy at the time you start the new Web query because the New Web Query dialog box doesn't provide a way to search the Internet, nor does it give you access to your Web favorites as you have in your Web browser.
To capture a URL for the page that you want to query, visit the Web site in your Web browser by using your Web favorites or its search capability. Then highlight the URL that appears in the Web browser's Address bar and copy it into the Clipboard before you switch back to Excel. There, select the text currently displayed in the Address bar of the New Web Query dialog box (refer to the steps that follow) and paste the page's URL address into this text box by pressing Ctrl+V.
To perform a new Web query in Excel, follow these steps:
- Open the worksheet where you want the Web data to reside and position the cell pointer in the first cell where you want the imported data to appear.
- Choose Data → Import External Data → New Web Query to open the New Web Query dialog box.
When the New Web Query dialog box opens, Excel connects you to the Internet and displays your Web browser's home page. - Enter the URL address of the Web site whose data you want to extract.
- Click the Go button to visit the page.
The first part of the page that you visit appears within the body of the New Web Query dialog box. - Click somewhere on the Web page to make it active.
After the Web page is active, Excel shows which elements on the page you can import by displaying yellow buttons with black arrows pointing to the right in front of each table or text, and you can then move new parts of the Web page into view by pressing the arrow keys (→, ↓, ←, or ↑). - Select all the tables and text on the Web page that you want to import into your Excel worksheet.
To select a table or text on the page for importing, click its yellow button, whereupon it changes to a green button containing a black check mark indicating that it's selected.
After you select all the elements on the Web page, you can either click the Import button to go ahead and bring in the selected text and data or first save the Web query in a separate query file (with an .iqy filename extension for an Internet query).
To save your query for reuse, follow Step 7. Otherwise, skip to Step 8. - Click the Save Query button to open the Save Query dialog box, enter the name for your Web query in the File Name text box, and click the Save button.
When importing data and text from Web pages into a worksheet, Excel doesn't bother to retain the Web page formatting. If you want the data in your worksheet to look exactly as it does on the Web page with all its fonts and colors, you need take Step 8. If you're only concerned with the raw data, skip to Step 9. - Click the Options button, select either the Full HTML Formatting or the Rich Text Formatting Only option in the Web Query Options dialog
box, and then click OK.
When choosing between the HTML Formatting and Rich Text Formatting Only options, keep in mind that Excel renders RTF formatting more faithfully than the HTML formatting. - Click the Import button.
The New Web Query dialog box closes, and the Import Data dialog box opens, where you indicate where to import the data. - Select the appropriate option in the Where Do You Want to Put the Data section and then click OK.
By default, Excel selects the Existing Worksheet option and selects the current cell as the place at which to starting importing the Web data. If you want to import the Web data at the beginning of a new worksheet, select the New Worksheet option instead. If you want to import the data in the current worksheet but starting at a different cell, enter its cell address in the text box or click it directly in the worksheet.
As soon as Excel closes the Import Data dialog box, the program begins importing the selected Web data. Because this procedure can take some time, depending upon how much data you're importing and how fast your Internet connection is, Excel inserts a temporary message Getting data in the current cell. The actual Web data replaces the message as it's imported.
After the data is imported into the worksheet, Excel displays the External Data toolbar in the window. You can then click its Edit Query button to revisit the Web page in the Edit Web Query dialog box. You can modify which tables and text to import or click its Refresh button to update the data - a very important feature when you import data such as stock quotes that you definitely want to keep up-to-date.
If you save the Web query, you can redo the query at any time simply by choosing Data → Important External Data → Import Data and then selecting its .iqy file. When opening a saved Web query, all you have to do is specify where to place the imported data in the Import Data dialog box to once more get the data from the Web page.
