The top of the Cisco worksheet
is shown below. We have entered information for 125 trading
days ranging in dates from March 20, 2003 to September 15, 2003,
an interval of about 6 months. For the illustration the fast
moving average has only 2 periods. The first 20 days are used
for a history.
Cells L2 and L3 hold the initial wealth and the number of shares
per transaction. No transaction costs are charged and there
is no return for funds not invested.
The analysis computes the ratios of the two moving averages
in column J. Whenever this ratio passes from a number less than
or equal to 1, this means that the mean price for the two day
period is greater than the long run average. This indicates
that the market may be going up and we choose to buy. The initial
entry in J29 is set to 1, and since the ratio is more than 1
in J30, the stock is purchased on the first day.
Columns J through Q hold formulas that compute various quantities.
The formulas return *** when the results are not numeric. Column
K is 1 when stock is owned and 0 of not. Column L holds the
buy/sell action. Column M holds the number of shares owned.
Column N shows the change in capital caused by a buy or sell.
Column O holds the amount of the capital that is not invested,
and column P shows the value of the stocks owned. Column Q is
the sum of O and P and is the investor's wealth. The goal is
to maximize the ending wealth.