Calculation of total volatility and Idiosyncratic Volatility calculation

[Miral Ayan]

Dear, all,
Can you help me calculate annual Volatility and Idiosyncratic Volatility from monthly CRSP data?
Annual volatility: The annualized standard deviation of monthly stock returns over the previous year.
Idiosyncratic Volatility: The standard deviation of the residual from a regression of monthly stock returns on the CRSP value‐weighted market portfolio return

thanks in advance




* Example generated by -dataex-. For more info, type help dataex
clear
input double permno long date double(ret vwretd)
10000 9496 . .04306149
10000 9527 .c .009830249000000001
10000 9555 -.2571428716182709 .07250131
10000 9586 .36538460850715637 .053886830000000004
10000 9616 -.09859155118465424 -.007903121
10000 9646 -.22265625 .05084652
10000 9677 -.005025125574320555 .01424398
10000 9708 -.08080808073282242 -.059700130000000004
10000 9737 -.6153846383094788 .06618293
10000 9769 -.05714285746216774 -.07901992000000001
10000 9800 -.24242424964904785 .04930472
10000 9828 .05999999865889549 .01509305
10000 9861 -.37735849618911743 -.02638714
10000 9891 -.21212121844291687 .1284987
10000 9919 0 .047921820000000004
10000 9951 -.38461539149284363 .02366445
10000 9981 -.0625 -.01699554
10000 10010 -.06666667014360428 .005122664000000001
10000 10042 . .04365455
10001 9496 . .04306149
10001 9527 .c .009830249000000001
10001 9555 .020408162847161293 .07250131
10001 9586 .025200003758072853 .053886830000000004
10001 9616 .009900989942252636 -.007903121
10001 9646 -.009803921915590763 .05084652
10001 9677 -.013069307431578636 .01424398
10001 9708 -.010204081423580647 -.059700130000000004
10001 9737 .07216494530439377 .06618293
10001 9769 -.003076923545449972 -.07901992000000001
10001 9800 .03921568766236305 .04930472
10001 9828 .056603774428367615 .01509305
10001 9861 .014999999664723873 -.02638714
10001 9891 -.0357142873108387 .1284987
10001 9919 -.07407407462596893 .047921820000000004
10001 9951 .03680000081658363 .02366445
10001 9981 -.03921568766236305 -.01699554
10001 10010 -.0714285746216774 .005122664000000001
10001 10042 .051428571343421936 .04365455
10001 10073 .021276595070958138 .04424724
10001 10104 .0833333358168602 .0371465
10001 10134 -.02230769209563732 -.02077283
10001 10164 .019999999552965164 -.2253613
10001 10195 -.029411764815449715 -.07227181
10001 10226 -.03353535383939743 .07032388
10001 10255 .06382978707551956 .04487472
10001 10286 .07999999821186066 .051691270000000004
10001 10317 -.07629629969596863 -.01659768
10001 10346 .030612245202064514 .01099253
10001 10378 .019801979884505272 .0004473087
10001 10408 -.01203883532434702 .05145697
10001 10437 .029999999329447746 -.007272285000000001
10001 10470 .029126213863492012 -.02800578
10001 10500 -.021132076159119606 .03720373
10001 10531 .03921568766236305 .01764535
10001 10561 0 -.01641086
10001 10591 -.021132076159119606 .02107066
10001 10623 .019607843831181526 .06610128
10001 10651 .03846153989434242 -.01644182
10001 10682 .017777778208255768 .02146452
10001 10710 .07407407462596893 .04820698
10001 10743 -.03448275849223137 .03933567
10001 10773 .017142856493592262 -.004855535
10001 10804 .0357142873108387 .07712836
10001 10835 .27586206793785095 .022126
10001 10864 -.027027027681469917 -.0014733320000000002
10001 10896 .07042253762483597 -.02928516
10001 10926 .03947368264198303 .01781615
10001 10955 .037974681705236435 .01829401
10001 10988 -.018518518656492233 -.07011361
10001 11016 -.006289307959377766 .01489994
10001 11046 .012658228166401386 .02414776
10001 11077 0 -.028283060000000002
10001 11108 -.012658228166401386 .0889347
10001 11137 .014102564193308353 -.004192862
10001 11169 .025641025975346565 -.009391909
10001 11200 -.05000000074505806 -.0919028
10001 11228 .04078947380185127 -.05384421
10001 11261 -.012820512987673283 -.01249875
10001 11291 0 .06574162
10001 11322 .00129870162345469 .02951084
10001 11353 .01315789483487606 .04908338
10001 11381 .012987012974917889 .07584751
10001 11409 -.011538460850715637 .02892205
10001 11442 .03947368264198303 .0033108020000000003
10001 11473 0 .04073652
10001 11501 .07848101109266281 -.04402354
10001 11534 -.0357142873108387 .04677859
10001 11564 .1358024626970291 .026806780000000002
10001 11595 .013043479062616825 -.01097861
10001 11626 .1304347813129425 .017847000000000002
10001 11655 .13461539149284363 -.03729085
10001 11687 -.0067796604707837105 .1067822
10001 11718 -.0517241396009922 -.0011635480000000001
10001 11746 -.20000000298023224 .013355210000000001
10001 11778 .08181818574666977 -.02369942
10001 11808 .010638297535479069 .01341432
10001 11837 .010526316240429878 .006407138000000001
10001 11869 -.007708333432674408 -.0192581
10001 11900 .06382978707551956 .03989972
10001 11931 .03999999910593033 -.02081892
end
format %td date

[Clyde Schechter]

Assumptions:

1. the CRSP value‐weighted market portfolio return is what is in variable vwretd.
2. The idiosyncratic volatility, like the annual volatility, is to be calculated separately for each permno and uses data from the previous year.
3. The "previous year" does not include the current month but begins with the preceding month and extends back 12 months from there.

gen mdate = mofd(date)
format mdate %tm

rangestat (sd) annual_volatility = ret, by(permno) interval(mdate -12 -1)

rangestat (reg) ret vwretd, by(permno) interval(mdate -12 -1)
gen residual = ret - b_cons - b_vwretd*vwretd
rangestat (sd) idiosyncratic_volatility = residual, by(permno) interval(mdate -12 -1)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

I have created a monthly date variable and used it in the calculations. I do this because your daily variable date is actually only instantiated once in each month. Specifying one year in terms of daily date variables is a bit rough because leap years complicate things. By contrast, one year is always 12 months.

If the assumptions underlying this code outlined above are not correct, it will not be hard to modify the code to correct them. Do post back if assistance with that is wanted.

[Miral Ayan]

Dear Clyde Schechter, thank you for this code. I ran this code and it worked. I got the following data:

Now I want to make a panel data by keeping annual volatility, idiosyncratic volaity permno and year. Please guide further,

thanks



clear
input double permno long date float mdate double(ret annual_volatility idiosyncratic_volatility)
10000 9496 311 . . .
10000 9527 312 .c . .
10000 9555 313 -.2571428716182709 . .
10000 9586 314 .36538460850715637 . .
10000 9616 315 -.09859155118465424 .4401934026716633 .
10000 9646 316 -.22265625 .32351013932717215 .
10000 9677 317 -.005025125574320555 .287275461494355 .
10000 9708 318 -.08080808073282242 .2497209443172927 .1944770387771282
10000 9737 319 -.6153846383094788 .22387296146554064 .1380249704613783
10000 9769 320 -.05714285746216774 .29574120273293747 .26733356871717306
10000 9800 321 -.24242424964904785 .27503221911518094 .23784889559209582
10000 9828 322 .05999999865889549 .2604116945576039 .2168632668119147
10000 9861 323 -.37735849618911743 .25313355515175273 .2396263333540186
10000 9891 324 -.21212121844291687 .2528009477256856 .2332286779865339
10000 9919 325 0 .24195382430910603 .22455354487327403
10000 9951 326 -.38461539149284363 .24253155932510617 .23026216627038887
10000 9981 327 -.0625 .19744256579096123 .22407767750220076
10000 10010 328 -.06666667014360428 .19916816281377836 .2209313345991184
10000 10042 329 . .20144603637430408 .22295751617403922
10001 9496 311 . . .
10001 9527 312 .c . .
10001 9555 313 .020408162847161293 . .
10001 9586 314 .025200003758072853 . .
10001 9616 315 .009900989942252636 .003388343202472687 .
10001 9646 316 -.009803921915590763 .007825409952049266 .
10001 9677 317 -.013069307431578636 .015528872772758516 .
10001 9708 318 -.010204081423580647 .0173455025501062 .005758392203141855
10001 9737 319 .07216494530439377 .016951373064828853 .017290384208618025
10001 9769 320 -.003076923545449972 .03013863659447641 .04070710623153139
10001 9800 321 .03921568766236305 .02851283140091501 .03619247222798989
10001 9828 322 .056603774428367615 .02823260425387488 .03272440287473294
10001 9861 323 .014999999664723873 .029758493925786823 .03259820410494465
10001 9891 324 -.0357142873108387 .028253825274579432 .030217523410746176
10001 9919 325 -.07407407462596893 .031139810356196472 .04338081331219246
10001 9951 326 .03680000081658363 .040019295873722625 .04997440942867417
10001 9981 327 -.03921568766236305 .04065989195779515 .048785455570668816
10001 10010 328 -.0714285746216774 .04275705811732977 .04821314169215136
10001 10042 329 .051428571343421936 .04782061295160657 .05165922390732203
10001 10073 330 .021276595070958138 .050065931393455165 .05464224978451226
10001 10104 331 .0833333358168602 .05012874848264206 .05494397014978217
10001 10134 332 -.02230769209563732 .05155727395277617 .05690002431157596
10001 10164 333 .019999999552965164 .05218316564553668 .056846543228429164
10001 10195 334 -.029411764815449715 .05132814742696329 .056977356176964214
10001 10226 335 -.03353535383939743 .04919599997606414 .05535966124254774
10001 10255 336 .06382978707551956 .049513598930681794 .055030957054296156
10001 10286 337 .07999999821186066 .052640260786791605 .055874121863421757
10001 10317 338 -.07629629969596863 .051560942907324415 .05312032289141079
10001 10346 339 .030612245202064514 .05694912184055931 .059702845730771066
10001 10378 340 .019801979884505272 .05568851804754189 .05792029892361026
10001 10408 341 -.01203883532434702 .049472860193790213 .05193625361656328
10001 10437 342 .029999999329447746 .048892742173052166 .05242758063728302
10001 10470 343 .029126213863492012 .04910588574595511 .05247119108424021
10001 10500 344 -.021132076159119606 .04428779254729865 .04791577058314218
10001 10531 345 .03921568766236305 .04421513526538216 .048125623054610114
10001 10561 346 0 .04501367184209555 .048514408114905594
10001 10591 347 -.021132076159119606 .0434451809541379 .046916923159761925
10001 10623 348 .019607843831181526 .042385916590276775 .047399736981494286
10001 10651 349 .03846153989434242 .03943162074239483 .04247968254531923
10001 10682 350 .017777778208255768 .03418422963750553 .03682355426479776
10001 10710 351 .07407407462596893 .022190200317938932 .026634621537684054
10001 10743 352 -.03448275849223137 .027920844633780608 .03297354031179776
10001 10773 353 .017142856493592262 .03170988602239003 .03667316483887524
10001 10804 354 .0357142873108387 .030693541840289905 .03458974111477671
10001 10835 355 .27586206793785095 .030978207144783526 .0350370802025355
10001 10864 356 -.027027027681469917 .08131941785831083 .08163929854913811
10001 10896 357 .07042253762483597 .08171775755058794 .0841247307068636
10001 10926 358 .03947368264198303 .0823144789115315 .08438899457103237
10001 10955 359 .037974681705236435 .08140369206935451 .08432291838704885
10001 10988 360 -.018518518656492233 .07897808442313335 .08288687091607086
10001 11016 361 -.006289307959377766 .08092672974023163 .08682011266136218
10001 11046 362 .012658228166401386 .08222118483556545 .08917744467713964
10001 11077 363 0 .08236113901162931 .09018374929122688
10001 11108 364 -.012658228166401386 .082330631514203 .08856038379260975
10001 11137 365 .014102564193308353 .08091943824879702 .09028604667074251
10001 11169 366 .025641025975346565 .08098651676906991 .09045768057511322
10001 11200 367 -.05000000074505806 .0810322485784727 .0900153717912055
10001 11228 368 .04078947380185127 .03321576880524072 .036430034801186714
10001 11261 369 -.012820512987673283 .0326366761264625 .040269404137820944
10001 11291 370 0 .02775743754361587 .034180329842748616
10001 11322 371 .00129870162345469 .02567286986294692 .03316575773385202
10001 11353 372 .01315789483487606 .023132681214384963 .033112319135008936
10001 11381 373 .012987012974917889 .02269062920432937 .031832057683429824
10001 11409 374 -.011538460850715637 .022720653219726623 .03132566945828204
10001 11442 375 .03947368264198303 .02293217111801384 .03099918377122257
10001 11473 376 0 .025361319745255285 .034552526017662365
10001 11501 377 .07848101109266281 .02481583534435712 .027802303834826413
10001 11534 378 -.0357142873108387 .032480533566922064 .03635930380454828
10001 11564 379 .1358024626970291 .03479149360397292 .03903314229702267
10001 11595 380 .013043479062616825 .046732617977497504 .04976257117710676
10001 11626 381 .1304347813129425 .046394261419269306 .05006739862664932
10001 11655 382 .13461539149284363 .05495813025870037 .05592707317874592
10001 11687 383 -.0067796604707837105 .061324512517684934 .0553487983253316
10001 11718 384 -.0517241396009922 .061861945052321135 .054369470727684895
10001 11746 385 -.20000000298023224 .06721542484104338 .06861602150464997
10001 11778 386 .08181818574666977 .0959803632026455 .10227981121248411
10001 11808 387 .010638297535479069 .09707139915248067 .10268211941081071
10001 11837 388 .010526316240429878 .09708121341197705 .10222049044116158
10001 11869 389 -.007708333432674408 .09688986244306684 .10233591069498346
10001 11900 390 .06382978707551956 .09576062413801557 .10001923076760061
10001 11931 391 .03999999910593033 .0950013589435447 .10144883491550508
end
format %td date
format %tm mdate
[/CODE]
------------------ copy up to and including the previous line ------------------

[Clyde Schechter]

I don't understand what you want here. Please explain in greater detail. You already have a panel data set and it contains all the variables you mentioned. So what do you want that is different from what you already have?

[Hirindu Kawshala]

I'm engaging with quarterly data. I want to calculate Quarterly Volatility: The quarterly standard deviation of stock returns.
My codes are as follows, but it didn't work:

egen quarter_id = group(fyearq fqtr)

sort gvkey_numeric quarter_id
gen ret_simple = prccq/l.prccq - 1
by gvkey_numeric quarter_id: egen sd_return = sd(ret_simple)
rename sd_return TOTAL_VOLATILITY
list gvkey gvkey_numeric quarter_id fyear fqtr prccq ret_simple TOTAL_VOLATILITY in 1/50

Please help me with this. Thank you.

[Clyde Schechter]

You can't calculate quarterly volatility from quarterly return data. With quarterly return data, as you can see in your own example, you have only 1 observation per quarter for each firm. Consequently, the standard deviation is undefined (denominator N-1 = 0). The return data has to be at a more fine-grained level than the period over which you calculate volatility--not the same level.

[Hirindu Kawshala]

Clyde Schechter, Thank you. Yes, then I calculated log_total_risk_by_fquarter (similar to as above. TOTAL_VOLATILITY) and merged it with my quarterly dataset. Below are my codes. Please correct me if this approach is correct. Because, after merging, it shows a high number of observations (before merge, I have only a total of 527,888 firm quarterly observations). For the analysis, I want only my quarterly data.

xtset LPERMNO datadate
gen returns = (prccd - L.prccd) / L.prccd

gen fiscal_date = datadate
replace fiscal_date = fiscal_date + (12 - fyrc)*30 if month(datadate) > fyrc
replace fiscal_date = fiscal_date - fyrc*30 if month(datadate) <= fyrc

gen fyearq = year(fiscal_date)
gen fqtr = quarter(fiscal_date)
gen fyq = yq(fiscal_year, fiscal_quarter)

bysort LPERMNO fyq: egen var_returns_by_fquarter = var(returns)

gen log_total_risk_by_fquarter = log(var_returns_by_fquarter)


Merge with Quarterly Dataset:
sort GVKEY fyearq fqtr
merge 1:m GVKEY fyearq fqtr using "C:\Analysis\Daily.dta"
keep if _merge ==3
drop _merge


Result # of obs.
-----------------------------------------
not matched 2,376,677
from master 50,878 (_merge==1)
from using 2,325,799 (_merge==2)

matched 29,523,818 (_merge==3)
-----------------------------------------

[Clyde Schechter]

I a potential problem here. In the daily data set you grouped the calculations by LPERMNO, whereas when you -merge-d the data set you linked them on gvkey. Unless there is a 1:1 correspondence between LPERMNO and gvkey, then there are going to be mismatched data after the -merge-.

As for your -merge- results, the output shows that you have a large number of unmatched observations. Should that be the case? If so, can you continue to use the data from observations that appear in only one of the two data sets? Perhaps you should consider the -keep()- option in your -merge- command to reduce the data set to observations that are usable for the rest of your work with this data.

Now, even the matched observations are quite numerous. But that is no surprise. Each quarter will contain 91 days on average (perhaps a smaller number if only trading days are included), so the -merge- will multiply the size of the data by roughly that number. If what you wanted to do is just bring in one observation for each quarter for each firm to capture the variance in returns by quarter, what you should have done is

xtset LPERMNO datadate
gen returns = (prccd - L.prccd) / L.prccd

gen fiscal_date = datadate
replace fiscal_date = fiscal_date + (12 - fyrc)*30 if month(datadate) > fyrc
replace fiscal_date = fiscal_date - fyrc*30 if month(datadate) <= fyrc

gen fiscal_year = year(fiscal_date)
gen fiscal_quarter = quarter(fiscal_date)
gen fyq = yq(fiscal_year, fiscal_quarter)

collapse (sd) sd_returns_by_vqarter = returns (first) gvkey fyearq fqtr, by(LPERMNO fyq)
gen log_total_risk_by_fquarter = 2*log(sd_returns_by_fquarter)

Then you can -merge- the two data sets with a 1:1 -merge-.

Note: This code is untested because the example data, shown as a screenshot, cannot be imported to Stata to work with. Consequently it may contain typos or other errors.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 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.

[Hirindu Kawshala]

Thank you Clyde Schechter for the codes and data example command. Yes, as you mentioned, I want one observation for each quarter for each firm to capture the variance in returns by quarter. I checked, for some firms for one GVKEY there are multiple LPERMNO. In that case, I have to use GVKEY. As per the below red text GVKEY, I can't use it because (GVKEY may not be both target and by()). How can I overcome this? Thank you.

xtset GVKEY datadate
gen returns = (prccd - L.prccd) / L.prccd

gen fiscal_date = datadate
replace fiscal_date = fiscal_date + (12 - fyrc)*30 if month(datadate) > fyrc
replace fiscal_date = fiscal_date - fyrc*30 if month(datadate) <= fyrc

gen fyearq = year(fiscal_date)
gen fqtr = quarter(fiscal_date)
gen fyq = yq(fyearq, fqtr)

collapse (sd) sd_returns_by_fqtr = returns (first) GVKEY fyearq fqtr, by(GVKEY fyq)
gen log_returns_by_fqtr = 2*log(sd_returns_by_fqtr)

[Clyde Schechter]

So, in this situation, just remove GVKEY from the list of targets. That is:

collapse (sd) sd_returns_by_fqtr = returns (first) fyearq fqtr, by(GVKEY fyq)

[Hirindu Kawshala]

It worked. Thank you so much Clyde Schechter