Office XP Tips: Bring Live Web Data into Excel

Create spreadsheets that update stock prices and other data automatically.

Jim Welp, PCWorld.com

Microsoft Excel has always been great for manipulating data, from stock portfolios to business and financial data to sports statistics. Using Excel's tools--which include statistical functions, charts, graphs, and reports--you can fine-tune your data analysis in a myriad of ways. Excel 2002 ups the ante by letting you take advantage of live, updated information from Web sites. Once you import data from the Web, Excel automatically updates any changes made on the source Web site at an interval you set.

One great application for live data is tracking a stock portfolio. Many Web sites let you create a custom portfolio of stocks you own, with frequent updates to the prices of your stocks. Or you might like to track data from your company's intranet or corporate Web site. Sports junkies might enjoy keeping track of baseball batting averages or NFL passing leaders.

The beauty of tracking live Web data in Excel instead of a browser is that you have all of Excel's tools at your disposal. For example, you could sum a column of stock prices to get running updates of your portfolio's total value. Or you could create a bar or pie chart based on the live data. Whenever Excel updates the data from the Web site, your sums or charts automatically reflect the new info.

Some Lively Information

Excel calls the process of importing live data a "Web query." To take a test drive, launch Excel and choose Data, Import External Data, New Web Query. The New Web Query dialog box is sort of like a browser. It allows you to visit a Web page and select data tables to import into Excel.

Along the top of the window you'll find standard browser elements you can use to find your data. There's an Address field with its companion Go button, Back and Forward arrows, a Stop button, and a Refresh button. In the main window, Excel displays the Web page you designate in the Address field. Excel helpfully points out data it thinks you might want to import by placing yellow boxes, each with a small arrow, beside the data. Hovering your mouse pointer over a yellow box causes a blue outline to surround the associated data. (To turn off those arrows, click the Hide Icons button at the top-right corner of the dialog box, next to the Refresh button.)

To select the data you want to import, click one or more of those little yellow arrow boxes, then click Import. Excel grabs the data from the Web site and displays it in your worksheet. (Note that Excel will import the entire table; if you want a portion of the table's data only, you can delete the unwanted portion in Excel. Once the data is refreshed, however, Excel again displays the entire table.)

Importing Info

To illustrate the process, I'll import a table of market data--but feel free to follow along with any table you like. Like many financial sites, CNN and Money Magazine provide frequent updates to U.S. financial markets. Let's import today's most active NASDAQ stocks.

To begin, enter the following address into the Address field in the New Web Query dialog box: http://money.cnn.com/markets/us_actives.html

When Excel loads the page, scroll down to "NASDAQ Most Actives" and click the yellow arrow box next to it. When you do, Excel turns the arrow into a check mark and changes the yellow to green. Next, click the Options button in the top-right corner of the dialog box. In the Web Query Options box, click "Full HTML formatting" (unless you're a plain-text kind of person). Including the HTML formatting lets your table keep any Web links within it, and the table will be prettier. Click OK to return to the New Web Query dialog box, then click Import.

Next, Excel presents the Import Data dialog box, which lets you specify the location for your data in the current worksheet or choose a new worksheet. Once you're satisfied with the destination, click Properties. The important setting here is "Refresh control" in the External Data Range Properties dialog box. If you want Excel to refresh your data automatically, click "Refresh every" and choose a frequency. Unless you're going to monitor your data constantly, the default 60 minutes is a good option. If you prefer to update only when you open the file, choose "Refresh data on file open." If you don't want automatic updating, leave both boxes unchecked. (You can always update manually.) Click OK to lock in the settings.

Back in the Import Data dialog box, click OK to import the NASDAQ info. Excel retrieves the table and opens the External Data toolbar, which you can use to make changes to your query.

That's all there is to it. As long as you have a live Internet connection, Excel automatically updates the information according to the interval you set in the External Data Range Properties dialog box.

If you want to make changes to the data, the External Data toolbar is the way to go: You can edit your query, open the Data Range Properties dialog box, refresh the current table, or refresh all tables in the entire worksheet. If you prefer to ditch the External Data toolbar, you can access these commands by right-clicking the table to open a pop-up menu.

Caveats

A few things to bear in mind: First, if you have a slow Net connection, the updates might take a while. The updates freeze Excel, meaning that you have to wait while the table is being refreshed. For that reason, you might want to auto-update infrequently or not at all. You can update manually by clicking the Refresh Data button on the External Data toolbar or by choosing that command from the right-click pop-up menu. Also, even though Excel lets you designate an update frequency as brief as every minute, you probably don't want to do that. Few Web sites update information that often, so you'll only be retrieving the same data over and over. Finally, note that any links in your table are live (assuming you imported the info with HTML formatting), which can be handy when you want more information about an item in your table.

More Lively Information

If you've missed any issues of this newsletter (or if you blew out your short-term memory in the sixties), please visit the Office XP Tips page.