Get external data from a Web page

You can create or run a Web query to retrieve text or data from a Web page. Web pages often contain information that is perfect for analysis in Excel. Depending on your needs, you can retrieve data that is refreshable. That is, you can update the data in Excel with the latest data on the Web page. Or you can retrieve data from a Web page and keep it static on the worksheet.

What do you want to do?

Learn more about getting external data from a Web page

Create and edit a Web query

Create a Web query parameter query

Copy data from a Web page

Run a saved Web query file

Make a Web page redirect a Web query to another data source

Issue: I am having problems sorting, filtering, or searching external data from a Web query

Learn more about getting external data from a Web page

You can use a Web query to retrieve refreshable data that is stored on your intranet or the Internet, such as a single table, multiple tables, or all of the text on a Web page. Then you can analyze the data by using the tools and features in Excel. For example, you can retrieve and update stock quotes from a public Web page or retrieve and update a table of sales information from a company Web page.

Web queries are especially useful for retrieving data that is in tables or preformatted areas. (Tables are defined with the HTML <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.

To create a Web query, you need access to the World Wide Web (WWW) through your company's intranet or through a modem on your computer or network. Or you can make a query against local HTML or XML files.

Sample Web queries

Several sample Web queries are installed with Microsoft Office Excel 2007. By default, these queries are located in the \Program Files\Microsoft Office\Office12\QUERIES folder on your hard disk:

  • MSN MoneyCentral Investor Major Indicies.iqy

  • MSN MoneyCentral Investor Currency Rates.iqy

  • MSN MoneyCentral Investor Stock Quotes.iqy (a Web query parameter query)

Ways to get external data

If you are using Excel, you can import data that originates from a Web page by using the New Web Query dialog box. (On the Data tab, in the Get External Data group, click From Web.)

If you are using Internet Explorer, there are two ways to get external data into Office Excel:

  • Use familiar copy and paste commands to bring the data from a Web page onto an Excel worksheet. When you paste Web page data into Excel, you can keep the data static or make it refreshable by clicking Paste Options Button image and then clicking Create Refreshable Web Query.

  • Right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu. This displays the New Web Query dialog box.

Note   You can also open any HTML file, MHTML file, or well-formed XML file in Excel. When you open an HTML or MHTML file, you get the entire Web page, but you might lose some formatting, scripts, image files (HTML only), or lists of data in a single cell. When you open an XML file, you have the option of applying one or more referenced stylesheets. In either case, the data is not refreshable.

Creating a Web query parameter query

A parameter query is a query that prompts for input every time that you run it. You can create a Web query parameter query by editing a saved Web query file (.iqy). For example, a Web query that gets stock quotes from a Web page can prompt you for a parameter value, such as a stock symbol, each time you run the Web query parameter query.

A Web query parameter query is based on a URL query string, which is one or more name/value pairs appended to the end of a URL. Some Web sites use a URL query string to change the contents of a Web page. A URL query string has the following format as the following example shows: 1 2 3 4 5 -----------------------------URL------------------------------?<Name>=<Val> http://moneycentral.msn.com/investor/external/excel/quotes.asp?quote=MSFT

1

URL

The URL of the Web page

2

?

The question mark character (?) begins the URL query string.

3

<Name>

The name, in this example, is Quote.

4

=

The equal sign character (=) delimits the name from the value.

5

<Val>

The value, in this example, is MSFT.

If you are passing two or more name/value pairs, separate them with an ampersand character (&). For example: 1 2 3 ...?<Name>=<Val>&<Name>=<Val> ...?Quote=MSFT&Quote=IBM

1

<Name>=<Val>

The first name/value pair is Quote=MSFT.

2

&

The ampersand character (&) delimits each name/value pair.

3

<Name>=<Val>

The second name/value pair is Quote=IBM.

You base a Web query parameter query on a URL query string by using the following format. For example: 1 2 3 4 5 6 ...?<Name>=["Parameter","Prompt"] ...?Symbol=["StockSymbol","Enter stock symbol:"]

1

<Name>

The name, in this example, which is Symbol.

2

=

The equal sign character (=) delimits the name from the parameter and prompt string.

3

"Parameter"

The name of the parameter, surrounded by quotes, that Excel uses to identify the parameter in the Parameters dialog box , in this example, which is StockSymbol.

4

,

The comma character (,) delimits the parameter from the prompt.

5

"Prompt"

The prompt, surrounded by quotes, that Excel displays in the Enter stock symbol: dialog box, in this example, which is Enter stock symbol:.

6

[...]

The left bracket ([) and right bracket (]) characters delimit the prompt string.

Redirecting Web queries to other data sources

If you are a Web page author, you can increase the reliability of the data that users retrieve from your pages by having Web queries from Excel redirected to a data source such as an XML or text file.

For example, if an HTML page contains a table of stock quotes, that table data can be used in a Web query, even if the data is being pulled from another data source. Or, if you are pointing to a table on a Windows SharePoint Services site that summarizes the project status, you can create a Web query to pull data from the master status list.

This redirection has two primary advantages. First, the HTML Web page that you are importing can be formatted for viewing, even though the data is optimized for analysis (for example, in XML format). Second, the HTML view of the data can be broken up into pages of data (for example, 20 results at a time), but the Web query can be redirected to the full set of data.

You can use an HTML attribute with the TABLE, PRE, XMP, LISTING, or PLAINTEXT tag that identifies where the underlying data is stored. Web page authors can use this redirection attribute, o:WebQuerySourceHRef, to list the data source URL, so that Web queries can link to the underlying source data. This redirection attribute is defined in the Microsoft Office namespace to avoid conflict with other redirection attributes. The data source can be any supported Web query data type, such as HTML, Text, or XML. When this attribute is used, you do not have to worry about getting the correct content when you refresh. The Web query contains information about the path to the data source and can pull the updated data directly from that source.

Top of Page

Create and edit a Web query

  1. Do one of the following to either create a new Web query or edit an existing Web query:

    Create a new Web query    

    1. Do either of the following:

      • In Office Excel, on the Data tab, in the Get External Data group, click From Web.

      • In the browser, browse to the Web page from which you want to query the data, right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu.

        The New Web Query dialog box appears.

    2. In the New Web Query dialog box, enter the URL 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.

      Note   The maximum length of a URL is 255 characters.

    3. Click Go.

      Edit an existing Web query    

    4. On the Data tab, in the Connections group, click Connections.

      Excel Ribbon Image

    5. In the Workbook Connections dialog box, select the Web query, and then click Properties.

    6. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.

  2. Click the Select a Table button next to the tables that you want to import, or click the button Select a Table in the upper-left corner of the page to import the entire page.

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

  3. To set the formatting and import options for how data is returned, click Options, and then do one or more of the following in the Web Query Options dialog box:

    1. Under Formatting, click one of the following:

      • None     This option returns none of the formatting on the Web page that you are querying, only text. Formatting that exists on the worksheet is applied.

      • Rich text formatting only     This option returns the type of formatting that Office Excel can reproduce most closely, such as font styles. It does not include HTML formatting, such as hyperlink formatting.

      • Full HTML formatting     This option returns all HTML formatting that Excel supports, such as hyperlink formatting. This option clears the Preserve cell formatting check box in the External Data Range Properties dialog box.

    2. Under Import settings for preformatted <PRE> blocks, select one or more of the following:

      • Import <PRE> blocks into columns      Blocks of data surrounded by <PRE> tags will be imported into separate columns on the worksheet. Clear this check box to return data into a single column.

      • Treat consecutive delimiters as one     If you have characters that define, or delimit, the text for each column in your <PRE> sections (such as commas), you can specify that when Excel encounters more than one of these delimiters together, they will be treated as one. As a result, a blank column is not placed between each consecutive delimiter. This check box is available only when you select the Import <PRE> blocks into columns check box.

      • Use the same import settings for the entire section     Select this check box to use your setting for Treat consecutive delimiters as one for all preformatted sections on the Web page. Clear this check box to use your setting for the first preformatted section only, or if you want Excel to determine the best settings. This check box is available only when you select the Import <PRE> blocks into columns check box.

    3. Under Other Import settings, select one or more of the following:

      • Disable date recognition     This option ensures that numbers on a Web page that appear similar to dates appear as numbers on the worksheet. For example, a sports standing score of 03-07 is recognized by Excel as the date March 7 unless this option is selected.

      • Disable Web query redirections     This option ensures that the Web query is not redirected to a different data source from what you see on the Web page that you are querying. Select this check box for compatibility with queries that are created in previous versions of Excel.

  4. Click OK. The Web query is saved with your workbook.

  5. Optionally, save the Web query to a Web query file (.iqy) so that you can run the query in other workbooks and so that you can create a parameter query.

    For more information, see Create a Web query parameter query.

    Save a Web query to a Web query file

    1. In the Edit Web Query dialog box, click Save Query Button image.

    2. Locate the folder in which you want to save the query.

      By default, saved queries are located in the \Documents and Settings\<username>\Application Data\Microsoft\Queries folder on your hard disk.

    3. Enter a file name in the File name box.

    4. Click Save.

      The query is saved in a text file with an .iqy file name extension.

  6. Click Import.

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

    1. 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, and then click OK.

    2. 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.

      After you click OK, a Background Refresh icon Refresh indicator appears on the status bar to indicate that the query is running. To check the status of the query, double-click the refresh icon.

Top of Page

Create a Web query parameter query

  1. In Windows Explorer, locate the query file on your computer.

    By default, saved queries are located in the \Documents and Settings\<username>\Application Data\Microsoft\Queries folder on your hard disk.

  2. Right-click the file, and then click Edit with Notepad. The query opens in Microsoft Notepad, where you can make changes to the file.

  3. In the text of the file, find the URL of the Web address from which the imported data originated. For example:

http://moneycentral.msn.com/investor/external/excel/quotes.asp

At the end of the URL, type the following (do not insert a space at the end of the URL):

?Symbol=["Quote", "Enter one or more financial symbols separated by commas."]

  1. On the File menu, click Save, and then exit Notepad.

Top of Page

Copy data from a Web page

  1. In your Web browser, select the data that you want to copy.

  2. Copy the data.

    Note   See your browser's Help for information about a copy operation.

  3. Switch to Excel.

  4. Click in the upper-left corner of the worksheet area where you want the copied data to appear.

  5. On the Home tab, in the Clipboard group, click Paste.

    The Clipboard group on the Home tab

  6. If the data does not appear as you expect, click Paste Options Button image and then click one of the following options:

    • Keep Source Formatting to make no changes.

    • Match Destination Formatting to match the existing cell formatting.

    • Create Refreshable Web Query to create a query to the Web page from which you copied the data. If the Web page changes at a later time, you can refresh the data.

Top of Page

Run a saved Web query file

Web queries are saved in text files with .iqy file name extensions. The saved queries are useful for sharing the same Web query in different workbooks or with other users and for creating a Web query parameter query.

  1. Click the Microsoft Office Button Office button image, and then click Open.

  2. In the Look in box, locate the folder where your query file is stored.

  3. Select the Web query that you want to run.

  4. Click Open.

  5. 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, 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.

  6. If the Web query is a parameter query, the Enter Parameter Value dialog box prompts you to enter parameters, unless you already set the parameter query to return a specific value.

    To edit the settings for the parameters, click Parameters in the Import Data dialog box in step 5.

    For more information, see Customize a parameter query.

    Excel runs the query. A Background Refresh icon Refresh indicator appears on the status bar to indicate that the query is running. To check the status of the query, double-click the refresh icon.

Top of Page

Make a Web page redirect a Web query to another data source

  1. Include the Microsoft Office namespace declaration in the opening HTML tag of your Web page:

    <HTML xmlns:o="urn:schemas-microsoft-com:office:office”>

  2. In the body of the Web page, add the following attribute to the opening TABLE, PRE, XMP, LISTING, or PLAINTEXT tag. For example:

    <TABLE ... o:WebQuerySourceHRef="URL">

    where the value is a URL (relative or absolute) to which you want to redirect your Web query.

Note    Users can bypass redirection on Web pages by selecting the Disable Web query redirections check box in the Web Query Options dialog box. You can access this from the New Web Query dialog box.

Top of Page

Issue: I am having problems sorting, filtering, or searching external data from a Web query

External data from a Web query may contain leading, trailing, or multiple embedded Unicode space characters (value 160). These space characters can sometimes cause unexpected results when you sort or filter data, or perform a search operation.

For more information about how to handle these and other characters, see Remove spaces and nonprinting characters from text.

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language