I asked a similar question previously but the post is old and instead of reviving it, I am creating a new post. My data is at the end of the post after using dataex. Note this is after I ran the code that I came up with (and am unhappy with).
I am trying to get quarterly returns for securities. The dates are month wise so essentially quarterly returns that I use as qRi should be: multiply the current month return with the one prior and the one even before that, thus compounding 3 months of returns.
So I am using the CRSP monthly stock file, to run the following code:
Two problems:
I am trying to get quarterly returns for securities. The dates are month wise so essentially quarterly returns that I use as qRi should be: multiply the current month return with the one prior and the one even before that, thus compounding 3 months of returns.
So I am using the CRSP monthly stock file, to run the following code:
Code:
generate ryear=year(date)
generate md=month(date)
rename CUSIP cusip8
generate Ri=RET+1
generate qRi=.
egen uniquePERMNO=group(PERMNO)
summarize uniquePERMNO
sort PERMNO date
forvalues i = 1/`r(max)' {
replace qRi=Ri[_n]*Ri[_n-1]*Ri[_n-2] if uniquePERMNO == `i'
}
- The code takes a very long to run. I am sure there would be a quicker way to do it.
- I just discovered some month's data is missing for many PERMNOs. So when I use the command sort PERMNO date, its possible that all the dates are not consecutive. So when I run the loop that multiply the observation's Ri with previous two Ri observations, there is a chance that all the 3 observations are not consecutive and I get a wrong quarterly return. Note quarterly return here just means the returns over the previous 3 months. One way I thought I could possibly fix this is if I could ensure that the multiplication that happens in the loop happens only if the dates are consecutive but I am very bad at setting conditions and navgating dates honestly in Stata
Code:
* Example generated by -dataex-. For more info, type help dataex clear input double PERMNO long date str8 TICKER str36 COMNAM double PERMCO str8 cusip8 double(PRC RET ALTPRC) float(ryear md uniquePERMNO Ri qRi) 10001 14640 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.125 -.04411764815449715 8.125 2000 1 1 .9558824 . 10001 14669 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.25 .015384615398943424 8.25 2000 2 1 1.0153847 . 10001 14700 "EWST" "ENERGY WEST INC" 7953 "36720410" -8 -.015757575631141663 -8 2000 3 1 .9842424 .9552942 10001 14728 "EWST" "ENERGY WEST INC" 7953 "36720410" -8.09375 .01171875 -8.09375 2000 4 1 1.0117188 1.0110962 10001 14761 "EWST" "ENERGY WEST INC" 7953 "36720410" -7.90625 -.023166023194789886 -7.90625 2000 5 1 .976834 .9727083 10001 14791 "EWST" "ENERGY WEST INC" 7953 "36720410" 8 .02766798436641693 8 2000 6 1 1.027668 1.015625 10001 14822 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.875 -.015625 7.875 2000 7 1 .984375 .9881757 10001 14853 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.25 .0476190485060215 8.25 2000 8 1 1.0476191 1.0597826 10001 14882 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.75 .07575757801532745 8.75 2000 9 1 1.0757576 1.1093751 10001 14914 "EWST" "ENERGY WEST INC" 7953 "36720410" 9 .02857142873108387 9 2000 10 1 1.0285715 1.1591839 10001 14944 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.5625 .0625 9.5625 2000 11 1 1.0625 1.1756495 10001 14973 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.75 .03267974033951759 9.75 2000 12 1 1.0326798 1.1285715 10001 15006 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.875 .012820512987673283 9.875 2001 1 1 1.0128205 1.1112891 10001 15034 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.75 -.012658228166401386 9.75 2001 2 1 .9873418 1.0326798 10001 15064 "EWST" "ENERGY WEST INC" 7953 "36720410" 10 .03846153989434242 10 2001 3 1 1.0384616 1.0384616 10001 15095 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.75 -.02500000037252903 9.75 2001 4 1 .975 .9996836 10001 15126 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.699999809265137 .09743587672710419 10.699999809265137 2001 5 1 1.0974358 1.1111538 10001 15155 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.800000190734863 .11495330929756165 11.800000190734863 2001 6 1 1.1149533 1.193 10001 15187 "EWST" "ENERGY WEST INC" 7953 "36720410" 12.100000381469727 .025423744693398476 12.100000381469727 2001 7 1 1.0254238 1.254698 10001 15218 "EWST" "ENERGY WEST INC" 7953 "36720410" 12.350000381469727 .020661156624555588 12.350000381469727 2001 8 1 1.0206611 1.1669214 10001 15246 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.949999809265137 -.021862395107746124 11.949999809265137 2001 9 1 .9781376 1.0237287 10001 15279 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.550000190734863 -.03347277268767357 11.550000190734863 2001 10 1 .9665272 .9649296 10001 15309 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.5 -.004329020623117685 11.5 2001 11 1 .995671 .941304 10001 15340 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.449999809265137 .006956504657864571 11.449999809265137 2001 12 1 1.0069565 .9690376 10001 15371 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.300000190734863 -.013100403361022472 11.300000190734863 2002 1 1 .9868996 .9894629 10001 15399 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.699999809265137 -.053097378462553024 10.699999809265137 2002 2 1 .9469026 .9409986 10001 15427 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.399999618530273 -.015887869521975517 10.399999618530273 2002 3 1 .9841121 .9196507 10001 15460 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.949999809265137 -.043269213289022446 9.949999809265137 2002 4 1 .9567308 .8915376 10001 15491 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.09749984741211 .014824124984443188 10.09749984741211 2002 5 1 1.0148242 .9554878 10001 15519 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.720000267028809 -.024015802890062332 9.720000267028809 2002 6 1 .9759842 .9475963 10001 15552 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.75 -.09979426115751266 8.75 2002 7 1 .9002057 .8916109 10001 15582 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.899999618530273 .01714281365275383 8.899999618530273 2002 8 1 1.0171428 .893648 10001 15613 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.75 -.0016853498527780175 8.75 2002 9 1 .9983147 .9140946 10001 15644 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.579999923706055 -.01942858099937439 8.579999923706055 2002 10 1 .9805714 .9957002 10001 15673 "EWST" "ENERGY WEST INC" 7953 "36720410" -8.375 -.02389276586472988 -8.375 2002 11 1 .9761072 .9555297 10001 15705 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.35099983215332 -.10614927113056183 7.35099983215332 2002 12 1 .8938507 .8555428 10001 15736 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.4399995803833 .14814308285713196 8.4399995803833 2003 1 1 1.148143 1.0017481 10001 15764 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.739999771118164 .035545047372579575 8.739999771118164 2003 2 1 1.035545 1.0627472 10001 15795 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.650000095367432 -.1092676967382431 7.650000095367432 2003 3 1 .8907323 1.0590395 10001 15825 "EWST" "ENERGY WEST INC" 7953 "36720410" 5.170000076293945 -.32418301701545715 5.170000076293945 2003 4 1 .675817 .6233691 10001 15855 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.4399995803833 .6324950456619263 8.4399995803833 2003 5 1 1.632495 .9827164 10001 15886 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.010000228881836 -.2879146337509155 6.010000228881836 2003 6 1 .7120854 .7856209 10001 15917 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.119999885559082 .01830277033150196 6.119999885559082 2003 7 1 1.0183028 1.1837524 10001 15946 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.650000095367432 .08660134673118591 6.650000095367432 2003 8 1 1.0866014 .7879148 10001 15978 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.900000095367432 .03759398311376572 6.900000095367432 2003 9 1 1.037594 1.1480865 10001 16009 "EWST" "ENERGY WEST INC" 7953 "36720410" 6 -.1304347962141037 6 2003 10 1 .8695652 .9803922 10001 16037 "EWST" "ENERGY WEST INC" 7953 "36720410" 5.96999979019165 -.005000034812837839 5.96999979019165 2003 11 1 .9949999 .8977442 10001 16070 "EWST" "ENERGY WEST INC" 7953 "36720410" 5.949999809265137 -.0033500806894153357 5.949999809265137 2003 12 1 .9966499 .8623188 10001 16100 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.010000228881836 .010084104724228382 6.010000228881836 2004 1 1 1.0100842 1.0016668 10001 16128 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.489999771118164 .07986681163311005 6.489999771118164 2004 2 1 1.0798668 1.0871022 10001 16161 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.25 .11710327863693237 7.25 2004 3 1 1.1171033 1.2184875 10001 16191 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.159999847412109 -.01241381373256445 7.159999847412109 2004 4 1 .9875862 1.1913477 10001 16219 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.590000152587891 -.07960889488458633 6.590000152587891 2004 5 1 .9203911 1.0154084 10001 16252 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.659999847412109 .010622108355164528 6.659999847412109 2004 6 1 1.0106221 .9186207 10001 16282 "EWST" "ENERGY WEST INC" 7953 "36720410" 7 .051051076501607895 7 2004 7 1 1.051051 .9776536 10001 16314 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.03000020980835 .004285744391381741 7.03000020980835 2004 8 1 1.0042857 1.0667678 10001 16344 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.099999904632568 -.13229022920131683 6.099999904632568 2004 9 1 .8677098 .9159158 10001 16373 "EWST" "ENERGY WEST INC" 7953 "36720410" 5.760000228881836 -.05573765188455582 5.760000228881836 2004 10 1 .9442623 .8228571 10001 16405 "EWST" "ENERGY WEST INC" 7953 "36720410" 5.940000057220459 .031249968335032463 5.940000057220459 2004 11 1 1.03125 .8449502 10001 16436 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.900000095367432 .16161616146564484 6.900000095367432 2004 12 1 1.1616162 1.1311476 10001 16467 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.619999885559082 -.04057973995804787 6.619999885559082 2005 1 1 .9594203 1.1493056 10001 16495 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.321000099182129 -.04516613110899925 6.321000099182129 2005 2 1 .9548339 1.0641415 10001 16526 "EWST" "ENERGY WEST INC" 7953 "36720410" 7.110000133514404 .12482202798128128 7.110000133514404 2005 3 1 1.124822 1.0304347 10001 16555 "EWST" "ENERGY WEST INC" 7953 "36720410" 6.578999996185303 -.07468356192111969 6.578999996185303 2005 4 1 .9253165 .9938067 10001 16587 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.020000457763672 .21903032064437866 8.020000457763672 2005 5 1 1.2190304 1.2687867 10001 16617 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.050000190734863 .1284288913011551 9.050000190734863 2005 6 1 1.1284289 1.2728553 10001 16646 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.140000343322754 .009944767691195011 9.140000343322754 2005 7 1 1.0099448 1.389269 10001 16679 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.5 .03938727080821991 9.5 2005 8 1 1.0393872 1.1845386 10001 16709 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.510000228881836 .21157896518707275 11.510000228881836 2005 9 1 1.211579 1.271823 10001 16740 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.099900245666504 -.11903561651706696 10.099900245666504 2005 10 1 .8809644 1.1093982 10001 16770 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.5 -.0593966506421566 9.5 2005 11 1 .9406034 1.0039605 10001 16800 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.699999809265137 .021052611991763115 9.699999809265137 2005 12 1 1.0210526 .846083 10001 16832 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.489999771118164 -.01649484969675541 9.489999771118164 2006 1 1 .9835051 .9445637 10001 16860 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.390000343322754 -.010537347756326199 9.390000343322754 2006 2 1 .9894627 .9936288 10001 16891 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.989999771118164 .17039397358894348 10.989999771118164 2006 3 1 1.170394 1.138959 10001 16919 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.949999809265137 -.09463147819042206 9.949999809265137 2006 4 1 .9053685 1.048472 10001 16952 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.765999794006348 -.010452263057231903 9.765999794006348 2006 5 1 .9895477 1.0485622 10001 16982 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.020000457763672 -.07638739794492722 9.020000457763672 2006 6 1 .9236126 .8274695 10001 17013 "EWST" "ENERGY WEST INC" 7953 "36720410" 10.439900398254395 .15741683542728424 10.439900398254395 2006 7 1 1.1574168 1.0578312 10001 17044 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.630000114440918 .12357395142316818 11.630000114440918 2006 8 1 1.1235739 1.201106 10001 17073 "EWST" "ENERGY WEST INC" 7953 "36720410" 11 -.05417025834321976 11 2006 9 1 .9458297 1.229998 10001 17105 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.079999923706055 .0072727203369140625 11.079999923706055 2006 10 1 1.0072727 1.0704384 10001 17135 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.529999732971191 .051444027572870255 11.529999732971191 2006 11 1 1.051444 1.0017197 10001 17164 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.100000381469727 -.03729395940899849 11.100000381469727 2006 12 1 .962706 1.0195932 10001 17197 "EWST" "ENERGY WEST INC" 7953 "36720410" 11.358400344848633 .023279275745153427 11.358400344848633 2007 1 1 1.0232793 1.0357956 10001 17225 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.1899995803833 .26162126660346985 14.1899995803833 2007 2 1 1.2616212 1.2428447 10001 17255 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.470000267028809 .01973225548863411 14.470000267028809 2007 3 1 1.0197322 1.316465 10001 17286 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.510000228881836 .002764337230473757 14.510000228881836 2007 4 1 1.0027643 1.2900722 10001 17317 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.989999771118164 .04341829940676689 14.989999771118164 2007 5 1 1.0434183 1.0669485 10001 17346 "EWST" "ENERGY WEST INC" 7953 "36720410" 15 .0006671266746707261 15 2007 6 1 1.0006671 1.0470006 10001 17378 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.149999618530273 -.05666669085621834 14.149999618530273 2007 7 1 .9433333 .9849479 10001 17409 "EWST" "ENERGY WEST INC" 7953 "36720410" -14.539999961853027 .027561862021684647 -14.539999961853027 2007 8 1 1.0275619 .96998 10001 17437 "EWST" "ENERGY WEST INC" 7953 "36720410" 13.90999984741211 -.03232463076710701 13.90999984741211 2007 9 1 .9676754 .9380001 10001 17470 "EWST" "ENERGY WEST INC" 7953 "36720410" 13.350000381469727 -.04025876894593239 13.350000381469727 2007 10 1 .9597412 .9543152 10001 17500 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.25 .07940071821212769 14.25 2007 11 1 1.0794008 1.0024589 10001 17531 "EWST" "ENERGY WEST INC" 7953 "36720410" 14.140000343322754 -.003929800353944302 14.140000343322754 2007 12 1 .9960702 1.0318744 10001 17562 "EWST" "ENERGY WEST INC" 7953 "36720410" 14 -.006082060746848583 14 2008 1 1 .9939179 1.0686197 10001 17591 "EWST" "ENERGY WEST INC" 7953 "36720410" 9.499899864196777 .021703556180000305 9.499899864196777 2008 2 1 1.0217036 1.0114989 10001 17622 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.96500015258789 -.05251631140708923 8.96500015258789 2008 3 1 .9474837 .9621598 10001 17652 "EWST" "ENERGY WEST INC" 7953 "36720410" 8.600000381469727 -.036698244512081146 8.600000381469727 2008 4 1 .9633018 .9325218 end format %td date
Comment