Latest Stories

The Automated Spreadsheet

“Hello, this is an alert from your spreadsheet. Call your office ASAP.”

BY ANNA M. ROSE AND JACOB M. ROSE

Related

TOPICS

Uncategorized Article

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.

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.

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.

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.

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.

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.

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 zarowin@mindspring.com
.