Tip #2002-12: A Macro to Calculate Returns from a Trading Strategy
In Willow Tips 2002 – 11 we discussed back testing a trend following trading system using a dynamic moving average which allowed
you to change the time periods for the moving average by changing a single cell value. When the closing price of the instrument
crossed above the moving average, a buy signal was generated. When the closing price crossed below the moving average a sell signal
occurred. We used Excel’s IF and AND functions to create a formula which identified the dates on which the buy and sell signals
occurred. By changing the time period for the moving average you could visually adjust the moving average for each instrument you
were analyzing.
In this Willow Tip we will carry the process one step further and add a macro to calculate the returns from the buy and sell
signals. Rather than roughly guess which moving average, if any, provides the best results, we will now be able to sum our profits
and losses and know exactly what our gains or losses would be if we followed this trading system.
Formulate a Trading Discipline
Before we could write the macro to calculate the returns, we needed to formulate a trading discipline with a set of rules to follow
when making a buy or sell decision. In a trend following trading system in which signals are generated when a moving average is
crossed, we assume you will always be either long or short. If you are long, when a sell signal is generated it is assumed that you
will close your long position at the open price on the next trading session and then go short. If you are short and a buy signal
occurs it is assumed that you will cover your short and go long at the open price of the next trading session.
With these buy and sell procedures in place we could then write a macro which would move through a table of historical pricing
information and be able to buy and sell at the opening price for the session after the signal occurred. Once we had prices at which
we bought, sold and sold short we could figure the profit or loss from the transactions and summarize the returns. Each time we change
the length of the moving average, we can run the macro to calculate the returns and see which moving average created the largest
trading profit.
This Willow Tip builds on information contained in previous Willow Tips. How to retrieve historical data from the internet was
discussed in Willow Tip 2002-8. How to create a chart from the historical data is explained in Tip 2002 -6. The macro code to scale
the chart was discussed in Willow Tip Willow 2002-7. Back testing a trading strategy with a dynamic moving average is discussed in
Willow Tip 2002-11. To create the mini app for this Willow Tip, we added an additional worksheet (Returns) to the 2002-11 mini app to
list the buy, sell and sell short transactions which were generated by the signals when the moving average was crossed. In the
adjoining columns, we added a formula to calculate the profit or loss and then summed the results. We suggest you download it to
better follow our discussion.
How the Macro Works
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.
|
