Discussion
Excel has the capability of importing data from a web page located on the Internet or an intranet site. You can retrieve a table from a web page to analyze its data with Excels analysis tools. For example, you may want to retrieve stock quotes from the Internet and then perform trend analysis or chart the data.
When importing data from the Internet or an intranet site using the New Web Query dialog box, Excel creates a refreshable link to the original data source.
Imported data can be static or dynamic. A static data source is one that does not normally change or need to be refreshed. However, you may want to refresh the data if you make changes to the imported data on the worksheet and want to return to the original data. Dynamic data, on the other hand, is usually in a constant state of change.
Real-time data is the actual reported data with little delay. Stock market quotes and currency exchange rates on the Internet are examples of real-time data.
When importing tables using the New Web Query dialog box, each table on the web page appears with a small arrow icon located in its upper, left corner. These arrow icons enable you to select the table you want to import. In addition to selecting one specific table, you can import all the tables on the web page or the web page itself by selecting multiple arrow icons. If the arrow icons do not appear, you may need to
Page 96 Villanova UNIT Training©
select the Show Icons button on the toolbar in the New Web Query dialog box. When importing XML data from a web page, you should use the Options button on the toolbar to select the Full HTML formatting option in the Web Query Options dialog box.
You can use the Save Query button in the New Web Query dialog box to save the query to a query file (.iqy). This file can then be opened and run in another Excel workbook. By default, the query is saved with the workbook.
Setting external data range properties
If the HTML file was originally created in Excel, you can import the data back to Excel by opening the HTML file in Internet Explorer and using the Edit button or list on the Internet Explorer toolbar to open the file in Excel.
You can manually refresh dynamic data or use the External Data Range Properties dialog box to create an automatic refresh setting.
To edit a Web Query, right-click in the imported data and select Edit Query from the shortcut menu that appears.
To edit the External Data Range Properties, right-click in the imported data and select Data Range Properties from the shortcut menu that appears or select the Properties button in the Connections group on the Data tab.Villanova UNIT Training© Page 97
You must have a browser installed to use the New Web Query dialog box to import data from a web page on the Internet or an intranet site. In addition, you must be able to connect to the Internet to import data from the World Wide Web. Procedures
1. Select the Data tab.
2. Select in the Get External Data group.
3. Type the web address of the web page containing the table you want to import into the Address box.
4. Select .
5. If necessary, click the Show Icons button on the toolbar to display the selection arrows.
6. Select the table you want to import.
7. Select the Options button on the toolbar in the New Web Query dialog box.
8. Select the desired options.
9. Select .
10. Select .
11. Select the cell where you want the upper left corner of the imported table to appear.
12. To change the external data range settings, select . 13. Select the desired options.
14. Select .
15. Select .
Step-by-Step
Import dynamic data from the Web.
Page 98 Villanova UNIT Training©
Note: You will need an Internet connection to complete this step-by-step.
Display the Currency Exchange worksheet.
Steps Practice Data
1. Select the Data tab.
The Data tab is displayed.
Click Data
2. Select the From Web button in the Get External Data group.
The New Web Query window appears with the text in the Address box selected.
Click
3. Type the web address of the web page containing the table you want to import into the Address box.
The web address appears in the Address box.
The selected web page appears in the New Web Query dialog box.
Click
5. If necessary, click the Show Icons button on the toolbar to display the selection arrows.
Arrows in yellow boxes appear beside selectable items in the browser window.
Click , if necessary
6. Select the table you want to import.
The arrow for the selected table changes to a green check box and the table is selected.
Scroll as necessary and click in the upper, left corner of the Currency Rates table
7. Select the Options button on the toolbar in the New Web Query dialog box.
The Web Query Options dialog box opens.
Click
8. Select the desired options.
The options are selected.
Click
9. Select OK.
The Web Query Options dialog box closes.
Click
Villanova UNIT Training© Page 99
Steps Practice Data
10. Select Import.
The New Web Query dialog box closes and the Import Data dialog box opens.
Click
11. Select the cell where you want the upper left corner of the imported table to appear.
The cell reference appears in the Import Data dialog box.
Click cell A5
12. To change the external data range settings, select Properties.
The External Data Range Properties dialog box opens.
Click
13. Select the desired options.
The options are selected.
Follow the instructions shown below the table before continuing on to the next step
14. Select OK.
The External Data Range Properties dialog box closes.
Click
15. Select OK.
The Import Data dialog box closes and the table appears in the worksheet.
Click
Under Refresh control, select the Refresh every option and change the spin box to 1 minute. Then, under Data formatting and layout, select the Overwrite existing cells with new data, clear unused cells and Fill down formulas in columns adjacent to data options.
Return to the table and continue on to the next step (step 14).
Click a cell in column A in the imported data range (click cell A7). Wait one minute for the automatic refresh to see how the change in the currency rates affect your prices.