Unbalanced Panel Data: Rolling Window

[Liam Jansen]

Dear All,

I want to calculate monthly alphas by running the Carhart 4-factor model against monthly excess returns of funds. I am working with an unbalanced panel as not every fund is observed monthly over the whole period (e.g. funds who don't exist anymore). I have used the following Mata code written by Abraham Wolde-Tsadick earlier in this forum however this provides me with an alpha calculated from the monthly observations of for instance 2008, 2007, 2006 for the year 2008. This alpha is included in a column for all the observations of that particular fund in 2008.
I want to calculate my alpha on a monthly basis for a rolling window of 36 months. So the alpha of December 2012 is calculated over 12/2009 - 11/2012 and alpha of January 2013 over 01/2010 - 12/2012. I have tried to change the Mata code but couldn't get what I want.

This is the Mata code written by Abraham:

gen alpha = .
gen b_mktrf = .
gen b_smb = .
gen b_hml = .
gen b_umd = .
mata
mata clear
st_view(crsp_fundno =.,.,"crsp_fundno")
st_view(r_year=.,.,"r_year")
st_view(VW_ExcRet_Gr=.,.,"VW_ExcRet_Gr")
st_view(mktrf=.,.,"mktrf")
st_view(smb=.,.,"smb")
st_view(hml=.,.,"hml")
st_view(umd=.,.,"umd")
st_view(alpha=.,.,"alpha")
st_view(b_mktrf=.,.,"b_mktrf")
st_view(b_smb=.,.,"b_smb")
st_view(b_hml=.,.,"b_hml")
st_view(b_umd=.,.,"b_umd")
p = panelsetup(crsp_fundno,1)
f
or (i=1; i<=rows(p); i++) { for (o=p[i,1]; o<=p[i,2]; o++) { y = VW_ExcRet_Gr[o,1] X = (mktrf[o,1], smb[o,1], hml[o,1], umd[o,1], 1) b = . for (t=p[i,1]; t<=p[i,2]; t++) {if (t != o & crsp_fundno[o,1] == crsp_fundno[t,1] & (r_year[o,1] - r_year[t,1] <= 2) & r_year[o,1] >= r_year[t,1] )

{ y = y \ VW_ExcRet_Gr[t,1] X = X \ (mktrf[t,1], smb[t,1], hml[t,1], umd[t,1], 1) } }

if (rows(y)>=6) { b = invsym(cross(X,X))*cross(X,y)
alpha[o,1] = b[5,1]
b_mktrf[o,1] = b[1,1]
b_smb[o,1] = b[2,1]
b_hml[o,1] = b[3,1]
b_umd[o,1] = b[4,1] } } }
end

Could someone please help me to change this Mata code? Or maybe can help me with a Stata code which will not take so much time (I have large data set).

Thank you so much in advance!

[Robert Picard]

Well you certainly don't make it easy to figure out what you are trying to do. I believe the code you refer to comes from this thread.

In full disclosure, I know nothing about finance or what a Carhart 4-factor model eats in the winter (a French Canadian expression).

I've adapted Abraham's suggested code (using a regress loop) to use an 8 month rolling window. I assume that you are trying to use the Mata version because your real data is big and the Stata regression loop would be too slow. If that's the case, here's how to do the same thing using rangestat, a new program recently made available on SSC (with Nick Cox and Roberto Ferrer). To install it, type in Stata's command window:

ssc install rangestat

You can then type help rangestat to understand what's happening in the example below:

clear
input float(crsp_fundno r_year month mktrf smb hml umd excess_r)
2 1997  3 -.0503 -.0032  .0386  .0094 -.0181431
2 1997  4  .0404 -.0519 -.0102  .0489  .0117428
2 1997  5  .0674  .0483 -.0438 -.0519  .0372053
2 1997  6   .041   .015  .0072  .0259  .0310222
2 1997  7  .0733 -.0252 -.0013  .0384  .0402394
2 1997  8 -.0415  .0734  .0137 -.0252 -.0292168
2 1997  9  .0535  .0268 -.0025  .0145  .0381404
2 1998  1  .0015 -.0094 -.0207   .001  .0056473
2 1998  2  .0703  .0032 -.0086  -.011  .0395531
2 1998  3  .0476 -.0099  .0123  .0214  .0277491
2 1998  4  .0073  .0048  .0027  .0078  .0005439
2 1998  5 -.0307 -.0354  .0412  .0189 -.0093562
2 1998  6  .0318 -.0315 -.0222  .0726   .002362
2 1998  7 -.0246 -.0492 -.0115  .0371 -.0232616
2 1998  8 -.1608 -.0575  .0524  .0187  -.091043
2 1998  9  .0615 -.0015 -.0388 -.0063  .0222817
2 1998 10  .0713  -.032 -.0277 -.0535  .0311223
2 1998 11   .061  .0114 -.0343  .0118  .0300834
2 1998 12  .0616  -.003  -.047  .0904  .0168859
7 1994  1  .0287  .0014   .021  .0001  .0183894
7 1994  2 -.0256  .0272 -.0141 -.0026 -.0170168
7 1994  3 -.0478 -.0096  .0134 -.0132 -.0656004
7 1994  4  .0068 -.0091  .0169  .0041 -.0032034
7 1994  5  .0058 -.0201  .0018 -.0216 -.0093189
7 1994  6 -.0303 -.0048  .0168 -.0083 -.0506594
7 1994  7  .0282 -.0178  .0098  .0019  .0199595
7 1994  8  .0401  .0145 -.0347  .0154  .0419298
7 1994  9 -.0231  .0268 -.0181  .0131 -.0135341
7 1994 10  .0134  -.022 -.0236  .0145  .0129598
7 1994 11 -.0404 -.0017 -.0005 -.0019 -.0433825
7 1994 12  .0086  .0005  .0026   .035  .0152948
end

* generate a Stata monthly date 
gen mdate = mofd(mdy(month, 1, r_year))
format %tm mdate

* regression over a window of 8 months, including the current obs
gen alpha = .
gen b_mktrf = .
gen b_smb = .
gen b_hml = .
gen b_umd = .
 qui forval p = 1/`=_N' {
    capture ereturn clear
    capture regress excess_r mktrf smb hml umd if crsp_fundno == crsp_fundno[`p'] & inrange(mdate, mdate[`p']-7, mdate[`p'])
    capture replace alpha = _b[_cons] in `p'
    capture replace b_mktrf = _b[mktrf] in `p'
    capture replace b_smb = _b[smb] in `p'
    capture replace b_hml = _b[hml] in `p'
    capture replace b_umd = _b[umd] in `p'
}


* define a linear regression using quadcross() - help mata cross(), example 2
mata:
mata set matastrict on

real rowvector myreg(real matrix Xall)
{
    real colvector y, b, Xy
    real matrix X, XX

    y    = Xall[.,1]
    X     = Xall[.,2::cols(Xall)]
    
    XX = quadcross(X, X)
    Xy = quadcross(X, y)
    b  = invsym(XX) * Xy

     return(rows(X), b')

}

end

* add a constant
gen double one = 1

rangestat (myreg) excess_r one mktrf smb hml umd, by(crsp_fundno) interval(mdate -7 0) casewise
gen diff = abs(alpha - myreg2)

[Liam Jansen]

Thank you so much. I'm gonna try it this afternoon.

[Liam Jansen]

Hi Robert,

It works great Thank you again.

[Mike Kraft]

Hi Robert
I link this post to mine here in case someone has a similar problem http://www.statalist.org/forums/foru...esidual-values

[maria nasir]

Hi ,

In my research, I have to generate time series for a variable (speed of adjustment) that I find it by user commands for dynamic panel data estimation which names are xtbcfe and xtlsdvc. My data is an unbalanced panel data with 3500 N and the T is from 2 to 29 years.
I am wondering if it is possible to generate the value for this variable by using rolling window.
Could you please advice me how to do that?

Thank you so much in advance.

[maria nasir]

I have received help from Ignace the developer of xtbcfe codes. I did it by xtbcfe.