An Excel Tutorial:
Use Excel's Web Queries
To Easily View Web Data
(continued from Page 1)
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.