ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd

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.


Charley Kyd is a Microsoft Excel MVP 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 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:

Excel's New Web Query dialog

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

Excel's New Web Query dialogWhen 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:

WEB
1
http://moneycentral.msn.com/investor/invsub/results/
statemnt.asp?lstStatement=Income&Symbol=msft
&stmtView=Ann

Selection=10
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

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:

WEB
1
http://moneycentral.msn.com/investor/invsub/results/
statemnt.asp?lstStatement=Income&Symbol=["Ticker",
"Enter ticker"]&stmtView=Ann

Selection=10
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

When you run this query, Excel first launches this dialog:

Excel's Enter Parameter dialog

With the check box not checked, as shown here, this query will ask for a ticker symbol each time you refresh the data.

Hide Icons

The third item from the right at the top of this figure toggles the yellow icons shown in the body of the display.

Excel's New Web Query dialog

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 green handle 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:

Excel's New Web Query 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:

Excel's Import Data dialog

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

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 this dialog:

Excel results

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

Other Useful Links

MoneyCentral provides several categories of information that you might find useful. Both Yahoo and Hoovers, a 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 Yahoo and 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 and Hoover's provide similar information.

MoneyCentral Real-Time Quote. Provides real-time information about a given company's pricing and volume.

Yahoo Competitors. This Yahoo page summarizes the performance of a given company's major competitors. The 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 Hoover's Industries 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.

 




Dashboard Reporting With Excel


Charley's SwipeFile charts