How To Link To Web Data

Would you like to download financial, sales or stock market information from a Web site and then plug it directly into a spreadsheet on your computer? Better yet, would you like all the data refreshed automatically every time you open the spreadsheet file? Just a year or so ago, you would have had to go through a series of tedious steps to perform such downloads. (See Taking Stock on the Internet, JofA, Jan.97.) But now, with the Web Query function in Excel 97, a built-in wizard will walk you through the entire setup.

Why, you may ask, would you want to do that? There are many reasons. Consider just a few of them.

• Up-to-date corporate financial statements or other financial data often can be found on a Web site, so anyone wishing to analyze that information in greater depth conveniently can download the data into an instantly updated spreadsheet.
• Employees who travel or work at home can access their companies Web pages loaded with data such as sales and financial and inventory information and download the fully formatted data directly into spreadsheet files for in-depth analysis.
• Suppliers and their customers that need to keep in close touch can access each others delivery, sales and inventory data as a way to synchronize records and the transfer of goods.
• Publicly held businesses can track the market value of securities they hold automatically. The posting of the securities market value is required under FASB Statement no. 115, Accounting for Certain Investments in Debt and Equity Securities . Tracking the information manually is tedious, but this computer tool can do it with a few mouse clicks.
• Individual investors can keep track of their portfolios by downloading the latest stock prices into spreadsheets for analysis.
• Currency exchange and money market rates, which are in constant flux, can be fed directly into formulas or charts and refreshed to keep spreadsheets updated automatically.

DYNAMIC DATA
What makes this Excel feature especially valuable is that it provides an opportunity to create a spreadsheet with dynamic rather than static data. An ordinary spreadsheet is static, that is, the information changes only if you open it and manually enter new data. Of course, if you link your spreadsheet to another file, another spreadsheet, a database or a word processor file, any changes in that linked file automatically will be reflected in your spreadsheet file, converting it into a dynamic file. Web Query links your computer files with a remote Web site that is continually updated.

Some businesses post their current financial statements on their home pages in an Excel format. Individual investors or security analysts can link to those sites, download the latest data into their own spreadsheets and analyze the information at leisure.

In addition, many companies post password-protected sales and inventory data on Web sites, linking traveling sales staff with the home office to keep all data synchronized. In fact, some businesses even provide access to such password-protected sites to suppliers and corporate customers so they, too, can synchronize their data.

HOW IT WORKS
Web queries can be embedded in an Excel template so the spreadsheet automatically pulls in external data. Excel includes sample queries that work without modification, but if you have special needs, you can modify a query easily. Users with Microsoft Office loaded on their computers can find the sample queries under Microsoft OfficeQueries.)

See exhibit 1 for a fully formatted Excel template created for this article to meet the requirements of FASB Statement no. 115. A user can download that template from http://woodroof.mtsu.edu/downloads/JofA.htm. The template, labeled Portfolio.xls, contains two sheets: Trading Stock and Web Query. To customize the template with your choice of securities, all you have to do is plug in the appropriate stock symbols and the number of shares held for each security on the Trading Stock sheet.

The Trading Stock sheet can accommodate any number of stocks or mutual funds. Simply insert a row within the created range, copy the formulas and input the appropriate stock or mutual fund symbol.

However, if you want to build this template from scratch, follow these steps:

• Create an Excel file with two sheets. Label one Trading Stock and the other Web Query. On the Trading Stock sheet, format the columns as indicated in exhibit 1 and add the symbols for the stocks or mutual funds you want to monitor. Under the Market column, copy this formula: =WebQuery!B5*C5. Now copy this formula into as many cells as there are stock or mutual fund symbols that you want to track.
• On the Web Query sheet, place your cursor in cell A1. Click on the Data button on the toolbar and click again on Get External Data. Then click on Run Web Query. A menu of sample Web queries is displayed, as shown in exhibit 2. Select the query you want (for this application, select Multiple Stock Quotes by PC Quote, Inc.iqy).
• Click on Get Data, which displays the screen Returning External Data to Microsoft Excel, as shown in exhibit 3. By default, the results of the Web Query will be placed in a range whose upper left corner is A1.
• Click on Properties. The dialog box shown in exhibit 4 will appear. Set the properties exactly as shown in the exhibit and click on OK, being especially sure that the box Refresh data on file open is checked.
• Click on Parameters (see exhibit 3). The dialog box shown in exhibit 5 will appear. Choose Get the value from the following cell and click on the square icon at the right of the text box. Now click on the Trading Stock page and highlight the range where the stock symbols are placed (see exhibit 1). Press ENTER and then click OK. The Returning External Data to Microsoft Excel dialog box (exhibit 3) reappears. Click on OK.

Current stock prices for the companies in the stock symbol column are automatically pulled in, as shown in exhibit 6. The updated Trading Stock page will look similar to exhibit 7, below.

Now save the spreadsheet template. As it is saved, the query is automatically embedded into the file. The next time you wish to generate the investment report to comply with FASB Statement no. 115, just open the spreadsheet. Web Query will command the computer to go out to the Internet, download the data and then generate the report as shown in exhibit 7.

It can’t get much easier, a dynamic link between a simple spreadsheet and a live Internet data source.

As you can see, there are many places to use this handy tool. Once you get started, you’ll probably find other uses to make your work more efficient and effective.