Get and Analyze Data from the Web in Excel 2000
From
Create Your Own Web Query
First, it's a good idea to have in mind the Web site from which you want to get data, and to have the address of that Web site. Then, open the workbook with the worksheet where you want to display the data. When you're ready, follow these steps:
1. On the Data menu in Excel, point to Get External Data, and then click New Web Query.
2. In the first section of the New Web Query dialog box, type or paste an address for the Web page. Or, if you don't have the address, click the Browse button to start your browser, locate the page you want, and switch back to Excel using ALT+TAB. The address will be automatically filled in for you.
3. In the second section of the New Web Query dialog box, choose the data you want returned:
Theentire page returns text, tables and other data on a page (does not include graphic images). Choose this option when the page contains just the data you want and does not include advertisements, banners, navigation buttons, or other elements that may clutter the worksheet or that Excel may not be able to interpret properly.
Only the tables returns all tables or preformatted (<PRE>) sections on the page. Use this option when you do not want to import advertisements, banners, navigation, or other elements on a page.
One or more specific tables on a page returns only the table(s) that you specify. Use this option when you want data out of a specific table or tables only. To specify which tables to return, enter table numbers separated by commas (such as 2, 3, 4 to return the second, third, and fourth tables on the page) or enter table names in quotation marks, separated by commas (such as "Table 1","Table 2"). You can specify the tables - and preformatted sections (<PRE>), which are recognized as tables - by counting them or by entering their names if they are defined on the Web page. You may need to view the HTML source code to determine the table name or number. The table name is defined on the Web page by the ID tag in the TABLE attribute (for example, <TABLE ID="My Table" where "My Table" is the table name). See the Help in your browser for information on how to view HTML source code.
4. In the third section of the New Web Query dialog box, choose the type of formatting you want returned:
None returns plain text without formatting such as font face or color.
Rich text formatting returns most Web page formatting such as font face and color, but does not return hyperlinks or other types of advanced formatting.
Full HTML formatting returns all of the Web page formatting that Excel supports, such as hyperlinks.
5. Click OK.
6. In the Returning Data to Microsoft Excel dialog box, specify where you want to put the data (starting in a cell you specify on an existing worksheet or, in a new worksheet). For more advanced options such as refresh control and query definition, click Properties.
Update the Data
Later, to update the data to match the latest information in the Web page, click the Refresh Data button on the External Data toolbar that is enabled when you click a cell within the queried data. To cancel the query, click Stop Refresh.
Modify the Query
If you want to change the type of data or formatting that your query returns, click a cell within the queried data, and then click the Edit Query button on the External Data toolbar.
You can also change some properties of the query before you run it. For example, if you want to change the query so that it is refreshed automatically each time you open the workbook, click Properties in the Returning Data to Microsoft Excel dialog box, displayed after you click Get Query in the Run Query dialog box.
Analyze the Data
Use Excel's powerful analysis tools and formulas to analyze your data. For instance, if you're calculating stock option amounts, you can create a formula that determines net profit by calculating the market price of the stock minus the purchase amount. Instead of using the actual stock price in the formula, use a reference to the cell that contains the latest stock price. That way, when you update the data, the formula will recalculate to show the latest profit amount.
Save Your Query
When you click OK in the New Web Query or Edit Web Query dialog box, a query is created and stored in the workbook so that data can be updated later. To save the query in a separate file so that you can use it with other workbooks or share it with other people, click the Save Query button. Type a file name in the File name box, and click OK. By default, the file is saved as a text file with an *.iqy extension in the \Windows\Application Data\Microsoft\Queries folder. This location provides the easiest access to the file from the Run Query dialog box, which is displayed when you click Run Saved Query on the Get External Data menu (Data menu). To share the file, simply save it on another user's machine.
To run a saved query, click Get External Data on the Data menu, and then click Run Saved Query and choose the query you want to run in the Run Query dialog box.
After the saved query is run in a workbook for the first time, the information to update the query is stored in the workbook. Therefore, if you make modifications to the query in the Edit Web Query dialog box, the modified information will be stored in the workbook rather than in the *.iqy file. To update the *.iqy file as well, click Save Query.
Special Handling
Click the Advanced button if the page you are importing contains preformatted (<PRE>) sections that you want handled in a certain way, or when there are numbers on the page that could be mistaken for dates (for example, a part number such as 00-01-45). For information on an option, click the Help button in the upper-right corner of the dialog box, and then click the option for which you want help.
1