Data in web pages can be
displayed a variety of ways:
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.
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
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.
Now you can run your saved web query as described earlier.
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):
http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=["QUOTE","Enter stock, fund or other MSN MoneyCentral Investor symbols separated by commas."]
Here's another example of a
dynamic web query. This simple is for Yahoo Finance where
jasz is a stock market symbol:
This example also queries Yahoo,
but this one puts the results into a single column
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 - Returning External
Data to Excel
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
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
Figure 3 - Parameters
You must use full URLs and they
must be http:// type URLs. Here is a code
example showing VBA syntax:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.domainname.com", Destination:= _
.PostText = "local"
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 1
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.UseListObject = False
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.