Using Excel to backtest trading strategies

First off; thanks to the kind soul who wrote this article (in the Trading Articles section). I have learnt more about Excel in one morning since reading it and experimenting - even with my ancient 2003 version.
Suffice to say that there is all kind of potential here. Does anyone else have any interest in expanding this information and coming up with additional formulas for backtesting and optimization?

neath: If you haven't already done so, check out this site: http://exceltrader.blogspot.com. The author provides a great simulator and daily ES data updates so you can replay that day's action. If you wanted to get fancy and automatically backtest strategies, you'd have to write your own indicators and interface.

Thanx culso..I'm revisiting that site now. My backtesting strategies would be in KISS mode so I'm hoping someone that has a few more mega bytes of programming savvy upstairs might help me come up with some simple formulas. For example, Erik Hadik has a Two stop reversal (new high, close below both previous opens)which I'd like to tweak a bit.

One of the greatest tools I've found for this is NinjaTrader. Go to Mirus Futures and download NinjaTrader w/Zen-fire. You'll have to register to get the demo, but you'll get their data feed and no sales pressure. I don't think I've ever received a call or email. (Not that that would be bad - their commission rates are only $4.40 per round trip.) There are excellent tutorials and help files plus a very active development community on their forum. If you know any C#, you'll do just fine. If you know VB/VBA, you should be able to pick it up quickly.

You can design a strategy with the stragey wizard using common indicators or create your own indicators and use them in your strategies. You can then backtest them against historical data, forward test them against real-time data or run a "virtual" feed which provides random data.

Out of the box, you get a simple stochastics crossover strategy that enters a long or short trade whenever %k crosses %d. You establish the period you wish to backtest and press the button. You get your results in a nice table and a chart showing when and where trades were entered/exited. Very nice. I've been playing with several months worth of data, but you'll really want much more than that to do an accurate test. My best strategy with 6 months worth of data has been this: Go short and stay short. I've made a paper mint.

The downside is you will realize just how relevant everything is - e.g. a 144 tick chart yields much different results than a 233 tick chart which is completely different than a 5 minute chart.

Alas my programming skills are virtually zilch. I just opened a sim account w/ Tradestation to do some backtesting but, after a week or so of faffing around on their site, I've pretty much concluded that their Easy Language is not easy enough for me. Actually, their whole platform is rather daunting and overwhelming for me. Perhaps NT would be a better bet.

My best strategy with 6 months worth of data has been this: Go short and stay short. I've made a paper mint.



neath-

My personal experience is C# is several orders of magnitude harder to program than Easy Language.

It sounds like you need someone (with programming skills) to collaborate with in developing your strategy.

Is your goal to use these tools to find (discover / invent / create) a strategy that works, or to automate an existing strategy ?

pt_emini:
I have a number of very simple candle based PA strategies that I'd like to backtest in various time frames. For now, I've been manually backtesting them through Ensign, literally marking off each signal and tabulating the results with different exit strategies. The results thus far are promising so I'd like to put them to the test over a larger data sample to check how they'd stand up in different market phases.
I have a friend out of state, an excellent programmer but without any knowledge of trading, who agreed to help me on this project,however the phrase "time sensitive" means nada to him.
Therefore, I've decided to put it out to the Universe and see if anyone out there could code, what I consider, a simple strategy (as simple really as the two stop reversal) in Excel.

If you can keep the candle pattern to within 3 or 4 candles, then it should be easy to get the code written. Then you will need some historic data to run against. I think Tradestation has a pretty deep price history database. This should help you narrow down the best time frame and trade management stuff.

One thing you need to keep in mind. We are in a protracted phase of high volatility (ie. $VIX > 20). So any backtesting you do needs to take this into account. If your running against data prior to 2008 (with a low $VIX < 20) its not going to give the same results you will see going forward (assuming $VIX stays high). These changing phases of volatility can play havoc on backtesting results.