I have 15 years of weekly data that I am backtesting for its ability to predict future stock market returns. The model works as follows: when a weekly observation is more than x standard deviations above its y-week moving average a sell signal is generated, when the observation is z standard deviations below its y-week moving average a buy signal is generated, and if an observation falls within the x and z standard deviation band the prior Buy or Sell signal is maintained. I have created a spreadsheet with dynamic moving averages and standard deviation formulas that allow me to change the cell values for x, y, and z and use IF statements to generate either a BUY or SELL signal for each week and then calculate the fifteen year return (Rt) of my model versus an index over this same period.
The problem is that I am trying to maximize Rt given three variables (x, y, and z) and my current approach is to manually plug random guesses in for the value of x, y and z. Using this approach I will never know what the optimal combination of x, y and z are to maximize Rt. Is there an excel formula that I could use to optimize Rt? I am familiar with goal seek which is theoretically what I am trying to accomplish but since I don't know the value for Rt and I have more than one input it is not usable. Any help is much appreciated. Thank you.
The problem is that I am trying to maximize Rt given three variables (x, y, and z) and my current approach is to manually plug random guesses in for the value of x, y and z. Using this approach I will never know what the optimal combination of x, y and z are to maximize Rt. Is there an excel formula that I could use to optimize Rt? I am familiar with goal seek which is theoretically what I am trying to accomplish but since I don't know the value for Rt and I have more than one input it is not usable. Any help is much appreciated. Thank you.
