How to Link to Web Data
Jon Woodroof · Journal of accountancy online/Journal of accountancy · 1999
Automatically download real-time Internet information to your spreadsheet. 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, page 41.) 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 other's 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 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 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 Office\Queries.) See exhibit 1, above, 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. …