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

[@geoffdunstan8567]

Just found your early excel tutorial thanks Mark. I'm mucking around with using MACD variations and back testing manually in excel is a bit of a chore! Will continue through your tute's, enjoying the learning process and hopefully will save me a lot of time! Thanks again, regards Geoff D from Merimbula Australia

[@MarkUrsell]

Great to hear, good luck Geoff!

[@phoebe8821]

This is really interesting. I tested this out on XOM weekly closing prices, and you basically ended up with the same amount as you had started with, vs the holding strategy which made one rich! Wonder what the underlying differences are in the nature of the price movements between XOM and S&P500 that lead to these different results. Lots to explore here.

[@MarkUrsell]

The latest versions of my 21 Technical Indicators & Candlesticks Spreadsheets (new formulas & descriptions) are available for instant download in my Spring Store: teespring.com/stores/tradinformed

[@WTA122]

Hi Mark appreciate your contribution can we backtest Wyckoff Strategies in Excel, if yes kindly giude

[@hamidpourmohammadnobari1821]

Thanks Bro Good Luck

[@ALEXSHUNCAI]

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. Thanks Alex

[@geoQuant]

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?

[@billmietelski]

Bill was here. :)

[@billmietelski]

Yeah, I thought I'd go back to the beginning, both to pick up any nuggets I've missed, and to enjoyy the evolution of the channel.

[@MarkUrsell]

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.

[@kingich]

Mark Ursell I would like to buy your advanced backtesting model from the website, just was wandering, does it have the functionality of trailing stop loss? Thanks

[@JoseAntonio-gu2fx]

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

[@heyyou5170]

can someone tell me please Why for example u should period of 20 ? what if my data are monthly (from 2007 till 2015) ?

[@Richbeatz70]

Great video to learn how to backtest cheaply, but the strategy doesn't beat a buy and hold index . Before or after (transaction cost and taxes).

[@Matt-Olive-Digital]

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.

[@pauliusgedrimas978]

One question Mark, shouldn't this EMA crossover strategy have more than one if statement? Because the only thing your If statement did is buy the security but never sell it.

[@darthforexvader7201]

hi do you have any recommendations to where I can get accurate historical price data from please?

[@MarkUrsell]

Hi Darth, this is a question that is asked a lot. I think the best source of data is from the broker that you trade with. Many brokers and platforms do offer this. Otherwise, there are lots of online sources. I have written an article about sources of free data. You can read this here: wp.me/p32qmZ-1By

[@darthforexvader7201]

thanks very much for your response Mark, may the force be with you

[@ulrikebielecke4075]

when the price break through signal level, we consider the formation as confirmed and we open the position accordingly. then we follow the market until we reach a target equal to the size of the formation. if you want to learn more tips how these works, i recommend you follow the steps of Dmitry Vladislav using his Blended model strategy that cover one of the best trading system in history

[@jiangziyang2129]

Hi, So I have one question. Why did you use the first 19 numbers to create the base for EMA instead of using 20 numbers ?

[@MarkUrsell]

+jiang ziyang Agreed. It would have been more accurate to use 20 numbers.

[@nitya7760]

Hi Mark, Can we backtest a strategy using Force Index ( by Dr. Alexander ) signals in Excel? Please advise.

[@MarkUrsell]

+suresh kumar Hi Suresh. I have recorded a new video about using the Force Index on the S&P 500.

[@nitya7760]

+Mark Ursell , Thanks a lot Mark, you have always been of help.

[@_danahmed]

When you round up the EMA from 0.095238 to 0.10, You're adding close to 5% error into the calculation. Wouldn't you be better off rounding to 0.095?

[@MarkUrsell]

Thank you for commenting. The spreadsheet is not actually rounding the EMA factor. It is only changing the display so that it fits better and is more readable. The full value is still used in the calculation.

[@ViolaAndJan]

Good that Speed 1.5 exists :)

[@samueltsoi630]

Still slow on 2.0 speed.....

[@cabbarto]

Does this means that when not over the EMA all stocks should be sold?

[@MarkUrsell]

cabbarto It means that the portfolio should be neutral. Either by selling the stocks or hedging.

[@kevinkavi222]

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.

[@biswajitkumarchaturv]

This is a backtest, meaning you have already decided when to buy. Now, it is just to check if your decision to buy when a certain criteria meets is fetching you expected positive returns.

[@biswajitkumarchaturv]

@mateo qirjaqi I suppose you take your money out and hold no position when the criteria is not met and then reenter when the conditions are as per your strategy.

[@ggp1065]

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$ ?

[@morattolucas]

i have the same doubt about the first question. Im trying to figure it out how to solve this. but untill now i cant.

[@biswajitkumarchaturv]

I suppose you take your money out and hold no position when the criteria is not met and then reenter when the conditions are as per your strategy.

[@biswajitkumarchaturv]

@@morattolucas I suppose you take your money out and hold no position when the criteria is not met and then reenter when the conditions are as per your strategy.

[@Thomas.D.B]

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 ;)

[@biswajitkumarchaturv]

I suppose you take your money out and hold no position when the criteria is not met and then reenter when the conditions are as per your strategy.

[@AncalagonGames]

i don't work the prices and all the date goes in one colum