Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Weighted average means in a rolling fashion

    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:

    Code:
    bys quarter_date : asrol var1, window(weeks 12) stat(mean)
    I think -rangestat- can also do the same thing as follows

    Code:
    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

  • #2
    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:

    Code:
    . 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
    .................................

    Comment


    • #3
      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:

      Code:
       
       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.

      Code:
      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 -----------------------
      Code:
      * 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

      Comment


      • #4
        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)?

        Comment


        • #5
          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"

          Code:
          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

          Code:
          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
          Originally posted by Lisa Wilson View Post
          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)?

          Comment


          • #6
            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:
            Code:
            bys quarter_date : asrol tempvar1-tempvar40, window(myWEEK 12) stat(sum)
            but I am not sure how to get
            Code:
            gen tempvar1 = weight*var1
            for all variables

            and also do this more efficiently for all variables in
            ​​​​​​
            Code:
            gen weightedmean = numerator/sum

            Comment


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

              Code:
              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*

              Comment

              Working...
              X