Calculation of lagged value for the current period

Alberto Alvarez

Join Date: Jul 2016
Posts: 195

Calculation of lagged value for the current period

15 Mar 2017, 14:09

Dear Stata Experts,

Please, help me to calculate 6 month lagged returns. Below I upload the data sample, where "permno" - firm ID, "prc" - price, "year" - year, "month" - month, "fdate" - quarter+year date. What I need is to calculate cumulative return for the last 6-month for each firmID in each year+quarter. Say in the example below the return for firmID=10001 in "fdate"=2003q3 will be equal to the division of the prices (6.01/8.4399)-1 (the last price in 2003q2/the first price in 2003q1).

I tried this code, but it did not work

Code:

bysort permno fdate month: g cum_ret=prc[3]/prc[-3]

Please, advise me this issue.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date str8 cusip double(prc ret) float(qrt year month fdate return return_1 year2 a1 a2)
10001 15736 "36720410"     8.4399995803833    .14814308285713196 1 2003  1 172          .           . 1001    8.44 1
10001 15764 "36720410"   8.739999771118164   .035545047372579575 1 2003  2 172   1.035545   2.0355449 1001    8.44 1
10001 15795 "36720410"   7.650000095367432    -.1092676967382431 1 2003  3 172   .8752861    1.875286 1001    8.44 1
10001 15825 "36720410"   5.170000076293945   -.32418301701545715 2 2003  4 173    .675817    1.675817 1001    5.17 1
10001 15855 "36720410"     8.4399995803833     .6324950456619263 2 2003  5 173   1.632495    2.632495 1001    5.17 1
10001 15886 "36720410"   6.010000228881836    -.2879146337509155 2 2003  6 173   .7120854   1.7120854 1001    5.17 1
10001 15917 "36720410"   6.119999885559082    .01830277033150196 3 2003  7 174  1.0183028    2.018303 1001    6.12 1
10001 15946 "36720410"   6.650000095367432    .08660134673118591 3 2003  8 174  1.0866014   2.0866013 1001    6.12 1
10001 15978 "36720410"   6.900000095367432    .03759398311376572 3 2003  9 174   1.037594   2.0375938 1001    6.12 1
10001 16009 "36720410"                   6    -.1304347962141037 4 2003 10 175   .8695652   1.8695652 1001       6 1
10001 16037 "36720410"    5.96999979019165  -.005000034812837839 4 2003 11 175   .9949999       1.995 1001       6 1
10001 16070 "36720410"   5.949999809265137 -.0033500806894153357 4 2003 12 175   .9966499     1.99665 1001       6 1
10001 16100 "36720410"   6.010000228881836   .010084104724228382 1 2004  1 176  1.0100842   2.0100842 1002    6.01 1
10001 16128 "36720410"   6.489999771118164    .07986681163311005 1 2004  2 176  1.0798668    2.079867 1002    6.01 1
10001 16161 "36720410"                7.25    .11710327863693237 1 2004  3 176  1.1171032    2.117103 1002    6.01 1
10001 16191 "36720410"   7.159999847412109   -.01241381373256445 2 2004  4 177   .9875862   1.9875863 1002    7.16 1
10001 16219 "36720410"   6.590000152587891   -.07960889488458633 2 2004  5 177   .9203911    1.920391 1002    7.16 1
10001 16252 "36720410"   6.659999847412109   .010622108355164528 2 2004  6 177  1.0106221    2.010622 1002    7.16 1
10001 16282 "36720410"                   7   .051051076501607895 3 2004  7 178   1.051051   2.0510511 1002       7 1
10001 16314 "36720410"    7.03000020980835   .004285744391381741 3 2004  8 178  1.0042857   2.0042858 1002       7 1
10001 16344 "36720410"   6.099999904632568   -.13229022920131683 3 2004  9 178   .8677098   1.8677098 1002       7 1
10001 16373 "36720410"   5.760000228881836   -.05573765188455582 4 2004 10 179   .9442623   1.9442623 1002    5.76 1
10001 16405 "36720410"   5.940000057220459   .031249968335032463 4 2004 11 179    1.03125     2.03125 1002    5.76 1
10001 16436 "36720410"   6.900000095367432    .16161616146564484 4 2004 12 179  1.1616162   2.1616163 1002    5.76 1
10001 16467 "36720410"   6.619999885559082   -.04057973995804787 1 2005  1 180   .9594203   1.9594202 1002    6.62 1
10001 16495 "36720410"   6.321000099182129   -.04516613110899925 1 2005  2 180   .9548339    1.954834 1002    6.62 1
10001 16526 "36720410"   7.110000133514404    .12482202798128128 1 2005  3 180   1.124822   2.1248221 1002    6.62 1
10001 16555 "36720410"   6.578999996185303   -.07468356192111969 2 2005  4 181   .9253165   1.9253165 1002   6.579 1
10001 16587 "36720410"   8.020000457763672    .21903032064437866 2 2005  5 181  1.2190303   2.2190304 1002   6.579 1
10001 16617 "36720410"   9.050000190734863     .1284288913011551 2 2005  6 181  1.1284289    2.128429 1002   6.579 1
10001 16646 "36720410"   9.140000343322754   .009944767691195011 3 2005  7 182  1.0099448    2.009945 1002    9.14 1
10001 16679 "36720410"                 9.5    .03938727080821991 3 2005  8 182  1.0393872   2.0393872 1002    9.14 1
10001 16709 "36720410"  11.510000228881836    .21157896518707275 3 2005  9 182   1.211579   2.2115788 1002    9.14 1
10001 16740 "36720410"  10.099900245666504   -.11903561651706696 4 2005 10 183   .8774891    1.877489 1002 10.0999 1
10001 16770 "36720410"                 9.5    -.0593966506421566 4 2005 11 183   .9406034   1.9406034 1002 10.0999 1
10001 16800 "36720410"   9.699999809265137   .021052611991763115 4 2005 12 183  1.0210526   2.0210526 1002 10.0999 1
10001 16832 "36720410"   9.489999771118164   -.01649484969675541 1 2006  1 184   .9783505   1.9783505 1003    9.49 1
10001 16860 "36720410"   9.390000343322754  -.010537347756326199 1 2006  2 184   .9894627   1.9894626 1003    9.49 1
10001 16891 "36720410"  10.989999771118164    .17039397358894348 1 2006  3 184   1.170394    2.170394 1003    9.49 1
10001 16919 "36720410"   9.949999809265137   -.09463147819042206 2 2006  4 185   .9053685   1.9053686 1003    9.95 1
10001 16952 "36720410"   9.765999794006348  -.010452263057231903 2 2006  5 185   .9815075   1.9815075 1003    9.95 1
10001 16982 "36720410"   9.020000457763672   -.07638739794492722 2 2006  6 185   .9236126   1.9236126 1003    9.95 1
10001 17013 "36720410"  10.439900398254395    .15741683542728424 3 2006  7 186  1.1574168   2.1574168 1003 10.4399 1
10001 17044 "36720410"  11.630000114440918    .12357395142316818 3 2006  8 186  1.1139953   2.1139953 1003 10.4399 1
10001 17073 "36720410"                  11   -.05417025834321976 3 2006  9 186   .9458297   1.9458297 1003 10.4399 1
10001 17105 "36720410"  11.079999923706055  .0072727203369140625 4 2006 10 187  1.0072727   2.0072727 1003   11.08 1
10001 17135 "36720410"  11.529999732971191   .051444027572870255 4 2006 11 187  1.0406137   2.0406137 1003   11.08 1
10001 17164 "36720410"  11.100000381469727   -.03729395940899849 4 2006 12 187    .962706    1.962706 1003   11.08 1
10001 17197 "36720410"  11.358400344848633   .023279275745153427 1 2007  1 188  1.0232793   2.0232792 1003 11.3584 1
10001 17225 "36720410"    14.1899995803833    .26162126660346985 1 2007  2 188  1.2492956   2.2492957 1003 11.3584 1
10001 17255 "36720410"  14.470000267028809    .01973225548863411 1 2007  3 188  1.0197322   2.0197322 1003 11.3584 1
10001 17286 "36720410"  14.510000228881836   .002764337230473757 2 2007  4 189  1.0027643   2.0027642 1003   14.51 1
10001 17317 "36720410"  14.989999771118164    .04341829940676689 2 2007  5 189  1.0330806   2.0330806 1003   14.51 1
10001 17346 "36720410"                  15  .0006671266746707261 2 2007  6 189  1.0006671    2.000667 1003   14.51 1
10001 17378 "36720410"  14.149999618530273   -.05666669085621834 3 2007  7 190   .9433333   1.9433334 1003   14.15 1
10001 17409 "36720410" -14.539999961853027   .027561862021684647 3 2007  8 190 -1.0275619 -.027561903 1003   14.15 1
10001 17437 "36720410"   13.90999984741211   -.03232463076710701 3 2007  9 190  -.9566712   .04332876 1003   14.15 1
10001 17470 "36720410"  13.350000381469727   -.04025876894593239 4 2007 10 191   .9597412   1.9597412 1003   13.35 1
10001 17500 "36720410"               14.25    .07940071821212769 4 2007 11 191  1.0674157   2.0674157 1003   13.35 1
10001 17531 "36720410"  14.140000343322754  -.003929800353944302 4 2007 12 191   .9922807   1.9922807 1003   13.35 1
10001 17562 "36720410"                  14  -.006082060746848583 1 2008  1 192    .990099    1.990099 1004      14 1
10001 17591 "36720410"   9.499899864196777   .021703556180000305 1 2008  2 192   .6785643   1.6785643 1004      14 1
10001 17622 "36720410"    8.96500015258789   -.05251631140708923 1 2008  3 192   .9436942    1.943694 1004      14 1
10001 17652 "36720410"   8.600000381469727  -.036698244512081146 2 2008  4 193   .9592862   1.9592862 1004     8.6 1
10001 17682 "36720410"  10.899999618530273      .271627813577652 2 2008  5 193  1.2674417   2.2674417 1004     8.6 1
10001 17713 "36720410"               10.75  -.010091708973050117 2 2008  6 193   .9862385   1.9862385 1004     8.6 1
10001 17744 "36720410"  10.140000343322754   -.05302322283387184 3 2008  7 194   .9432558    1.943256 1004   10.14 1
10001 17773 "36720410"   9.899999618530273  -.019723936915397644 3 2008  8 194   .9763313   1.9763312 1004   10.14 1
10001 17805 "36720410"                 8.5   -.13737370073795319 3 2008  9 194   .8585859    1.858586 1004   10.14 1
10001 17836 "36720410"   8.300000190734863   -.01882350631058216 4 2008 10 195   .9764706   1.9764706 1004     8.3 1
10001 17864 "36720410"   7.179999828338623   -.13012051582336426 4 2008 11 195   .8650602   1.8650602 1004     8.3 1
10001 17897 "36720410"   8.260000228881836     .1559889167547226 4 2008 12 195  1.1504179   2.1504178 1004     8.3 1
10001 17927 "36720410"   8.501999855041504   .034140389412641525 1 2009  1 196  1.0292978   2.0292978 1004   8.502 1
10001 17955 "36720410"     8.9399995803833    .05622203275561333 1 2009  2 196  1.0515172   2.0515172 1004   8.502 1
10001 17987 "36720410"   8.180000305175781   -.08053683489561081 1 2009  3 196   .9149889    1.914989 1004   8.502 1
10001 18017 "36720410"                 8.5    .04462098702788353 2 2009  4 197  1.0391197   2.0391197 1004     8.5 1
10001 18046 "36720410"   8.479999542236328   .002941122744232416 2 2009  5 197    .997647    1.997647 1004     8.5 1
10001 18078 "36720410"   8.599900245666504   .019445838406682014 2 2009  6 197  1.0141392   2.0141392 1004     8.5 1
10001 18109 "36720410"   8.149999618530273   -.04708201438188553 3 2009  7 198   .9476854   1.9476854 1004    8.15 1
10001 18140 "36720410"   8.539999961853027     .0533742792904377 3 2009  8 198  1.0478528   2.0478528 1004    8.15 1
10001 18170 "36720410"   8.599900245666504   .012283406220376492 3 2009  9 198   1.007014    2.007014 1004    8.15 1
10001 18200 "36720410"   8.880000114440918    .03780274838209152 4 2009 10 199  1.0325701   2.0325701 1004    8.88 1
10001 18231 "36720410"    8.89799976348877    .00709455506876111 4 2009 11 199   1.002027    2.002027 1004    8.88 1
10001 18262 "36720410"  10.300000190734863    .16262087225914001 4 2009 12 199  1.1575636   2.1575637 1004    8.88 1
10001 18291 "36720410"    10.0600004196167   -.01893201656639576 1 2010  1 200   .9766991    1.976699 1005   10.06 1
10001 18319 "36720410"  10.008399963378906 -.0006561087793670595 1 2010  2 200   .9948707   1.9948707 1005   10.06 1
10001 18352 "36720410"  10.170000076293945    .02064267173409462 1 2010  3 200  1.0161464   2.0161464 1005   10.06 1
10001 18382 "36720410"  11.390000343322754    .12438547611236572 2 2010  4 201  1.1199607   2.1199608 1005   11.39 1
10001 18410 "36720410"  11.399999618530273   .004828733392059803 2 2010  5 201  1.0008779   2.0008779 1005   11.39 1
10001 18443 "36720410"  10.859999656677246  -.043421052396297455 2 2010  6 201   .9526316   1.9526316 1005   11.39 1
10001 18473 "36720410"  11.720000267028809    .08333339542150497 3 2010  7 202  1.0791898   2.0791898 1005   11.72 1
10001 18505 "36720410"  10.371000289916992   -.11126279085874557 3 2010  8 202   .8848976   1.8848976 1005   11.72 1
10001 18535 "36720410"  11.119999885559082    .07655959576368332 3 2010  9 202  1.0722206   2.0722206 1005   11.72 1
10001 18564 "36720410"  11.439900398254395    .03281479328870773 4 2010 10 203  1.0287681    2.028768 1005 11.4399 1
10001 18596 "36720410"  10.039999961853027   -.11843638122081757 4 2010 11 203     .87763     1.87763 1005 11.4399 1
10001 18627 "36720410"  10.520000457763672   .052290886640548706 4 2010 12 203  1.0478088   2.0478086 1005 11.4399 1
10001 18658 "36720410"  10.779999732971191    .02899232506752014 1 2011  1 204  1.0247147   2.0247147 1005   10.78 1
10001 18686 "36720410"  10.979999542236328   .022727256640791893 1 2011  2 204  1.0185529   2.0185528 1005   10.78 1
10001 18717 "36720410"  11.729999542236328    .07240437716245651 1 2011  3 204   1.068306    2.068306 1005   10.78 1
10001 18746 "36720410"  11.229999542236328  -.038789428770542145 2 2011  4 205   .9573743   1.9573743 1005   11.23 1
end
format %d date
format %tq fdate

Tags: None

Clyde Schechter

Join Date: Apr 2014

Posts: 30119
#2

15 Mar 2017, 14:41

Say in the example below the return for firmID=10001 in "fdate"=2003q3 will be equal to the division of the prices (6.01/8.4399)-1 (the last price in 2003q2/the first price in 2003q1).

Please clarify. Looking at your data, the last price in 2003q2 is, indeed 6.01, but the first price in 2003q1 is 6.12 (to two decimal places. The number you site, 8.4399 is just the immediately preceding price in 2003q2. So what do you actually want here?
Comment
Alberto Alvarez

Join Date: Jul 2016

Posts: 195
#3

15 Mar 2017, 15:00

Thank you for the reply. What I meant is the return for firmID=10001 in "fdate"=2003q3 will be equal to Price (at fdate=2003q2 (month 3)) / Price(at fdate=2003q1 (month 1)), so that there is a 6 month window.

Sorry for misinterpretation.
Comment
Clyde Schechter

Join Date: Apr 2014

Posts: 30119
#4

15 Mar 2017, 15:16

I'm sorry, but now I'm even more confused. The interval from the third month of 2003q2 to the first month of 2003q1 is only 1 month, not 6.

The following code will get you a 6 month forward return on every observation in your data set. Each quarter will then have three different observations, one for each month. I gather you want to select one of these (not clear to me at all which one) to apply to the entire quarter. But the code to do this last part can be tacked on at the end, once it's clear what is wanted.

Code:

// CREATE A MONTHLY DATE gen mdate = mofd(date) format mdate %tm // XTSET SO WE CAN USE FORWARD OPERATOR // AND AVOID PROBLEMS IF THERE ARE ANY DATE GAPS xtset permno mdate // CALCUALTE 6 MONTH RETURN AT EVERY DATE gen six_month_return = F6.prc/prc
Comment

Alberto Alvarez

Join Date: Jul 2016
Posts: 195

15 Mar 2017, 16:02

Sorry, let me clarify once again the problem. What I need is to calculate cumulative 6 month return that are preceding each quarter. In the example below I have firmID=17750. So, for each year+quarter (fdate) I will have a past 6 month cumulative return. Say, for fdate=2011q3 I will have a cumulative return that is equal to the price(in fdate=2011q1, 1st month of that quarter) / price(in fdate=2011q2, last month in that quarter). So, in total, I will have cumulative return for past six months starting from 2011q1 January till 2011q2 June.

Hope, this will make sense. Sorry for confusions.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double prc float(year month fdate)
17750 18658   64.7300033569336 2011  1 204
17750 18686   65.9000015258789 2011  2 204
17750 18717   65.2699966430664 2011  3 204
17750 18746  66.05999755859375 2011  4 205
17750 18778  68.30000305175781 2011  5 205
17750 18808  66.55999755859375 2011  6 205
17750 18837  65.36000061035156 2011  7 206
17750 18870  69.16000366210938 2011  8 206
17750 18900  71.01000213623047 2011  9 206
17750 18931  69.70999908447266 2011 10 207
17750 18961  71.47000122070313 2011 11 207
17750 18991  73.55999755859375 2011 12 207
17750 19023  71.55999755859375 2012  1 208
17750 19052  72.87999725341797 2012  2 208
17750 19082  73.88999938964844 2012  3 208
17750 19113  78.47000122070313 2012  4 209
17750 19144   79.3499984741211 2012  5 209
17750 19173   83.7699966430664 2012  6 209
17750 19205  86.91000366210938 2012  7 210
17750 19236   83.5999984741211 2012  8 210
17750 19264  85.77999877929688 2012  9 210
17750 19297  83.44999694824219 2012 10 211
17750 19327  85.72000122070313 2012 11 211
17750 19358  84.43000030517578 2012 12 211
17750 19389  89.51000213623047 2013  1 212
17750 19417  94.27999877929688 2013  2 212
17750 19445   97.9800033569336 2013  3 212
17750 19478 103.19000244140625 2013  4 213
17750 19509  96.83000183105469 2013  5 213
17750 19537  97.13999938964844 2013  6 213
17750 19570  98.80000305175781 2013  7 214
17750 19600   93.4800033569336 2013  8 214
17750 19631  94.22000122070313 2013  9 214
17750 19662                108 2013 10 215
17750 19691 109.16000366210937 2013 11 215
17750 19723 104.45999908447266 2013 12 215
17750 19754 109.37000274658203 2014  1 216
17750 19782  110.3499984741211 2014  2 216
17750 19813             110.25 2014  3 216
17750 19843             112.25 2014  4 217
17750 19873  112.3499984741211 2014  5 217
17750 19904 111.22000122070312 2014  6 217
17750 19935 103.87000274658203 2014  7 218
17750 19964                108 2014  8 218
17750 19996 107.56999969482422 2014  9 218
17750 20027  114.2699966430664 2014 10 219
17750 20055 116.58999633789062 2014 11 219
17750 20088 115.54000091552734 2014 12 219
end
format %d date
format %tq fdate

Comment

Clyde Schechter

Join Date: Apr 2014
Posts: 30119

15 Mar 2017, 17:31

I think I understand it now. If so:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date double prc float(year month fdate)
17750 18658   64.7300033569336 2011  1 204
17750 18686   65.9000015258789 2011  2 204
17750 18717   65.2699966430664 2011  3 204
17750 18746  66.05999755859375 2011  4 205
17750 18778  68.30000305175781 2011  5 205
17750 18808  66.55999755859375 2011  6 205
17750 18837  65.36000061035156 2011  7 206
17750 18870  69.16000366210938 2011  8 206
17750 18900  71.01000213623047 2011  9 206
17750 18931  69.70999908447266 2011 10 207
17750 18961  71.47000122070313 2011 11 207
17750 18991  73.55999755859375 2011 12 207
17750 19023  71.55999755859375 2012  1 208
17750 19052  72.87999725341797 2012  2 208
17750 19082  73.88999938964844 2012  3 208
17750 19113  78.47000122070313 2012  4 209
17750 19144   79.3499984741211 2012  5 209
17750 19173   83.7699966430664 2012  6 209
17750 19205  86.91000366210938 2012  7 210
17750 19236   83.5999984741211 2012  8 210
17750 19264  85.77999877929688 2012  9 210
17750 19297  83.44999694824219 2012 10 211
17750 19327  85.72000122070313 2012 11 211
17750 19358  84.43000030517578 2012 12 211
17750 19389  89.51000213623047 2013  1 212
17750 19417  94.27999877929688 2013  2 212
17750 19445   97.9800033569336 2013  3 212
17750 19478 103.19000244140625 2013  4 213
17750 19509  96.83000183105469 2013  5 213
17750 19537  97.13999938964844 2013  6 213
17750 19570  98.80000305175781 2013  7 214
17750 19600   93.4800033569336 2013  8 214
17750 19631  94.22000122070313 2013  9 214
17750 19662                108 2013 10 215
17750 19691 109.16000366210937 2013 11 215
17750 19723 104.45999908447266 2013 12 215
17750 19754 109.37000274658203 2014  1 216
17750 19782  110.3499984741211 2014  2 216
17750 19813             110.25 2014  3 216
17750 19843             112.25 2014  4 217
17750 19873  112.3499984741211 2014  5 217
17750 19904 111.22000122070312 2014  6 217
17750 19935 103.87000274658203 2014  7 218
17750 19964                108 2014  8 218
17750 19996 107.56999969482422 2014  9 218
17750 20027  114.2699966430664 2014 10 219
17750 20055 116.58999633789062 2014 11 219
17750 20088 115.54000091552734 2014 12 219
end
format %d date
format %tq fdate

// CREATE A MONTHLY DATE VARIABLE
gen int mdate = mofd(date)
format mdate %tm

//    CALCULATE RETURN OVER PRECEDING 6 MONTHS
//    IN EACH OBSERVATION
xtset permno mdate
by permno (mdate), sort: gen cum_ret = L1.prc/L6.prc

//    AND APPLY THE RESULT FROM THE FIRST MONTH OF EACH
//    AUARTER TO THE ENTIRE QUARTER
by fdate (mdate), sort: replace cum_ret = cum_ret[1]

Announcement

Calculation of lagged value for the current period

Comment

Comment

Comment

Comment

Comment