Annualized standard deviation of residuals

[Patrick Johannes]

Hey community,

i have a dataset with ~900 companies and daily excess return data as well as the smb, hml and mom factor from the Carhart Modell. Now i need to calculate the idiosyncratic risk which is the annualized standard deviation of the residuals. Here is my code for one company, however this gives me the same standard deviation for all ~250 trading days with different values per year. I need to run the regression for every company in my sample (u_s_1 - u_s_913) but just to demonstrate here is my code for 1 company.

regress u_s_1 smb hml mom u_i_sp500composite if year == 2002
predict resid
egen yearly_sd_resid = sd(resid), by(year)

Can somebody help me correcting the code?

Best regards, Patrick

[Clyde Schechter]

capture program drop one_company
program define one_company
    regress u_s_1 smb hml mom u_i_sp500composite if year == 2002
    predict resid
    egen yearly_sd_resid = sd(resid), by(year)
    exit
end

runby one_company, by(company) status

In the -by()- option of the -runby- command, replace company with the actual name of whatever variable(s) uniquely identify companies in your data.

Note: Your code uses 2002 as a reference year for which the regression is fitted, and then calculates residuals for all observations, including in other years. That's perfectly legal, and may be exactly what you need. I'm just calling it to your attention to make sure it isn't a mistake.

-runby- is written by Robert Picard and me and is available from SSC.

As no example data was provided this code is not tested and may be incorrect or simply not compatible with your data. If further assistance is required, be sure to include example data, created with the -dataex- command when you post back. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Patrick Johannes]

Hi Clyde,

thank you very much! That already helped me a lot. I need the standard deviation of the residuals per company per year in the end, however as i tried your code i got the same value for all companies for the respective year. Maybe you can help me out with this one. Here is an example of my data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(year date) float(u_s_1 u_s_2 u_i_sp500composite) double(smb hml mom)
2002 15341            .             .            .     .     .                   .
2002 15342  -.016094288   -.005387293  -.001130835  -.65   .17  -.9500000000000001
2002 15343  -.010333146   -.006104591  .0021504585   .85  -.43                -.42
2002 15344 -.0040030316   -.008791095 -.0008067903   .11   .39                -.29
2002 15347   -.01902853   -.014014973  -.013515748  -.43   .86  .35000000000000003
2002 15348  -.012637663   -.013519084   -.01033493  1.17   .28  .35000000000000003
2002 15349  -.010483081    -.01246576  -.011762526   .17   -.1                 .07
2002 15350  -.019725377   .0020923638  -.005761276  -.08    .2                 .21
2002 15351 -.0018032007   .0082706185  -.016464153  -.07   .22  .35000000000000003
2002 15354  -.025001645    -.00682113   -.01329938  -.56  -.09                 .86
2002 15355  -.027429355   -.009149608 -.0001872491  -.49   .26  .35000000000000003
2002 15356  -.063639075    .006004785     -.023296   .15   .49                1.29
2002 15357   .003093715    .008803273   .002993642   .34  -.54                 .13
2002 15358   .014584108   -.006825357  -.016968502  -.54    .8                 .36
2002 15362   -.01056762     -.0057814   -.01436167  -.19   .03  1.1500000000000001
2002 15363  .0027273665   -.008566898  .0009020387   .71  -.16  -.6900000000000001
2002 15364   .011434672   -.007871788 -.0034821925  -.28    .3                -.68
2002 15365   .009042934   -.008745859  -.006005221   -.2   .31                 .31
2002 15368  -.013221037   -.013836685   -.00717511    .1   .66                  .5
2002 15369  -.024244905   -.018143533   -.03595424  1.37   .21                1.02
2002 15370    .00416412   -.002385661    .00481484   .26  -.69                 .46
2002 15371  .0032324386      .0143706   .007941674  -.58   .21  .35000000000000003
2002 15372   .013246407   -.011516553  -.014101977   .35  -.29                 .17
2002 15375   -.04085313   -.021555353   -.03203846    .7   .17                2.35
2002 15376  -.014514463 -.00013391771  -.011014806   .31  -.52                1.16
2002 15377  .0022532817    -.02612962  -.012885878  -.38   -.1                 .45
2002 15378  -.008645334    -.01309844  -.009887138 -1.18   .91                -.01
2002 15379   .008405613   -.012231473   .007760128  -.33   .41               -1.04
2002 15382   .017867718  .00002890327   .007284353  -.26  -.42                -.66
2002 15383  -.005598874   .0010494735  -.010994696   .71  -.53                 .54
2002 15384   .007257977   -.009854548   .003229162  -.02  -.13                -.13
2002 15385  .0023734085  -.0016542597  -.008735649  -.55  -.16                 .46
2002 15386  -.018878805     .01183693  -.018035185   .89   .17   .6900000000000001
2002 15390    -.0203333   -.020166267   -.02602625   .28   .45                 .88
2002 15391        .0252    .010343022   .006491663  -.32   .36                -.03
2002 15392  -.016436124   -.014496088   -.02261075  -.46   .65                1.26
2002 15393   .018527457   .0010167671   .001196313   .43  -.07                -.02
2002 15396   .005117078    -.02168816   .010815435 -1.43   .58                 -.8
2002 15397  -.016599154   -.011944562  -.006961725   .31   .05                 .15
2002 15398  -.009517924   -.008771116  -.006132836     0   .38                 .07
2002 15399  -.016032362  -.0041672005   -.00982104  -.26   .42                 .21
2002 15400   .013069322     .00948618    .01539232  -.51  -.34                -.77
2002 15403   .015109953    -.00908972   .012330463  -.44  -.17               -1.34
2002 15404  -.030523675    -.04882438  -.013692074   .55    .1               -1.04
2002 15405   .004744025    .011011448   .007599426   -.1   .29  -.9500000000000001
2002 15406  -.010621263    -.03726425  -.011474172   .62   .08                -.06
2002 15407  -.003952359   -.011794533  -.001164391    .4   -.1                -.73
2002 15410  -.015266945   .0036654705  -.003604493    .1  -.02                -.64
2002 15411 -.0003784032     .00246677 -.0092785675   .34  -.22   .8200000000000001
2002 15412    -.0219509   -.001398786  -.016644241   .51  -.01                 .88
2002 15413  .0020765862    -.01896336  -.007894756   .42  -.03                 .29
2002 15414 -.0003059019    -.02225072   .004315262 -1.01   .33                 .42
2002 15417 -.0004273332   -.016300369  -.007503291   .64    .1                -.12
2002 15418   -.01110446   -.009432572   -.00293846   .14  -.18                 .38
2002 15419  -.021901453   -.037046187  -.022836573   .43   .62                 .98
2002 15420   -.02043035    .003752614  -.005466897  1.04  -.31                -.34
2002 15421   -.02328147     .01827033   -.01124324  -.18   .27                 .53
2002 15424  -.012940103   -.031315666   -.02176053   .63  -.06                 .51
2002 15425  .0011720073    .001171277 -.0010224354   .52  -.32   .5700000000000001
2002 15426  -.004344348    -.02475694 -.0016505726  -.07   .63                 .22
2002 15427   -.02381007    .006204656 -.0045453906  -.18   .35                -.42
2002 15431  -.004829702     .00509367  -.007739449  -.09  -.07                -.33
2002 15432  -.009347395  -.0025023164  -.015568067   .25   .19                 .31
2002 15433  -.025251504   -.016959721  -.016865779   .28   .49  .41000000000000003
2002 15434   .003661413   -.009836903  -.006146415  -.08   .48                 .62
2002 15435     .0591935   -.023999386   -.01019212  -.19   .74                 .84
2002 15438   .006844451   -.006229464  -.004485667   .51   .14   .5700000000000001
2002 15439   .003381272    .006574274  -.013626295   .45   .62                 .37
2002 15440   -.00523953    .028811296     .0043112    .7  -.34                 .43
2002 15441  -.017445141    -.01666554    -.0309287   .84   .76                1.23
2002 15442   -.02016688   -.009418315  -.000385211  1.01   .17                 .07
2002 15445   -.01249482   -.005882961    -.0146393   .44  -.04                 .01
2002 15446   .016255898    .003732768   .016151996  -.65    .7               -1.56
2002 15447  -.014178229   -.003875656  -.008980075  -.49   .34  -.8300000000000001
2002 15448   -.00570715  -.0038859195  -.008405976    .5  -.62                 .38
2002 15449  .0027375435   -.005537743  -.006378365  -.26   .25                 .52
2002 15452   -.00604084   -.013596795   -.02253047   .85  -.54                1.19
2002 15453  -.012857048   -.003513262   -.01322262   .14   .52                 .85
2002 15454   .002771901    -.02399942  -.014114077   .03   .48                 .97
2002 15455  .0044868537   -.012044456  -.008513026   .21  -.09                 .62
2002 15456   -.02729489    .006580018  -.020891827  -.01   .27                1.12
2002 15459  -.021249853    -.02527375  -.017109456   .92  -.61   .6900000000000001
2002 15460   .018445542   .0081279725  .0037129165   .54   .13                 .07
2002 15461  -.005413463    .012275712  .0019109985    -1   .73                 .11
2002 15462  .0021649234   -.025349423  -.008619613   .53   .34                1.31
2002 15463  -.008263611   -.017611876  -.017286029   .61   .65                 .73
2002 15466   -.02913284    -.03067307  -.026519196  -.05   .91                1.59
2002 15467  -.006432631   -.016628291  -.010009116  -.85   .89                 .65
2002 15468   .008092998    .006835748    .02995247  -.86  -1.9               -4.53
2002 15469  -.006050642   -.011782067  -.021457044  -.27   .63                1.52
2002 15470  -.002260508   -.024408435  -.023927635  -.08   .38                1.05
2002 15473    .00552585   -.011498265     .0115115  -.35 -1.01               -1.52
2002 15474   .004533636    .007013478   .013950523   .52  -1.1               -1.62
2002 15475  -.010779376     -.1052133  -.012358244   .29   .62                 .62
2002 15476   -.00769585   -.031825982 -.0004270914   -.8  -.72 -.41000000000000003
2002 15477  -.001910316     .03264062  .0008308836  -.21  -.39                -.32
2002 15480  -.021173967    -.01947251  -.020382803  -.16   .99                 .49
2002 15481   -.01687056     -.0151164  -.018033905   -.3   .18                 .72
2002 15482  .0022473289     .01358891  -.001269773  -.65  -.12                 .11
2002 15483  .0004229355    -.00385331   .003142402   .03   -.1                -.56
end
format %tdnn/dd/CCYY date

It would be great if you could have a look and maybe try to help me with that.

Best Wishes, Patrick

[Clyde Schechter]

Your data example is, unfortunately, not helpful for this. It does not include a variable that identifies companies (or if it does, I cannot guess from the variable names which it might be.)

In addition, I do not understand your description of what you are getting and what you want. Please in your response show an example of the output you are getting and then show or explain what it should look like.

[Patrick Johannes]

Hi Clyde,

thanks for mentioning that. I changed the dataformat to be a panelstructure. See example below. In the end i need one idiosyncratic risk factor per company per year. The idiosyncratic risk is the standdard deviation of the residuals. Can you follow me on that one?

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(date company_code) float(u_i_sp500composite u_s_) double rf float id int year double(smb hml rmw cma mom)
15707   1 .02775197    .02259933 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   2 .02775197   -.00375076 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   3 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   4 .02775197    .05615969 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   5 .02775197    .05111694 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   6 .02775197    .02890717 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   7 .02775197    .05679418 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   8 .02775197     .0369642 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707   9 .02775197    .09074519 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  10 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  11 .02775197   .026707014 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  12 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  13 .02775197    .02349245 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  14 .02775197   .014004753 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  15 .02775197    .07671017 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  16 .02775197    .06526811 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  17 .02775197   .017117431 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  18 .02775197   .019945944 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  19 .02775197    .03778914 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  20 .02775197    .04265119 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  21 .02775197    .05863059 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  22 .02775197    .00970646 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  23 .02775197  -.009691338 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  24 .02775197    .01338257 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  25 .02775197 -.0000890791 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  26 .02775197    .07568891 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  27 .02775197   .014662322 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  28 .02775197 .00025962395 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  29 .02775197    .01445272 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  30 .02775197     .0513337 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  31 .02775197     .0279899 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  32 .02775197    -.0144544 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  33 .02775197   .021528784 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  34 .02775197  -.013183867 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  35 .02775197   .026252544 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  36 .02775197  -.003550374 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  37 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  38 .02775197   .008717018 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  39 .02775197    .01510541 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  40 .02775197    .01067799 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  41 .02775197    .02123923 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  42 .02775197   .035227932 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  43 .02775197    .02088828 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  44 .02775197    .03629157 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  45 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  46 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  47 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  48 .02775197    .06866694 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  49 .02775197    .06696253 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  50 .02775197  -.009452214 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  51 .02775197    .03036154 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  52 .02775197   .022360845 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  53 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  54 .02775197    .02562666 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  55 .02775197        -.005 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  56 .02775197    .05270774 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  57 .02775197   .011973458 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  58 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  59 .02775197   .014305619 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  60 .02775197    .03166102 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  61 .02775197   .036478326 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  62 .02775197    .08184934 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  63 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  64 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  65 .02775197    .05433533 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  66 .02775197    .00559454 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  67 .02775197    .01507042 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  68 .02775197    .05301949 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  69 .02775197  .0040912973 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  70 .02775197   .024254926 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  71 .02775197   .015046902 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  72 .02775197    .07662035 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  73 .02775197   -.04339243 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  74 .02775197    .03036068 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  75 .02775197   .016057795 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  76 .02775197 -.0025340524 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  77 .02775197  .0021271522 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  78 .02775197    .03036326 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  79 .02775197   .034732044 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  80 .02775197  -.002334637 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  81 .02775197    .02323481 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  82 .02775197    .02725069 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  83 .02775197     .0675579 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  84 .02775197   .065373406 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  85 .02775197     .0605878 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  86 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  87 .02775197   .004629391 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  88 .02775197  -.000333372 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  89 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  90 .02775197   .003472999 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  91 .02775197   .008227102 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  92 .02775197            0 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  93 .02775197    .02851983 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  94 .02775197    .05928532 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  95 .02775197  -.003048216 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  96 .02775197    .09425052 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  97 .02775197  .0043899785 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  98 .02775197   .015759772 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707  99 .02775197   .029308293 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
15707 100 .02775197  .0013474497 .004999999888241291 254 2003 -.82 -.39 -.62 .5 -1.62
end
format %tdnn/dd/CCYY date

I really hope you can help me out with that on. Thanks in advance!

Best Wishes, Patrick

[Clyde Schechter]

I think this will do it:

capture program drop one_company
program define one_company
    regress u_s_1 smb hml mom u_i_sp500composite
    predict resid
    quietly summ resid
    gen risk = `r(sd)'
    exit
end

runby one_company, by(company year) status

Note: This doesn't work with your example data because there is only one date in the data set, so there is only one observation per company#year combination, hence no regression. But if I correctly understand what you want, this should do what you want in your real data set.

[Patrick Johannes]

Thank you very much! This worked perfectly
Is there any way to get an output with only one risk value per company and per year? I need something like a matrix with 913 values for 2002, for 2003 and so on. Is that possible?
Really appreciate your help.

[Joro Kolev]

You can tag one observation per group

egen mytag = tag(company year)

and then when you want to include only one company per year in the calculation you do

calculation if tag

or alternatively you drop everything else by

keep if tag