+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    04-07-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    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!!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,492

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Constrained regression using Excel solver
    By samquerty in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-30-2017, 11:05 AM
  2. how to constrain coefficients in a multiple regression using solver
    By psheeha1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2012, 11:02 PM
  3. Replies: 5
    Last Post: 03-18-2012, 04:20 PM
  4. multiple regression solver
    By koylita in forum Excel General
    Replies: 5
    Last Post: 10-31-2011, 01:03 PM
  5. Using solver for regression
    By abhirules in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-27-2011, 06:36 PM
  6. problem with rolling solver
    By keis386 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-17-2011, 11:17 AM
  7. Use Solver for Constrained Regression
    By abhirules in forum Excel General
    Replies: 1
    Last Post: 04-22-2010, 12:19 PM
  8. [SOLVED] Solver returns non binary answer in binary constrained cells
    By Navy Student in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1