| CPAs can use Web
sites to store many years of
data, permitting clients quick and easy
access 24/7. The sites, also called
reporting portals, can handle data that
are formatted in many different ways. |
Data that are
otherwise hard to display can
be formatted without any special
technology so they can be read by a Web
A special function
in Excel can transform a static
file being uploaded to a Web site to a
dynamic, or interactive, file. Typical
dynamic worksheets are loan-payment
calculators and pivot tables, whose data
adjust when their underlying information
Easy access to
sensitive client data comes
with a price. You don’t want such
information to be easily accessible to
everyone, so you must implement very
effective security measures.
organization’s Internet site
does not have effective
security, you have two choices: Hire a
Web consultant with security experience
or rent a professionally managed site
and let its specialists set up a
security system for you.
CPA/CITP, is the founder of Click
), Seal Beach, Calif., which
specializes in network support and
application development. His e-mail
ow do you distribute
financial reports to clients, shareholders and key
people in your organization? If you’re low-tech,
you probably mail them. If you’ve been following
my suggestions of several years ago, you create
hyperlinks in Excel files and e-mail the files so
clients can drill down into them for details (see
Financial Reports in a Snap, ” JofA
, Apr.00, page 31). But today’s technology
lets you do it a better way: uploading all
clients’ financial information into a Web site so
they can access current and past reports 24/7, no
matter how the data were originally formatted. If
you want to learn how to do that, read on.
To help readers follow the
instructions in this
article, we used two
type is used to identify
the names of icons, agendas
serif type shows the names
of files and the names of
commands and instructions
that users should type into
As you know, client data come in different
formats. For example, tax software programs
calculate and display returns with proprietary
technology, and usually are able to convert
themselves into the free Web-friendly Adobe
Acrobat (files with a .pdf extension). Tax
professionals usually prepare support data for
schedule D basis computations in Excel, which
isn’t native to a Web browser, but it, too, can
convert itself to hypertext markup language (HTML)
so browsers can read it. P&L statements, which
CPAs usually calculate and display with
proprietary accounting software programs, also can
translate themselves into Adobe Acrobat. Thus CPAs
can easily store this virtual Tower of Babel of
financial information, and clients can easily
access it any time of the day or night via a Web
site without any special technology.
| ||No Web
Site? No Problem |
I f your
organization doesn’t have a Web
site, you can use your local
area network (LAN) to achieve
the same goal—give quick and
easy access to years of
corporate data to those
employees who are on a LAN. The
techniques to store the data are
almost identical. Security must
be given high priority.
A Web site also lets you extend Excel’s
presentation in a very powerful way. If you
display a static worksheet, such as a balance
sheet, and change only a single number on it, you
have to replace the entire worksheet. But a
special function in Excel transforms a static file
being uploaded to a Web site into a dynamic, or
interactive, file. Typical dynamic worksheets are
loan-payment calculators and pivot tables, whose
data adjust when their underlying information
changes; there’s no need to upload a whole new
A B ANK OF D ATA
that we’ve described the flexibility and diversity
of information that can be stored on the Web,
let’s see what such a site, also called a
reporting portal, looks like.
Exhibit 1 shows a screenshot of a typical
opening page of a portal created by a CPA firm. As
you can see, it contains three years of data—2003
to 2005—for client John Smith.
A click on any of the years’ icons drills down
to three further choices for that period: a .pdf
copy of the client’s 2004 final tax return, an
HTML version of an Excel file on schedule D basis
support and a .pdf version of final QuickBooks
financial statements (
exhibit 2 ).
If a client is applying for a mortgage, say,
and needs a copy of last year’s tax return and a
current P&L statement, all he or she has to do
is open the portal and click on a few icons for
C ONVERT AN E XCEL F ILE
To transform an Excel worksheet so it can be
viewed by a browser, begin by opening the target
file, which has an .xls extension (see exhibit 3 ).
Then click on File ,
Save as Web Page ( exhibit 4 ).
That launches the first screen of a
Save As wizard ( exhibit 5 ) that performs
the actual conversion. In the File name
box add a name; I used bsheet.htm . Be
aware that the original Excel file ( .xls ) is
left intact. You can select how much of the
spreadsheet to include in the new bsheet.htm .
You’ll get the entire workbook if you place a
check next to Entire Workbook or
just the current worksheet if you check
Selection: Sheet . Click on
When the .htm page appears, it will be an exact
visual duplicate of the original file right down
to the formatting of the double underlines ( exhibit 6 ).
Finally, to make the report available for
viewing, upload bsheet.htm to your Web server.
To prepare a dynamic display for the site,
create an interactive file, such as a loan-payment
worksheet (see exhibit 7
), which uses Excel’s = pmt()
function in C6 to perform the dynamic
Then follow the
same steps for a static file except place a check
in the Add interactivity checkbox
(see screenshot at right) in
exhibit 5 .
To specify additional
options, click on the Publish
button (see exhibit
5 ) to produce the dialog shown in exhibit 8 , below.
Make sure you select Spreadsheet
functionality from the Add
interactivity with option. The other
choices in that menu are Pivot Table
functionality , which allows you to
display pivot tables, and AutoRepublish
option, which automatically uploads to
the site any revisions you make in that workbook.
Click on Publish to close
the wizard and the loan-payment workbook is ready
Alert : In order to use the dynamic
worksheet you must access it with Microsoft’s
Internet Explorer browser; it will not work with
Firefox or any other browser.
TECH 2006: The
AICPA Information Technology
THE SECURITY ISSUE
Easy access to sensitive client data comes
with a price. After all, you don’t want such
information to be readily available to everyone.
So you must implement very effective security
measures to be sure only authorized users get at
If your organization’s Internet site
is of a do-it-yourself variety, it’s not likely to
have an effective security feature. That leaves
you two choices: Engage a Web site consultant with
security experience, or instead of using your own
site, rent a professionally managed site and let
its specialists set up a sophisticated security
system for you. For a list of vendors that provide
such services, see “ Space for
Rent, ” below.
As you can see, using
a reporting portal provides you with a convenient
place to store many years of data and allows your
clients to view information, even if it’s
formatted in proprietary ways.
||Space for Rent
Vendors that rent Web
) provides Web sites with
CyberCabinet online file
sharing. Prices start at $695
www.cch.com ) software,
ProSystem fx File
Share, allows secure file
sharing. The first 50
megabytes (Mb) of storage
space are included with the
basic subscription. Additional
space is available in 100 Mb
increments for about $100 a
year. Free trial is available.
) offers Web site
templates with a secure online
file-exchange portal. Prices
start at $50 per month.
Solutions Thomson (
) offers two products, Web
Builder CS and NetClient CS,
that provide clients with
access to their financial
reports through the Internet.
Demos are available on its Web
site. Prices start at $120 a
month, with a one-time setup
fee of $500.
www.ike.com ) offers
secure file sharing. A 15-day
trial is available. Prices
start at about $100 a year.
including file sharing. A
30-day trial is available.
Prices start at about $50 a
www.xdrive.com ) lets
clients share files securely.
Free trials are available.
Prices start at about $100 a