Home >  Exploring Excel  > 

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
November, 2005

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

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

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 titled 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 page.

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 that address.

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

Let's look more closely at these three buttons, working from right to left...

Options...

When you 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.

Save Query

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 ticker"] 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)