
                Automated Documentation Of Spreadsheet Equations
                ------------------------------------------------

                                       by

                      Copyright (c) 1988, John D. Pearson


Have you ever wondered why spreadsheets have to be so @DA..  @DIFF..  to read.
Commercial "cell" listings help but so far they do not translate the structure
of the spreadsheet back into english.  This program tries a different tack.

It assumes that your spreadsheet follows a convention that rows correspond to
variables and columns to time periods like so:

               A         B         C         D         E
     1
     2         ;;           Jan       Feb       Mar       Apr
     3         apples         1         2         0         3
     4         oranges       22        44       -10       100
     5         -----     ------    ------    ------    ------
     6         Total     +B3+B4    +C3+C4    +D3+D4    +E3+E4

The example shows a very simple spreadsheet.  Apart from the data it has one
repeated formula in the cell range A6..E6.  Commercial cell listing programs
repeat this formula in every cell as shown.  AutoDoc treats this as an equation.

To AutoDoc an equation is simply a cell formula that is repeated for two or more
cells in a row.  In the example above the equation in range A6..E6 defines the
"total" as the sum of the "apples" and "oranges" for each of the periods "Jan"
to "Apr".

AutoDoc recovers the equations from the spreadsheet and displays the equation in
the context of the text and data as follows.

Cell Range     |A        |B        |C        |D        |E
---------------------------------------------------------------
     A2..E2    ;;        Jan       Feb       Mar       Apr
     A3..E3    apples    1         2         0         3
     A4..E4    oranges   22        44        -10       100
     A5..E5    -----     -----     -----     -----     -----

               Total[t] = apples[t] + oranges[t]
                 = <<23..103>>, for t = Jan to Apr in B6 to E6

     A6        Total     23        46        -10       103

Now this may appear to be trivial (and it is), however take a more complicated
situation with many equations with forward and backward references and this
simple idea makes a complex spreadsheet very readable.

AutoDoc generates an ASCII output file like the above which contains the
following:

         The first column shows the range of cells in the
         rows that contain entries since only one page width
         of text/data will be displayed. (Up to 255 characters)

         The remaining columns contain the entries as they
         might appear on the screen but left justified in
         each column width.

         All equations found are summarized as above with
         [t] referring to the current cell "time period" and
         <<23..103>> provides the first and last values
         computed using the equation.  The cell range shows
         where they are in the spreadsheet row even if not displayed

You may customize the names by adding a column and rows(s) of sensible
abbreviations then add markers ;; as above to show which row contains the
current column headers and which column contains sensible variable names.

If you do not make any changes to your spreadsheet then AutoDoc will construct
variable names from the leading text in each row, but the column headers will be
the usual 1-2-3 headers.  A..IV.  AutoDoc generates proper names that do not
contain algebraic operator symbols (+-/).  If nothing is provided then surrogate
row names are invented Row:236 etc.  Even this can be helpful.

AutoDoc appends a listing of the ranges found and a simple unsorted listing of
the references to each of the variable names or rows.

Absolute row and column references are flagged as in 1-2-3 using the $ sign.  An
absolute column reference will be [$D] while an absolute row reference will
appear as row:232:$[$D].  If range names are defined then AutoDoc uses these in
the equation listing in upper case.

Operation And Screen Output
---------------------------

To use AutoDoc you will need this program and an IBM compatible running with PC
or MS-DOS, then type:

AUTODOC  [input [output]]

at the DOS prompt, with [ ] meaning optional.  (Do not type the [] symbols).
The INPUT and OUTPUT files may be fully qualified drive\path\name specifications
with .WK1 default on input and .FOR on output.

It takes no other parameters.  If neither INPUT nor OUTPUT are given then
AUTODOC is entirely menu driven.  A configuration file is automatically written
to the source drive for each use and is read for the default options next time.

For example if you have a file test.wk1 on c:\123\data and your AUTODOC.EXE file
is in a utility directory c:\utils then type:

c:
cd c:\123\data
c:\utils\autodoc

-- and you will be prompted for everything else by the menus including the
format options available for the formula file output.  The resulting
configuration file will be written back to "c:\utils\autodoc.cnf".

The help key F1 and the file choice keys F9 and F10 provide a range of available
choices for each option in most cases which try to be self explanatory.  Try
experimenting on the test example to get a feel for the programs capability.

After providing the file to be listed and approving the output format, AUTODOC
will read the file once to identify all usable row and column names.  Next it
re-reads the file and generates the equation listing on an auxiliary file with
extension .FOR.  At the same time a video MAP is displayed showing the first 55
columns of the spreadsheet.  You may also list this map as one of the options.

The MAP display flags in color and highlights changes in row structure along
each row.  This may show a formula that has been miscopied along a row.  Each
break in the row is independently listed to the formula file and should be
examined.  Most users are so entranced with the marching screen display that
they do not notice the errors.  The MAP list reports the changes by capitalizing
the cell type symbols.

Limitations of this program:
----------------------------
Comments on the current version (3.5) of AutoDoc:

AutoDoc ONLY thrives on spreadsheets that follow the variable/time convention
described above.  On all others it will generate more paper than insight since
every cell may becomes a fresh equation.

It is designed to fully process rows 1..1001 of Lotus 1-2-3 version 2 files.
Further rows 1002..  are processed without any ;; names but otherwise everything
else continues.  First time users with an arbitrary spreadsheet are advised to
turn on "variable names" and "row numbers" as configuration options.

If you have insufficient memory then on large spreadsheets or with a large
number of range names you may encounter a "heap over-flow" error.  Memory used
to be cheap..buy some.

It does NOT automatically check and correct for duplicate variable names, macro
text or range names used out of context.  New 1-2-3 or @addin functions will
appear as "@unknown_function" and cause that formula to be truncated but the
listing continues.

Be warned that absolute row or column references can be VERY misleading.  If
duplicate names are used then key references will appear to be the same unless
the option "add row numbers to names" is used.

The popular LIST.COM program by Vernon Buerg and available on most bulletin
boards, is an excellent way to peruse the output file and selectively print
pages.

------------------------------------------------------------------------------
Finally this is a simple but powerful interpretative program.  It is your
responsibility to review the output carefully and to base any conclusions
derived from the results solely upon your own judgement.  If in doubt check a
1-2-3 cell listing.

While every effort has been made to ensure that the program is bug-free on a
turbo XT and on a COMPAQ 386, there are almost certainly situations and PC
configurations that AutoDoc has not encountered and in which it may not function
correctly.
------------------------------------------------------------------------------

Updates
-------
AutoDoc is copyrighted by the author and distributed "as-is" for comment and
personal use only.  It is not placed in the public domain.  You are encouraged
to try it and circulate it with this file attached.  If you find it useful or
for an updated version of this program with documentation write to:

     Dr. John D. Pearson,
     2007 Franklin Avenue,
     McLean Va. 22101.

and enclose $15 to cover expenses.  Suggestions for improvements are welcome.


This file supercedes version 2.1 called AD21.ARC (version 3.6 9/1/88.)

It is automatically appended to the .FOR output file as long as it is
on the source path to AUTODOC.EXE

