Optimise Trading Strategies using Excel and Tips for Good Practice

Last Updated on October 6, 2013 by Mark Ursell

Ebook Course – How to Backtest a Trading Strategy Using Excel

Do you want to improve your trading skills and profitability?  I have a new course available via the Amazon Kindle Store.  The course will show you how to program your own Excel Backtest Models.

Learn more about the course

Previous Videos in this Series

In this series of videos I have shown how to build a basic backtesting model which we can use on any historic price data.  Then I have shown how we can add extra functions to the model like an EMA filter and then an ATR stop-loss.  You can see the first of these series of videos on the post An Easy Way to use Excel to Backtest a Trading Strategy.

The next videos constructed a slightly more complicated model to test a trading strategy based on the MACD indicator.  In this model we have much more control over the backtest and are able to alter the size of our trading position as a percentage of our capital. The first of these videos can be found at How to backtest a MACD Trading Strategy.

In this current video, shown below, I make use of one of the built-in functions of excel called Scenario Manager.  We can use this function to quickly test a number of different inputs into our trading strategy.

Tips on Best Practice to Optimise Trading Strategies

Optimisation is a powerful tool that can even make bad trading strategies look good.  Systems that give amazing results on historic data may be unprofitable when traded in real time.  The following tips provide a guide

1. Avoid the temptation to over-optimise or curve-fit your results.  If a trading strategy is profitable with a broad range of inputs this is usually good enough.  Historic data will never correspond exactly with real time data.
2. Don’t set your optimisation increments too low.  A trading strategy that is unprofitable using a 27 period ema will not be profitable using a 28 period ema.  Choose a strategy that is profitable with an ema of both 20 and 30.
3. Test one variable at a time.  Understand how each variable is affecting the results before combining them.
4. Use common sense.  If the optimisation process is giving illogical results assume that there is a problem with the process.  Open a chart of the data and go through trade by trade if necessary. Usually it will be a problem with the inputs, occasionally you will develop a useful new strategy.
5. Be careful to check that your inputs are not gaming the system to give unreliable results. For example, if you are testing on the daily timeframe and using a stop-loss and take profit of 1 x ATR for each, there will be certain days when both will be hit.  However the historic data will not be able to tell which was hit first and your model will give the results based on whichever you told it to check first.  If you want to test tighter stop-losses then you need to use historic data on a lower timeframe.

If you have any comments about the video or backtesting tips please don’t hesitate to comment below to get the discussion started.