Spreadsheet Backtesting

Discussion in 'Strategy Development' started by kut2k2, Jun 4, 2005.

  1. kut2k2


    Is anyone familiar with backtesting EOD systems on a spreadsheet, and would you be willing to answer some structure set-up questions?


    PS the spreadsheet isn't excel so I can't just buy some trading-system add-on. But the formula syntax should be the same as excel or nearly so.
  2. kut2k2


    Nobody? :(

    Well maybe I can get some answers to some general questions ...

    As indicated, I am using an EOD system. Assuming a next-day market entry after a buy signal is generated, should I assume the average fill will be, say, one-third of (O + H + L)? Something else?

    Assuming I can enter a trailing stop at the same time I enter my buy order, should I assume the stop gets hit on average at, say, half of (stop + L)? Something else?

    Is there an extra charge for updating the trailing stop on a daily basis, or is there a fixed roundtrip fee per trade?

    Please advise. Thanks.
  3. If your signal is generated today, then you would enter tomorrow either at the Open, or Close, those are the only two prices that you know you can get, using any other price will provide an inaccurate analysis.

    Good luck!

  4. kut2k2


    It's going to be inaccurate anyway. There's something called slippage. Nobody gets filled exactly at the open, unless they have some connections. I don't. And if a market order I put in early doesn't get filled until the close, I'll be mightily pissed, as anyone should be.

    Can someone else answer these questions? I'm just trying to do a reasonably realistic backtest, that's all. Thanks.
  5. Vince1


  6. AusTrader


    I hope I can help you out. I have been systems trading for 20 years - mainly TradeStation - but use spreadheets alot. What exactly are you after?
  7. kut2k2


  8. kut2k2


    Hi, AusTrader

    Thanks for your offer. I'm just trying to set up a realistic backtest on a spreadsheet. If I can enter at the open, that's great, but I didn't want to just make that assumption in my test. I have an indicator that can be used stop-and-reversal fashion but that strikes me as somewhat impractical, so I'm also trying to incorporate a trailing stop. Can you in particular answer the questions I asked about trailing stops? I may have more questions later but those are the main ones I'm concerned about now, especially the one about updating the trailing stop daily.
  9. mmillar


    The problem with using EOD data is that it has been adjusted, is different from intraday day, and hence is not tradeable.

    There all sorts of gap systems, yesterday's close > today's open etc that work wonderfully on daily EOD data, but fail miserably in the real world. In fact there are lots of systems that are marketed as profitable but have only been tested on EOD data. When you try them on intraday data they are terrible.

    You will only get a system to work well on daily data if it is long term and hence your average trade is large. And even then you have to be careful.
  10. If you are trading NYSE, there is no slippage, you get the open no problem, and if you are exiting the close, you get the close. If you are entering on the close, SS might be a problem. (and you don't need connections to get this!) I also believe that you can trade NASDAQ stocks and get the open and closing print. (through AMEX, but have never traded amex before) As for your model, factor in an amount that will include slippage and commissions and you will have an accurate idea of how it performed historically. (Adjust your slippage based upon the liquidity of a stock)

    Think for a few minutes about what you are trying to accomplish
    and you might be able to get the answers yourself. It is easy enough to set up three scenarios, best case, worst case, and most likely.
    #10     Jun 5, 2005