Last Updated on April 14, 2021 by Mark Ursell
This post continues the series of video articles about how to use Microsoft Excel to backtest trading strategies. In this post I show how to calculate a stop-loss using the ATR and then how to backtest the trading strategy.
For the previous video articles see, An Easy Way to use Excel to Backtest a Trading Strategy – Part 2.
The Average True Range
Developed by J. Welles Wilder the ATR is very popular with traders. On its own, the ATR can be used to measure market volatility and market range. It is also frequently used in other technical indicators such as the SuperTrend indicator and the ADX.
One of the most popular uses for the ATR was developed by Chuck LeBeau and is referred to as the Chandelier Exit. The chandelier exit sets the stop-loss distance as a multiple of the ATR. The ATR reacts to market conditions so when things are calm, the stop-loss will be relatively close and when things are volatile, the stop-loss will be further away. https://www.youtube.com/embed/a7pNSJL2YEU
Formulas Used:
H-L = High-Low
H-PC = abs(High-Previous Close)
L-PC = abs(Low-Previous Close)
True Range =max(range)
ATR =average(range)
SL =ATRFactor Max Weekly Drawdown = Low-Previous Close Trading Strategy =IF(F34>G34,IF(N35>M34,((F34-M34)/F34)Q34,(F35/F34)*Q34),Q34)