Calculate variance of error by group and save it

[Francois Durant]

Hi,

I am trying to calculate the idiosyncratic volatility of each stock of my sample. In other words, I am trying to find the standard deviation of the error term of the following regression:

bys permno: regress ret mkt

Once the variance is found for each stock ("permno"), I'd like to save it as a new variable "idiosync_vol". I'd like to save the coefficient on "Mkt" for each stock and name it "beta".

Here is a small sample of the data I have:

 
permno
daily_date
ret
Mkt

10090
2-Jan-92
-3.33333
0.040759

10095
2-Jan-92
-2.55474
0.040759

10097
2-Jan-92
-7.14286
0.040759

10100
2-Jan-92
-5.12821
0.040759

10104
2-Jan-92
2.586207
0.040759

10107
2-Jan-92
2.47191
0.040759

10108
2-Jan-92
-1.98676
0.040759

10114
2-Jan-92
-1.35135
0.040759

10116
2-Jan-92
-4.13223
0.040759

10119
2-Jan-92
-1.1976
0.040759

26518
20-Feb-92
0.738916
1.381473

26542
20-Feb-92
0.641026
1.381473

26550
20-Feb-92
1.333333
1.381473

26578
20-Feb-92
11.53846
1.381473

26585
20-Feb-92
0.45045
1.381473

26586
20-Feb-92
-2.92398
1.381473

26606
20-Feb-92
1.351351
1.381473

26614
20-Feb-92
1.204819
1.381473

26650
20-Feb-92
-0.86957
1.381473

26681
20-Feb-92
0.9375
1.381473

26710
20-Feb-92
0.350877
1.381473

26711
20-Feb-92
4.477612
1.381473

11508
7-Oct-92
2.702703
-0.71958

11509
7-Oct-92
0.847458
-0.71958

11511
7-Oct-92
10
-0.71958

11522
7-Oct-92
3.773585
-0.71958

11525
7-Oct-92
1.351351
-0.71958

11531
7-Oct-92
-1.78571
-0.71958

thanks for your help

[Friedrich Huebler]

Could you please use dataex to share excerpts from your data? This makes it easier for other list members to experiment with your data. dataex is mentioned in section 12 of the FAQ.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int permno str9 daily float(ret mkt)
10090 "2-Jan-92"  -3.33333  .040759
10095 "2-Jan-92"  -2.55474  .040759
10097 "2-Jan-92"  -7.14286  .040759
10100 "2-Jan-92"  -5.12821  .040759
10104 "2-Jan-92"  2.586207  .040759
10107 "2-Jan-92"   2.47191  .040759
10108 "2-Jan-92"  -1.98676  .040759
10114 "2-Jan-92"  -1.35135  .040759
10116 "2-Jan-92"  -4.13223  .040759
10119 "2-Jan-92"   -1.1976  .040759
11508 "7-Oct-92"  2.702703  -.71958
11509 "7-Oct-92"   .847458  -.71958
11511 "7-Oct-92"        10  -.71958
11522 "7-Oct-92"  3.773585  -.71958
11525 "7-Oct-92"  1.351351  -.71958
11531 "7-Oct-92"  -1.78571  -.71958
26518 "20-Feb-92"  .738916 1.381473
26542 "20-Feb-92"  .641026 1.381473
26550 "20-Feb-92" 1.333333 1.381473
26578 "20-Feb-92" 11.53846 1.381473
26585 "20-Feb-92"   .45045 1.381473
26586 "20-Feb-92" -2.92398 1.381473
26606 "20-Feb-92" 1.351351 1.381473
26614 "20-Feb-92" 1.204819 1.381473
26650 "20-Feb-92"  -.86957 1.381473
26681 "20-Feb-92"    .9375 1.381473
26710 "20-Feb-92"  .350877 1.381473
26711 "20-Feb-92" 4.477612 1.381473
end

Ideally, data excerpts allow others to reproduce your approach. This is the result of the regress command with the data above:

. bys permno: regress ret mkt

----------------------------------------------------------------
-> permno = 10090
insufficient observations

----------------------------------------------------------------
-> permno = 10095
insufficient observations

----------------------------------------------------------------
-> permno = 10097
insufficient observations

[repeat for all other values of permno]

[Carole J. Wilson]

Francois, precisely because of Friedrich's observation, when you do re-post your dataset with dataex, it would probably make more sense to provide complete (or at least 15+ dates) for a two or three PERMNOs than to provide a few dates with for many PERMNOs. You can use if/in statements with dataex.

[Roman Mostazir]

I guess you need -statsby- . See help stasby . Probably what you are looking for is:

statsby _b _se, by(permno) saving(fileneame.dta,replace) : reg ret mkt

This will save a new dataset named 'filename.dta'' in your current directory with coefficient and standard errors. This is purely a mechanistic advice given what you have asked. As I can see from Friedrich's post #2, that running the regression by 'permno' you will have insufficient observation.

[Clyde Schechter]

I fear that -statsby: regress- won't quite do everything the original poster asked for, because he also wants the standard deviation of the error term. So he'll need to write a short program to feed to -statsby-.

capture program drop regress_wrapper
program define regress_wrapper, rclass
    regress ret mkt
    return scalar rmse = e(rmse)
    exit
end

statsby _b _se rmse = r(rmse), by(permno) saving(filename.dta, replace): regress_wrapper

However, as the others have noted, with only one observation per permno, this will just produce an empty data set. Presumably the real data has multiple observations per permno.

[Roman Mostazir]

Thanks Clyde. Apologies to the poster for thinking around the standard error.

[Clyde Schechter]

It dawns on me that my solution in #5 is a bit more complicated than is needed. -statsby- can pick up -e(rmse)- directly from -regress-'s e() output, so there is no need for a wrapper program:

statsby _b _se rmse = e(rmse), by(permno) saving(filename.dta, replace): regress ret mkt

should work just fine.

[Francois Durant]

Thanks again Clyde!