Making and Using Excel web queries

Web queries bring tables from the web directly into Microsoft Excel. These instructions explain how to make and use web queries for Microsoft Excel on your Mac.

By Jim Gordon, co-author of
Office 2011 for Mac All-in-One For Dummies

Data in Web pages

Data in web pages can be displayed a variety of ways:

With Microsoft Excel 2011 for Mac you can grab data from row and column HTML tables. If a query result is presented as an HTML table, you can capture that too. You can't import data directly from pictures or PDF files. Only data arranged in row and column HTML tables can be imported using a web query. Other techniques (e.g. Optical Character Recognition, Adobe PDF tools, etc) not discussed on this page can be used for capturing data from pictures and PDF files.

Getting the data

You can get HTML row and column data by running a web query. A web query is a text file  saved with a .iqy file extension. A web query file contains the URL of the web page that holds the data. 

Free samples - but now they're broken!

Excel comes with these three free sample web queries that were supposed to let you see web queries in action: Dow Jones put the kabash on these samples, so they no longer work. 

See below ffor how to make your own queries. Once you've made a web query, follow these instructions to run it.

Run a web query

Your Mac must have a live internet connection to run queries on web pages that are found on the internet. 

There are three ways to run a web query in Excel 2011. Excel comes with some example web queries. To try one of the example queries, run the MSN MoneyCentral Currencies query using the first method described below. The result set will be a data range containing up to the minute currency exchange rates for the US dollar vs other currencies. The query becomes part of the Excel worksheet. The result set of a query is called a QueryTable.

To refresh the data from the web page, select any cell within the query results and then from the Data menu choose Refresh Data.

Web query results are displayed differently in Excel from the way the look on a web page. Formatting is minimally retained. Hyperlinks may or may not work. "Relative" hyperlinks and specialty hyperlink protocols will not work when clicked.

Make a simple web query

The simplest web query consists of the URL of a web page which has an HTML table saved as a text file. To make your web query, take the following steps:

  1. Open Microsoft Word to a new, blank document
  2. Type or paste the URL of the web page that has an HTML table into your document. In this example use http://www.agentjim.com/. When pasting a URL, use the little widget that appears and Keep Text Only from the pop-up menu.
  3. Use File > Save As and choose file format Plain Text (.txt). Give the text file a sensible name. Navigate to this location: Applications:Microsoft Office 2011:Office:Queries and then click the Save button.
  4. The File Conversion dialog opens. In the options for Text Encoding choose MS-DOS, and in the Options section click the check box for Insert Line Breaks. Then click the OK button to complete saving your file.
  5. We're done with Word. If you want to he a purist, Take this optional step: in Finder, navigate to the file you just saved. Change the file extension from .txt to .iqy. You have to do this in Finder because Word won't let you change the file extension form .txt to .iqy when you save the file. The web query file will work even if you don't change the file extension to .iqy and leave it as .txt.

Now you can run your saved web query as described earlier.

Make a dynamic (parameter) web query

Some web pages request input so that custom result sets can be delivered. You can see how such a web query is constructed by opening the files in Microsoft Word to examine the contents of the built-in web queries. When you run the web query MSN MoneyCentral Stock Quotes you are prompted to enter the code for a particular stock. If you use Microsoft Word to open the sample web query MSN MoneyCentral Stock Quotes you can see the text for that query. This query uses the formal structure for a web query. The question mark embedded after the URL triggers Excel to display a dialog box prompt for the web page, which is expecting a stock market SYMBOL parameter from a web browser. QUOTE is the name of the parameter (See Figure 3 below):

WEB

1

http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=["QUOTE","Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas."]

 

Selection=EntirePage

Formatting=All

PreFormattedTextToColumns=True

ConsecutiveDelimitersAsOne=True

SingleBlockTextImport=False

Here's another example of a dynamic web query. This simple is for Yahoo Finance where jasz is a stock market symbol:

http://www.finance.yahoo.com/q?f=jasx

This example also queries Yahoo, but this one puts the results into a single column

http://finance.yahoo.com/d/quotes.csv?s=ORCL&s=CSCO&s=COHR&f=sl1

Working with query result sets

The result set querytable of a web query has many properties you can control. When you first make your web query, there are settings available to you in the Returning External Data to Excel dialog (see Figure 1).

Figure 1 - Returning External Data to Excel

Returning Data to Excel dialog screen shot

Click the Properties button to display the External Data Range Properties dialog when you first make a query. You can also display this dialog by right-clicking into the querytable result set and choosing Data Range Properties from the pop-up menu. The options presented are context sensitive. Not all options are available for web queries.

Figure 2 - External Data Range Properties

Data Range Properties dialog

If your web query is dynamic (requests a parameter), the Parameters button becomes available in the Return External Data to Excel dialog box. Click the Parameters button to display the Parameters dialog, (see Figure 2). In this dialog you can modify the text of the prompt, enter a value to always use, or choose a cell and use that cell's value to satisfy the prompt.  The Parameters dialog can also be displayed by right-clicking in the querytable result set of a parameter query and choosing Parameters from the pop-up menu.

Note the option to "Fill down formulas in columns adjacent to data." Enabling this option lets you use calculated columns with the web query result set.

Figure 3 - Parameters

Web Query Parameters dialog

Web queries and Visual Basic for Applications (VBA)

You must use full URLs and they must be http:// type URLs. Here is a code example showing VBA syntax:

Sub MakeWebQuery()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.domainname.com", Destination:= _
        Range("A1"))
        .PostText = "local"
        .Name = False
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = True
        .RefreshOnFileOpen = 1
        .BackgroundQuery = False
        .TablesOnlyFromHTML = True
        .SaveData = True
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
End Sub

Use the following code to refresh a query. The selection cursor must be in the QueryTable before running this code. If a parameter query is refreshed, the user will be prompted for parameters.

Sub RefreshQuery()
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub