Last Updated on January 26, 2023 by Mark Ursell
In this article, I show how you can use Excel to test your own stock market trading strategies. The strategy in this article uses the concept of Relative Strength and tests the Nasdaq 100 and S&P 500 indices.
I also share my thoughts about why I think every trader should backtest their strategies.
Table of Contents
Why Should I Backtest my Trading Strategy?
“Those who cannot learn from history are doomed to repeat it”George Santayana
When we backtest a trading strategy we look at what has happened in the past to guide our future trading decisions.
Backtesting is difficult and time-consuming. It is easy to make mistakes and hard to avoid curve-fitting and over-optimization. To backtest correctly you need to be rigorous, disciplined and be prepared to spend a lot of time developing the necessary skills and experience.
It is easy to get curve-fitted results, confirmation bias, and make simple and complex errors. You can have problems with not enough historical data and sometimes too much historical data. It is difficult to be certain that your sample of trades significant.
Because of these difficulties, many people warn against backtesting. However, I have a different opinion. I think that despite all the difficulties, you must backtest your strategies. Simply because there is no other alternative to developing new trading strategies.
What Happens When you Lose?
“Everyone has a Plan Until they get Punched in the Mouth”Mike Tyson
Possibly the strongest predictor of long-term trading success is how you recover and learn from losses. It is easy to be disciplined when you are sitting on a string of big wins. After a few wins, it is easy to forget what a losing streak is like. Big losing trades are horrible. They scramble your brain, make you nervous and angry. In this condition, it is easy to make mistakes that lead to a bigger loss. Or even worse: not take the trades that would have made you a big profit.
When a boxer loses a fight he goes back into the gym and starts training again. He talks to his coach, works on his defense and speeds up his jab. When I take a big loss, I go back to my backtest. Every strategy that I trade has been tested many different times. But after a big loss, I want to know how this fits into the historical record. I want to check that I have not missed some crucial information. Most of all, I want to have the confidence to take the next trade.
If you do not have a backtest you have nothing to come back to. You do not know how significant this loss is. If you are relying on someone else’s trading strategy, you are in a vulnerable position. You can either push forward and hope that things will turn around or take your losses and walk away.
Trade Using Relative Strength
Relative Strength is a trading style that is intuitive and easy to understand. One way to use Relative Strength is to buy when a stock market is strong and the sell when it is weak.
I use relative strength in a number of my trading strategies and think it is a good way to identify both entries and exits.
The Trading Strategy
The strategy that I will demonstrate, looks at the ratio between the Nasdaq 100 and the S&P 500. These stock market indices are very popular and widely traded.
The Nasdaq 100 is an index of mostly technology companies. This index should perform better than the S&P when investors are feeling confident.
The S&P 500 is an index of large-cap stocks. This index should outperform the Nasdaq when investors are fearful.
- Calculate an exponential moving average of both indices.
- Divide the Nasdaq average by the S&P 500 average to get the ratio.
- Enter Long position when the ratio has turned upwards.
- Close Long position when the ratio has turned downwards.
Check out the video below to see me describing the strategy. You can also see me demonstrate how the backtest model works by testing different scenarios.
The backtest was carried out in Excel using the Tradinformed Advanced Backtest Model spreadsheet. This backtest model allows you to test different timeframes as well as entries and exits, including stop-losses, profit targets and trailing stops. The results shown below are based on an EMA of 100 and a profit target of 10 * ATR.
Results[table caption=”” width=”400″ colwidth=”150|150″ colalign=”left|center|left|right”] Metric, Result
Gross Winning Trades,” $173,364 “
Gross Losing Trades,” $-77,344 “
Net Profit,” $96,020 “
Net % Gain,109.4%
Largest Winning Trade,” $18,103 “
Largest Losing Trade,” $-12,311 “
Get more information by watching the video.