Panel data; moving/rolling average

[John Mess]

Dear all,
I am working with an unbalanced panel dataset where the panel var is the fund number and the time var is the month. Thus, I am working with monthly time series but with gaps. What I want is to calculate the 3-year Sharpe ratio and also the 3-year jensens alpha for each fund. So, if I am at year 1992 I would like to calculate the Sharpe ratio for that year using the monthy observations of the years 1992 1991 1990. To do so i need the mean and sd of the excess returns of each fund during that period. In addition, I would like to estimate the Jensens Alpha by running the CAPM model using again the monthly observations from years 1992 1991 1990. To do so I could use the statsby command and use the coefficients of a regression running during that period. I haved tried many commands like rollreg, movavg, ma etc and also some locals with foreach/forvalues but I cant employ them as I dont have a balanced panel and I dont want to eliminate funds because I might have one or two gaps.

this is an example of my dataset

crsp_f~o r_year month mktrf smb hml umd ExcessR~s |
|---------------------------------------------------------------------------|
2 1997 1 . . . . . |
2 1997 2 -.0049 -.0261 .0469 -.0204 . |
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 |


Thanks in advance!
Best,
J.M.

[Attaullah Shah]

see this thread http://www.statalist.org/forums/foru...evious-5-years

[John Mess]

Thanks for the post! This might help with the Sharpe Ratio but for sure not with the regression! Do you know anything else that you can share?

[Attaullah Shah]

Try tsfill
That will make your time series as not missing. Then in the statsby command you can tell Stata through do file to ignore missing observations. I think it will help.

[Nick Cox]

There is a need for careful wording here. tsfill fills in gaps in time series data by adding observations. But although the gaps are now populated as far as identifier and time variable are concerned, everything else necessarily remains missing.

[Abraham Wolde-Tsadick]

Hi John,

You can also look at the following thread about the rolling regressions:

http://www.statalist.org/forums/foru...eexcess_return

If you look at Mata solution in the thread, I think there is a need to replace

invsym(X'X)*X'y

with

invsym(cross(X,X))*cross(X,y)

to dop observations with missing value in one of your regression variables.

Also you may need to normalize your variables so that they are scaled similarly.

Abraham

[John Mess]

Dear all,

Thank you a lot for your posts. Regarding the sharpe ratio this is the code that I wrote and solve my problem.

gen MeanVWExcRetGr=.
sort crsp_fundno r_year month
forval i=1990(1)2013{
local m=`i'-2
by crsp_fundno : egen Mean`i'=mean(VW_ExcRet_Gr) if r_year<=`i' & r_year>=`m'
replace MeanVWExcRetGr=Mean`i' if r_year==`i'
}

Its not perfect, but i have my means in one column now; so each year I have the same value of rolling mean within my monthly observations (egen). I am saying its not perfect because within the commands i do not specify that I want to average the values only in the case that i have 3 years of observations. So it also calculates the mean in the case in which i have 2 years of observations. The good news is that I can eliminate myself those observations.

I post the above because I want you to understand what I need exactly. I want to have the alpha and beta, each one in one column so that I can use them to regress them on other variables. Therefore, in the year 1995 for the fund no 100 which has 11 monthly observations for example, I want the alpha-output of the of the 3 year rolling (1995,1994,1993) capm/4 factor capm regression to be repeated in the 11 rows-cells of the alpha column. The same applies for beta. I applied the Mata code with some changes

egen g = group(crsp_fundno )
gen alpha = .
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(g=.,.,"g")
st_view(alpha=.,.,"alpha")
p = panelsetup(crsp_fundno ,1)
for ( i=1; i<=rows(p); i++ ) {
for ( o=p[i,2]; o>=p[i,1]; o-- ) {
y = J(1,1,.)
X = J(1,5,.)
b = .
for ( t=o; t>=p[i,1]; t-- ) {
if ( g[o,1] == g[t,1] & r_year[o,1] - r_year[t,1] <= 2 ) {
y = y \ VW_ExcRet_Gr[t,1]
X = X \ (mktrf[t,1],smb[t,1],hml[t,1],umd[t,1],1)
}
}
y=y[(2..rows(y)),.]
X=X[(2..rows(X)),.]
if (rows(y)>=6) {
b = invsym(cross(X,X))*cross(X,y)
alpha[o,1] = b[5,1]
}
}
}
end


but the outcome is this one and it doesnt include beta as well. Can you please help me?


fundno r_year month g alpha
5487 2001 1 478 -.0045781
5487 2001 2 478 -.0049922
5487 2001 3 478 -.0044039
5487 2001 4 478 -.0058963
5487 2001 5 478 -.0057021
5487 2001 6 478 -.0037893
5487 2001 7 478 -.0046226
5487 2001 8 478 -.0027665
5487 2001 9 478 -.0037288
5487 2002 1 478 .0009866
5487 2002 2 478 .0019246
5487 2002 3 478 .0019994
5487 2002 4 478 .002021
5487 2002 5 478 .0025631
5487 2002 6 478 .0019815
5487 2002 7 478 .0037848
5487 2002 8 478 .0035144
5487 2002 9 478 .003802
5487 2002 10 478 .0012915
5487 2002 11 478 .0016832
5487 2002 12 478 .0015888

Best,
J.M.

[Abraham Wolde-Tsadick]

John,

I am not sure if I understand you. However, repeating the advice in the thread to which I referred you earlier about not using Mata code while Stata code is available, here is an adapted code from that thread that will do the rolling regression. It will take you a lot of time if you have a large data set. Let me know if it takes you a lot of time. I advise you to check the results.

gen alpha = .
gen b_mktrf = .
gen b_smb = .
gen b_hml = .
gen b_umd = .
 qui forval p = 1/`=_N' {
    capture ereturn clear
    capture regress  VW_ExcRet_Gr mktrf smb hml umd if crsp_fundno == crsp_fundno[`p']  & inrange(r_year,r_year[`p']-2,r_year[`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'
}

Abraham

[John Mess]

Dear Abraham,
Thanks a lot! It is a bit slow but it works perfectly! It works exactly in the same manner with the one that I wrote. I will drop the observations for which I shouldnt have any values and thats it!

Again, thanks!

John

[Abraham Wolde-Tsadick]

Ok. Here is a comparable Mata code that would run faster:

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)
for (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

It might help you in the process of comparing alternative models when you have a large data set and you don't want to wait hours to see the resuts of each model. Anyway check the results and compare with Stata's regress.

Abraham

[Attaullah Shah]

To Abraham : Excellent meta code. Though a little off topic, but can you please translate this Stata code into meta code. I had posted this request some time ago, but no one replied.

set more off
levelsof loc, local(country)
foreach c of local country{
levelsof industry if loc=="`c'", local(ind) // loc is string that identifies each country
foreach i of local ind{
levelsof gvkey if industry==`i' & loc=="`c'", local(gk)
foreach g of local gk{
        forval y=1997/2009{
        quietly summarize ROA if gvkey!= `g' & fyear>`y'-1 & fyear<`y'+4 & loc=="`c'" & industry==`i'
        quietly replace SDx= r(sd) if gvkey==`g' & fyear==`y'+4
      }
}
}
}

For what the code does, you can see post http://www.statalist.org/forums/foru...ing-focal-firm

[John Mess]

To Abraham: Really fast Mata code. I only needed 1 minute instead of 2 hours. Furthermore, it works better as it gives back missing values if i have only one year's observation. Thanks really a lot. One final question. If i need the capm regression, that means only VW_ExcRet_Gr and mktrf, but not the smb hml umd, is this how the code should be like?

gen Alpha = .
gen b_Mktrf = .
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(Alpha=.,.,"Alpha")
st_view(b_Mktrf=.,.,"b_Mktrf")
p = panelsetup(crsp_fundno,1)
for (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] , 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] , 1)
}
}
if (rows(y)>=6) {
b = invsym(cross(X,X))*cross(X,y)
Alpha[o,1] = b[2,1]
b_Mktrf[o,1] = b[1,1]

}
}
}
end

[Abraham Wolde-Tsadick]

Hi Attaullah,

In your code, you compute standard deviation by country and industry (using summarize) but then you replace this value into SDx of other coutries (in the inner loop).
Is that what you want to do? I wrote the Mata code assuming that you want to compute the standard deviation per country and industry.

If you want to compute per country and industry you need to add:

loc=="`c'" & industry==`i'

into your code

replace SDx= r(sd) if gvkey==`g' & fyear==`y'+4

Here is the Mata code (it computes the standard deviation also when the window is less than 4 years):

gen SDx = .
mata
mata clear
st_view(loc =.,.,"loc")
st_view(industry=.,.,"industry")
st_view(gvkey=.,.,"gvkey")
st_view(fyear=.,.,"fyear")
st_view(ROA=.,.,"ROA")
st_view(SDx=.,.,"SDx")
st_view(idnew=.,.,"idnew")
p = panelsetup(idnew,1)
for (i=1; i<=rows(p); i++) {
    for (o=p[i,1]; o<=p[i,2]; o++) {
        h = J(1,1,0)
        for    (t=p[i,1]; t<=p[i,2]; t++) {
              if ( gvkey[t,1] != gvkey[o,1] & ROA[t,1] !=. & (fyear[o,1] > fyear[t,1] )  ///
              & (fyear[o,1] < fyear[t,1] + 5) )  {                            
            h = h \ ROA[t,1]
            }            
        }
        if (rows(h)>=2) {
        SDx[o,1] = sqrt(variance(h[(2..rows(h)),.]))
        }
    }
}
end

Abraham

[Abraham Wolde-Tsadick]

John,
Here is the code for one independent variable:

gen double alpha = .
gen double b_mktrf = .
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(alpha=.,.,"alpha")
st_view(b_mktrf=.,.,"b_mktrf")
p = panelsetup(crsp_fundno,1)
for (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], 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], 1)
            }            
        }
        if (rows(y)>=2)  {
            b = invsym(cross(X,X))*cross(X,y)
            alpha[o,1] = b[2,1]
            b_mktrf[o,1] = b[1,1]
        }
    }
}
end

Abraham

[Abraham Wolde-Tsadick]

Attaullah,

I noticed that

egen idnew = group(loc industry)

is missing from the code I posted earlier. It should be run before calling Mata.

Abraham