[athina lionaki]

hello nick...thanks again for your answer..i post my data in excel and the sd results as smooth3. the code that i use is:
smooth3



. drop id
. egen id = group ( Name )
. sort Name year
. bys id: gen t=_n
. tsset id t
. tsset id year
. tsegen double avgval6years = rowmean(L(1/6).JONES1 )
. label variable smooth3 "sd residuals jones1"

but i have still the problem in 2011...

[Nick Cox]

Sorry, but a spreadsheet file is no use to me. Precisely why is explained in the FAQ Advice #12 I've referred you to twice. I can't copy and paste your Stata data from a spreadsheet file I can't even open.

Someone else may be willing to help further.

[athina lionaki]

ok nick pls tsek this file....

[Nick Cox]

Well, you're asked not to post .dta files either -- but seemingly you are struggling to understand our guidelines.

This is a model question and a model answer:

Question: I used tsegen (SSC) to get the mean of the previous 6 years' values of a variable JONES1 for a panel dataset. Why I do get missing values for 2011? Two of my panels show the problem. My time variable extends from 2010 to 2015.

clear
input str109 Name str3 CountryCode int year double(JONES1 smooth3)
`"AAK AB (publ)"' `"SWE"' 2010 . .
`"AAK AB (publ)"' `"SWE"' 2011 -.0309122 .
`"AAK AB (publ)"' `"SWE"' 2012 -.0333146 -.03091223
`"AAK AB (publ)"' `"SWE"' 2013 -.0371781 -.03211342
`"AAK AB (publ)"' `"SWE"' 2014 -.0373874 -.03380165
`"AAK AB (publ)"' `"SWE"' 2015 -.0324988 -.03469809
`"AB SKF"' `"SWE"' 2010 . .
`"AB SKF"' `"SWE"' 2011 -.0321861 .
`"AB SKF"' `"SWE"' 2012 -.0331026 -.03218611
`"AB SKF"' `"SWE"' 2013 -.0348539 -.03264434
`"AB SKF"' `"SWE"' 2014 -.0320926 -.03338086
`"AB SKF"' `"SWE"' 2015 -.0305047 -.03305881
end

egen id = group(Name), label
tsset id year
tsegen double avgval6years = rowmean(L(1/6).JONES1)

list id year JONES1 avg, sepby(id)

     +-----------------------------------------------+
     |            id   year      JONES1   avgval6y~s |
     |-----------------------------------------------|
  1. | AAK AB (publ)   2010           .            . |
  2. | AAK AB (publ)   2011   -.0309122            . |
  3. | AAK AB (publ)   2012   -.0333146    -.0309122 |
  4. | AAK AB (publ)   2013   -.0371781    -.0321134 |
  5. | AAK AB (publ)   2014   -.0373874   -.03380163 |
  6. | AAK AB (publ)   2015   -.0324988   -.03469808 |
     |-----------------------------------------------|
  7. |        AB SKF   2010           .            . |
  8. |        AB SKF   2011   -.0321861            . |
  9. |        AB SKF   2012   -.0331026    -.0321861 |
 10. |        AB SKF   2013   -.0348539   -.03264435 |
 11. |        AB SKF   2014   -.0320926   -.03338087 |
 12. |        AB SKF   2015   -.0305047    -.0330588 |
     +-----------------------------------------------+

Answer: Thanks for using dataex (SSC) and explaining where tsegen comes from, as requested in the FAQ Advice.

Note that in your set-up there is no scope for getting the value 6 years before as the latest year, 2015, is just 5 years after the first year, 2010.

But tsegen does what it can.

For 2010 tsegen can do nothing as the previous year 2009 is not even in the dataset.

For 2011 tsegen could at most use the value for 2010 but whenever that is missing the mean returned will inevitably also be missing.

For 2012, 2013, 2014, 2015 tsegen can use at most 2, 3, 4, 5 values and will return a mean whenever any of those is not missing.

It is a good idea to keep track of how many values a mean is based on: see also rangestat (SSC).

[athina lionaki]

thank you...nick...i was thinking the same... i will tsek and rangestat (SSC).
thank you....

[Attaullah Shah]

Someone just liked my asrol post, that is dated 2014. I just wanted to update that asrol has come a long way in terms of options and speed. The latest version is 5.0 (June 2020). It uses a flexible window that can take up to three arguments. Also, the latest version uses extremely efficient algorithm for finding the rolling window indices. You may like to know about the latest version here or update it from ssc in a week time

ssc install asrol

[Fadi Ansar]

Dear All,

I was trying to calculate the 3-year rolling standard deviation of return on asset .I have unbalnced panel data from 2005-2021 for bank level data.
I have used both the following commands such as

rangestat (count) roa (sd) roa,by(bank) interval(year 0 3)

as well as asrol command

bys:asrol roa, w(year 3) s(sd) g(sd)

.Since I am calcualting same rolling standerd deviation ,I should get the same valus right? .But I am getting diffrent values .Please see the below example.first sd is based on rangestat and sd is based on asrol command.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 bankname int year str14 ownership float(capitalisation roa) long bank double(returnonassets_count returnonassets_sd_sd3 sd_N)
"ALLAHABAD BANK"      2005 "Public Sector"  .05155952  1.33 1 3                      .                   .
"ALLAHABAD BANK"      2006 "Public Sector"  .06580575  1.42 1 3   .0004390931080029926  .06363954961532742
"ALLAHABAD BANK"      2007 "Public Sector"   .0661629  1.26 1 3     .08466196277729261  .08020804325068985
"ALLAHABAD BANK"      2008 "Public Sector"  .06295023  1.32 1 3     .08045848564305398  .08082901467917224
"ALLAHABAD BANK"      2009 "Public Sector"  .05992902    .9 1 3     .04772181485486514  .22715636384997973
"ALLAHABAD BANK"      2010 "Public Sector"  .05548889  1.16 1 3     .07054337757331035   .2119748753168801
"ALLAHABAD BANK"      2011 "Public Sector"  .05623365  1.11 1 3      .0901792688389379  .13796135104577556
"ALLAHABAD BANK"      2012 "Public Sector"  .05743369  1.02 1 3     .10102107509519449  .07094598559743424
"ALLAHABAD BANK"      2013 "Public Sector"  .05554799   .64 1 3     .03517967670670633  .24946610634105637
"ALLAHABAD BANK"      2014 "Public Sector"    .053534   .57 1 3     .14536674442014735  .24214320303140308
"ALLAHABAD BANK"      2015 "Public Sector"   .0556714   .29 1 3     .13773876674232197  .18520258974696194
"ALLAHABAD BANK"      2016 "Public Sector"  .05864283  -.33 1 3     .36248086835604376  .46057934844953496
"ALLAHABAD BANK"      2017 "Public Sector"  .06031272  -.13 1 3      .6804868174066903   .3164385148414566
"ALLAHABAD BANK"      2018 "Public Sector"  .04063241 -1.96 1 3      .4541167047733162   1.003809431269485
"ALLAHABAD BANK"      2019 "Public Sector"  .03673051 -3.48 1 2     .47815013386305705   1.677388856667172
"ALLAHABAD BANK"      2020 "Public Sector"  .04908012 -2.22 1 1     .05513769918261641     .81297805556945
"ANDHRA BANK"         2005 "Public Sector"  .26091126  1.38 2 3                      .                   .
"ANDHRA BANK"         2006 "Public Sector"  .05612817  1.31 2 3 3.6247902169389374e-08 .049497511772285015
"ANDHRA BANK"         2007 "Public Sector"  .07115781  1.16 2 3    .025722107396185318  .11239811147526751
"ANDHRA BANK"         2008 "Public Sector"  .06639069  1.09 2 3      .0284144877149058  .11239806021299266
"ANDHRA BANK"         2009 "Public Sector"  .05741565  1.39 2 3    .031010150838286333  .15695008273204414
"ANDHRA BANK"         2010 "Public Sector"  .05326472  1.36 2 3    .040150649754136195   .1652270959283402
"ANDHRA BANK"         2011 "Public Sector"  .04881476  1.19 2 3      .1921771158497549  .10785789654444057
"ANDHRA BANK"         2012 "Public Sector"  .05961776   .99 2 3     .14678534681492603  .18520259537907327
"ANDHRA BANK"         2013 "Public Sector"  .06005353   .29 2 3     .21941077113077764   .4725815912137361
"ANDHRA BANK"         2014 "Public Sector"  .05769821   .38 2 3     .16717189283766762   .3808324310266309
"ANDHRA BANK"         2015 "Public Sector"  .05221348   .28 2 3     .49214143676375516 .055075704354555687
"ANDHRA BANK"         2016 "Public Sector"  .05435045   .08 2 3      .4252328344621754  .15275252218968471
"ANDHRA BANK"         2017 "Public Sector"  .05498245 -1.46 2 3      .2391473706836734    .952120462448358
"ANDHRA BANK"         2018 "Public Sector"  .05094886 -1.09 2 3      .3557916932487735   .8038864129377447
"ANDHRA BANK"         2019 "Public Sector"  .04467204   -.5 2 2      .4144200177619128   .4841831809273453
"ANDHRA BANK"         2020 "Public Sector"  .05280597  1.21 2 1    .010300836974367556  1.1945850394500346
"AXIS BANK"           2005 "Private Sector" .05014084  1.18 3 3                      .                   .
"AXIS BANK"           2006 "Private Sector" .06415912   1.1 3 3       .071164932033737  .05656848854695771
"AXIS BANK"           2007 "Private Sector" .05802455  1.24 3 3     .07427146475475173  .07023768127603842
"AXIS BANK"           2008 "Private Sector" .04644193  1.44 3 3     .03979089445994405   .1708800933235088
"AXIS BANK"           2009 "Private Sector" .08004068  1.67 3 3     .10571406069197627  .21517431999127154
"AXIS BANK"           2010 "Private Sector" .06914881  1.68 3 3     .07344321297982027  .13576935166180149
"AXIS BANK"           2011 "Private Sector"  .0888171  1.68 3 3     .02571304867164073 .005773497185855025
"AXIS BANK"           2012 "Private Sector" .07827681   1.7 3 3    .028256320489889578 .011547063197225461
"AXIS BANK"           2013 "Private Sector" .07985406  1.78 3 3     .00677187671847061  .05291502081449223
"AXIS BANK"           2014 "Private Sector" .09721574  1.83 3 3      .3415537236229211  .06557437724416511
"AXIS BANK"           2015 "Private Sector" .09972863  1.72 3 3       .413956407504573 .055075710667583364
"AXIS BANK"           2016 "Private Sector" .09671658   .65 3 3     .25381435796462437   .6518435744028364
"AXIS BANK"           2017 "Private Sector" .09848618   .04 3 3      .3035812980729367    .850431281114237
"AXIS BANK"           2018 "Private Sector" .09271078   .63 3 3     .03796193671932448   .3465544610084355
"AXIS BANK"           2019 "Private Sector"  .0917728    .2 3 3      .1265030358059831  .30512292331788216
"AXIS BANK"           2020 "Private Sector" .08324168    .7 3 2      .1181860816893096   .2707397204677344
"AXIS BANK"           2021 "Private Sector" .09282245  1.21 3 1     .10209474614088712   .5050082684479728
"BANK OF BARODA"      2005 "Public Sector"  .19210494   .75 4 3                      .                   .
"BANK OF BARODA"      2006 "Public Sector"  .05944964   .79 4 3     .02023609892395137 .028284286420327365
"BANK OF BARODA"      2007 "Public Sector"  .06917949    .8 4 3     .06378875242218508 .026457521671453203
"BANK OF BARODA"      2008 "Public Sector"   .0604273   .89 4 3     .05809594492070316  .05507568721922883
"BANK OF BARODA"      2009 "Public Sector"  .06149199  1.09 4 3     .02128707115880506  .14843630889950282
"BANK OF BARODA"      2010 "Public Sector"  .05682091  1.21 4 3     .04981574705404479  .16165810309994594
"BANK OF BARODA"      2011 "Public Sector"  .05427766  1.33 4 3     .08338996871901346  .12000000476837158
"BANK OF BARODA"      2012 "Public Sector"  .05871567  1.24 4 3     .10260934551020158  .06244998723771628
"BANK OF BARODA"      2013 "Public Sector"  .06142528    .9 4 3    .021856224472793378  .22678921421913545
"BANK OF BARODA"      2014 "Public Sector"  .05843058   .75 4 3      .3410018148058771   .2510644090304655
"BANK OF BARODA"      2015 "Public Sector"  .05456471   .49 4 3      .3180378149361379  .20744475590619504
"BANK OF BARODA"      2016 "Public Sector"  .05571466  -.78 4 3     .16403141685116387    .818677781450771
"BANK OF BARODA"      2017 "Public Sector"  .05987546    .2 4 3      .1929140610060536    .665507313398499
"BANK OF BARODA"      2018 "Public Sector"  .05800069  -.34 4 3     .13804616651149945  .49084959144360296
"BANK OF BARODA"      2019 "Public Sector"  .06027053   .06 4 3     .14632215604295704  .28023799709031416
"BANK OF BARODA"      2020 "Public Sector"  .05882438   .06 4 2     .12962704893863636  .23094010896632872
"BANK OF BARODA"      2021 "Public Sector"  .06205653   .07 4 1   .0009175172457541416 .005773503638247094
"BANK OF INDIA"       2005 "Public Sector"    .466394   .68 5 3                      .                   .
"BANK OF INDIA"       2006 "Public Sector"  .04700944   .88 5 3    .012802731923103664   .1414213478079398
"BANK OF INDIA"       2007 "Public Sector"  .04439028  1.25 5 3     .06230095943499194  .28919428031639566
"BANK OF INDIA"       2008 "Public Sector"   .0415703  1.49 5 3     .06324303986587651  .30730007743747584
"BANK OF INDIA"       2009 "Public Sector"  .05921484    .7 5 3     .20295553988431098  .40501029855090953
"BANK OF INDIA"       2010 "Public Sector"  .05984396   .82 5 3     .20283892260525396    .425715095929033
"BANK OF INDIA"       2011 "Public Sector"  .05175172   .72 5 3    .021318113847855903   .0642909993870333
"BANK OF INDIA"       2012 "Public Sector"  .04923698   .65 5 3     .05645556319386119  .08544004317366005
"BANK OF INDIA"       2013 "Public Sector"  .05451198   .51 5 3     .11763744994444933  .10692678130352644
"BANK OF INDIA"       2014 "Public Sector"  .05284582   .27 5 3     .20210111163764008  .19218045339097498
"BANK OF INDIA"       2015 "Public Sector"  .05220445   .94 5 3      .2997827176342837   .3394603508640031
"BANK OF INDIA"       2016 "Public Sector"   .0508273  -.24 5 3       .286329126667747   .5918051438344596
"BANK OF INDIA"       2017 "Public Sector"   .0508491  -.91 5 3      .3637105459455049    .936642951561532
"BANK OF INDIA"       2018 "Public Sector"  .04912135  -.84 5 3     .15583246763631703   .3682843160387013
"BANK OF INDIA"       2019 "Public Sector"  .05830399  -.43 5 3     .15599969016996956   .2592939124762318
"BANK OF INDIA"       2020 "Public Sector"  .06666607   .28 5 2     .24099958537832802   .5666568521311204
"BANK OF INDIA"       2021 "Public Sector"  .06669196   .43 5 1     .24983329250150924   .4593836519806258
"BANK OF MAHARASHTRA" 2005 "Public Sector"   .0750479   .16 6 3                      .                   .
"BANK OF MAHARASHTRA" 2006 "Public Sector"  .04690353   .76 6 3     .22821387345583471   .4242640644972437
"BANK OF MAHARASHTRA" 2007 "Public Sector"  .05036581   .75 6 3     .18509303759303083  .34355979510528606
"BANK OF MAHARASHTRA" 2008 "Public Sector"   .0446529   .72 6 3     .06696930796063315 .020816640142347336
"BANK OF MAHARASHTRA" 2009 "Public Sector"  .03699832    .7 6 3     .06030743079027291   .0251661184158651
"BANK OF MAHARASHTRA" 2010 "Public Sector"  .04264226   .47 6 3    .012726817613230744  .13892444684500613
"BANK OF MAHARASHTRA" 2011 "Public Sector"   .0402277   .55 6 3     .05477705200594472  .11676185879120402
"BANK OF MAHARASHTRA" 2012 "Public Sector"  .05194676   .74 6 3     .05603066685848089   .1386842979490136
"BANK OF MAHARASHTRA" 2013 "Public Sector"  .05181912    .3 6 3     .05402991358915148   .2206807637727425
"BANK OF MAHARASHTRA" 2014 "Public Sector"  .05469675   .33 6 3      .2545150181082897  .24583191921883454
"BANK OF MAHARASHTRA" 2015 "Public Sector"  .05405034   .07 6 3     .25138320794551816   .1422439290338149
"BANK OF MAHARASHTRA" 2016 "Public Sector"   .0552486  -.86 6 3     .41777452957471295    .625646345667263
"BANK OF MAHARASHTRA" 2017 "Public Sector"  .05459301  -.73 6 3      .5912887625154251    .503620236705608
"BANK OF MAHARASHTRA" 2018 "Public Sector"  .04631772 -3.01 6 3     .30866887607028876   1.280481664959036
"BANK OF MAHARASHTRA" 2019 "Public Sector"  .06360928   .23 6 3      .9361201073929193  1.6642115189786766
"BANK OF MAHARASHTRA" 2020 "Public Sector"  .03488269    .3 6 2      .9922691720984395  1.8911460362307069
"BANK OF MAHARASHTRA" 2021 "Public Sector"  .06369076   .55 6 1    .006046225349259058  .16822604140655073
end
label values bank bank
label def bank 1 "ALLAHABAD BANK", modify
label def bank 2 "ANDHRA BANK", modify
label def bank 3 "AXIS BANK", modify
label def bank 4 "BANK OF BARODA", modify
label def bank 5 "BANK OF INDIA", modify
label def bank 6 "BANK OF MAHARASHTRA", modify

What might be the reson for this or is I am a doing something wrong.
Can anyone please kindly help me with this

Nick Cox and @Attaullah Shah

[Nick Cox]

I will speak for rangestat.

Your option call int(year 0 3) will use data for up to 4 years, starting with the current year and including the next 3 years too, There is a fence-post year in your thinking, I fear: the sequence 0 1 2 3 includes 4 numbers, not 3.

Attaullah Shah is the authority on his command, but window(year 3) is, I think, quite different: his windows always look back.

So for year 2007, rangestat with the call you give looks at data for 2007 2008 2009 2010, while asrol looks at 2005 2006 2007.

If you want rangestat to use the same window as asrol, the call would be int(year -2 0).

There may be other differences. For example, rangestat uses #values - 1 in the denominator for SD. I haven't checked whether asrol does that.

[Attaullah Shah]

A few corrections are needed here:
1. The correct code for computing the standard deviation corresponding to each bank would be as follows:,

bys bank:asrol roa, w(year 3) stat(sd) gen(sd)

, Fadi Ansar has incorrectly written it as:

bys:asrol roa, w(year 3) s(sd) g(sd)

2. Nick Cox stated,

his windows always look back.

This was indeed the case in the past. However, with the introduction of version 5 of asrol, this behavior has changed. The window() option in asrol now accepts up to three arguments, enabling calculations to be performed on both backward and forward looking windows. Detailed information and examples on the usage of window in asrol can be found on the corresponding webpage.

By implementing Nick’s suggestion of using int(year -2 0), the subsequent code yields results that are identical.

 bys bank:asrol roa, w(year 3) stat(sd) gen(sd_asrol)

. rangestat (sd) roa, by(bank) int(year -2 0)

. list bankname year roa sd_asrol roa_sd in 1/20

     +-------------------------------------------------------+
     |       bankname   year     roa    sd_asrol      roa_sd |
     |-------------------------------------------------------|
  1. | ALLAHABAD BANK   2005    1.33           .           . |
  2. | ALLAHABAD BANK   2006    1.42   .06363955   .06363955 |
  3. | ALLAHABAD BANK   2007    1.26   .08020804   .08020804 |
  4. | ALLAHABAD BANK   2008    1.32   .08082901   .08082901 |
  5. | ALLAHABAD BANK   2009      .9   .22715636   .22715636 |
     |-------------------------------------------------------|
  6. | ALLAHABAD BANK   2010    1.16   .21197488   .21197488 |
  7. | ALLAHABAD BANK   2011    1.11   .13796135   .13796135 |
  8. | ALLAHABAD BANK   2012    1.02   .07094599   .07094599 |
  9. | ALLAHABAD BANK   2013     .64   .24946611   .24946611 |
 10. | ALLAHABAD BANK   2014     .57    .2421432    .2421432 |
     |-------------------------------------------------------|
 11. | ALLAHABAD BANK   2015     .29   .18520259   .18520259 |
 12. | ALLAHABAD BANK   2016    -.33   .46057935   .46057935 |
 13. | ALLAHABAD BANK   2017    -.13   .31643851   .31643851 |
 14. | ALLAHABAD BANK   2018   -1.96   1.0038094   1.0038094 |
 15. | ALLAHABAD BANK   2019   -3.48   1.6773889   1.6773889 |
     |-------------------------------------------------------|
 16. | ALLAHABAD BANK   2020   -2.22   .81297806   .81297806 |
 17. |    ANDHRA BANK   2005    1.38           .           . |
 18. |    ANDHRA BANK   2006    1.31   .04949751   .04949751 |
 19. |    ANDHRA BANK   2007    1.16   .11239811   .11239811 |
 20. |    ANDHRA BANK   2008    1.09   .11239806   .11239806 |
     +-------------------------------------------------------+

[Fadi Ansar]

Dear Nick Cox

I am again confused with the calculation of using rangestat. While calculating a 3-year rolling window standard deviation, it should give a missing value from 2005,2006 as there is no sufficient data for 2005 and 2006.The calculation of 3 years rolling standard deviation for 2007 will be like the standard deviation of 2005+2006+2007 and if it is for 2005 it will be for the previous years (SD(2003+2004+2005) But in the above data and calculation I am getting only one-year missing values for every bank

Am I missing anything here? Is something is wrong with my data?

Any help is highly appreciated

[Nick Cox]

I don't know what is confusing you. rangestat uses the available data within the specified window. In Stata the SD is calculated using sample size MINUS 1. So, if only one value is available, the SD is missing. If two or more values are available a SD can always be calculated. There is no rule that a value must be present for each value in the window,

[Fadi Ansar]

Thank you so much for your response. My confusion was If I need to calculate 3 year rolling window for 2005(calculated using

rangestat (count) roa (sd) roa,by(bank) interval(year -2 0)

,i need the data for the previous 2-year data, right? and similarly for other years as well. I don't know whether I am making the right assumption here. But for 2006 -only value is available and still the SD is calculated.


Your help is highly appreciated as always

[Nick Cox]

Sorry, but #27 looks like the same question to me, and so attracts the same answer.

If your data start in 2005, then for 2006 we can use the value for 2006 and that for 2005. Two values allow calculation of an SD.

If you want to insist that SDs are only cited for complete windows you should also count how many values are being used and only report SDs accordingly.