An Easy Way to Use Excel to Backtest a Trading Strategy – Part 1

This video provides an easy way for anyone to backtest a trading strategy. ▷If you are interested in testing your own trading strategies, there are a range of …


  1. Two very important problems.
    1. Look ahead bias. You can't use the current week's closing price if it hasn't occured yet. If you bought on the next weekend's close or open it would solve this.
    2. Your equity curve assumes a full rebalancing of every position in the portfolio, but that's not how people actually trade. People will hold the stock for multiple weeks. An equity curve that maps how people actually trade is a little more advanced than that.

  2. Hi Mark, very good presentation, I wanted to know, why you buy the stock when the closing price is higher than the EMA price, I assume your intention is when the stock is on the upper trend? right?, Please explain.


  3. Hi mark
    This makes no sense to me. You invest the same amount in both scenarios indicating in the first “buy index” scenario that you keep the initial $1000 in the market and it accumulates over time. In the “buy above ema” scenario you just seem to ignore any week where previous weeks close is below ema indicating that you just skip that week loss and reinvest the same amount the next time the”if” requirement is meet. I am probably missing something here but shouldn’t this back test take into consideration when to exit the position (ie when the “if” criteria is not meet) ? I would be super thankful for a reply on this one as I can’t get this to fit in my head 😉

  4. Hello Mark.
    I think its a great idea to work with excel. This make one undestand what is really doing.
    I am verey very new in this matter. What are you doing in the Buy Index colum? Buy in a week and selling the next week? Its the same that buy in the first period and sell in the last ? Thanks in advance

  5. hello i cannot understand one thing how is it possible since you start with 100$ and then the index is going downwards your value to be the same? (although you are not buying, shouldnt your account value getting less?) and each time you buy do you add another 100$ ?

  6. hi Mark
    i have watch this video many times and i still dont understand what information to get from it to help me know either to buy/short a trade. i am new to trading and my biggest problem is to extract the information i really need. please advise me.

  7. Thanks for the comment. There is a factor in the model for adjusting the percentage of our capital invested per trade. I think what you are looking for is for an optimisation function to compare these different percentages. If so we could optimise manually by changing the factor. This could be automated and I would be able to do another video showing how this could be added to the spreadsheet.

  8. this is great. Mark, could you tell me how to measure the performance of allocation effects? for example, an indicator that increase and decreases the percentage of investment made?

Leave a Reply

Your email address will not be published.


66 + = 74