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. Then set up the cells as shown in the exhibit.

[ILLUSTRATION OMITTED]

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, below) 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.

[ILLUSTRATION OMITTED]

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, at right) 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, at right). 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).

[ILLUSTRATIONS OMITTED]

Next, click on Parameters and choose Get the value from the following cell." (exhibit 5, at right). 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.

[ILLUSTRATION OMITTED]

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 emails based on any criteria you specify.

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

[ILLUSTRATION OMITTED]

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, at right).

[ILLUSTRATION OMITTED]

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, page 37). 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 email messages.

TECHNOLOGY WORKSHOP

Exhibit 8

This code creates a routine that executes      Private Sub Worksheet
whenever any values are calculated on your     Calculate()
Excel sheet.

This code establishes the criteria used to     price = Range ("C5")
generate an e-mail message. In this example,   Value If price < 30 Then
the e-mail will be sent only if the value in
cell C5 falls below 30. Otherwise, no e-mail
is generated. For different messages based
on different criteria you can substitute any
cell locations for cells B5 and C5 or add as
many more cell references as necessary.

This code creates the variables needed to      Dim Address As String,
generate an e-mail address, subject and        Subject As String
message.                                       Dim Body As String,
                                               Hyper As String

This code sets the e-mail address and          Address =
defines the subject of the e-mail message.     [e-mail]
You can create any subject you wish. All you   Subject = "Excel
need to do to create new messages or new       automated e-mail"
recipients is to set new values for the
Address, Subject and/or Body variables. For
example, to send this message to Anna Rose
instead of Jake Rose, change address =
"[e-mail]" to address =
[e-mail]

This code creates the message. In this         Body = "The price of" &
example, the message tells you the new price   Range ("B5") & "has
of Oracle stock. The & symbol is used to       changed to:" & Format
combine pieces of text and cell references.    (Range ("C5"). Value,
All cell references follow the format          "$ #,###.#0") & "."
Range("cell address"). The Format command
creates a standard format for the
presentation of currency values. If your
criteria are not based on currency, you can
eliminate the Format command.

Defining the Hyper variable creates a          Hyper = "mailto:" &
hyperlink. The FollowHyperlink (Hyper)         Address & "?subject="
command activates the e-mail hyperlink,        & Subject "&body=" & Body
opens your e-mail browser and creates the      ActiveWorkbook.
message. This code uses the Hyper variable     FollowHyperlink (Hyper)
to create the e-mail message.

This code allows the system to wait a few      Application.Wait [Now +
seconds while the e-mail program opens and     TimeValue ("0:00:15")]
the message is generated. You can adjust the   Application.SendKeys "%s"
waiting period if necessary. The SendKeys
"%s" command simulates pressing Alt-S, which
is the keyboard shortcut for clicking on
Send in Microsoft Outlook Express.

The End If code ends the If statement          End If
and the End Sub code ends the routine.         End Sub

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, at right).

[ILLUSTRATION OMITTED]

Then type the following:

Private Sub Workbook_Open()
Range("15").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, page 41.

Exhibit 10

(Code changes are indicated in red type)

Private Sub Worksheet Calculate()

control1 = Range("15").Value

price = Range("C5").Value
If price [is less than] 30 And control1 = "MAIL" Then

Dim Address As String, Subject As String
Dim Body As String, Hyper As String

Address = "[e-mail]"
Subject = "Excel automated e-mail"
Body = "The price of" & Range("B5") & "has changed to:" &
Format(Range("C5").Value, "$ #,###.#0") & "."

Hyper = "mailto:" & Address & "?subject=" & Subject &
"&body=" & Body

ActiveWorkbook.FollowHyperlink (Hyper)

Application.Wait (Now + TimeValue("0:00:15"))
Application.SendKeys "%s"

Range("15").Select
ActiveCelI.FormulaR1C1 = "STOPMAIL"

End If
End Sub

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("15").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 [is less than] 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.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

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 [e-mail] and jakerose @utk.edu.