Get external 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 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

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

To create a Web query, you need access to the World Wide Web (WWW) (World Wide Web (WWW): The multimedia branch of the Internet that presents not only text, but also graphics, sound, and video. On the Web, users can easily jump from item to item, page to page, or site to site by using hyperlinks.) 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 (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.) file, MHTML (Single File Web Page (MHTML): An HTML document saved in MHTML format, which integrates inline graphics, applets, linked documents, and other supporting items referenced in the document.) file, or well-formed XML (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) 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 (XML stylesheet: Contains formatting rules which are applied to an XML file that references the stylesheet. The standard set of rules for XML stylesheets is the Extensible Style Language (XSL).). 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 (Web query: A query that retrieves data stored on your intranet or the Internet.) from Excel redirected to a data source such as an XML (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) 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 (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/.), 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 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.

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

 Note   The maximum length of a URL is 255 characters.

  1. Click Go.

Edit an existing Web query    

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

Excel Ribbon image

  1. In the Workbook Connections dialog box, select the Web query, and then click Properties.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
  1. Click thebutton image button next to the tables that you want to import, or click the button Button image in the upper-left corner of the page to import the entire page.

 Tip   If there are no Button image buttons next to the tables on the page, click Show Icons Button image at the top of the dialog box to display them.

  1. 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:
  • Under Formatting, click one of the following:
  • 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.
  • 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.
  1. Click OK. The Web query is saved with your workbook.
  2. 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.

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

  1. Enter a file name in the File name box.
  2. Click Save.

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


  1. Click Import.

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

After you click OK, a Background Refresh icon Refresh indicator appears on 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.

Top of Page 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.

  1. Right-click the file, and then click Edit with Notepad. The query opens in Microsoft Notepad, where you can make changes to the file.
  2. 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 Top of Page

Copy data from a Web page

  1. In your Web browser (Web browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Windows Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), select the data that you want to copy.
  2. Copy the data.

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

  1. Switch to Excel.
  2. Click in the upper-left corner of the worksheet area where you want the copied data to appear.
  3. On the Home tab, in the Clipboard group, click Paste.

Excel Ribbon image

  1. 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 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 Button image, and then click Open.
  1. In the Look in box, locate the folder where your query file is stored.
  2. Select the Web query that you want to run.
  3. Click Open.
  4. In the Import Data dialog box, do one of the following:
  5. 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 (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.

Top of Page 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 (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.) tag of your Web page:

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

  1. 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 (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/.) (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 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 (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) 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 Top of Page

 
 
Applies to:
Excel 2007