Dependent Double-Sorting 25 Portfolios

[Sartaj Hussain]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float s_id str52 stock float mdate byte time float(rt mcap idiovol beta size bmratio mom rev illiq coskew idioskew)
1 "3M India Ltd."              654  1   .086848   5078.8 .0740308 .28576872  8.53283 .1427253  .2371685  .1603841  .018356  -7.991834  .3408433
1 "3M India Ltd."              655  2  .2003571  5519.88 .0948782 .41061905 8.616112 .1313204   .418756   .033574 .3336723 -10.509234  .5163485
1 "3M India Ltd."              656  3  .0796447  6625.83 .1138512  .5519853 8.798731 .1094011  .4740936   .086848 .0130151 -15.409613  1.363466
1 "3M India Ltd."              657  4 -.0153538  7153.54 .0582864  .6276699 8.875362 .1047699  .5769172  .2003571 .0112899  -24.02697  1.437632
1 "3M India Ltd."              658  5  .0044701  7043.71 .0575414  .7145491  8.85989 .1064036  .6563837  .0796447 .0275447 -16.657455   1.42974
1 "3M India Ltd."              659  6  .0302676   7075.2 .0598738  .7707288 8.864351 .1059301  .5437807 -.0153538 .0077173 -14.407308  1.455296
1 "3M India Ltd."              660  7   .049886  7289.35 .0783344  .8217717  8.89417 .1051022  .5559843  .0044701 .0147622 -13.308914  1.328962
1 "3M India Ltd."              661  8  .1655173  7652.98 .0525226  .8991754  8.94285 .1001082  .5616655  .0302676 .0107533  -14.69247  1.328398
1 "3M India Ltd."              662  9  .0064347  8919.68 .0841407  .9966476 9.096015 .0858916  .6546758   .049886 .0075832 -18.494099  1.347207
1 "3M India Ltd."              663 10 -.0020643  8977.08 .0745462 1.0060159  9.10243 .0899742  .7449591  .1655173  .015277  -16.20922   1.26751
1 "3M India Ltd."              664 11  .0058158  8958.55 .1183632 1.1105146 9.100364 .0901603  .6026221  .0064347 .0103327 -18.009785  1.210737
1 "3M India Ltd."              665 12  .0387623  9010.65 .0538988 1.1562696 9.106162  .089639   .567533 -.0020643 .0182174 -11.104167  1.234542
2 "63 Moons Technologies Ltd." 654  1  .2660587   1162.1 .0603534 1.6175942 7.057984 2.183743  -.723613  -.168171 .0003871  -7.866135   -2.4911
2 "63 Moons Technologies Ltd." 655  2 -.2038835  1471.29 .2044508  1.722967 7.293895 1.724836  .7935855 -.0397868 .0005179  -8.012454 -2.456248
2 "63 Moons Technologies Ltd." 656  3  -.120771  1171.32   .15993 1.4639108 7.065886 2.166562  .8166623  .2660587 .0004231 -11.134826  1.681997
2 "63 Moons Technologies Ltd." 657  4 -.2187919  1029.86 .1130937  1.400238 6.937178 2.782103  .3830972 -.2038835 .0006017  -42.34183   1.40249
2 "63 Moons Technologies Ltd." 658  5  .0492554   804.53 .2397872 1.2259215 6.690258 3.561283  .2932156  -.120771 .0007087  -39.92591  .8132252
2 "63 Moons Technologies Ltd." 659  6  .1080786   844.16 .0932977   .956523 6.738342 3.394105  -.060018 -.2187919  .000574  -61.85197  .5251374
2 "63 Moons Technologies Ltd." 660  7 -.0073892   935.39 .1563368 1.1401676 6.840963 3.057882 -.3990765  .0492554 .0005541  -65.79335  .5179105
2 "63 Moons Technologies Ltd." 661  8 -.1131514   928.48 .0363065  1.001254 6.833549 3.080645 -.4947864  .1080786 .0004334  -44.20967 -.0347651
2 "63 Moons Technologies Ltd." 662  9  .0730274   823.42 .0857855 1.0637394 6.713466 3.473699 -.6167269 -.0073892 .0006919  -41.85156 -.0151643
2 "63 Moons Technologies Ltd." 663 10 -.1016949   883.56 .2393835 1.0984282 6.783959 3.146336 -.5692424 -.1131514 .0006494 -37.778828  .6636997
2 "63 Moons Technologies Ltd." 664 11 -.0557329    793.7 .0728837 1.1487825 6.676705 3.502525 -.3146299  .0730274 .0008165  -41.43835  .6515598
2 "63 Moons Technologies Ltd." 665 12 -.0624039   749.47 .0375842 1.3541955 6.619366 3.709253 -.3812755 -.1016949 .0010716 -18.114159  .7005147
3 "A B B India Ltd."           654  1  -.072323 23777.18   .07301 1.1671702 10.07648 .1167862  .5906103  .0760307  .000912  18.956474  1.837977
3 "A B B India Ltd."           655  2 -.0559612 22057.54 .0720003    1.1953 10.00141 .1258911  .9138873  .2407254 .0010026  15.236788  1.706302
3 "A B B India Ltd."           656  3  .1675571 20823.18 .0388558 1.2993245 9.943822 .1333537  .6490756  -.072323  .002041   17.29581   1.75024
3 "A B B India Ltd."           657  4  .0169528 24312.25 .1249597 1.2121063 10.09874 .1160725   .446917 -.0559612 .0007801 -3.1454756  1.606238
3 "A B B India Ltd."           658  5 -.0479537 24724.41  .069379 1.2320365 10.11555 .1141376  .5331985  .1675571 .0022556  -5.853405  1.587443
3 "A B B India Ltd."           659  6  .1579042 23538.78  .066181 1.2895766  10.0664 .1198866   .521236  .0169528 .0014126  -2.469619  1.566551
3 "A B B India Ltd."           660  7 -.0010885 27255.66 .1629557  1.247352 10.21302 .1031721  .6505838 -.0479537 .0008899  .17122038  1.998291
3 "A B B India Ltd."           661  8  .0859278 27225.99 .0482678 1.1598969 10.21193 .1032846  .5854182  .1579042 .0007821   4.921515  1.947242
3 "A B B India Ltd."           662  9 -.0990539 29565.46 .1039571  1.085881 10.29436 .0951118  .4131808 -.0010885 .0008295  4.2670894  1.680357
3 "A B B India Ltd."           663 10  .0596261 26636.88 .0585043 1.0303168 10.19005 .1076054  .5068556  .0859278  .001012    8.98696  1.718861
3 "A B B India Ltd."           664 11  .0060438 28225.14 .0597199 1.0735446 10.24797 .1015504  .3292668 -.0990539 .0011505   6.628744  1.671148
3 "A B B India Ltd."           665 12 -.0128731 28395.72 .0661221  .9750184 10.25399 .1009403  .1714867  .0596261 .0010219  -1.777482  1.719543
4 "A B G Shipyard Ltd."        654  1 -.0972099  1432.68 .0947438  .6082916 7.267302 .8887507  .0576688  .2618214  .002023  -6.823472  2.965098
4 "A B G Shipyard Ltd."        655  2 -.0690945  1293.41 .0549549  .7447889 7.165038 .9844488 -.0153429 -.0237682 .0051123  -8.006575  2.967876
4 "A B G Shipyard Ltd."        656  3 -.0471558  1204.05 .1168856  .9582399 7.093446 1.057517 -.2995211 -.0972099 .0034588  -27.66782  2.982876
4 "A B G Shipyard Ltd."        657  4  .0095428  1147.27 .1267581  1.156727  7.04514 .9592099 -.2120439 -.0690945 .0018091  -33.08691  2.873167
4 "A B G Shipyard Ltd."        658  5 -.0118707  1188.16  .065058 1.2086453 7.080161 .9567817 -.1728076 -.0471558 .0016282 -34.662495   2.87232
4 "A B G Shipyard Ltd."        659  6  .0260289  1174.06  .096084 1.3125815 7.068223 .9682758  -.189477  .0095428 .0020928 -36.056618   2.86358
4 "A B G Shipyard Ltd."        660  7 -.1151344  1234.19 .0637892 1.2926178  7.11817 .7111882 -.2800893 -.0118707 .0017641  -29.46725  2.825309
4 "A B G Shipyard Ltd."        661  8  .1573144  1092.09 .0272032   1.12907 6.995849 .8037246 -.1224939  .0260289 .0023388 -15.021177  2.805774
4 "A B G Shipyard Ltd."        662  9 -.0398052  1269.39  .263799  .9645222 7.146292 .6965064 -.2264217 -.1151344 .0023097  -20.28839   2.24346
4 "A B G Shipyard Ltd."        663 10 -.0976847  1220.81 .2147563 1.0716174  7.10727 .4151709  .0333687  .1573144 .0015214 -18.431244    2.0633
4 "A B G Shipyard Ltd."        664 11 -.3022972  1101.56  .077692  1.093864 7.004483 .4601173 -.2398066 -.0398052 .0017944 -18.695293  2.049402
4 "A B G Shipyard Ltd."        665 12  .2980736   769.47 .1198758  .9517041 6.645702 .6610157 -.3185426 -.0976847 .0022093 -12.905464  1.416851
5 "A C C Ltd."                 654  1 -.0501005 27561.96 .0567216 1.0900002 10.22419 .3071012  .1341395  .0370055 .0003227   4.937481  .2280212
5 "A C C Ltd."                 655  2  .0697024 26181.09 .0603539  1.139813 10.17279 .3232987  .4228837  .0959686 .0005682    6.30066  .2320677
5 "A C C Ltd."                 656  3 -.0586914 28005.97 .0583056 1.1897534 10.24017 .3022324  .2259711 -.0501005 .0004551  10.089437  .4942137
5 "A C C Ltd."                 657  4  .0689029 26362.26 .0497101 1.3074735 10.17969 .3288538  .2717871  .0697024 .0003581   12.75665  .4889652
5 "A C C Ltd."                 658  5 -.0203545  28178.7 .0399468 1.3060973 10.24632 .3076554  .2472633 -.0586914 .0005178  13.973518  .4391165
5 "A C C Ltd."                 659  6 -.0482538 27605.14 .0335001 1.3118976 10.22576 .3140477  .3033279  .0689029 .0003528  16.681864  .4593353
5 "A C C Ltd."                 660  7  .1147992 26273.09 .0399086  1.246978  10.1763 .3134558  .3747089 -.0203545 .0001953  12.870836  .4904843
5 "A C C Ltd."                 661  8  .0789718 29289.21 .0526107 1.2442046 10.28497 .2811769  .2376019 -.0482538 .0003993   6.702893  .5534865
5 "A C C Ltd."                 662  9  -.072865 31602.24 .0587099  1.196225 10.36098 .2605971  .1142996  .1147992 .0002823   7.460404  .3927228
5 "A C C Ltd."                 663 10  -.078111 29299.54 .0572899 1.1195886 10.28533 .2892862  .2647673  .0789718 .0003956   8.657016  .5658225
5 "A C C Ltd."                 664 11  .0527212 27010.92 .0853668  1.119679   10.204 .3137972  .1527739  -.072865 .0005578   12.48231  .5528097
5 "A C C Ltd."                 665 12 -.0487934 28434.97 .0454032  1.103854 10.25537 .2980819 -.0201951  -.078111 .0004452   6.504608  .6094587
6 "A I A Engineering Ltd."     654  1  .0056017  7324.45 .0764178  .5426878 8.898973 .1863241  .9147949  .2457522 .0020668   -9.08077  1.152484
6 "A I A Engineering Ltd."     655  2  .1254962  7365.48 .0810239  .5429146 8.904559 .1852862   .881708  .0333333 .0021618  -6.583352  1.101441
6 "A I A Engineering Ltd."     656  3  .0709409  8289.82 .0827517 .52390045 9.022783 .1646262  .9043885  .0056017 .0017781 -19.995876  1.098817
6 "A I A Engineering Ltd."     657  4 -.0012218   8877.9 .0922927   .595527  9.09132 .1666295  .8154774  .1254962 .0021369  -20.65523  .9876203
6 "A I A Engineering Ltd."     658  5  .2806084  8867.06 .0628295  .5425695 9.090098 .1668333  .8104124  .0709409 .0043089  -26.68434  1.060474
6 "A I A Engineering Ltd."     659  6 -.0994269 11355.23 .1055368    .45518 9.337434 .1302766  .6773174 -.0012218 .0019632 -26.657295  .9551212
6 "A I A Engineering Ltd."     660  7  .0481922 10226.21 .0903679 .52706116 9.232709  .153634  .8538257  .2806084 .0032034 -23.433657  .8660261
6 "A I A Engineering Ltd."     661  8 -.0042237 10719.04 .1141845  .5125227 9.279777 .1465705  .6475331 -.0994269 .0016639 -24.029285  .8361357
6 "A I A Engineering Ltd."     662  9  .1008704 10673.76 .0728799 .56981456 9.275543 .1471922  .7140873  .0481922 .0017266  -22.26089  .8239583
6 "A I A Engineering Ltd."     663 10  -.076176 11750.43 .0953728  .4743832 9.371645 .1405603  .6291003 -.0042237 .0015918  -18.96955   .698714
6 "A I A Engineering Ltd."     664 11 -.1156052 10855.33 .1057325 .55625314 9.292412 .1521505   .505462  .1008704 .0032665 -19.964684  .7588429
6 "A I A Engineering Ltd."     665 12 -.0170949   9600.4 .0962553  .8524997  9.16956 .1720391  .3934385  -.076176  .002661  -6.487801  .4675851
7 "Aarti Drugs Ltd."           654  1  .1788753   640.36 .1204179  .7882901  6.46203 .4187199  .6888735  .1285971 .0518204 -28.271824  1.112205
7 "Aarti Drugs Ltd."           655  2  .1933404   754.91 .0823769  .7267371 6.626598 .3551849  1.029294  .3169323 .0199357  -28.77351  1.095358
7 "Aarti Drugs Ltd."           656  3  .0369086   900.88   .16378  .6621826 6.803372 .2976344   1.26571  .1787929 .0091056   -40.3316   1.48504
7 "Aarti Drugs Ltd."           657  4 -.0640604   934.11 .0816029  .8686052 6.839594   .30711  1.366281  .1933787 .0110806  -63.09448  1.636039
7 "Aarti Drugs Ltd."           658  5  .1057005    874.3 .0589362  .8594756 6.773424 .3281213  1.383317  .0368817 .0253287  -62.46744   1.60655
7 "Aarti Drugs Ltd."           659  6  .0678641   966.69 .0703788  .9039111 6.873878 .2967621  1.167314 -.0639842 .0148601  -61.93893  1.608199
7 "Aarti Drugs Ltd."           660  7  .4082624  1032.31 .0854154  .9756119 6.939554 .2999355  1.270646  .1055839 .0178361  -54.75111  1.625931
7 "Aarti Drugs Ltd."           661  8  .0469765  1453.75 .1071695 1.0120056 7.281902 .2129852  1.198911  .0679427 .0057835  -39.94343  1.626559
7 "Aarti Drugs Ltd."           662  9  .0369928  1522.04 .1444659  1.044934 7.327807 .2034288  1.516903  .4081824 .0093696  -41.62393  1.493404
7 "Aarti Drugs Ltd."           663 10  .0115075  1578.35 .1452737 1.2262063 7.364135 .1924204  1.261946  .0470447 .0070981   -29.8683  1.290187
7 "Aarti Drugs Ltd."           664 11  .0125142  1596.51  .208062  1.281799 7.375575 .1902313  1.177352  .0369758  .002157 -33.828568  1.957995
7 "Aarti Drugs Ltd."           665 12 -.0544569  1616.49 .1346303  1.578162 7.388012 .1878801  .9135262  .0114766 .0032829  -3.106104  1.729012
8 "Aarti Industries Ltd."      654  1  .0576734  1803.28 .1141759  .5789863 7.497363 .4547777  .7322689  .2317073 .0090809  -7.359739  .9831751
8 "Aarti Industries Ltd."      655  2  .2984672  1907.38  .067956  .7596719 7.553486 .4299582  1.108288  .2596535 .0089251 -11.055405  .8533925
8 "Aarti Industries Ltd."      656  3  .0025039  2476.58 .1108625  .8943537 7.814634 .3311393   1.13561  .0576734 .0041557 -18.086958  .9924058
8 "Aarti Industries Ltd."      657  4  .0811389  2482.78 .0892635  .9601383 7.817134 .3507583  1.259475  .2984672 .0048692  -26.12984  1.033442
8 "Aarti Industries Ltd."      658  5 -.0532673  2684.33 .0980072  1.104271 7.895186 .3244224  1.209674  .0025039 .0124009 -25.398817  .9330894
8 "Aarti Industries Ltd."      659  6 -.1183853  2541.25 .0504318 1.1550949 7.840411 .3426878  1.119624  .0811389  .006466 -26.293034  .9650715
8 "Aarti Industries Ltd."      660  7  .1232108  2240.48 .0830493 1.1527193 7.714446 .4078292  1.083871 -.0532673 .0143195  -20.47767  .9809302
8 "Aarti Industries Ltd."      661  8  .0178835  2516.45 .0654694 1.1019819 7.830605 .3631051  .8512334 -.1183853 .0056784 -14.730659  .9841634
8 "Aarti Industries Ltd."      662  9  .2198243  2561.63 .1406958 1.1062407 7.848399 .3567007   .840748  .1232108 .0090189 -18.709845  .9742112
8 "Aarti Industries Ltd."      663 10 -.1109725  3124.63 .1483257  1.208637 8.047071 .2905302  .7901881  .0178835 .0043093 -1.5893823  .5568086
8 "Aarti Industries Ltd."      664 11  .0107794  2777.79 .1021029  1.296909 7.929411 .3268059  .7804937  .2198243 .0050977 -3.8938966  .5124294
8 "Aarti Industries Ltd."      665 12  .0711807  2807.91 .0960541  1.280355 7.940196 .3233002  .4320299 -.1109725 .0068195  10.653624  .4279609
9 "Aban Offshore Ltd."         654  1  .0136761  3309.21 .0908723 1.3966976 8.104465 .5820895  1.017388  .3745012 .0000797  -9.756478  2.706338
9 "Aban Offshore Ltd."         655  2  -.080755  4190.44 .1278767 1.3582484 8.340561 .6386586  1.270976  .0768958 .0000755  -.7949162  2.283722
9 "Aban Offshore Ltd."         656  3 -.1626447  4029.19 .0524127 1.3668088 8.301321 .6884843  1.279417  .0136761  .000116   2.786975  2.427754
9 "Aban Offshore Ltd."         657  4  .0515716  3373.87 .0775951  1.675677 8.123816 .8386618  1.029112  -.080755 .0001296   8.740345  2.354396
end
format %tm mdate

Dear Clyde Schechter,

First of all, let me express that I feel highly grateful to you for supporting me time to time on various issues concerning STATA .

Now, i need your help again in another problem that i had put on this forum earlier also but there was a poor response. As the subject mentions, it is about conditional double sorting and portfolio return series creation. I found a code that you have written for someone in past to do conditional bi-variate sorts. I am appending the code here with modification. Hope your code serves the purpose mentioned here very well. I want that the code should do conditional or dependent bi-variate monthly sorts of stock returns in case of my data, first on one of the following variables: mcap beta size bmratio mom rev illiq coskew idioskew and then secondly on: idiovol. For example, the first sort will be on size variable to make 5 portfolios using quintile breakpoints of size variable. Then the second sort will be within each size quintile again to create 5 portfolios in each using quintile break points of idiovol variable. The sorts should follow ascending order.Thus, this entire conditional double sorting gives rise to sorting of stocks into 25 size-idiovol based portfolios.

Finally i require 25 monthly equal weighted and value-weighted portfolio returns series for the entire data. The value-weighted series will use monthly mcap of stocks as weights. The purpose of this portfolio analysis is to study relation between expected stock returns, i.e., rt and their idiosyncratic volatility, i.e., idiovol controlling for any effects of size.

capture program drop one_fyear
program define one_fyear
gen cutoffs = .
_pctile size, nq(5)
forvalues i = 1/4 {
replace cutoffs = r(r`i') in `i'
}
display `"`cutoffs'"'
xtile size_quintile = size, cutpoints(cutoffs)
drop cutoffs
by size_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
tab *_quintile
exit
end

// IDENTIFY FISCAL YEARS FROM JUNE 1 TO MAY 31
gen fyear = year(dofm(mofd(date) + 7))

// DOUBLE SORT EACH FISCAL YEAR
runby one_fyear, by(fyear) status

The portion of this code in bold need modification, as i want sorts as well as portfolio construction on monthly. The code for creation of 25 value-weighted and equal weighted portfolio returns series is given below:

// 25 value-weighted portfolio returns series construction -

bys mdate: asgen vw_1_Size = (rt / (nq_size ==1 & idiovol_size ==1)), w(mcap)
bys mdate: asgen vw_2_Size = (rt / (nq_size ==1 & idiovol_size ==2)), w(mcap)
bys mdate: asgen vw_3_Size = (rt / (nq_size ==1 & idiovol_size ==3)), w(mcap)
bys mdate: asgen vw_4_Size = (rt / (nq_size ==1 & idiovol_size ==4)), w(mcap)
bys mdate: asgen vw_5_Size = (rt / (nq_size ==1 & idiovol_size ==5)), w(mcap)
bys mdate: asgen vw_6_Size = (rt / (nq_size ==2 & idiovol_size ==1)), w(mcap)
bys mdate: asgen vw_7_Size = (rt / (nq_size ==2 & idiovol_size ==2)), w(mcap)
bys mdate: asgen vw_8_Size = (rt / (nq_size ==2 & idiovol_size ==3)), w(mcap)
bys mdate: asgen vw_9_Size = (rt / (nq_size ==2 & idiovol_size ==4)), w(mcap)
bys mdate: asgen vw_10_Size = (rt / (nq_size ==2 & idiovol_size ==5)), w(mcap)
bys mdate: asgen vw_11_Size = (rt / (nq_size ==3 & idiovol_size ==1)), w(mcap)
bys mdate: asgen vw_12_Size = (rt / (nq_size ==3 & idiovol_size ==2)), w(mcap)
bys mdate: asgen vw_13_Size = (rt / (nq_size ==3 & idiovol_size ==3)), w(mcap)
bys mdate: asgen vw_14_Size = (rt / (nq_size ==3 & idiovol_size ==4)), w(mcap)
bys mdate: asgen vw_15_Size = (rt / (nq_size ==3 & idiovol_size ==5)), w(mcap)
bys mdate: asgen vw_16_Size = (rt / (nq_size ==4 & idiovol_size ==1)), w(mcap)
bys mdate: asgen vw_17_Size = (rt / (nq_size ==4 & idiovol_size ==2)), w(mcap)
bys mdate: asgen vw_18_Size = (rt / (nq_size ==4 & idiovol_size ==3)), w(mcap)
bys mdate: asgen vw_19_Size = (rt / (nq_size ==4 & idiovol_size ==4)), w(mcap)
bys mdate: asgen vw_20_Size = (rt / (nq_size ==4 & idiovol_size ==5)), w(mcap)
bys mdate: asgen vw_21_Size = (rt / (nq_size ==5 & idiovol_size ==1)), w(mcap)
bys mdate: asgen vw_22_Size = (rt / (nq_size ==5 & idiovol_size ==2)), w(mcap)
bys mdate: asgen vw_23_Size = (rt / (nq_size ==5 & idiovol_size ==3)), w(mcap)
bys mdate: asgen vw_24_Size = (rt / (nq_size ==5 & idiovol_size ==4)), w(mcap)
bys mdate: asgen vw_25_Size = (rt / (nq_size ==5 & idiovol_size ==5)), w(mcap)

collapse(mean) vw_1_Size vw_2_Size vw_3_Size vw_4_Size vw_5_Size vw_6_Size vw_7_Size vw_8_Size vw_9_Size vw_10_Size vw_11_Size vw_12_Size vw_13_Size vw_14_Size vw_15_Size vw_16_Size vw_17_Size vw_18_Size vw_19_Size vw_20_Size vw_21_Size vw_22_Size vw_23_Size vw_24_Size vw_25_Size, by (mdate)

// 25 equal-weighted portfolio returns series construction -

bys mdate: asgen ew_1_Size = (rt / (nq_size ==1 & idiovol_size ==1))
bys mdate: asgen ew_2_Size = (rt / (nq_size ==1 & idiovol_size ==2))
bys mdate: asgen ew_3_Size = (rt / (nq_size ==1 & idiovol_size ==3))
bys mdate: asgen ew_4_Size = (rt / (nq_size ==1 & idiovol_size ==4))
bys mdate: asgen ew_5_Size = (rt / (nq_size ==1 & idiovol_size ==5))
bys mdate: asgen ew_6_Size = (rt / (nq_size ==2 & idiovol_size ==1))
bys mdate: asgen ew_7_Size = (rt / (nq_size ==2 & idiovol_size ==2))
bys mdate: asgen ew_8_Size = (rt / (nq_size ==2 & idiovol_size ==3))
bys mdate: asgen ew_9_Size = (rt / (nq_size ==2 & idiovol_size ==4))
bys mdate: asgen ew_10_Size = (rt / (nq_size ==2 & idiovol_size ==5))
bys mdate: asgen ew_11_Size = (rt / (nq_size ==3 & idiovol_size ==1))
bys mdate: asgen ew_12_Size = (rt / (nq_size ==3 & idiovol_size ==2))
bys mdate: asgen ew_13_Size = (rt / (nq_size ==3 & idiovol_size ==3))
bys mdate: asgen ew_14_Size = (rt / (nq_size ==3 & idiovol_size ==4))
bys mdate: asgen ew_15_Size = (rt / (nq_size ==3 & idiovol_size ==5))
bys mdate: asgen ew_16_Size = (rt / (nq_size ==4 & idiovol_size ==1))
bys mdate: asgen ew_17_Size = (rt / (nq_size ==4 & idiovol_size ==2))
bys mdate: asgen ew_18_Size = (rt / (nq_size ==4 & idiovol_size ==3))
bys mdate: asgen ew_19_Size = (rt / (nq_size ==4 & idiovol_size ==4))
bys mdate: asgen ew_20_Size = (rt / (nq_size ==4 & idiovol_size ==5))
bys mdate: asgen ew_21_Size = (rt / (nq_size ==5 & idiovol_size ==1))
bys mdate: asgen ew_22_Size = (rt / (nq_size ==5 & idiovol_size ==2))
bys mdate: asgen ew_23_Size = (rt / (nq_size ==5 & idiovol_size ==3))
bys mdate: asgen ew_24_Size = (rt / (nq_size ==5 & idiovol_size ==4))
bys mdate: asgen ew_25_Size = (rt / (nq_size ==5 & idiovol_size ==5))

collapse(mean) ew_1_Size ew_2_Size ew_3_Size ew_4_Size ew_5_Size ew_6_Size ew_7_Size ew_8_Size ew_9_Size ew_10_Size ew_11_Size ew_12_Size ew_13_Size ew_14_Size ew_15_Size ew_16_Size ew_17_Size ew_18_Size ew_19_Size ew_20_Size ew_21_Size ew_22_Size ew_23_Size ew_24_Size ew_25_Size, by (mdate)

[Clyde Schechter]

To convert the code from doing things by fiscal year into doing things by month, just skip the -gen fyear = ...- command and change -fyear- to -mdate- in the -runby- command. Probably for transparency, it would also be a good idea to change the name of the program from one_fyear to one_mdate.

capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile size, nq(5)
    forvalues i = 1/4 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile size_quintile = size, cutpoints(cutoffs)
    drop cutoffs
    by size_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
    tab *_quintile
    exit
end


// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

The code you show after that uses the -asgen- command, which I am not familiar with. I'm guessing that it is intended to calculate weighted means of the variable rt for each of the 25 portfolios in each month. It seems to rely on variables nq_size and idiovol_size which are not instantiated in your example data. So I'm not clear on what you are trying to do there. But since those variables seem to range from 1 to 5, I'm guessing you intend them to be the quintile numbers created by the earlier code. Since those are not the names of the variable created by the earlier code. If I have that right, you may want something like this:

capture program drop one_weighted_return
program define one_weighted_return
    egen numerator = total(mcap*rt)
    egen denominator = total(mcap)
    gen vw_size = numerator/denominator
    exit
end

runby one_weighted_return, by(mdate size_quintile idiovol_quintile)

This will not create 25 separate variables. Rather you will get a single variable vw_size, which varies across portfolios. This long data layout will prove easier to work with for further analysis than having 25 wide variables. It also doesn't number the portfolios 1 through 25. If you want a variable that numbers them that way, you can do that with:

gen int portfolio_num = (size_quintile-1)*5 + idiovol_quintile

The equally weighted calculations are even simpler than the above and can be done just with -by mdate size_quintile idiovol_quintile, sort: egen ew_size = mean(rt)-.

Note: It is not a good idea to address posts to particular individuals unless it represents a direct response to something they posted. There are many people who respond here on the Forum, and by mentioning one person by name you discourage others from even looking at your post. Moreover, I'm not here every day. Sometimes I'm away for weeks at a time. Your post would languish unattended. I understand that your earlier post, which I don't recall seeing, garnered no responses. The solution to that is to revise the post to make it clearer and more focused, and, if appropriate and not originally done, show example data and code. I think that this question might have lacked appeal because it is long and raises numerous questions at once. It looks like it would require more effort than is reasonable to respond to. On a busier day, I probably would have passed it over myself.

[Sartaj Hussain]

Dear Clayde,

I have noted your advice for future.

The code specified in #2 (part 1) works fine. But i want to know, does it do monthly conditional (dependent) bi-variate sorts of rt variable correctly: first sort on size variable to create quintiles and then within each size quintile on idiovol as sort variable to create further quintiles? All this process should lead to 25 combination of size-idiovol quintiles. By quintile i mean the slicing of monthly rt data into five equal segments using a certain sorting variable.

Having said that, then in the next step, i require to have simple and weighted mean (weighted by mcap) of each size-idiovol quintile combinations of rt for each month. This means that the rt's of a given size-idiovol quintile should pool into a equal and weighted mean for each month. The rest two parts of the code can't do that. I tried using the asgen command to produce monthly variable series of equal weighted portfolio rt, say for size_quintile = 1 & idiovol_quintile = 1 using following code.

bys mdate: asgen ew_1_Size = (rt / (size_quintile ==1 & idiovol_quintile ==1))

But the results do not match with results of equal weighted average for size_quintile = 1 & idiovol_quintile = 1 that i obtained by conditional bivariate sort analysis on same date in MS-Excel manually for a cross verification purpose. So asgen seems not reliable.

Now, can this code be substantiated to calculate monthly portfolio average (both equal and weighted by mcap) rt for each size and idiovol quintile combination.

capture program drop one_mdate
program define one_mdate
gen cutoffs = .
_pctile size, nq(5)
forvalues i = 1/4 {
replace cutoffs = r(r`i') in `i'
}
display `"`cutoffs'"'
xtile size_quintile = size, cutpoints(cutoffs)
drop cutoffs
by size_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
tab *_quintile
exit
end


// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

[Clyde Schechter]

The code specified in #2 (part 1) works fine. But i want to know, does it do monthly conditional (dependent) bi-variate sorts of rt variable correctly: first sort on size variable to create quintiles and then within each size quintile on idiovol as sort variable to create further quintiles?

Yes, it does. Why do you think otherwise?

All this process should lead to 25 combination of size-idiovol quintiles. By quintile i mean the slicing of monthly rt data into five equal segments using a certain sorting variable.

Usually, but not always. If there are tied values of size or idiovol, they must always go together in the same "quintile." If there are a large number of ties, it may be impossible to actually create 5 equal size groups when all tied observations are allocated to the same group. So, in that case, you may get five groups (ultimately 25 after the second partition) with unequal sizes, or with sever enough clumping of the data, there may be fewer than 5 (and hence ultimately fewer than 25) groups.

Having said that, then in the next step, i require to have simple and weighted mean (weighted by mcap) of each size-idiovol quintile combinations of rt for each month. This means that the rt's of a given size-idiovol quintile should pool into a equal and weighted mean for each month. The rest two parts of the code can't do that. I tried using the asgen command to produce monthly variable series of equal weighted portfolio rt, say for size_quintile = 1 & idiovol_quintile = 1 using following code.

bys mdate: asgen ew_1_Size = (rt / (size_quintile ==1 & idiovol_quintile ==1))

But the results do not match with results of equal weighted average for size_quintile = 1 & idiovol_quintile = 1 that i obtained by conditional bivariate sort analysis on same date in MS-Excel manually for a cross verification purpose. So asgen seems not reliable.

I am not an -asgen- user so I cannot comment on that. Also, as a matter of principle, I never use Excel to do statistical analysis and I do not trust results created that way--for one thing, it is impossible to be sure that one has done it correctly because there is no audit trail.

Now, can this code be substantiated to calculate monthly portfolio average (both equal and weighted by mcap) rt for each size and idiovol quintile combination.

capture program drop one_mdate
program define one_mdate
gen cutoffs = .
_pctile size, nq(5)
forvalues i = 1/4 {
replace cutoffs = r(r`i') in `i'
}
display `"`cutoffs'"'
xtile size_quintile = size, cutpoints(cutoffs)
drop cutoffs
by size_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
tab *_quintile
exit
end


// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

No, there is no calculation of means (weighted or unweighted) mentioned anywhere in that code. The penultimate code block in #2 shows you how to get the weighted means. For the unweighted means, you just need a single command: -by size_quintile idiovol_quintile, sort: egen unweighted_mean = mean(r5)-.

Added: -xtile()- is not an official Stata egen function. To use it you have to install the -egenmore- package from SSC.

[Sartaj Hussain]

Thanks. But still not up-to what i require basically. Let me share that I tried below code to generate un-weighted returns series for one of the quintile portfolios. Seems it will work for others also. But do not understand how weighted return series can be generated if at all by augmenting the code to include for weights.

gen size_idio_1 = rt if size_quintile ==1 & idiovol_quintile ==1

collapse(mean) size_idio_1, by(mdate)

[Clyde Schechter]

Well, it's hard for me to discern what you want here. Perhaps you are trying to create a data set that contains one observatin for each month and each of the (up to ) 25 groups in each month and gives the weighted and unweighted means of rt.

Putting together the pieces of code from before, and adding a little bit, it can all be done with:

capture program drop one_mdate
program define one_mdate
    gen cutoffs = .
    _pctile size, nq(5)
    forvalues i = 1/4 {
        replace cutoffs = r(r`i') in `i'
    }
    display `"`cutoffs'"'
    xtile size_quintile = size, cutpoints(cutoffs)
    drop cutoffs
    by size_quintile, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
    tab *_quintile
    exit
end


// DOUBLE SORT EACH FISCAL YEAR
runby one_mdate, by(mdate) status

capture program drop one_weighted_return
program define one_weighted_return
    egen numerator = total(mcap*rt)
    egen denominator = total(mcap)
    gen vw_mean_rt = numerator/denominator
    exit
end

runby one_weighted_return, by(mdate size_quintile idiovol_quintile)

gen int portfolio_num = (size_quintile-1)*5 + idiovol_quintile

by mdate size_quintile idiovol_quintile, sort: egen ew_mean_rt = mean(rt)

keep mdate *_quintile *_mean_rt
by mdate *_quintile, sort: keep if _n == 1

[Sartaj Hussain]

#6 Absolutely. You got it very rightly. Well, let me check the new code.

[Sartaj Hussain]

I think the code has now done it brilliantly. Only one thing is left now. That is, how can i split the output which is presently the variables in two columns, i.e., 1. vw_mean_rt and 2. ew_mean_rt into 50 variables (columns): 25 for un-weighted and 25 for weighted returns one observation per month, i.e., 12 observations per portfolio in the given case for each bi-variate sort portfolio.

Hope it is clearly expressed. This will surely make my analysis very convenient.

[Clyde Schechter]

gen str32 group = "size_q" + string(size_quintile) +"_idiovol_q" + string(idiovol_q) + "_"
drop *_quintile
reshape wide vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string

Actually, it will almost surely make your analysis very inconvenient. Having 50 variables side-by-side like that almost always works badly in Stata--there are very few things you can do with the data laid out that way. That data arrangement is only workable for a small number of Stata commands, or for making graphs or exporting to a spreadsheet or other vehicle for displaying data to human eyes. But for analysis, this arrangement will be an albatross around your neck. If you are planning further analysis, I strongly advise you not to do this: leave the data as it was.

[Sartaj Hussain]

You are right handling too many variables will of course be inconvenient. I must say that I received a great deal of support here on my work. Now, about the above code, i want to understand a few more things.

1. How would the first part of code be, if, instead of dependent bi-variate sorts on size and idiovol, i want a uni-variate sort on idiovol to create 10 partitions or deciles. Rest part of the code would be same. Moreover,

2. If the same code is to do unconditional (independent) bi-variate sorts instead of condition (dependent), then what modifications in the above code is to be made, and finally,

3. Does the sort in the code follow ascending order of sort variable for quintiles from 1 - 5.

[Clyde Schechter]

1. How would the first part of code be, if, instead of dependent bi-variate sorts on size and idiovol, i want a uni-variate sort on idiovol to create 10 partitions or deciles. Rest part of the code would be same. Moreover,

Actually, you cannot just change the first part of the code because the later code also references the quintiles of both size and idiovol. You have to modify everything. But the resulting code is appreciably shorter and simpler.

by mdate, sort: egen idiovol_decile = xtile(idiovol), nq(10)

capture program drop one_weighted_return
program define one_weighted_return
    egen numerator = total(mcap*rt)
    egen denominator = total(mcap)
    gen vw_mean_rt = numerator/denominator
    exit
end

runby one_weighted_return, by(mdate idiovol_decile)

by mdate idiovol_decile, sort: egen ew_mean_rt = mean(rt)

keep mdate idiovol_decile *_mean_rt
by mdate idiovol_decile, sort: keep if _n == 1

2. If the same code is to do unconditional (independent) bi-variate sorts instead of condition (dependent), then what modifications in the above code is to be made, and finally,

by mdate, sort: egen idiovol_quintile = xtile(idiovol), nq(5)
by mdate: egen size_quintile = xtile(size), nq(5)

capture program drop one_weighted_return
program define one_weighted_return
    egen numerator = total(mcap*rt)
    egen denominator = total(mcap)
    gen vw_mean_rt = numerator/denominator
    exit
end

runby one_weighted_return, by(mdate size_quintile idiovol_quintile)

by mdate size_quintile idiovol_quintile, sort: egen ew_mean_rt = mean(rt)

keep mdate *_quintile *_mean_rt
by mdate size_quintile idiovol_quintile, sort: keep if _n == 1

3. Does the sort in the code follow ascending order of sort variable for quintiles from 1 - 5.

I don't understand this question.

[Sartaj Hussain]

Thanks. Also please change the code in #9 for #11 (1) and #11 (2) both. Then it would be useful.

The question #10 (3) is that after cutting of quintiles, which of the five quintile contains observations with highest value and which quintile has observations that have lowest values of sort variable.

Moreover, in #11 (2) , does it matter whether we run first this code:

by mdate, sort: egen idiovol_quintile = xtile(idiovol), nq(5)

or we run this code first:

by mdate: egen size_quintile = xtile(size), nq(5)

[Clyde Schechter]

Thanks. Also please change the code in #9 for #11 (1) and #11 (2) both. Then it would be useful.

//  FOR #11(1)
gen str32 group = "_idiovol_d" + string(idiovol_decile) + "_"
drop idiovol_decile
reshape wide vw_mean_@rt ew_mean_@rt, i(mdate) j(group) string

For #11(2) the code in #9 does not require any changes.

The question #10 (3) is that after cutting of quintiles, which of the five quintile contains observations with highest value and which quintile has observations that have lowest values of sort variable.

Quintile 1 contains the lowest values and Quintile 5 contains the highest values.

Moreover, in #11 (2) , does it matter whether we run first this code:

by mdate, sort: egen idiovol_quintile = xtile(idiovol), nq(5)

or we run this code first:

by mdate: egen size_quintile = xtile(size), nq(5)

No, you can do it in either order.

[Sartaj Hussain]

Thanks indeed. It is amply clear. I have learnt a lot from all this. Just one more thing:

For #11 (2), If i want the first sort on size with just 2 quantiles and the second sort on idiovol with 3 quantiles, i.e., 2x3 sort instead of 5x5 earlier. What will the new code in #11(2) and #9 be.

[Clyde Schechter]

The nq() parameter is what tells Stata how many quantiles to do. So just change the nq(5)'s to nq(2) and nq(3) in the appropriate places.