Monthly Idiosyncratic volatility and grouping variables by average value of a different variable with same ticker

[Ben Townsend]

I'm working on some analysis of herding in the FTSE 100 companies using CSAD as a measure and have come across a problem I can't seem to navigate. I want to group the return data I have calculated from getsymbols for the FTSE 100 companies based on the idiosyncratic volatility (IVOL) which I have calculated with the following code:

foreach i of varlist r* {
regress `i' marketreturn
predict temp, resid
gen l`i' = temp
drop temp
}

foreach g of varlist l* {
egen ivo`g' = sd(`g')
}

drop l*

the formula for IVOL is the SD of the residual of the regression of the individual stock return (depvar) and market return (indepvar)

this has generated a new variable (ivol (ticker symbol)) for each ticker symbol which is the IVOL value for the whole time period I'm assessing, (I'm using daily data spanning roughly 2 years) so this is an IVOL across the whole time period. I would prefer to do a monthly calculation for ivol but could not work out how to do this.

I want to use the IVOL values to group the return variables (r (ticker symbol)) in three groups high IVOL med IVOL and low IVOL so I can run three separate regressions but as they are all individual variables I cannot use sort.
This is some example data of just three of the 100 ticker symbols both return on each and IVOL, and then market return and CSAD. market return and CSAD is irrelevant for this issue but are useful for context.

input float(rAAL_L rABF_L rADM_L ivolrAAL_L ivolrABF_L ivolrADM_L marketreturn CSAD)
-.012693928 .0028985536 .008987719 .016522266 .017822612 .013433126 .0019799867 .007097834
.005102094 -.005367514 .01031409 .016522266 .017822612 .013433126 .02037216 .00869989
-.000710697 -.01203816 -.0041762795 .016522266 .017822612 .013433126 -.0005990755 .008090226
-.009956293 -.016386602 -.003261873 .016522266 .017822612 .013433126 -.011083074 .011534536
-.007285748 -.017513841 .0004674552 .016522266 .017822612 .013433126 -.003157762 .00826433
-.011474112 .005217381 -.005140207 .016522266 .017822612 .013433126 .008188129 .008555441
-.005123835 -.0021626167 .0004697114 .016522266 .017822612 .013433126 .00056108064 .007501436
.020390896 -.0013003607 .019718375 .016522266 .017822612 .013433126 .008089009 .0088896025
-.009064644 -.01909726 -.01151019 .016522266 .017822612 .013433126 .00086224 .00933866
.01413716 .002654917 -.0013973204 .016522266 .017822612 .013433126 .005516214 .009386916
-.00471505 .022947917 -.006063407 .016522266 .017822612 .013433126 -.002376801 .008638217
.006488153 .017256258 .01032371 .016522266 .017822612 .013433126 -.005582077 .008813342
.033459596 -.01229863 .01439862 .016522266 .017822612 .013433126 .006880976 .01200508

[Clyde Schechter]

If you don't even have a date variable, it is clearly impossible to do what you seek.

If you do have a date variable, please post back with example data that includes it. Also don't show the data with the variables you have already derived. Show the original return variables that you used (including the market return) along with the date variable. And use real -dataex-: don't try to emulate it as in #1. This will be particularly important with respect to your date variable and handling it correctly. If you are running version 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.

[Ben Townsend]

Hi Clyde,

Apologies for not including my time variable, period is the one imported using getsymbols, and timevar is the reformatted one to exclude gaps and the timevar used for tset. This is actual data obtained from dataex.

period timevar rAAL_L rABF_L rADM_L ivolrAAL_L ivolrABF_L ivolrADM_L marketreturn
21584   2  -.012693928  .0028985536    .008987719 .016522266 .017822612 .013433126    .0019799867
21585   3   .005102094  -.005367514     .01031409 .016522266 .017822612 .013433126      .02037216
21586   4  -.000710697   -.01203816  -.0041762795 .016522266 .017822612 .013433126   -.0005990755
21587   5  -.009956293  -.016386602   -.003261873 .016522266 .017822612 .013433126    -.011083074
21588   6  -.007285748  -.017513841   .0004674552 .016522266 .017822612 .013433126    -.003157762
21591   7  -.011474112   .005217381   -.005140207 .016522266 .017822612 .013433126     .008188129
21592   8  -.005123835 -.0021626167   .0004697114 .016522266 .017822612 .013433126   .00056108064
21593   9   .020390896 -.0013003607    .019718375 .016522266 .017822612 .013433126     .008089009
21594  10  -.009064644   -.01909726    -.01151019 .016522266 .017822612 .013433126      .00086224
21595  11    .01413716   .002654917  -.0013973204 .016522266 .017822612 .013433126     .005516214
21598  12   -.00471505   .022947917   -.006063407 .016522266 .017822612 .013433126    -.002376801
21599  13   .006488153   .017256258     .01032371 .016522266 .017822612 .013433126    -.005582077
21600  14   .033459596   -.01229863     .01439862 .016522266 .017822612 .013433126     .006880976
21601  15  -.014950513 -.0025762615   -.005952355 .016522266 .017822612 .013433126   -.0084663965

[Clyde Schechter]

I asked you to use -dataex- to show your example. You did not do that. It will take me longer to clean-up the tableau you showed in #3 and import it into Stata than to actually develop and test the code.

Here is the general approach in words: perhaps you will be able to work it out for yourself.

The first step wlil be to reshape the data to long, so that each different ticker symbol gets its own observation in each time period.

I'm guessing your variable period is actually a Stata internal format numeric date variable. So, for example 21584 is actually 04feb2019. So the first thing is to create a month variable. The -mofd()- function will extract the month from period.

Then looping over levels of the month variable, you can regress the observed return on the market return, and then use the -egen sd()- function to calculate that volatility monthly.

If you wish, I can fill in all these details for you, but only if you post back with a usable example from -dataex-.

[Ben Townsend]

Apologies, again I thought I had done it correctly, I didn't realise the full example was required, hopefully this is what you require.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(period timevar rAAL_L rABF_L rADM_L ivolrAAL_L ivolrABF_L ivolrADM_L marketreturn)
21584   2  -.012693928  .0028985536    .008987719 .016522266 .017822612 .013433126    .0019799867
21585   3   .005102094  -.005367514     .01031409 .016522266 .017822612 .013433126      .02037216
21586   4  -.000710697   -.01203816  -.0041762795 .016522266 .017822612 .013433126   -.0005990755
21587   5  -.009956293  -.016386602   -.003261873 .016522266 .017822612 .013433126    -.011083074
21588   6  -.007285748  -.017513841   .0004674552 .016522266 .017822612 .013433126    -.003157762
21591   7  -.011474112   .005217381   -.005140207 .016522266 .017822612 .013433126     .008188129
21592   8  -.005123835 -.0021626167   .0004697114 .016522266 .017822612 .013433126   .00056108064
21593   9   .020390896 -.0013003607    .019718375 .016522266 .017822612 .013433126     .008089009
21594  10  -.009064644   -.01909726    -.01151019 .016522266 .017822612 .013433126      .00086224
21595  11    .01413716   .002654917  -.0013973204 .016522266 .017822612 .013433126     .005516214
21598  12   -.00471505   .022947917   -.006063407 .016522266 .017822612 .013433126    -.002376801
21599  13   .006488153   .017256258     .01032371 .016522266 .017822612 .013433126    -.005582077
21600  14   .033459596   -.01229863     .01439862 .016522266 .017822612 .013433126     .006880976
21601  15  -.014950513 -.0025762615   -.005952355 .016522266 .017822612 .013433126   -.0084663965
21602  16   .033772264 -.0043046456    .006448742 .016522266 .017822612 .013433126     .001562658
21605  17   -.00875062   -.01729356     .00091511 .016522266 .017822612 .013433126    .0007104586
21606  18   .006130422   .020237513   -.002286037 .016522266 .017822612 .013433126    -.004538065
21607  19   -.01048011  -.027598087   -.005958047 .016522266 .017822612 .013433126    -.006138902
21608  20   -.01305412  -.005321498    .005993758 .016522266 .017822612 .013433126   -.0045728274
21609  21    -.0017471  .0022291439    .009165809 .016522266 .017822612 .013433126      .00452316
21612  22  .0030001376   .004893275   -.004087374 .016522266 .017822612 .013433126     .003897689
21613  23   .005981996  .0035413464   -.000455886 .016522266 .017822612 .013433126     .006868132
21614  24    .01065415  .0088221785  -.0004560939 .016522266 .017822612 .013433126    .0017540577
21615  25  -.003187082  -.009619553    -.04701058 .016522266 .017822612 .013433126    -.005336284
21616  26  -.022626737   -.01103757     .02490423 .016522266 .017822612 .013433126    -.007446671
21619  27   .008304123  .0031250035    -.01869152 .016522266 .017822612 .013433126     .003702025
21620  28 -.0031445944   .005340555    .015714223 .016522266 .017822612 .013433126     .002888971
21621  29   .009663548  -.006640066   .0042194147 .016522266 .017822612 .013433126    .0011186934
21622  30  -.017414186    .01470577    .011204355 .016522266 .017822612 .013433126     .003659586
21623  31 -.0021612877   .004830954    .009695324 .016522266 .017822612 .013433126     .005970426
21626  32   .020732166   .010926615   -.005486937 .016522266 .017822612 .013433126     .009808723
21627  33   .013288326   .009943713    .002758664 .016522266 .017822612 .013433126     .003397606
21628  34  -.006133178   -.01155817    -.01604764 .016522266 .017822612 .013433126   -.0045057344
21629  35   .012693166   .029016843    .006523747 .016522266 .017822612 .013433126     .008819065
21630  36  -.027892066   -.01725585   -.008333344 .016522266 .017822612 .013433126     -.02008072
21633  37  -.002649979  -.008993586    .002334243 .016522266 .017822612 .013433126    -.004162273
21634  38   .018702095   .024200436   -.004191901 .016522266 .017822612 .013433126     .002605287
21635  39  -.005718335   .010970566 -.00046773415 .016522266 .017822612 .013433126  -.00029176235
21636  40   .005044902   .003338857    .007019175 .016522266 .017822612 .013433126      .00557388
21637  41    .03076994   .014559084    .008364263 .016522266 .017822612 .013433126     .006206598
21640  42    .02337489   -.00492004  -.0009215547 .016522266 .017822612 .013433126     .005247789
21641  43   .016654652  .0024722344     .01476014 .016522266 .017822612 .013433126      .01007191
21642  44   .013339568   .008631226    .012272684 .016522266 .017822612 .013433126     .003680062
21643  45  -.012702025    .00937258    .002245208 .016522266 .017822612 .013433126    -.002210736
21644  46    .02198823   .005248131   .0031362495 .016522266 .017822612 .013433126      .00607952
21647  47   .010070876  .0004016639   -.006699408 .016522266 .017822612 .013433126    .0006714203
21648  48  .0033992275  -.005620139   -.007644004 .016522266 .017822612 .013433126    -.003529275
21649  49   .005419915   .014937352    .007702884 .016522266 .017822612 .013433126   -.0004983025
21650  50  -.014824712 -.0011932903             0 .016522266 .017822612 .013433126   -.0005254588
21651  51   .007523955   .006371885    .006744593 .016522266 .017822612 .013433126     .002574831
21654  52   -.02014035   .005540271    .003126329 .016522266 .017822612 .013433126 -.000026918464
21655  53   .005080942  -.004722639    .015583273 .016522266 .017822612 .013433126     .004437333
21656  54  -.006663641   -.01067621   -.017536215 .016522266 .017822612 .013433126   .00018740576
21657  55  .0037010384   .003996878   -.003569776 .016522266 .017822612 .013433126   -.0015258258
21662  56 -.0020741876  -.002388481    .004030515 .016522266 .017822612 .013433126      .00847199
21663  57   -.03902996   .020351093    .008920515 .016522266 .017822612 .013433126    -.006819036
21664  58   -.02235048  -.014861056   -.008841643 .016522266 .017822612 .013433126      -.0050456
21665  59  -.012045148     .0186581   -.009812704 .016522266 .017822612 .013433126    -.000793627
21668  60  .0014928704            0   -.006756746 .016522266 .017822612 .013433126    .0016827764
21669  61   -.01545339  -.002727984  -.0004536387 .016522266 .017822612 .013433126    -.003023909
21670  62 -.0041385395  -.007033936   .0018151443 .016522266 .017822612 .013433126    -.004435091
21671  63   -.01905533 -.0003936022   -.009511133 .016522266 .017822612 .013433126     -.00460374
21672  64    .02335197   .004330643  -.0036579184 .016522266 .017822612 .013433126    .0039857295
21676  65   -.01878036  -.007055995   -.017898101 .016522266 .017822612 .013433126    -.016272403
21677  66   -.00535088 -.0027635244   -.003738316 .016522266 .017822612 .013433126    .0014461814
21678  67  -.017897746  -.008709389    -.03431648 .016522266 .017822612 .013433126    -.008747091
21679  68  -.003054914   .002396112    .006852701 .016522266 .017822612 .013433126  -.00056887337
21682  69   -.01521555  -.008764907   -.014098203 .016522266 .017822612 .013433126    -.005497426
21683  70    .02478536    .01125403    .015779087 .016522266 .017822612 .013433126     .010874255
21684  71  .0019893788   .007949176  -.0038834785 .016522266 .017822612 .013433126      .00765023
21685  72    .03030307   .009069325     .03313832 .016522266 .017822612 .013433126     .007742908
21686  73  -.003854002   .004689323   -.007075442 .016522266 .017822612 .013433126   -.0006663361
21689  74   -.01588273  -.017502908    -.01947741 .016522266 .017822612 .013433126    -.005130256
21690  75   .004862401   .011480572 -.00048449406 .016522266 .017822612 .013433126     .002462077
21691  76   -.01410477  -.005870806   -.006301538 .016522266 .017822612 .013433126    .0007232045
21692  77  -.007414343  -.018503942   -.008780451 .016522266 .017822612 .013433126     -.01407109
21693  78    .02261964   .006819072    .008366106 .016522266 .017822612 .013433126     .006458331
21697  79  .0043209977  .0003984634    .021473985 .016522266 .017822612 .013433126   -.0011954594
21698  80   -.02315103   -.01354038   -.021022547 .016522266 .017822612 .013433126    -.011514678
21699  81   .014052036   .004037117     .01122494 .016522266 .017822612 .013433126    .0045788474
21700  82  -.020165415   -.00763982   -.004826235 .016522266 .017822612 .013433126    -.007827436
21703  83   .012981487   .012966007    .001939918 .016522266 .017822612 .013433126    .0032254364
21704  84   .008230915   .004400033   .0043562255 .016522266 .017822612 .013433126      .00410589
21705  85 -.0080602905   .025487836     .00578317 .016522266 .017822612 .013433126    .0008178743
21706  86   .010417756  -.018708937  -.0004791546 .016522266 .017822612 .013433126     .005498422
21707  87   .017527532  .0035785346   .0019175373 .016522266 .017822612 .013433126     .009917492
21710  88    .01808693  -.000792427   -.000478528 .016522266 .017822612 .013433126      .00594663
21711  89   .024384134  -.005947572  -.0033508714 .016522266 .017822612 .013433126    .0031184326
21712  90   .006315243  .0011966513    .001440916 .016522266 .017822612 .013433126    -.004176509
21713  91    .01930969  -.013545903             0 .016522266 .017822612 .013433126   .00013572941
21714  92 -.0004735231  -.005250379     -.0033573 .016522266 .017822612 .013433126     -.00309425
21717  93 -.0009476801  -.016240349     .00818097 .016522266 .017822612 .013433126    .0015655204
21718  94    .02679626   .009079634  -.0033413356 .016522266 .017822612 .013433126     .011648322
21719  95  -.020092376  -.014723849  -.0014367756 .016522266 .017822612 .013433126       -.005307
21720  96   .008248884   .007471889    .006235047 .016522266 .017822612 .013433126     .002822976
21721  97   .009350169   .005768482    .006673054 .016522266 .017822612 .013433126    -.002276265
21724  98   .009263612 -.0090126805     .03740532 .016522266 .017822612 .013433126     .001242011
21725  99  .0020651445  -.001240184    .001369232 .016522266 .017822612 .013433126    .0007684964
21726 100   .005495802   .004552919  -.0009116897 .016522266 .017822612 .013433126   -.0008083639
21727 101   .006604338   .018129442    -.00547443 .016522266 .017822612 .013433126    -.001901205
end
format %td period
label var period "Day"

[Clyde Schechter]

So, I've stripped the ivo variables out of your example and started from scratch. Here is the code to create monthly volatility calculations for each firm:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(period timevar rAAL_L rABF_L rADM_L marketreturn)
21584   2  -.012693928  .0028985536    .008987719    .0019799867
21585   3   .005102094  -.005367514     .01031409      .02037216
21586   4  -.000710697   -.01203816  -.0041762795   -.0005990755
21587   5  -.009956293  -.016386602   -.003261873    -.011083074
21588   6  -.007285748  -.017513841   .0004674552    -.003157762
21591   7  -.011474112   .005217381   -.005140207     .008188129
21592   8  -.005123835 -.0021626167   .0004697114   .00056108064
21593   9   .020390896 -.0013003607    .019718375     .008089009
21594  10  -.009064644   -.01909726    -.01151019      .00086224
21595  11    .01413716   .002654917  -.0013973204     .005516214
21598  12   -.00471505   .022947917   -.006063407    -.002376801
21599  13   .006488153   .017256258     .01032371    -.005582077
21600  14   .033459596   -.01229863     .01439862     .006880976
21601  15  -.014950513 -.0025762615   -.005952355   -.0084663965
21602  16   .033772264 -.0043046456    .006448742     .001562658
21605  17   -.00875062   -.01729356     .00091511    .0007104586
21606  18   .006130422   .020237513   -.002286037    -.004538065
21607  19   -.01048011  -.027598087   -.005958047    -.006138902
21608  20   -.01305412  -.005321498    .005993758   -.0045728274
21609  21    -.0017471  .0022291439    .009165809      .00452316
21612  22  .0030001376   .004893275   -.004087374     .003897689
21613  23   .005981996  .0035413464   -.000455886     .006868132
21614  24    .01065415  .0088221785  -.0004560939    .0017540577
21615  25  -.003187082  -.009619553    -.04701058    -.005336284
21616  26  -.022626737   -.01103757     .02490423    -.007446671
21619  27   .008304123  .0031250035    -.01869152     .003702025
21620  28 -.0031445944   .005340555    .015714223     .002888971
21621  29   .009663548  -.006640066   .0042194147    .0011186934
21622  30  -.017414186    .01470577    .011204355     .003659586
21623  31 -.0021612877   .004830954    .009695324     .005970426
21626  32   .020732166   .010926615   -.005486937     .009808723
21627  33   .013288326   .009943713    .002758664     .003397606
21628  34  -.006133178   -.01155817    -.01604764   -.0045057344
21629  35   .012693166   .029016843    .006523747     .008819065
21630  36  -.027892066   -.01725585   -.008333344     -.02008072
21633  37  -.002649979  -.008993586    .002334243    -.004162273
21634  38   .018702095   .024200436   -.004191901     .002605287
21635  39  -.005718335   .010970566 -.00046773415  -.00029176235
21636  40   .005044902   .003338857    .007019175      .00557388
21637  41    .03076994   .014559084    .008364263     .006206598
21640  42    .02337489   -.00492004  -.0009215547     .005247789
21641  43   .016654652  .0024722344     .01476014      .01007191
21642  44   .013339568   .008631226    .012272684     .003680062
21643  45  -.012702025    .00937258    .002245208    -.002210736
21644  46    .02198823   .005248131   .0031362495      .00607952
21647  47   .010070876  .0004016639   -.006699408    .0006714203
21648  48  .0033992275  -.005620139   -.007644004    -.003529275
21649  49   .005419915   .014937352    .007702884   -.0004983025
21650  50  -.014824712 -.0011932903             0   -.0005254588
21651  51   .007523955   .006371885    .006744593     .002574831
21654  52   -.02014035   .005540271    .003126329 -.000026918464
21655  53   .005080942  -.004722639    .015583273     .004437333
21656  54  -.006663641   -.01067621   -.017536215   .00018740576
21657  55  .0037010384   .003996878   -.003569776   -.0015258258
21662  56 -.0020741876  -.002388481    .004030515      .00847199
21663  57   -.03902996   .020351093    .008920515    -.006819036
21664  58   -.02235048  -.014861056   -.008841643      -.0050456
21665  59  -.012045148     .0186581   -.009812704    -.000793627
21668  60  .0014928704            0   -.006756746    .0016827764
21669  61   -.01545339  -.002727984  -.0004536387    -.003023909
21670  62 -.0041385395  -.007033936   .0018151443    -.004435091
21671  63   -.01905533 -.0003936022   -.009511133     -.00460374
21672  64    .02335197   .004330643  -.0036579184    .0039857295
21676  65   -.01878036  -.007055995   -.017898101    -.016272403
21677  66   -.00535088 -.0027635244   -.003738316    .0014461814
21678  67  -.017897746  -.008709389    -.03431648    -.008747091
21679  68  -.003054914   .002396112    .006852701  -.00056887337
21682  69   -.01521555  -.008764907   -.014098203    -.005497426
21683  70    .02478536    .01125403    .015779087     .010874255
21684  71  .0019893788   .007949176  -.0038834785      .00765023
21685  72    .03030307   .009069325     .03313832     .007742908
21686  73  -.003854002   .004689323   -.007075442   -.0006663361
21689  74   -.01588273  -.017502908    -.01947741    -.005130256
21690  75   .004862401   .011480572 -.00048449406     .002462077
21691  76   -.01410477  -.005870806   -.006301538    .0007232045
21692  77  -.007414343  -.018503942   -.008780451     -.01407109
21693  78    .02261964   .006819072    .008366106     .006458331
21697  79  .0043209977  .0003984634    .021473985   -.0011954594
21698  80   -.02315103   -.01354038   -.021022547    -.011514678
21699  81   .014052036   .004037117     .01122494    .0045788474
21700  82  -.020165415   -.00763982   -.004826235    -.007827436
21703  83   .012981487   .012966007    .001939918    .0032254364
21704  84   .008230915   .004400033   .0043562255      .00410589
21705  85 -.0080602905   .025487836     .00578317    .0008178743
21706  86   .010417756  -.018708937  -.0004791546     .005498422
21707  87   .017527532  .0035785346   .0019175373     .009917492
21710  88    .01808693  -.000792427   -.000478528      .00594663
21711  89   .024384134  -.005947572  -.0033508714    .0031184326
21712  90   .006315243  .0011966513    .001440916    -.004176509
21713  91    .01930969  -.013545903             0   .00013572941
21714  92 -.0004735231  -.005250379     -.0033573     -.00309425
21717  93 -.0009476801  -.016240349     .00818097    .0015655204
21718  94    .02679626   .009079634  -.0033413356     .011648322
21719  95  -.020092376  -.014723849  -.0014367756       -.005307
21720  96   .008248884   .007471889    .006235047     .002822976
21721  97    .00935017   .005768482    .006673054    -.002276265
21724  98   .009263612 -.0090126805     .03740532     .001242011
21725  99  .0020651445  -.001240184    .001369232    .0007684964
21726 100   .005495802   .004552919  -.0009116897   -.0008083639
21727 101   .006604338   .018129442    -.00547443    -.001901205
end
format %td period


reshape long r, i(period) j(ticker) string
gen int mdate = mofd(period)
format mdate %tm

capture program drop one_ticker_month
program define one_ticker_month
    regress r marketreturn
    predict resid, resid
    summ resid
    gen ivo = r(sd)
    exit
end

runby one_ticker_month, by(ticker mdate) status

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

I don't quite understand the part about grouping into high, medium, and low volatility, but with ivo in a single, sortable variable, my guess is you can take it from here. If not, please provide a more detailed explanation of what you want to do.

[Ben Townsend]

This works properly, Thank you very much for your help!