Query for data from a Web page

You can create or run a Web query (Web query: A query that retrieves data stored on your intranet or the Internet.) to retrieve text or data on a Web page. Web queries are especially useful for retrieving data that is in tables or preformatted areas (tables are defined with the HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) <TABLE> tag; preformatted areas are often defined with the HTML <PRE> tag). The retrieved data does not include pictures, such as .gif images, and does not include the contents of scripts.

 Note   If you retrieve data from a Web site, it is your responsibility to make sure you use the data in compliance with any applicable terms and conditions governing the use of the data by the owner and/or operator of such web site.

ShowCreate a new Web query

  1. Do one of the following:

ShowStart in Microsoft Excel

  1. On the Data menu, point to Import External Data, and then click New Web Query.
  2. In the New Web Query dialog box enter the URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.) for the Web page from which you want to get data. You can type the URL, paste it from a copied address, or click the arrow next to the Address list and select a recently used address.
  3. Click Go.

ShowStart in the browser

  1. In your browser, browse to the Web page from which you want to query data.
  2. Click the arrow next to the Edit with... button (which may appear as one of several application icons, depending on how the page was created) and click Edit with Microsoft Excel.
  1. Click Select a Table next to the tables you want to import or click Select a Table in the upper left corner of the page to import the entire page.

If there are no Select a Table next to tables on the page, click Show Icons Show/Hide icons at the top of the dialog box to display them.

  1. Web queries are automatically saved with your workbook. If you want to save the query so that it can be run in other workbooks, click Save Query Save Query . The query is saved in a text file with an .iqy extension.
  2. To set formatting and import options for how data is returned, click Options and select the options you want in the Web Query Options dialog box.
  3. Click Import.

In the Import Data dialog box, do one of the following:

After you click OK, a spinning refresh icon refresh indicator appears in the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. To check the status of the query, double-click the refresh icon.

ShowRun a saved query

Web queries are saved in text files with .iqy extensions. Several Web query files that retrieve data such as stock quotes and currency come with Microsoft Excel.

  1. On the Data menu, point to Import External Data, and then click Import Data.
  2. Locate the folder where your query file is stored in the Look in box.
  3. Select the Web query you want to run.
  4. Click Open.

In the Import Data dialog box, do one of the following:

To return the data from the Web page to the selected worksheet, click Existing worksheet. On your worksheet, click the cell where you want to place the upper-left corner of the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.), and then click OK.

To return the data to a new worksheet, click New worksheet, and then click OK. Excel adds a new worksheet to your workbook and automatically starts the external data range in the upper-left corner of the new worksheet.

  1. If prompted, enter parameters for the query. If you don't know what the valid parameters are, ask the person who created the query.

Excel runs the query. A spinning refresh icon refresh indicator appears in the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. To check the status of the query, double-click the refresh icon.

 
 
Applies to:
Excel 2003