| Web sites can be a rich source of information for your workbooks particularly financial sites and sites that offer products for sale. By building a query to such a site, you can refresh the results whenever you want without having to use your browser. CASE STUDY: Getting Financial Data Suppose that you want to keep an eye on both the levels and volume of major financial market indices. Doing so can be a good way to tell whether the markets are starting to turn, whether up or down. - Begin by selecting a worksheet cell that is not part of an existing external data range. If the active cell is part of such a range, the Import Data, the New Web Query, and the New Database Query menu items are disabled.
- Choose Data, Import External Data, New Web Query. The dialog box shown in Figure 6.23 appears.
Figure 6.23. The actual page that appears depends on the location of your browser's home page. 
- In the dialog box's Address box, enter the URL of the page you want to use, and click the Go button to reach the page. After the page has loaded, you'll see yellow boxes with black arrows scattered across the page. Each one indicates the presence of a table that can be queried.
- These square icons are by default turned off: That is, their associated tables are not selected. To select a table, click its icon. The icon turns green and the arrow is replaced by a checkmark. To make it easier to see what you're selecting, a table is outlined when you move your mouse pointer over its associated icon.
- When you've selected the table you want, click the Import button in the lower-right corner of the dialog box. The dialog box closes and the Import Data dialog box appears. You can set data range properties at this point, or do so later by right-clicking a cell in the data range and choosing Properties from the shortcut menu.
 The Import dialog box is discussed in "Handling the Data Range," p. 130.
- Click OK and the data is returned to an external data range in your workbook. Graphic images, if there were any in the table you selected, are not returned (see Figure 6.24).
Figure 6.24. This query used no data formatting. 
Now, you can update the information in your data range whenever you want. Just right-click a cell in the data range and choose Refresh External Data from the shortcut menu. TIP It's a good idea to use the Properties button on the Import Data dialog box to check the name that will be assigned to the data range. Some sites return meaningful names. Others return only the URL, which often isn't a good choice for a range name.
Using Web Query Options In Figure 6.23, if you click the Options button in the dialog box's upper-right corner, the dialog box shown in Figure 6.25 appears. Figure 6.25. You can reset these options by right-clicking in the data range and choosing Edit Query from the shortcut menu. 
You can choose None to get no formatting. If you choose Rich Text Formatting Only, you'll get the fonts used in the table: boldface, colors, italics, and so on. If you choose Full HTML Formatting, you'll get the fancy fonts, and if the table contains any hyperlinks, those hyperlinks become part of the external data range. This can be a useful way to get further information on an element in a table. Web pages often use <PRE> blocks to format sections of the page. If you select the Import <PRE> Blocks Into Columns option, those sections are returned to the worksheet in columns using the delimiters on the Web page. If you deselect this option, the remaining two options in this section of the dialog box are disabled. Choosing Treat Consecutive Delimiters As One means that consecutive delimiters won't result in a blank cell. If you select this option, the option labeled Use the Same Import Settings for the Entire Section is enabled. If you choose not to use the same import settings, your choices will be applied only to the first preformatted block that the query encounters. The query will attempt to select the best choices for any remaining preformatted blocks. You might encounter a Web page that contains numbers that look like dates but are really something else. For example, a page with product numbers might use a pattern such as 11-5-98. Excel would typically recognize this as a date and convert it to its standard date serial number representation. You can keep this from happening by choosing the Disable Date Recognition option. Finally, some Web pages have automatic redirection to other sites. When you refresh the query later on, you might be redirected and your data might not be refreshed properly. To prevent this from happening, choose the Disable Web Query Redirections option. |
