Although I have read a lot of general books on Excel and VBA I still do not really have a clue how to run complex backtests of trading strategies in Excel/VBA. Can anyone point me to a book or other resource that helps learning the functions that are important to do a backtest and how to organize data/set up the sheets/code? I heard lots of people prefer Matlab for doing backtests, but I only have very basic knowledge on Matlab and absolutely no clue how to use the system for backtest. Any tipps on a resource to learn doing backtests in Matlab? Thanks!
The concept is the same be it excel or matlab. You're simply applying your strategy to historical data. You're strategy is usually expressed using logic functions. Forget VBA or Matlab for a minute and just try to replicate in a basic spreadsheet what you'd do with a pen/paper if you were backtesting. Here's a simple example for you (see attached): The strategy here is buy the close on Wednesday and sell the close on Thursday (I realize this isn't great as you may/may not be able to actually buy at that price and this doesn't account for transaction costs, liquidity, etc.). P/L is shown in SPY price/points so you'd have to convert to whatever % change that represents or whatever $ amount based on your position size. Backtesting can be as simple, complicated, flawed, unflawed as you want it to be. It can be done in any language (Excel, Matlab, Ninjatrader, Tradestation, etc.) using any set of tools. Ninjatrader does seem to have a pretty good community and it's free - might be a good place to start if you have some comfort in programming. Otherwise just start playing with excel imo.
Nice advice and intro spreadsheet above. If you really want to learn VBA; I'd suggest to start working on examples related to finance. A good book to learn would be 'Financial Modeling,' by Simon Benninga.