An Excel 2003 Tutorial:
Use Excel's Web Queries
To Easily View Web Data
Excel's Web Queries provide a quick and easy way to import data
from web pages to your spreadsheet. You can use this data to
improve your business and investment analyses.
by Charley Kyd
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,
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.
To illustrate how this data could be used,
this ExcelUser page lists nearly 200
public companies. Each company in the list is linked to an
Excel-generated dashboard of the company's performance. The data for
those displays came from Hoovers.com.
But in the coming months I'll probably change the source to
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.
(continued on Page 2)