+ Reply to Thread
Results 1 to 4 of 4

Excel Rolling Regression

  1. #1
    Registered User
    Join Date
    02-16-2010
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007
    Posts
    7

    Excel Rolling Regression

    Hello,

    i have a problem with a regression.

    I have a standard regression with about 10 years of data.

    ex:

    r = a + Beta*X + e

    and i wish to calculate the beta, this is pretty simple, but what i want to do is at any given periode, only calculate the Beta based on the previous 100 days. Is this possible to do in excel? And is it possible to get the results graphically?

    If not in excel, is it easily enough to do in another program?

    thanks for your help

  2. #2
    Forum Contributor
    Join Date
    02-25-2010
    Location
    Boston
    MS-Off Ver
    Excel 2007
    Posts
    174

    Re: Excel Rolling Regression

    how is your data set up? Do you have a date field within your data?

    If so you should be able to get most of the parts by using if/sumif etc formulas.

    Edit: Also is your date field continuous, ie have no gaps and possibly only 1 line per date.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel Rolling Regression

    Use dynamic named ranges for calculation and plotting.

    http://www.contextures.com/xlNames01.html
    Last edited by shg; 05-04-2010 at 05:46 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    02-16-2010
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Excel Rolling Regression

    Hello,


    my data is set up with aprox 2000 days, with certain variables each day:

    date --- variable1(v1) ---- variable2(v2) ---- variable3(v3)

    what i am looking for is to find the Beta in this regression:

    v1 = a + Beta1*v2 + Beta2*v3

    which is not to hard, but the problem is when i only want to find the beta based on the past say 100 days, not all of the 2000...
    and i would like to find out how these Betas have evolved over my time sample, preferably in a graph.
    Is this possible in Excel?

    thanks for your help guys

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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