Hello
My study looks at investor horizon and return predictability. I tried checking the posts on how to calculate churn rate but seem not to get a solution. Prof Phil thanks for your response to the same question but I still need more help. You said that first xtset the data, but in this data there are many institutional investors (o_id) investing in different firms (c_id) at the same time, making the data have repeated time variable.
I really need help on how to code the institutional investor churn rate and investor turnover in stata.
Sample of my data
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input long date float c_id long PRC float o_id long(sharesheld changeinshares)
20190329 4556 44725 1    100     100
20120629  427 27859 2   2338    2004
20120330  428 26311 2    334     334
20121231 6114 25763 2      0   -2113
20120928 6114 22559 2   2113    -225
20120629  427 27859 3    148     148
20120928 6114 22559 3    244      96
20121231 6114 25763 3      0    -244
20131231 4556 50977 4   4000    4000
20150630 4556 46009 4      0   -3200
20140331 4556 50945 4      0   -4000
20150331 4556 46188 4   3200    3200
20100930  428 18540 5  20300       0
20100630  428 15280 5  20300       0
20100331  428 17501 5  20300   -3500
20090331  428 52963 5  22600   22600
20110331  428 25108 5      0  -20300
20090930  428 16712 5  23800    1200
20100930  464 32357 5  10200    1200
20030331  464 25038 5  15280   15280
20090930  464 32853 5   7200    -300
20110630  464 31428 5      0   -4700
20100630  464 27593 5   9000    1200
20100331  464 33488 5   7800     600
20030930  464 22967 5      0  -15280
20090331  464 28832 5   7500    7500
20110331  464 31292 5   4700   -5500
20110930 4556 41637 5 258275 -512981
20080930 4556 50839 5 100804    1663
20100331 4556 45195 5 201589  201589
20101231 4556 48852 5  61991 -154410
20091231 4556 44783 5      0 -100000
20130930 4556 48973 5  10594   10594
20190329 4556 44725 5      0  -56726
20140331 4556 50945 5      0  -30265
20110630 4556 48558 5 771256  697210
20090331 4556 42063 5      0 -193762
20100630 4556 41115 5 180519  -21070
20070629 4556 43133 5  13785     300
20111230 4556 42644 5      0 -258275
20181231 4556 36736 5  56726   56726
20080630 4556 11580 5  99141   99141
20110331 4556 51279 5  74046   12055
20081231 4556 41922 5 193762   92958
20070330 4556 42350 5  13485   13485
20100930 4556 43207 5 216401   35882
20131231 4556 50977 5  30265   19671
20090930 4556 41878 5 100000  100000
20070928 4556 45949 5      0  -13785
20000630  464 50285 6  58469  -60210
20000929  464  7128 6      0  -58469
20051230  236 24575 7    269       0
20050630  236 22541 7    269     269
20060331  236 26555 7      0    -269
20050930  236 18893 7    269       0
20140930  464 42523 7     33       0
20100630  464 27593 7    700       0
20131231  464 41903 7     33       0
20090630  464 33710 7    225       0
20091231  464 34709 7    700     700
20130930  464 37538 7     33       0
20100331  464 33488 7    700       0
20140630  464 43132 7     33       0
20090930  464 32853 7      0    -225
20141231  464 41342 7     33       0
20140331  464 45201 7     33       0
20130628  464 33702 7     33      33
20090331  464 28832 7    225     225
20100930  464 32357 7      0    -700
20060929 4556 37067 7      0   -5718
20170331 4556 39188 7   5620    -468
20131231 4556 50977 7  91147    6797
20141231 4556 48390 7  27690   -5425
20181231 4556 36736 7   5972    1819
20130328 4556 48026 7 155440     625
20170929 4556 38798 7   4640    -258
20150331 4556 46188 7  25280   -2410
20100930 4556 43207 7 135999  126200
20161230 4556 45143 7   6088    -625
20160331 4556 41678 7   7138   -1200
20180629 4556 46010 7   4150       3
20180928 4556 48022 7   4153       3
20101231 4556 48852 7 136680     681
20190329 4556 44725 7   3981   -1991
20121231 4556 41751 7 154815   -7061
20171229 4556 38967 7   4643       3
20060630 4556 41728 7   5718    5718
20130628 4556 45944 7 154061   -1379
20160630 4556 44693 7   6838    -300
20130930 4556 48973 7  84350  -69711
20080331 4556 51676 7      0   -3500
20100630 4556 41115 7   9799    -580
20091231 4556 44783 7  10309    -300
20111230 4556 42644 7 159386    6425
20120330 4556 43131 7 160724    1338
20140930 4556 53535 7  33115  -56574
20100331 4556 45195 7  10379      70
20120928 4556 41932 7 161876    3654
20170630 4556 37845 7   4898    -722
20090331 4556 42063 7  11428     310
end
label values PRC PRC
label def PRC 7128 "10.71179678", modify
label def PRC 11580 "126.19", modify
label def PRC 15280 "15.59904688", modify
label def PRC 16712 "16.80904682", modify
label def PRC 17501 "17.31190394", modify
label def PRC 18540 "18.24952294", modify
label def PRC 18893 "18.72", modify
label def PRC 22541 "20.88", modify
label def PRC 22559 "20.91", modify
label def PRC 22967 "21.15212849", modify
label def PRC 24575 "23", modify
label def PRC 25038 "23.7757848", modify
label def PRC 25108 "23.91190362", modify
label def PRC 25763 "24.72", modify
label def PRC 26311 "25.37857022", modify
label def PRC 26555 "25.84", modify
label def PRC 27593 "27.31948166", modify
label def PRC 27859 "27.89285581", modify
label def PRC 28832 "29.48531875", modify
label def PRC 31292 "31.10969657", modify
label def PRC 31428 "31.40023569", modify
label def PRC 32357 "33.19189361", modify
label def PRC 32853 "34.27041004", modify
label def PRC 33488 "35.78913727", modify
label def PRC 33702 "36.24255438", modify
label def PRC 33710 "36.25576071", modify
label def PRC 34709 "38.85740648", modify
label def PRC 36736 "40.5", modify
label def PRC 37067 "41.42", modify
label def PRC 37538 "42.94696472", modify
label def PRC 37845 "43.87", modify
label def PRC 38798 "46.89", modify
label def PRC 38967 "47.47", modify
label def PRC 39188 "48.21", modify
label def PRC 41115 "50.34", modify
label def PRC 41342 "51.1745044", modify
label def PRC 41637 "52.46", modify
label def PRC 41678 "52.65", modify
label def PRC 41728 "52.85", modify
label def PRC 41751 "52.96", modify
label def PRC 41878 "53.46", modify
label def PRC 41903 "53.56484899", modify
label def PRC 41922 "53.64", modify
label def PRC 41932 "53.72", modify
label def PRC 42063 "54.2", modify
label def PRC 42350 "55.47", modify
label def PRC 42523 "56.1885053", modify
label def PRC 42644 "56.8", modify
label def PRC 43131 "58.95", modify
label def PRC 43132 "58.95302907", modify
label def PRC 43133 "58.96", modify
label def PRC 43207 "59.12", modify
label def PRC 44693 "60.1", modify
label def PRC 44725 "60.23", modify
label def PRC 44783 "60.5", modify
label def PRC 45143 "62.29", modify
label def PRC 45195 "62.55", modify
label def PRC 45201 "62.57595947", modify
label def PRC 45944 "66.49", modify
label def PRC 45949 "66.53", modify
label def PRC 46009 "66.88", modify
label def PRC 46010 "66.89", modify
label def PRC 46188 "67.87", modify
label def PRC 48022 "71.58", modify
label def PRC 48026 "71.61", modify
label def PRC 48390 "73.82", modify
label def PRC 48558 "74.76", modify
label def PRC 48852 "76.54", modify
label def PRC 48973 "77.34", modify
label def PRC 50285 "8.796064476", modify
label def PRC 50839 "82.08", modify
label def PRC 50945 "82.88", modify
label def PRC 50977 "83", modify
label def PRC 51279 "85.21", modify
label def PRC 51676 "88.18", modify
label def PRC 52963 "9.968094763", modify
label def PRC 53535 "94.32", modify