# Rolling Window Multiple Regression (Constrained) using Solver (Solver-VBA)

1. ## Rolling Window Multiple Regression (Constrained) using Solver (Solver-VBA)

Hello Guys!

I am currently working on a paper and conduct an empirical analysis.
Therein I have already done a fixed-weight / non-rolling linear regression using the solver to minimize the RMSE by changing 16 variables standing for the weights invested in 16 financial indices over a window from 116 months. I have implemented 3 constraints: 1. The sum of all risk factor weights must be 1, 2. & 3. The individual optimized variable values RF1-16 have to be each smaller/equal to 1 and bigger/equal to -1.

This worked out quite well.

Now I have to decrease the Window Size to 24 months and create a rolling window, where the last t-1...24 expected returns (optimized beta weights multiplied with actual indices returns) are used to estimate the optimal factor weights in months t and the expected return.
THerefore, the regression equation keeps unchanged but now the Y values from t until t-23 are used as well as the last t-1...24 expected returns to minize the RMSE by changing the factor weights in months t.

However, after the first 24 months, when the rolling window actually starts, the factor weights keep unchanged until the end of the time period of 116 months.
I have no idea why this is the case?

I would be more than happy if one could help me!!!

I actually use a solver-macro to run the regression automatically but after obtaining the results from the macro I did it manually and obtained the same results. So, it is not a mistake in the makro.

Please find attached the excel with the described problem.

THank you very much!!!  Register To Reply

2. ## Re: Rolling Window Multiple Regression (Constrained) using Solver (Solver-VBA)

Two things tha puzzles me

The formula in the U column is ``Please Login or Register  to view this content.``
for row 3 and for all the other values U4 to U26 there the same B and S number as the row number.

This changes with U27, then the formula changes to ``Please Login or Register  to view this content.``
and this formula change is kept down to row 118.

If you drag the formula down from U26 to U118 and do a rerun of solver you will get different factor weights in range C27:R118.

I'm also a but unsure about you solver macro. I would think a macro like this should be sufficient. ``Please Login or Register  to view this content.``
Alf  Register To Reply