Rolling window average

[Francois Durant]

Hi,

I would like to create a 5 period (years in the example) rolling average of each of the coefficients "_b_mvalue _b_kstock". In the real dataset, I have ~100 variables instead of 2. Here is what I have so far, the last line is not correct:

webuse grunfeld, clear

xtset company year

statsby  _b, by(year) saving(test_RPS, replace): regress invest mvalue kstock

use test_RPS.dta, clear
egen avg_mvalue = mean(_b_mvalue)

How can I implement that?

[William Lisowski]

Here is the basic technique using the rolling command to produce rolling averages. You will need to modify it to loop over your independent variables.

webuse grunfeld, clear

xtset company year

statsby  _b, by(year) saving(test_RPS, replace): regress invest mvalue kstock

use test_RPS.dta, clear
tsset year
rolling mean_b_mvalue=r(mean), window(5): summarize _b_mvalue 
drop start
rename end year
merge 1:1 year using test_RPS.dta
sort year
drop _merge
list year _b_mvalue mean_b_mvalue, clean noobs abbreviate(16)

    year   _b_mvalue   mean_b_mvalue  
    1935    .1024979               .  
    1936    .0837074               .  
    1937    .0765138               .  
    1938    .0680178               .  
    1939    .0655219        .0792517  
    1940     .095399         .077832  
    1941    .1147638        .0840432  
    1942    .1428251        .0973055  
    1943    .1186095        .1074239  
    1944    .1181642        .1179523  
    1945    .1084709        .1205667  
    1946    .1379482        .1252036  
    1947     .163927        .1294239  
    1948    .1786673        .1414355  
    1949    .1615962        .1501219  
    1950    .1762168        .1636711  
    1951    .1831405        .1727095  
    1952    .1989208        .1797083  
    1953    .1826739        .1805096  
    1954    .1345116        .1750927

[Clyde Schechter]

In addition to using -rolling-, there are two other approaches available for this: -tsegen- and -rangestat-. Both are available from SSC. The former is by Robert Picard and Nick Cox, and the latter is by those two plus Roberto Ferrer. If your data set is large, you will probably find these commands faster than using -rolling-. With panel data that has been -xtset-, and given the the relatively short window, -tsegen- is probably more efficient than -rangestat- for this application. -tsegen mean_b_value = rowmean(L(0/4)._b_value-. Both -tsegen- and -rangestat- have the advantage that the running mean is generated directly as a variable in the data set active in memory, so you don't need to -merge- back to the original test_RPS file. If you are going to be doing this 100 times, the time saving might be appreciable.

[Francois Durant]

William and Clyde,

As mentioned in my post, I have ~100 variables. How would you adapt William's code to include a "global" list of variables? Thanks!

[Nick Cox]

Sounds like a foreach loop, as William has already hinted.

The details would necessarily depend on your variable names, the structure of the repeated regressions (are you cycling over responses or predictors or both?) and what file structure you seek for the results.

I see no details here on any of those, which I think is why colleagues could say nothing specific.

[William Lisowski]

At this point, I would recommend abandoning the technique I described and instead recommending installing and using tsegen, again creating a loop like foreach var of varlist mvalue kstock to apply the command sequentially to each of the independent variables. The output of help foreach explains the syntax for creating such a loop, and the foreach section of the Stata Programming Reference Manual PDF included in your Stata installation explains it in greater detail.

[Francois Durant]

Thank you all. I got it to work great with "tsegen"!