# tip 2002_11

Document Sample

```					                                                                                  Willow Tips November 2002

Back Testing a Trading Strategy with a Dynamic Moving
Average
One of the most attractive features of high-end charting software is the ability to allow
you to back test a trading or investment strategy. For example you can create a simple
trend following trading system with a moving average. You can buy when your
instrument crosses above the moving average or you can sell and go short when the
price of the instrument crosses below the moving average.

117.0                                                                                                   117.0

112.0                                                                                                   112.0

107.0                                                                                                   107.0

102.0                                                                                                   102.0

97.0                                                                                                   97.0

92.0                                                                                                   92.0

87.0                                                                                                   87.0

82.0                                                                                                   82.0

77.0                                                                                                   77.0
19-Feb   19-Mar   16-Apr   14-May   11-Jun   9-Jul   6-Aug   3-Sep   1-Oct       29-Oct    26-Nov

If you are a seasoned trader, you know that one moving average of a certain time length
can’t be applied to every instrument. For example, a 20 day moving average may work
well for a stock, such as IBM, but it does not work for a more volatile stock or perhaps a
futures contract such as wheat or crude oil. For these instruments, a 20 day moving
average may result in numerous unprofitable whipsaws. Perhaps a 30 day or longer
moving average may work better. Being able to easily change the time period for a
moving average is very important if you are going test a trend following strategy. You
can easily create a back testing model in Excel. Once you have retrieved historical data
into an Excel worksheet, you can create an exponential or simple moving average for
which you can easily adjust the time period by cha nging the value in a single cell.

1
Willow Tips November 2002

In this Willow Tip we are going to discuss how to add a dynamic moving average to a
stock price chart which displays the high, low and close. You create a dynamic moving
average by creating within the formula a reference to a single cell. This single cell holds
the number of days for the moving average. Once this is done the moving average will
recalculate when you change the value in a single cell e.g. from a 30 day moving
average to a 20 day moving. The moving average on the chart will adjust accordingly.
This lets you visually see how often the moving average is crossed and when a buy or
sell signal is generated.

We have created a mini app which uses a dynamic moving average. We suggest you
historical data for an equity instrument using a web query. The high, low and close are
displayed on a chart along with a dynamic exponential moving average. This Willow Tip
builds on information contained in previous Willow Tips. How to retrieve historical data
from the internet was discussed in Willow Tips 2002-8. The macro code to scale the
chart was discussed in Willow Tips 2002-7.

Calculating the Exponential Moving Average
A simple moving average calculates the average price for a set number of time periods.
To calculate a 10 day moving average you would calculate the sum of the closing price
for a period of 10 days and divide by 10. On the eleventh day you would add the price
for the eleventh day to the average and subtract the price for the first day.
Excel has a built in trend line feature. If you click on the chart you have created, and
along with other types of trend lines. The problem with this feature is that if you want to
change the length of the time periods for your trend line you need to go through several
steps to complete the process. Using a dynamic moving average the trend line is
adjusted with a single cell entry.
In order to reduce the lag in a moving average many technical market analysts use
exponentially weighted moving averages (EMA). Exponential moving averages reduce
the lag by applying more weight to recent prices relative to older prices. This type of
calculation makes the moving average more sensitive to a change in trend.

2
Willow Tips November 2002

The formula used to calculate an EMA uses a smoothing constant. The smoothing
constant applies the appropriate weighting for the most recent price relative to the
previous day’s EMA calculation. The formula for the smoothing constant is:

2 / (1 + N)

N is equal to the number of time periods for the EMA. If you are calculating a 10 day
EMA, N would be 10.

The complete formula for the calculation of the EMA is:

=(Today’s Last – Yesterday’s EMA) x (Smoothing constant) + Yesterday’s EMA

In our mini app we replaced the variables in the formulas with references to the cells
containing the values we want to calculate. We named the cell which contains the
number of periods in the moving average MAV, and reference it in the formulas which
calculate the moving average. In our mini app the cells containing the formula for
calculating the EMA are in column I. Since the number of periods in the smoothing
constant refers to a single cell (MAV) when the value in the cell changes so do values
displayed by the formulas which reference that cell. Setting up the formula in this way
allows you to recalculate a new moving average quickly.

We used Excel’s IF and AND functions to calculate Buy and Sell signals based on
whether the instrument’s closing price crossed above or below its exponential moving
average. Although it may appear complicated, the formula to calculate a buy signal is
pretty straightforward. If yesterday’s closing price is less than its EMA and the current
closing price is greater than its EMA then it would indicate the EMA crossed to the
upside and the price action would generate a buy signal. If these conditions were met,
then the word Buy appeared in the cell. If they were not met, then the cell remained
blank. The buy signal formula is:

3
Willow Tips November 2002

The closing prices are in column F and the EMA is in column I. We entered a similar
formula in the adjoining column to calculate a sell signal.

Some Design Tips
A chart is most visually appealing and easy to analyze when it is large and the data it
displays can easily be viewed. If it is sufficiently large it also hides the numerical data
displayed in the table. To overcome this problem we added Hide Chart / Show Chart
button which runs a macro which allows you to hide or display the stock chart. We
added this feature for the sake of convenience and to enhance the appearance our
mini-app. Along with being able to visually see when the last price crossed above or
below the moving average, with a single mouse click you can view the actual buy and
sell signals displayed in the cells on the analysis worksheet.
We created the button using Excel’s Control Toolbox toolbar. To view the code,
simultaneously press Alt F11 to open the Visual Basic Editor and double click on the
Analysis worksheet. The macro code is attached to the button click event. The code
does nothing more than change the chart’s visible property to true or false and also
changes the text property from hide chart to show chart.
Another example of dynamic updating is the column heading for the moving average
column. Each time you change the period for the moving average, the heading on the
column changes to display the changed time period for the moving average. This is
dynamic feature is created by using Excel’s concatenate operator (&) in a formula to
create the label for the moving average rather than entering text. It references the cell
containing the time period for the moving average and adds the text label.

=MAV&" D Mvg Avg"

4

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 11 posted: 12/24/2010 language: English pages: 4