MUTUAL FUND MONITORING SYSTEM FOR LOTUS 123

MU_FUND Version 1.0

by Doug Harris


This system consists of a template MU_FUND.WKS, which is the monitoring
worksheet, and this document MU_FUND.DOC. The system does not give "buy" and
"sell" signals, it just displays important past performance data and generates
line graphs. The beauty of this system is that it takes about a fifteen
minutes a week to run.
The first step in investing is deciding what funds to track. This can be
accomplished by your own research or by a subscription to a newsletter. There
are any number of good newsletters which advertize in Barrons or the Wall
Street Journal. Here are two that I've used.

NoLOAD FUND X
235 Montgomery St.
San Francisco, CA 94104 phone (415) 986-7979

Growth Fund Guide
Growth Fund Research Building
Box 6600
Rapid City, SD 57709

The first newsletter is nothing more than a data sheet that ranks a large
number of no-load and low-load mutual funds, based upon market performance.
The second newsletter provides in depth analysis of a selection of about 30
funds. After deciding which funds look promising, you are then ready to begin
using the worksheet.
Load MU_FUND into Lotus using File Retrieve. As you can see I've already
loaded the worksheet with a number of funds for illustrative purposes. All
these funds are from the Fidelity Fund Group. You can change these later.
The top row, starting with column C contains the Fund names. By plugging a
name into these cells you automatically propagate the name into the graphs (or
any other place you might want). So, to enter a new fund name, row 1 is all
that need be changed. The next four rows 2-5 are display only. They show
short term performance information, how much the fund has appreciated or
depreciated in the last 4, 8, 12, and 16 week periods.
The next data entry row is 7, shares purchased. This is optional, but if
entered this data will be used to calculate the next two rows. Row 8 displays
current value based on closing weekly price and row 9 displays what percent of
your total portfolio is invested in that column's fund. Column B is the
"cash" column. The value of all non-fund assets should be placed in B7 if you
want row 9 to reflect the relative percentages of your total portfolio. If
you want the relative percentages of just your mutual fund holdings, let B7
contain .01, one cent. If you don't enter any data in row 7, B7 must contain
one cent.

Rows 11 and 12 are used during graphing. Since it's necessary to invert
the price data before graphing, these rows contain formulas that are valid
only when the data is inverted. The formulas are the 16 and 40 week
percentages that are displayed under the X axis of the graphs.
Rows 14 through 53 contain the closing weekly price (usually friday) of
the fund. Column A contains the relative week number. Column B contains the
date, I use sunday since I began getting data from the sunday NY Times. When
a fund is initially entered into the system, the last 16 weeks closing week-
ending prices should be entered into the appropriate rows. This data can be
gotten from back issues of Barrons, sunday NY Times, or the Wall Street
Journal. It would be much more convenient to download this data from some
service. The Dow Jones News Service contains daily prices from which the
week-ending data can be extracted manually or by software. If someone knows
of a BBS that contains just week-ending data, please let me know. If you
don't want to bother with 16 weeks history, you can enter as many weeks as you
want, just make sure that week numbers 1 through 16 are filled with data even
if its a copy of your earliest real price.
Example WEEK # PRICE
1 15.21
2 15.10
3 14.98
Earliest real price===> 4 14.80
data fill====> 5 14.80
data fill====> 6 14.80
to
data fill====> 16 14.80

Just remember the better the data, the better the evaluation.


And now a word about mutual fund dividends. If a fund experiences long
term growth, the fund will declare a dividend. This represents a benefit for
your portfolio, but an annoyance for your graphing data. The problem is, that
the fund price will decrease by the amount of the dividend, and a graph using
that price will appear to show a loss. One way around this is to add the
amount of the dividend to the actual price, this is called "dividend
adjusting" the price. The price and dividend should be entered as a formula
i.e. (price+dividend) so each value is available separately. The rule in
dividend adjusting this way is: enter the dividend to the right of the price
starting the week of the dividend declaration. Read this example from bottom
to top.

Example WEEK # CELL ENTRY RESULT
price up another .25 1 (8.65+1.00+1.10) 10.75
same price, new dividend 1.10 2 (8.40+1.00+1.10) 10.50
no change 3 (9.50+1.00) 10.50
price up another .25 4 (9.50+1.00) 10.50
price up .25 plus dividend 1.00 5 (9.25+1.00) 10.25
price up .25 6 (10.00) 10.00
starting price 7 (9.75) 9.75

After a while it's possible to have a string of dividend values to the
right of the price. At this point you should remove all dividend adjustment
values that appear in all 40 cells of a fund's prices. In other words, at the
point that weeks 6 & 7 drop off the worksheet, all 1.00 dividends could be
removed. An alternative way of "trimming" dividend adjustment values is:
remove all values that appear in multiple cells and subtract the value from
those cells where it doesn't appear. If you did this, 1.00 would disappear
from weeks 1 through 5 and week's 6 & 7's price would have -1.00 to it's
right. You could even carry this a step further by using just negative
dividend adjustment values, which would make the cells look like this.

Example WEEK # CELL ENTRY RESULT
price up another .25 1 (8.65) 8.65
same price, new dividend 1.10 2 (8.40) 8.40
no change 3 (9.50-1.10) 8.40
price up another .25 4 (9.50-1.10) 8.40
price up .25 plus dividend 1.00 5 (9.25-1.10) 8.15
price up .25 6 (10.00-1.00-1.10) 7.90
starting price 7 (9.75-1.00-1.10) 7.65

In this method of adjusting, you subtract the value of the dividend from
all prices earlier than (below) the dividend date. Even though the RESULTs in
the two examples are different, the data yields approximately the same graph.
The advantage of this method is that week 1 is always the real price, which is
desireable since week 1's price is used to calculate the dollar value of your
shares. Another advantage is this is a one step process, once you subtract
the values, you're done; whereas with the addition method you eventually have
to remove adjustment values. I strongly recommend this method and even though
changing columns of cells for dividend adjusting seems like a major bother, it
can be accomplished by writing a small Macro loop that does all your
keystrokes for you. I have included a small Editing Macro Range Named \L that
is invoked by Alt-L. This Macro is an infinite loop that you exit by Ctrl-
Break.
Both these methods of dividend adjusting yield slightly different graphs
and percentages but they are good enough for an overall picture of fund
performance.

Enter Alt-H. This takes you to the command menu. To generate graphs
enter Alt-G. The Graph command first waits for the entry of a "1" or a "0".
If you key 1 + enter, all graphs you display will also be saved to a graph
file that can then be used later to print out the graph. If you key 0 +
enter, the graph files won't be saved. After entering the number, the Graph
Menu will be displayed at the top of your screen. Move the cursor to E SETUP
and hit enter. This prepares the price data for line graph display by
inverting it. Now you can display sets of graphs just by moving the cursor
and hitting enter. Graph selections are indicated by columns and range (16 or
40 week). When you've finished viewing you must enter F RESET which de-
inverts the data. Then enter G QUIT to exit the command.

Note: the first time you "save", the system will beep to indicate an
error. This is because the command is "replacing" a graph file that
doesn't exist. Subsequent "saves" eliminate this problem. Unfortunately
the error will cause you to exit the macro with messages "Enter X-axis
range: MI1015" and "Illegal cell or range address". When this occurs hit
enter and then re-invoke the ALT-G command. This will happen a total of
four times. Once on the first save of Graph Menu selections A, B, C, and
D.

To enter a new week's data enter ALT-N. This moves all the data down 1
week and creates a new week 1 from the new week 2. The command will leave you
in EDIT mode with the new week's date, which is of course a copy of week 2.
First change the date, hit enter, and then enter the new prices into Row 14's
proper columns. If you totally screw up a week you can back it out, resetting
the data to the way it looked before you hit ALT-N by entering ALT-B
(backout).
ALT-B is also handy if you want to enter some data temporarily, like in
the middle of the week, perhaps after some major market move. First enter
ALT-N to move the data down a week, enter your new prices, use the graph
commands, and then use ALT-B to backout the temporary prices.

This worksheet was built for expansion and modification by the end user,
you. Only some of the data columns are complete and the Graph macro works for
the first few columns. Once you are familiar with the sheet and macro
language, enhancement is pretty straight-forward. This information might be
useful:

The 1st row's cells, which contain the fund names, are all Range Named
cells, in the format FNc where F=fund, N=name, and c=column.

The Names of the Graphs are in the format Lwwc where L=line graph,
ww=number of weeks, c=column. This applies to both the Name for the graph
definition (Graph Name Use - command) and the graph save file (Graph Save
- command).

The Names of the inverted 16 and 40 week percentages are in the format
Pwwc where P=percent, ww=number of weeks, and c=column.



I hope this worksheet is as profitable for you as it's been for me. It
literally has paid for the cost of my complete PC system twice over since I
began using it in March 1986. If you have any questions or comments, if you
find any bugs, if you have any ideas for future enhancements or, if you have
developed any mutual fund software of your own - let me hear from you. I can
be reached on EntrePlex BBS (718) 743-2434; or New York Amateur Computer Club
BBS (212) 231-4616 E-MAIL, or (718) 539-3338 or 539-3560.
GOOD LUCK - DOUG HARRIS.







DISCLAIMER: THIS DOCUMENTATION AND WORKSHEET IS A MONITORING SYSTEM OF FUND
PERFORMANCE, AND NOTHING HEREIN SHOULD BE CONSTRUED AS ADVICE TO BUY OR SELL
SPECIFIC FUNDS. THE WRITER DISCLAIMS ANY AND ALL LIABILITY FOR LOSSES THAT
MAY BE SUSTAINED AS A CONSEQUENCE OF THE USE OF THIS SYSTEM.