l---+---L1----+-T--2----T----3--T-+----4T---+---T5----+-T--6----T----J--T-+--r
TRKINVES.DOC
Documentation for TRKINVS.WKS
By George Church
2308 NE 30th Court
Lighthouse Pt. FL 33064
(305) 942-6663
May 23, 1987

TRKINVS.WKS is a Lotus 123 worksheet to track the relative
performance of investment funds. This version only works on
funds which automatically reinvest income and gains. It
provides graph capability for each individual fund as well as
a comparative graph.

THIS IS A TEMPLATE! COPY THIS TEMPLATE TO A SEPARATE FILE FOR
REUSE ! (i.e. "copy trkinves.wks template.wks")

The worksheet provided is a TEMPLATE which must be customized
to suit your funds. Note that most of the worksheet is range
protected to prevent inadvertent erasure. Therefore, when the
sheet is customized, protection must be defeated (/WGPD).
After you finish with customizing re-enable protection
(/WPGE)

CUSTOMIZING The worksheet provides for tracking 6 funds. If
you need less, all rows associated with that fund, beginning
with the title row and including the blank line after the last
entry. (For example, to delete fund #6 in the template, delete
row:
22 X__(enter name of fund 6 at X)
23 6 ______ __ ___ etc
24 a blank line.)
The command is (/WDR).

For more than 6 funds, add copies of the fund template section
described above, as follows:
1. Make room.
Put the cursor on the solid dashed line below the last
fund, and type (/WIR) followed by two down arrows, then
.
2. Mark a fund template for copying.
Put the cursor in COL A of the fund title.
Type (/C.) NOTE the Period.
With the down arrow & the right arrow, mark the three rows
all of the way out to COL AB.
When the command line asks "Copy to___",
move the cursor to COL A of the second blank line after
the last fund entry and press .
Change the number of the fund to a new number.
NOTE: There must be a blank line following the last entry
for each fund.

NAME EACH FUND:

For each fund, go to COL B of the line which reads
"X__(enter...) and overwrite with the full name of your fund.

NOTE: The entire fund name must be written in cell B. CELL A
MUST BE BLANK!

Now, copy cell B to cell L and to cell R on the same row.

When each fund has been named, reinstate protection (/WGPE).

MAKE FIRST ENTRY:

In COL I (that's the letter i) of the row with the fund name,
enter the fund load. (e.g. If the fund charges a 3%
commission on purchases, enter 3. If no commission, enter 0.)

Fill in the (unprotected) blanks on the 'first entry' line by
overwriting.

Do not change the number in cell A!

In Cell B, put a short abbreviation for the fund name (not to
exceed 6 letters, preferably less).

In cells C, D & E enter the month, day and year (2 digits for
year).

In cell F, enter the transaction type (purchase, update etc.).

In cell G, enter the transaction code (see row 2 at top of
form).

In cell H, enter the dollar & cents amount of transaction. If
zero, enter 0. In cell I, enter the per share price.

Now press F9. The worksheet will compute the remaining values
in the form.

Repeat steps above for the remaining funds. The initialization
is now complete.

ADDING ENTRIES:

To add entries to any fund:

1. Put cursor on the blank line following the last entry
for this fund.

2. Press E (for Enter) (Hold down the "ALT" key
while typing E).

3. WAIT while the worksheet inserts a new line, with the
proper formulae in the appropriate cells. Entries for
cell A and cell B will be made automatically.

4. Enter data in the remaining unprotected cells. If
zero, type 0.

5. press F9 to calculate.

NOTE: Do not try to insert a row by using /WIR. It will not
work! Instead, use E. It invokes a MACRO which
copies a pre-programed row (with proper formulae).
(FYI, This line has a range name of "Insert")

VIEWING DATA:

Be sure that F9 (Calc) has been pressed and that protection is
enabled. (/WS will view system settings).

As submitted, the top 5 lines are a window which provides
permanent viewing of the transaction codes and Column titles.
To disable this feature, type /WWC Worksheet Window Clear). To
reestablish the window, press , move the cursor down 5
rows, and type /WWH (Worksheet Window Horizontal).

moves the data one screen left.
moves the data one screen right.
The leftmost screen displays the data as input.
The next two screens display calculated data as per the
titles.
The rightmost screen displays data calculated for use in
graphs. (Percentages multiplied by 100,000 to coordinate the
graph's display). Meaningless to the viewer.

GRAPHS:

After all data has been entered, press C (Compare). This
MACRO extracts the last entry from each fund into an area with
a RANGE name of "OUT1". It then presents a graph of this
comparison. Switch between graph and data by pressing F10.

Graphs of the performance of individual funds may be called up
by /GNU and pointing to the graph name.

NOTES AND CAUTIONS:

Graph names, titles etc. may need to be customized for your
use.

The "OUT1" extract file is exactly 11 rows high, followed by a
TOTALS row. a "CLEAR" range name covers the bottom 10 of the
"OUT1" rows. If either of these ranges is changed to a larger
number, they will wash out the TOTALS entries PERMANENTLY!
Therefore, if you need more than 10 funds, MOVE the TOTALS row
down an appropriate amount BEFORE running C!!!! Then
increase the size of "OUT!" and "CLEAR".

The ranges chosen for the graph function include all entries
for a given file PLUS the blank line below. When a new entry
is made (with E), the graph range automatically includes
this new entry. Also, the C MACRO needs this blank in
cell A to find the last entry for each fund. Therefore, be
sure to leave the line blank.!

Do not change the name of the graph "FNDCOMPR" unless you are
familiar with MACRO'S and modify "\C" accordingly.

If graphs are modified (eg. Titles etc.) be sure to reissue a
name before saving (/GNC_____ for Graph Name Create (name of
graph)) below each funds final entry.

LIST OF RANGE NAMES USED:

1 ...6 Points to the beginning of the first entry of fund #.

FUND1...FUND6 Points to the cell containing the title which
you chose for fund # 1...6. Used to automatically
create the main title to the graphs.

FUNDS Used with the F MACRO.

INSERT Range name for the blank line with formulae which is
located just below the solid dashed line at the end of
the last fund listed. It is used with I.

INPUT Range name for the entire data file starting with cell
A5 through AB__ (__ represents the blank row below the
last entry for the last fund. It is used in C
MACRO.

CRI1 A 2 row range from A to AB, where the first row is a
copy of the title headings from row 5. It is used
with C MACRO to define the Criterion.

OUT1 An 11 row range following the MACRO section assigned
to accept the output summary created by C.

CLEAR A 10 row range--overlaping "OUT1" but not including
the title Row. Used to erase old data from "OUT1".

TITLE The row following the last line of OUT1 used to total
the summarized OUT1.

MACRO'S

C Used to extract the last entry from each fund and copy
each to the output area "OUT1". Also creates the
graph FNDCOMPR. The MACRO uses the blank cell below
the last entry of each fund to find the last entry.
Therefore, be certain that the blank in this cell is
preserved.!

E (Enter) Used to insert a blank row with appropriate
formulae below the last entry of a selected fund.
Needed to make new entries to a fund.

F (Find) Can be used to find the beginning of a fund.