An Excel 2003 Tutorial
Use Excel 2003's Web Queries to View Web Data Easily
Excel's Web Queries provide a quick and easy way to import data from the web to your spreadsheet for business and investment analyses.
by Charley Kyd, MBA
Microsoft Excel MVP
The Father of Spreadsheet Dashboards
One of the amazing things about the Web is the wide variety of raw
data you can find there. That data can be very useful to Excel users in
business, in at least two different ways.
First, this external data can help to put your company's performance into perspective.
To illustrate, the data can show how trends in your financial performance compare to
those of your major competitors. More specifically, they can show how trends in your sales
by region compare with the populations in those regions, and the
regions' levels of
income. They can show which customers are doing well, and perhaps, which
companies should become your customers.
Second, this data can help you to manage your personal finances. This
is because much of the data that can help you to analyze business customers and
competitors also can be used to help you make investment decisions.
In this article, I'll explain how to use Excel Web Queries to import
that data into workbooks...where you can report and analyze the data easily.
Web Queries Under the Hood
Web queries take advantage of a widely used building block of html
pages: web tables.
Most web programmers use web tables frequently. They use tables to
organize data in various sections of their web page. They use tables to
display data in rows and columns. They even use tables to arrange tables
to contain tables!
When you use Web Queries in Excel, you use a special browser to view
whatever web page you want. The browser is special because it uses a
yellow button to mark
every web table found on the page you specify. To return data from the
page you click on the button for each table you want. Then
you click on the Import button to import data from your chosen tables
into an Excel spreadsheet.
The whole process is fast and easy. And you don't need to know
anything about html.
Introducing Microsoft MoneyCentral
I'm going to illustrate web queries with financial data that can be
used to inform both business and investments. The data will come from
the Microsoft web site, Money.MSN.com.
MoneyCentral contains a wide variety of information and services to
help you manage your personal finances. Among these resources is an
extensive database showing the operating performance of publicly traded
companies. Although this data is presumably intended to help you invest
more wisely, you also can use it to better understand the performance of
your customers, prospects, and competitors.
Create Your Web Query
Suppose you want to import Microsoft's recent annual income
statements into Excel. Your first step is to find a web page that
contains the data you want. The easiest way to do this is to use
Internet Explorer to go to MoneyCentral's page
Microsoft Corporation: Financial Statements.
This page gives you access to annual and quarterly financial
statements and a ten-year summary of Microsoft's performance. To return
similar information for another publicly traded company, enter its
symbol in the edit box provided, then click Go.
When you find the page you want, copy the URL. To do so, select it in your
browser's address bar and press Ctrl+C.
To create a web query of the data on the page you've chosen, open a
new workbook in Excel. Then choose Data, Import External Data, New Web
Query. When you do so, Excel launches the New Web Query window, which
resembles a web browser. This browser begins to open your normal home
Unless you want to import data from your home page, you don't have to
wait for the page to be displayed. Instead, you can immediately paste
the URL that you copied from your web browser into the address bar of
the New Web Query browser. To do so, select the address bar and press Ctrl+V. Then choose Go.
You'll probably find that the New Web Query browser takes longer to load
pages than your
normal web browser takes. To make sure the loading process has
completed before you take the next step, make sure that the progress bar
in the lower-right corner of the browser has completed.
Here's the control area for the New Web Query browser:
The Address area is where you paste the URL. You click Go to move to
The next four buttons, Back, Forward, Stop, and Refresh, work just as
they do in your normal web browser. The last three buttons need more
Let's look more closely at these three buttons, working from right to
click the Options button, Excel launches this dialog.
Generally, you'll use this dialog only to specify how much formatting
you want to see in your query results.
At times, however, when your query results are placed in the wrong
columns, or when you have other problems with the query, you can use the
other settings to try to fix the problem.
Use the Save Query button to save the current Web Query as an iqy file.
(The query also is saved with the Excel workbook to which it's
exported.) This iqy file, which can be opened in Notepad, contains all
of your settings for the query. By default, it has a complex file name;
but you can assign any name you want, and to any folder.
Even though you can save the iqy file anywhere you want, you'll probably
find it most convenient to save the file to this folder:
C:\Program Files\Microsoft Office\OFFICE11\QUERIES
The reason for this is that when you choose Data, Import External Data,
Import Data, Excel displays the Select Data Source dialog, which
includes the queries from that folder.
Alternatively, if you choose to save the iqy file in some other folder,
the easiest way to open the query probably will be to find the file in
Windows Explorer and then to double-click on the file.
Here is the full iqy file for the Annual Financial Statement:
The first two lines appear in every query. The third line is the
complete URL, wrapped into three lines in this instance. The arguments
after the double-spaced will vary, depending on your settings.
At times, it would be nice to have the Web Query ask you which company
you want to see. To do so, replace the "msft" ticker symbol in the
previous query with: ["Ticker", "Enter
Doing so produces this iqy file:
When you run this query, Excel first launches this dialog:
With the check box not checked, as shown here, this query will ask for a
ticker symbol each time you refresh the data.
The third item from the right at the top of this figure toggles the
yellow icons shown in the body of the display.
When those yellow icons are toggled on, they appear in the top-left
corner of each html table on the page, as shown in the figure. As the
message near the top of the figure instructs, you click the icon next to
each table that you want to import into Excel.
When you hover your mouse pointer over one of the yellow handles on page,
Excel draws a heavy black border around the table that's marked by the handle,
and the icon turns green, as shown in the figure.
And when you click the handle that marks the data you want, Excel shades the table area and turns the
with the arrow into a green handle with a check mark, also as shown.
After you mark all the tables you want from the page,
click the Import button near the lower-right corner of the web query
window. When you do so, Excel displays the Import Data dialog:
Specify where in your worksheet you want to place the top-left corner
of the data, and then choose OK. After a
short delay, Excel populates the worksheet with the data from the
table, data that typically looks something like this:
This is the data you were looking for. It's complete, with minimal
formatting and no web-formatted overhead. The data is ready to use.
Modify Your Web Query
Excel gives you the ability to modify your web query in a variety of
To recalculate the query, right-click your data and choose Refresh
Data from the bottom of the right-click menu.
To modify the query itself, right-click and choose Edit Query.
To modify the properties of the query, you could have clicked
Properties in the dialog shown above. You also can right-click the query
results and choose Data Range Properties. When you do so, Excel displays
You can give your own name to the query. (Excel assigns this name to
the range of data returned by the query to your spreadsheet. ) Also, you
can refresh the data on a regular basis and make other changes shown
here. You can experiment with the other settings to learn more about
Other Useful Links
MoneyCentral provides several categories of information that you
might find useful. Both Yahoo and
subsidiary of Dun & Bradstreet, provide similar information.
But each site provides some information that the others don't.
To illustrate differences between the sites, MoneyCentral offers five
years and five quarters of financial data about a public company;
Hoover's offers three years and five quarters; and Yahoo offers three
years and four quarters.
Most of the following links provide information about Microsoft, but
you can use the links to obtain information about other public companies:
MoneyCentral Company Report. Provides address, phone numbers, web
site, and summarizes a variety of measures of performance. Both
Hoover's provide similar information.
MoneyCentral Quotes. Provides a variety of stock information,
including average daily volume, day's highs and lows, dividend yield,
and so on. Both Yahoo
Hoover's provide similar information.
MoneyCentral Real-Time Quote. Provides real-time information about a
given company's pricing and volume.
This Yahoo page summarizes the performance of a given company's major
Hoover's Fact Sheet lists the top three competitors for a company,
and lists many other competitors for paid subscribers.
Yahoo Industry Center.
This page allows you to research companies by their industry and sector.
When you find lists of industry data that you want to report or analyze in Excel,
just set up a web query to retrieve the data. The
page provides similar information.
Hoover's Companies. Provides an alphabetic list of all companies
that the site covers.
MoneyCentral Currency Exchange Rates. Provides exchange rates for
virtually any currency in the world.
Can you suggest additional links to web data that Excel users
would find useful? If so, please email your
recommendations to me. Be sure to include a note that explains why
the link is useful and how much it costs, if anything. If I get enough
recommendations to make the effort worthwhile, I'll set up a
page of these annotated links so that everyone can benefit from them.