Get and Analyze Data from the Web in Excel 2000 

Do you want to analyze data from the Web (such as the latest stock quotes) in Excel 2000? Would you like to update the worksheet with the latest information with the click of a button? You can do this with the improved Web query feature in Excel 2000.

Try a Sample Web Query

Sample Web query files are included with Excel 2000 to help you get started. For instance, use the Microsoft Investor Stock Quotes Web query to see the latest stock quotes from MSN™ MoneyCentral™ Investor. To use this sample query, follow these steps:

1. Be sure you have access and are connected to the World Wide Web.
2. Open a new workbook in Excel.
3. On the Data menu, click Get External Data, and then click Run Saved Query.
4. In the file list in the Run Query dialog box, click Microsoft Investor Stock Quotes.iqy.
5. Click Get Data.
6. In the Returning Data to Microsoft Excel dialog box, click OK.
7. In the Enter Parameter dialog box, type the stock symbol for which you want to return a quote. For example, for Microsoft, type msft. Click OK.

Obtain Additional Sample Queries

You can obtain a variety of additional sample queries by clicking Get More Web Queries.iqy in the Run Query dialog box. This is a query that downloads information and hyperlinks to additional sample Web query files from Microsoft.

Create Your Own Web Query

Following the procedure above should give you an idea of how Web queries work. Want to 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:
   The entire 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. For information on the options in these dialog boxes, click the Help button in the upper-right corner of the dialog box, and then click the option for which you want help.

Update the Data

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.

More Information