Weighted average means in a rolling fashion

[Lisa Wilson]

Hi

I am not sure how to calculate weighted average means in a rolling fashion.

I can only calculate equally weighted means in a rolling fashion (for 12 weeks within the quarter) using -asrol- as follows:

bys quarter_date : asrol var1, window(weeks 12) stat(mean)

I think -rangestat- can also do the same thing as follows

rangestat (mean) var1, interval(week . 0) by(quarter_date ) casewise

However, both of them do not seem to be able to calculate weighted average means, say using weights= assets (which is total assets).

Can anyone help with this, please?

Thanks

[Joro Kolev]

Both of the commands that you are mentioning are user written, and I have never used any of them.

I think I know how to do what you are asking using the Stata's native -rolling-, like this:

. clear

. . webuse lutkepohl2
(Quarterly SA West German macro data, Bil DM, from Lutkepohl 1993 Table E.1)

. 
.         . tsset qtr
        time variable:  qtr, 1960q1 to 1982q4
                delta:  1 quarter

. rolling r(mean), window(10): summ consump [w=inc]
(running summarize on estimation sample)

Rolling replications (83)
----+--- 1 ---+--- 2 ---+--- 3 ---+--- 4 ---+--- 5 
..................................................    50
.................................

[Lisa Wilson]

Thanks.
My data contain gvkey(firm id), quarter_date, (quarterly date) and myWEEK (the week within the quarter which takes values from 1 to 12).

I want to calculate weighted averages in a rolling fashion such that the weighted average for week 1 in a given quarter is calculated from data in this week and then the weighted average for week 2 in the quarter is calculated using all data in week 1 and 2...and finally the weighted average for week 12 in the quarter is calculated using all data from week 1 to 12.

I can do exactly this with equally weighted averages (i.e. means) as follows:

 
 bys quarter_date : asrol var1, window(myWEEK 12) stat(mean)

I tried now to use rolling as you described and after changing it to be rolling within quarter.

bysort quarter_date: rolling r(mean), window(12): summ var1 [w=market_value]

However, -rolling- does not accept bysort and so I get an error message.


An example of my data is:

. dataex gvkey quarter_date myWEEK var1 var2 atq

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long gvkey float(quarter_date myWEEK var1 var2) double atq
 5709 84  7   .08055016  .8600017   385588
10618 84  7  -.37311205  .6712338   237728
 2497 84  8    .2122742  .8356309   131180
 8577 84  9    .4512017  .8577272   218943
 7922 84  9    .1730023  .6337475   296792
 6829 84  9   .07800133  .7656926   301728
 1410 84 10   .08243623  .8203975    67823
11259 84 10    .3284209  .7411835   592345
 3813 84 11   .22852953  .7249652  2155234
 9818 84 11   .23067483  .6205294  3834146
 4598 84 11   .46096885  .7854857   487281
 7116 84 11  .004986714  .7270674   550493
 7138 84 11  .033614047  .7377527    69358
 4990 84 11   .20032758  .6688451   120380
 5667 84 11   -.1699198  .9447361   142273
 6307 84 11   .08785275  .7212051  6102461
 9538 84 11  -.04841856  .9263492   150646
 9906 84 12    .1349105   .719992  1027906
 1913 84 12  .020135706  .6832787   346410
 5018 84 12  -.03091195  .8482092  1747335
 9599 84 12 -.073928945   .677292    14210
 8579 84 12  -.04923749  .4836178    72719
 8151 85  1   .25894284  .8430699   759373
65095 85  2   .08332882  .7699488   708877
11555 85  2   .08332882  .7699488   708877
 4798 85  2    .2689167  .7724686  5647645
 1161 85  2   .29093334  .4976593   224694
 8272 85  2   .02359083  .8851668  2370867
10984 85  2   .14018883  .6271937  4335660
11264 85  2   .14993037  .6989378   489018
 4517 85  2    .2689167  .7724686  5647645
 8215 85  2   .02737572  .8212099  3082901
 2817 85  2   .04299181  .7405852  6408098
 4988 85  3   .11450505 .56484914  1203203
11185 85  3    .3963384  .7002564    43366
 1209 85  3   .09258524   .526463  1840308
 1440 85  3    .0932021  .6606749 11100703
 9828 85  3    .0886558  .7413102  1820040
 5742 85  3    .3267858  .7930102  4621656
11161 85  3     .121522  .6819338    12608
 3036 85  3   .04214309  .6817594   753573
 5237 85  3   -.1071321  .6936299    39054
 6178 85  3  .002713267   .536874  1071248
10601 85  3    .1046787  .6090897  1734804
 7260 85  3   .16671346   .513506   300411
 3980 85  3   .07777518  .7849711  1404628
 9846 85  3   .12866454   .790919  7973305
 5256 85  3   .03319749  .6832724   399121
 7366 85  3   .17207083  .7545067  7490086
 1075 85  3    .1153273  .7153125  3022642
 7938 85  3   .25572988  .8227109   874685
 4029 85  3  -.03250061  .6720805  6611035
14912 85  3   .12866454   .790919  7973305
66591 85  3    .1779731  .7837205  2247094
11012 85  3   .11875774   .705156   185487
10507 85  3    .3031136  .6596744  6733430
 1742 85  3    .1153273  .7153125  3022642
 7993 85  3 -.014865967  .8537492   331065
 7985 85  3   .14641258  .7858456  1216500
 9698 85  3   .02408716  .7991259    48026
10867 85  3   .21332206  .8504531  6194297
 1045 85  3   .09361485  .9325973  3393143
 3897 85  4    .1529917  .6882424  5917039
12612 85  4     .220244  .7338203  1755980
 8455 85  4    .3561376  .6642694  4523297
 4241 85  4    .4034726  .6130147   729573
 1743 85  4   .06707016  .9221271   169110
 8199 85  4 -.015276105  .6366305   418844
13948 85  4    .1609601  .7486537  6230133
10502 85  4     .176176  .9165076   188914
 9850 85  4   .24174267  .6772937 11663203
23465 85  4   .16427736  .7099876  1075751
 3580 85  4   .01139313  .8093522  1019757
 2137 85  4    .1690758  .6937703  9754297
 6375 85  4   .11853556  .6055701  1224800
 7585 85  4   .13985388  .5737277  2163413
 7241 85  4   .08054866   .619671   931652
10860 85  4  -.03778432  .7882345  3637488
 2783 85  4    .3493773 .51358503  4323305
 8113 85  4    .1650878  .7382609  1764924
 8810 85  4   .29755652  .8144969  6795473
 7970 85  4    .3056838  .7480769  3745385
31596 85  4     .178029  .7392225 11317000
 5526 85  4    .5553062  .8155699   766174
 6867 85  4   .10144708  .7763379   588678
 1380 85  4    .3002398  .7827404  5802414
 8539 85  4   .14808817  .7806528  5829680
 7065 85  4     .405839  .6809729  1045160
65090 85  4    .2097874  .7919061  2003878
 5903 85  4   .27918783  .6944444  5488000
15448 85  4   .10401323  .6038108  2993933
 4093 85  4   .13561326   .689234  6258867
 3170 85  4   .06806282   .612512  2724287
 5525 85  4    .5553062  .8155699   766174
 5387 85  4   .16785666  .7070453  3008109
 8099 85  4    .1038211  .6954186  4132601
 8606 85  4   .13678727  .3473644  1095904
 4194 85  4   .07873064 .58844733  8885996
 2991 85  4   .14136566  .9090909 22934004
 4331 85  4    .1268315  .7212515   224197
end
format %tq quarter_date

I hope to get more assistance

[Lisa Wilson]

Dear All
Can anyone please help me with this?
If there is no Stata code that can calculate the rolling weighted average, can one calculate the weighted average by multiplying the ratio of the firm market value to the sum of market value over the rolling window by var1 and then calculate the simple average)?

[Joro Kolev]

You are not getting help because apparently what you are asking cannot be immediately done with any available command. Of course what you are asking can be ultimately done one way or another, but it will require some work. I am personally not willing to put that much work in. I can help you with some advice if you are willing to put in the work yourself.

First, check whether the user contributed -asrol- is really doing what you want. What you are describing in #3 is not rolling estimation, but rather recursive estimation.

Second, yes, you can do what you are saying below, but it requires a bit more work:

The formula is Weighted Mean = Sum(Wi*Xi)/Sum(Wi)

Assuming that indeed -asrol- is doing recursive estimation (I do not know whether this is true), you can do something like (pseudo code follows):

Lets say that the variable by which you want to weight is in a variable called "weight"

gen tempvar1 = weight*var1

bys quarter_date : asrol tempvar1, window(myWEEK 12) stat(sum)

and then you need to divide the output of the command above by the output of the command below

ren sum numerator

bys quarter_date if !missing(tempvar1): asrol weight, window(myWEEK 12) stat(sum)

gen weightedmean = numerator/sum

The weighted mean formula is

Dear All
Can anyone please help me with this?
If there is no Stata code that can calculate the rolling weighted average, can one calculate the weighted average by multiplying the ratio of the firm market value to the sum of market value over the rolling window by var1 and then calculate the simple average)?

[Lisa Wilson]

Thanks Joro.

Yes, I meant recursive estimation (i.e., rolling estimation with increasing window). I should have been more clear.
Anyway, I want to calculate those weighted averages for each week within the quarter so that the weighted average at a given week is calculated using all past data within the same quarter. I think your code should just do that.

I have a follow-up question. In my case, I am dealing with so many variables. They are labeled as var1, var2,....,var40.

Is there any efficient way to calculate the tempvar1 for all variables?

For example, I think in the second line of the code I can do:

bys quarter_date : asrol tempvar1-tempvar40, window(myWEEK 12) stat(sum)

but I am not sure how to get

gen tempvar1 = weight*var1

for all variables

and also do this more efficiently for all variables in

gen weightedmean = numerator/sum

[Joro Kolev]

Putting everything in a loop should do the job. Assuming that the weight variable is always the same, something like this:

forvalues i=1/40 {

gen tempvar`i' = weight*var`i'

bys quarter_date : asrol tempvar`i', window(myWEEK 12) stat(sum)

ren sum numerator

bys quarter_date if !missing(tempvar`i'): asrol weight, window(myWEEK 12) stat(sum)

gen weightedmean`i' = numerator/sum

drop sum numerator

}

drop tempvar*