- Buying at tomorrow’s opening price when today’s closing price is above the highest high price that occurred during the last 20 days
- Selling at tomorrow’s opening price when today’s closing price is below the lowest low that occurred over the last 20 days
A spreadsheet may be helpful for evaluating this system. You can, in fact, configure this system into a spreadsheet, include buy and sell signals, and perform analyses to determine how well the system performs. You also can use the spreadsheet to dig into the system’s results to find out what works and what doesn’t.
Spreadsheet courtesy of Michael GriffisSpreadsheet analysis for Donchian channels.
Re‐creating this spreadsheet is straightforward. After downloading the historical price data into a spreadsheet format, all you have to do is encode the formulas into the correct columns.
If you’re like most traders, the first thing you’ll do is calculate some statistics about the system. For example, you can use spreadsheet functions to calculate the following:
- Total gain or loss for the system
- Average gain (the numerical average)
- Median gain (the middle result)
- Maximum gain for any single trade
- Maximum loss for any single trade
- Standard deviation
If you’re like most traders, your impulse is to discard the idea and move on to another. But with the Donchian channel breakout system, you need to dig a little further before you do. During the time frame of this test, the system triggered 30 trades, 18 of which were losing trades.
However, 13 of those losing trades were profitable at some point during the process, and all the winning trades gave back a large part of the profits before the sell signal was triggered. In fact, many of the profitable trades gave back significantly more than half of the profits before the sell signal.
The image above shows how a single position from the spreadsheet progressed. The entry trade was triggered when the price of QQQQ closed above the September 4 Donchian high. In this simulated trade, the stock was purchased at the September 5 opening price of $49.56 and then sold on November 9, the day after the price of QQQQ closed below the November 8 Donchian low.
If you look through the last three columns, you’ll notice that this simulated trade was profitable but closed well below its most profitable price. If that happens once during simulated trading, you may not need to worry much about it, but if it occurs frequently, you need to think of ways to remedy the problem.
In the Donchian channels system, buy signals apparently work better than sell signals. Therefore, you need to consider different types of stops and sell signals. One simple idea that’s worth testing is stopping out (selling at a predetermined price) of a position if the stock’s low (instead of its close) falls below the Donchian low. Another is to shorten the time frame for the exit signal by using a five‐ or ten‐day Donchian channel. Or you can, for example, use a trailing stop or some completely different criteria to exit these positions.
In any case, this example gives you ideas about how you can use a spreadsheet to test and analyze any trading system. It also provides you with some suggestions about how you can use this kind of analysis to improve your system’s results.Accounting for slippage
Slippage is the term traders use to describe the costs of trading, which is made up of two components. The first is the actual transaction or commission cost for executing your trade. The second is more difficult to measure because it’s the sum of the costs of unfavorable fills. If, for example, you’re planning to buy at tomorrow’s opening price based on today’s closing price, those two prices can be much different. An unfavorable fill is a cost of trading and accounted for as slippage.Most trading system–development packages have a provision for estimating slippage costs when testing your trading ideas using historical data. If you know your transaction costs, enter the exact amounts. Otherwise, estimate the transaction cost. You probably need to overestimate the cost of unfavorable fills because it always seems to end up being worse in actual trading than most traders ever imagine. You may want to start with an estimate of 25 cents per share and adjust it as you gather data on your actual slippage costs.