Dear all,
I would like to construct a normalized flow variable, in order to construct this variable I need to perform the following: [1] split the sample into deciles based on fund size. [2] rank funds according to their fund flows within their size decile and compute percentiles of the fund flow rankings. I would like to construct this normalized variable for each fund in a given week.
Below I provide a sample of the data, where "newid" represents a fund.
Any help or suggestions on how I can construct this variable would be very much appreciated.
(Stata version I'm using: 16.1)
Kind regards,
Rudolf
I would like to construct a normalized flow variable, in order to construct this variable I need to perform the following: [1] split the sample into deciles based on fund size. [2] rank funds according to their fund flows within their size decile and compute percentiles of the fund flow rankings. I would like to construct this normalized variable for each fund in a given week.
Below I provide a sample of the data, where "newid" represents a fund.
Any help or suggestions on how I can construct this variable would be very much appreciated.
(Stata version I'm using: 16.1)
Kind regards,
Rudolf
Code:
* Example generated by -dataex-. For more info, type help dataex clear input float(newid week) double(Fund_size Fund_flow) 1 3068 140927914 1554427.362 1 3069 143057404 -203265.82100000003 1 3070 94262645 -51768246.817999996 1 3071 95713288 755807.0460000001 1 3072 96050477 -2194578.332 1 3073 94510531 11917.774999999965 1 3074 94754079 -32267.338000000003 1 3075 97859140 536499.936 1 3076 96721740 260476.536 1 3077 97016300 934522.92 1 3078 99360748 -104977.60900000001 1 3079 97260766 -28517.843000000008 1 3080 102413209 3079862.005 1 3081 104566979 304852.4130000002 1 3082 103683685 -83448.48800000001 1 3083 103719826 -55764.73599999996 1 3084 103023891 83830.689 1 3085 101119505 -278183.043 1 3086 95756551 -198669.16900000002 1 3087 94460702 252980.44299999997 2 3068 2900021 -22495.791999999998 2 3069 2886941 -88896.169 2 3070 2873182 -363.2720000000001 2 3071 2913576 -3051.8460000000005 2 3072 2940412 3461.702 2 3073 2859718 3260.5019999999995 2 3074 2848756 -884.3580000000004 2 3075 2955660 -7006.851000000001 2 3076 2852817 -13023.378 2 3077 2791801 -10912.132999999998 2 3078 2822223 13842.136 2 3079 2809292 20778.251 2 3080 2893342 74.22300000000018 2 3081 2921365 1125.9090000000003 2 3082 2968732 380.1470000000004 2 3083 2990003 1114.892 2 3084 2941715 -1267.36 2 3085 2932482 867.7850000000003 2 3086 2891156 -1006.8219999999999 2 3087 2813530 3073.6059999999998 3 3068 568301760 -970183.7509999999 3 3069 571509856 -508382.3209999999 3 3070 585754140 -2414001.245 3 3071 580574752 -1273670.8169999998 3 3072 601463748 -539311.6599999999 3 3073 597925064 -1392309.9510000004 3 3074 602491976 -6357578.207000001 3 3075 602128552 -7729388.744 3 3076 601720140 -170658.4079999999 3 3077 600016808 -507663.802 3 3078 603146252 767518.5690000001 3 3079 596956348 -969176.4260000003 3 3080 613603692 -418919.789 3 3081 619563940 -2535218.7579999994 3 3082 623547848 -3365845.548 3 3083 623474556 -1226279.1409999996 3 3084 637147244 -1528040.472 3 3085 632515984 -1131236.6 3 3086 594766376 -668137.5980000001 3 3087 597141056 -1576590.1500000001 4 3068 66208047 -75181.133 4 3069 66765990 -142621.74400000004 4 3070 69377966 185311.04799999995 4 3071 69014586 152051.01400000002 4 3072 71154893 -333114.85899999994 4 3073 71225724 -3028.737999999999 4 3074 72578564 -43737.211 4 3075 72939383 -42766.101999999984 4 3076 73397280 127362.20300000001 4 3077 73755690 279349.40499999997 4 3078 102088786 26815847.722999997 4 3079 101464980 -45475.198 4 3080 103846604 16577.615999999998 4 3081 105229784 176246.92099999997 4 3082 106324053 285566.98600000003 4 3083 106841678 113402.04899999998 4 3084 108451860 3498.740999999995 4 3085 108356264 106292.49999999997 4 3086 104355947 151178.62900000002 4 3087 105065093 49478.96599999999 5 3068 880622768 -3750348.5330000008 5 3069 890947328 -2070803.4900000002 5 3070 904514176 -2750180.33 5 3071 902528400 -6735699.4860000005 5 3072 919798048 -4363957.152000001 5 3073 911453376 -5562729.633 5 3074 1014968960 90068504.661 5 3075 1022164736 -3944467.7890000003 5 3076 1022859648 -5119968.709000001 5 3077 1023260656 -5366160.331 5 3078 1033568784 -4086425.2539999997 5 3079 1029866816 -4582063.402 5 3080 1030677328 -3309883.6590000005 5 3081 1026456064 -3671314.011 5 3082 1023784800 -3215818.529 5 3083 1014514544 -937107.3570000001 5 3084 1020217984 -2782757.815 5 3085 1018391568 -3245667.932 5 3086 1011543152 -3324948.399 5 3087 1013499200 -1554793.303 6 3068 10570476 -45904.384 6 3069 10830140 66263.44299999998 6 3070 11131149 -9741.03000000001 6 3071 11247267 106779.363 6 3072 11454548 2077.3020000000033 6 3073 11271092 -7698.141000000002 6 3074 11572994 -9695.623 6 3075 11655245 -15141.319000000003 6 3076 11567393 -39311.704 6 3077 11486711 -12810.161 6 3078 11767635 188.75600000000122 6 3079 11492726 -13610.142999999996 6 3080 11779564 6595.110000000001 6 3081 12048034 55296.276999999995 6 3082 12060822 -44988.708999999995 6 3083 12095119 -4427.1630000000005 6 3084 12028187 -134427.289 6 3085 12003182 -15320.02 6 3086 11454552 -111455.786 6 3087 11525694 -4800.375999999992 7 3068 0 0 7 3069 0 0 7 3070 0 0 7 3071 0 0 7 3072 387128254 0 7 3073 0 0 7 3074 0 0 7 3075 0 0 7 3076 409650959 0 7 3077 0 0 7 3078 0 0 7 3079 0 0 7 3080 0 0 7 3081 0 0 7 3082 0 0 7 3083 0 0 7 3084 0 0 7 3085 446682316 0 7 3086 0 0 7 3087 0 0 8 3068 1785632128 -1592388.0729999999 8 3069 1809530112 -2236252.104 8 3070 1881544448 -1929979.293 8 3071 1859072000 -2185810.687 8 3072 1928748928 -2588295.508 8 3073 1919453056 -2078057.139 8 3074 1918866688 -38025501.791 8 3075 1920668288 -22032767.682 8 3076 1927106176 -3349832.3230000003 8 3077 1915210240 -3499132.59 8 3078 1945672192 -1597277.5459999999 8 3079 1930233600 -1545893.5019999999 8 3080 1978390400 -3272978.885 8 3081 1997315456 -1909928.41 8 3082 2007900800 -515767.3119999998 8 3083 2010609536 -1455488.354 8 3084 2056087552 -1650202.207 8 3085 2050657280 -2650499.6500000004 8 3086 1936920320 -308872.6529999999 8 3087 2067952256 112516831.977 9 3068 383960901 -1618619.362 9 3069 390577661 63922.36800000007 9 3070 398543513 259277.45300000007 9 3071 398617838 -304545.31499999994 9 3072 407955620 850638.404 9 3073 433124533 28269100.684 9 3074 445537303 36621.29799999999 9 3075 446867086 61762.981000000014 9 3076 458112185 3031017.7759999996 9 3077 461135769 3412012.165 9 3078 473457044 4351000.874 9 3079 471936664 4787242.7749999985 9 3080 473014604 -7448738.088000001 9 3081 476755806 -421894.99100000004 9 3082 477186367 -442238.241 9 3083 475611407 -282652.31000000006 9 3084 483453071 299062.30400000006 9 3085 481882513 -1563028.307 9 3086 468824071 -525183.562 9 3087 474860621 612813.1540000001 10 3068 183124357 1173579.5750000004 10 3069 186850216 51119.72700000002 10 3070 192245921 -546412.18 10 3071 191124715 -69493.275 10 3072 195991337 235564.291 10 3073 0 0 10 3074 0 0 10 3075 0 0 10 3076 202601548 0 10 3077 0 0 10 3078 0 0 10 3079 0 0 10 3080 202756344 0 10 3081 0 0 10 3082 0 0 10 3083 0 0 10 3084 0 0 10 3085 211316000 0 10 3086 0 0 10 3087 0 0 11 3068 1116265600 1830916.048 11 3069 1139213312 6628051.635999998 11 3070 1166843008 4340272.217 11 3071 1169870720 1110656.3359999997 11 3072 1190875136 4453602.339 11 3073 1166780288 -2600290.7460000007 11 3074 1202509568 5515958.592 11 3075 1221940096 1807908.9849999999 11 3076 1231386112 7481306.199000001 11 3077 1214853760 2204429.255 11 3078 1243484416 968010.7409999999 11 3079 1219789184 1034567.314 11 3080 1247452672 3213166.1570000006 11 3081 1267818880 2466401.476 11 3082 1280716672 1924356.9249999998 11 3083 1283597568 884962.5 11 3084 1193879168 -82268908.93699999 11 3085 1182648832 914510.2179999999 11 3086 1137528192 1642873.466 11 3087 1150924672 5948287.954 12 3068 150458052 -581869.012 12 3069 166410464 14265142.430000002 12 3070 173940710 1155273.95 12 3071 175594260 3345379.1379999993 12 3072 183154092 1276215.1190000002 12 3073 179222312 2240056.818 12 3074 187780754 502038.91299999994 12 3075 188269728 1259502.5320000001 12 3076 185905342 775013.562 12 3077 189500402 1730382.155 12 3078 198632962 4721442.842000001 12 3079 199541454 1473643.2030000002 12 3080 205282196 784351.1139999998 12 3081 217516384 13025355.720999999 12 3082 218141692 1596565.4049999998 12 3083 219542036 1382271.9880000001 12 3084 213978884 -1491412.1409999998 12 3085 197234372 -11539706.177 12 3086 200281760 1343955.67 12 3087 205672244 1351695.211 13 3068 0 0 13 3069 0 0 13 3070 0 0 13 3071 0 0 13 3072 2566978816 0 13 3073 0 0 13 3074 0 0 13 3075 0 0 13 3076 2724783104 0 13 3077 0 0 13 3078 0 0 13 3079 0 0 13 3080 2884401920 0 13 3081 0 0 13 3082 0 0 13 3083 0 0 13 3084 0 0 13 3085 3040668928 0 13 3086 0 0 13 3087 0 0 14 3068 2388463 -1332.98 14 3069 2406173 1509.029 14 3070 2448423 -1736.366 14 3071 2433199 971.781 14 3072 2477798 623.4350000000001 14 3073 2463542 -369.44199999999995 14 3074 2499424 -1157.352 14 3075 2508665 -13.580000000000041 14 3076 2497412 314.81499999999994 14 3077 2492803 22.1400000000001 14 3078 2516892 937.6630000000001 14 3079 2483168 -1340.049 14 3080 2520202 -.5259999999998399 14 3081 2530974 1511.136 14 3082 2532600 -689.013 14 3083 2530684 396.722 14 3084 2544577 -.3909999999999627 14 3085 2531343 -1663.2719999999997 14 3086 2464766 527.679 14 3087 2490217 -18.110000000000184 15 3068 461129265 163639.922 15 3069 467993603 1379411.098 15 3070 479754814 702435.984 15 3071 478897280 -341126.5740000001 15 3072 492264438 1780424.8709999998 15 3073 489477998 756537.4090000001 15 3074 501265010 1666655.9030000002 15 3075 510420919 -2027639.436 15 3076 517222213 1744852.2330000002 15 3077 519121973 6486573.734 15 3078 533746810 2778275.589 15 3079 529135290 4207924.84 15 3080 545198974 2496116.628 15 3081 559239418 4581606.1450000005 15 3082 563402902 1516495.0880000002 15 3083 568508208 1937065.3959999997 15 3084 577718680 7018468.364 15 3085 575960071 933516.891 15 3086 550078096 4974698.147 15 3087 548387479 2683520.622999999 16 3068 0 0 16 3069 0 0 16 3070 0 0 16 3071 0 0 16 3072 0 0 16 3073 0 0 16 3074 0 0 16 3075 0 0 16 3076 0 0 16 3077 0 0 16 3078 0 0 16 3079 619168 4948.771 16 3080 681670 49984.806000000004 16 3081 777019 94486.898 16 3082 817411 36900.106 16 3083 884862 74827.406 16 3084 1043398 142517.889 16 3085 1179222 131741.722 16 3086 1241009 89825.895 16 3087 2609097 1351970.875 17 3068 0 0 17 3069 0 0 17 3070 0 0 17 3071 0 0 17 3072 2481294848 0 17 3073 0 0 17 3074 0 0 17 3075 0 0 17 3076 2790243584 0 17 3077 0 0 17 3078 0 0 17 3079 0 0 17 3080 2661138688 0 17 3081 0 0 17 3082 0 0 17 3083 0 0 17 3084 0 0 17 3085 2750412800 0 17 3086 0 0 17 3087 0 0 end format %tw week
Comment