The Automated Spreadsheet

EXECUTIVE SUMMARY * A SPREADSHEET CAN ALERT YOU to significant changes in data in a network database or on the Internet. * TO TRIGGER THE ALERTS, all you need is a computer that's linked to the Internet and the special code to command your spreadsheet application to search for the data. * IT IS, POSSIBLE TO: * Track stock market activity. * Monitor new information in your organization's database that requires a rapid response. * Keep a corps of business travelers current on different key market or inventory information. You're about to leave on a business trip. You've packed your laptop, Palm personal digital assistant, e-mail pager and cell phone. Yet despite all of this high-tech equipment, you feel disconnected from your office even before you board the plane. After all, you realize that being on the road significantly increases the possibility you won't be able to adequately monitor your business even if you can remotely connect to the Internet. What if you had a function that could robotically perform some of the monitoring for you--automatically, 24 hours a day, seven days a week--and alert you when it's appropriate? Well, you probably already have that power--Excel--in your briefcase. But to use it you need a computer that's linked to the Internet. This article will show you how to use Excel to search for the data you need and then, when certain preset criteria are met, alert you to take action--wherever you are. Here are some of the things the Excel function can do: * Track stock market activity. Excel can import current stock market data from the Internet and alert you by e-mail to critical market changes that require immediate action. * Monitor information in your organization's database that requires a rapid response. For example, alerts can be triggered by significant overtime on an engagement, unusually large sales, orders from your largest customers, receipt of an important shipment, failure to deliver products at specified times, unexpected changes in production quality or quantity, spending in excess of budgeted limits and unusual ratio fluctuations. You can program Excel to send e-mails to selected people in response to an alert. * Keep a corps of business travelers current on key market or inventory information. Excel can automatically activate e-mails to those out of the office, notifying them of changes in service or product prices, product availability, special discount offerings--and each person will receive only personally relevant information. Now let's learn how to create these automated messages. GET IT LIVE In prior issues of the JofA, Philip Haase ("Spreadsheet, Meet Database; Database, Meet Spreadsheet," JofA, Dec.99, page 33) detailed how to link Excel to an Access database with a database query, and Jon Woodroof ("How to Link to Web Data," JofA, Mar. 99, page 55) demonstrated how to use Excel to retrieve live data from the Internet with Web Queries. Readers can refer to these articles. In this article we will follow the directions in the above articles to link one Excel worksheet to a database and one to a Web site. We then will use the linked worksheets to develop and send automated e-mail messages. There is one critical change to the linking processes described in earlier JofA articles. The latest edition of Excel (2000) allows you to set the refresh rate (the timing intervals of the updates) to query either a Web site or a database. Excel 97 did not do that. Before designing an automated e-mail system, let's review the basic process of linking Excel to the Web, which is described more fully in "How to Link to Web Data." First, create an Excel file with two worksheets. Call one worksheet Trading Stock, (exhibit 1, at right)--this will track securities prices (in this case Oracle and Cisco) and make any desired calculations--and name the other WebQuery. …