Tip #2002-11: 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.
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 changing the value in a
single cell.
| =(Today’s Last – Yesterday’s EMA) x (Smoothing constant) + Yesterday’s EMA |
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 download it to
better follow our discussion. It lets you retrieve nearly a year’s worth of 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
Willow Solutions Inc. is a Microsoft Certified Partner with proven expertise and knowledge in developing
proprietary applications used primarily in the financial services industry. The company specializes in solutions
that combine the power of Microsoft Excel and Visual Basic, with an in-depth knowledge of financial markets.
Contact us if you would like to employ our services to help you enhance this model or help you design financial
models or customized applications.
|
