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.

ANNA M. ROSE, CPA, PhD, and JACOB M. ROSE, PhD, are assistant professors of accounting at the University of Tennessee, Knoxville. Their e-mail addresses are amrose@utk.edu and jakerose@utk.edu .

ou’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 )—this will track securities prices (in this case Oracle and Cisco) and make any desired calculations—and name the other WebQuery . Then set up the cells as shown in the exhibit.

In the Trading Stock worksheet, in cell C5, under the Market column, type =WebQuery!D4; the data collected in cell D4 in the Web Query worksheet will be copied into cell C5, as shown in exhibit 1 . Copy this formula, with the appropriate cell reference changes, into the Market column cell for each stock you will monitor and fill in the Stock Symbol column. Now you can create a Web Query to fetch current market prices for your portfolio.

To create the query, place your cursor in cell A1 of the WebQuery sheet ( exhibit 2 ) and click on the Data button on the toolbar. Now click on Get External Data and then Run Saved Query (at this point you may also create a new Web Query by clicking on New Web Query ). Now select a saved query and click on Get Data. We selected the saved query, which is called Microsoft Investor Stock Quotes .

In the Woodroof article, the author selected a different saved query ( Multiple Stock Quotes by PC Quote, Inc. at http://webservices.pcquote.com/cgi-bin/excel.exe ). We chose the Microsoft query because the query by PC Quote is not provided with new versions of Microsoft Office.

Click on Get Data to evoke an external data dialog box. Click on the Properties button (exhibit 3) and go to the Properties Dialog Box . Set your refresh rate at 30 minutes (or any appropriate time interval) and your Excel worksheet will regularly update with new data from the Internet ( exhibit 4 ). The same dialog box will appear after you create a database query, and you can set the refresh rate for those queries the same way. Click on OK after you have set the refresh rate and you will be returned to the external data dialog box ( exhibit 3 ).

Next, click on Parameters and choose Get the value from the following cell. ” ( exhibit 5 ). Click on the square icon on the right side of the textbox and highlight the range of cells that contains the stock symbols on the Trading Stock worksheet. Press Enter and click on OK. At this point, Excel will monitor the changes that occur every time a query is refreshed and can send automated e-mails for any events that you specify.

GOING ON AUTOMATIC

Now you’re ready to create automated e-mails for the Excel file that’s linked to live Web data. Of course, you’re not limited to stock prices; you can link Excel to any compatible site.

The next step requires you to create some Visual Basic (VB) code. For convenience, you can download the complete code from http://www.bus.utk.edu/Acct/rose/jofa.html and easily adapt it for other uses. Our code and examples assume the use of Excel 2000 and Outlook Express 5.0.

To begin, open the Trading Stock worksheet that now contains imported Web data. Excel will use the imported data to monitor changes and determine when to send an e-mail. In this example, we will monitor the price of Oracle stock and generate an alert when the stock falls below a specific price. You can generate e-mails based on any criteria you specify.

Click on Tools, Macro and then Visual Basic Editor ( exhibit 6 ).

Next, double click on Sheet 1 (Trading Stock) in the Project-VBA Project screen, which is on the left side of the VB editor ( exhibit 7 ).

This opens a window on the right side of the VB editor into which you will enter the automated e-mail code as a subroutine called Private Sub Worksheet_ Calculate() . The complete code is presented and described in exhibit 8 ). The Private Sub Worksheet_Calculate() statement can be used regardless of the type of data Excel retrieves or the criteria that you establish for generating e-mail messages.

REPEATED MESSAGES

One potential limitation of the code is the way it handles repeated messages. For example, if your sheet is refreshed every 30 minutes and the price of Oracle remains below $30 for the entire day, you’ll receive the same message every time any cell value changes.

While constant feedback is useful in some cases, you may wish to receive only a few notifications each day. To do this, you can reduce the query refresh rate or change your VB code so it sends only one message for a given change.

To change the VB code, double click on This Workbook ( exhibit 9 ).

Then type the following:

Private Sub Workbook_Open()

Range(“I5”).Select

ActiveCell.FormulaR1C1 = “MAIL”

End Sub

The code creates a subroutine that runs every time your Excel workbook is opened. The subroutine types the word “MAIL” in cell I5. Use this field to tell Excel that it should send an e-mail if the price of Oracle stock meets your criteria (that is, if it falls below $30). Now update your code in the Worksheet_Calculate subroutine. Double-click on the Trading Stock worksheet in your VB editor and enter the changes to the e-mail code that appear in red in exhibit 10 .

The additional code serves two purposes. First, adding And control1=“MAIL” to the If statement tells Excel that it should send an e-mail only if cell I5 contains the word MAIL . The range statement

Range(“I5”).Select

ActiveCell.FormulaR1C1 = “STOPMAIL”

changes the text in cell I5 to read “STOPMAIL.” If the price of Oracle remains below $30 the next time the worksheet updates, no additional e-mail messages will be sent.

WORK WITH A DATABASE

The code described above can generate e-mails based on data imported from a database rather than from the Web with just a few changes. First, link an Excel worksheet to an Access database. To create a database query, follow the procedures described in “Spreadsheet, Meet Database; Database, Meet Spreadsheet.” You can link Excel to many different types of database products.

Once linked, open the Excel sheet that contains the database query. Once again, you will click on Tools , then Macro and then Visual Basic Editor. Double-click on the sheet that contains your database query in the Project Screen (just as you did for the Web Query in exhibit 7 ). The code described above for sheets linked to Web sites remains essentially the same. The only necessary changes are to the criteria used to generate messages and the message itself.

Assume, for example, that your company tracks inventory levels in a real time database and you have linked your Excel sheet to that database. Further assume that you want to receive a message whenever the quantity of Item Number 104 falls below 100 (the reorder point). To modify the prior code, replace the price variable definition used in the Web Query example with the following new variables: quantity1 = Range (“H5”). Value (assuming the quantity of Item Number 104 is found in cell H5).

Now replace the old criteria with a new If-Then statement: If quantity1 < 100 Then. The only other change needed is to the message itself. To create the message body, redefine the body variable: Body = “The stock of” & Range(“G5”) & “has fallen to:” & Range(“H5”) & “.”. If the quantity of Item Number 104 falls to 89, you will receive a message that states, “The stock of Item Number 104 has fallen to 89.”

As with Web queries, you can monitor multiple variables with multiple If-Then statements, and you can limit the number of messages using the methods that are described above.

To activate your database monitor and automated e-mail system, open the Excel workbook that contains your database query and VB code. You can travel with confidence, knowing that your database monitor is constantly watching out for your interests. There are vast opportunities to use Excel as a monitoring device. With a little creativity, you can alter the queries and codes to create many different automated systems.