Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.


Home >  Exploring Excel  > 

An Excel Tutorial:

Use Excel's Web Queries
To Easily View Web Data

(continued from Page 1)

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.

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:

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

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.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  

ExcelUser, Inc.

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.


Excel Dashboards

Learn how to create top-quality dashboard reports with Excel.