No registration required! (Why?)

Backtesting Mving Average Crossover of Two Symbols with Excel


I'm interested in backtesting several 10-30 day moving average crossover strategies involving the ratio of two ETFs (let us say ABC/DEF) over say, the past 5 years. Amongst other problems I'm attempting to resolve, I want to assume the purchase and sale of a third ETF or entire stock index (say NASDAQ, ^NDX) every time the ratio of the two ETFs meets crossover criteria.

Is it possible to create such a backtest using Excel?

If so, can someone walk me through the steps? I will pay to have someone help me if necessary.

The steps I seek to accomplish are as follows:

1) Using closing prices over past 5 years or more for two ETFs, create a ratio of ABC to DEF.
2) Create 10 day and 30 day moving averages of this ratio of ABC/DEF.
3) When the 10 day moving average of ABC/DEF crosses over the 30 day moving average, simulate a buy of ^NDX.
4) When the 10 day moving average of ABC/DEF crosses below the 30 day moving average, simulate a sell of ^NDX.
5) Calculate the compound annualized return of the strategy.

Thank you for any response/help.
I am not sure if you can do it on Excel, but I have done similar exercises with Wealth-Lab. It is fairly easy to do it in Wealth-Lab

Let me know if you need help