Panel data, generate lag of previous timeperiod

[Jesper Eriksson]

Hi,

I want to calculate the change between a firms quarterly earnings per share (EPS). It is a panel dataset, dataex below. My problem is that i can not use xtset since there is repeated time values within panel. The reason is that dates are that date is the timevar and not period. The complete dataset is 4 million obs.

So my goal is to get the EPS change (percent or ratio does not matter) between different quarters (period). For example ID 6, year 2020, period 3. The change from period 2 to 3 would be (- 0.0571 - -0.0571) / abs(-0.0674) = .15281899 Nevermind the use of abs, that is only since there are negative EPS.

My question is how to get the lags of period to use in calculations of the change.

For the first lag (period / period[_n-1]) I have used the following code which works fine.
gen EPS_growth_temp =.
replace EPS_growth_temp = ((earnings_Per_Share- earnings_Per_Share[_n-1]) / abs(earnings_Per_Share[_n-1]))
gen EPSgrowth_L1 =.
bysort ID (year period date): gen count = 1 if period!= period[_n-1]
bysort ID (year period date): replace EPSgrowth_L1 = EPS_growth_temp if count==1
bysort ID (year period date): carryforward EPSgrowth_L1 if period==period[_n-1], replace

But it gets more difficult when I want the change between period 2 and 4. Or between year 2020 period 4 and year 2021 period 3. Some periods does not have any data on EPS (missing), these would of course not be usable in calculating the change between periods. In those cases I simply want the change to be missing as well. Sorry for long dataex example but I wanted to show the structure of my timevar.

Ideas how to identify lags of period?

* Example generated by -dataex-. For more info, type help dataex
clear
input int(ID date) double(Openprice_ Highprice_ Lowprice_ Closeprice_ Volume_) int year byte period double earnings_Per_Share
6 22223  1.616   1.62  1.564  1.576  318637 2020 2 -.0674
6 22224   1.55   1.55    1.4    1.5  556959 2020 3 -.0571
6 22225   1.49   1.49   1.45  1.482  161633 2020 3 -.0571
6 22228  1.504  1.576   1.47  1.494  340417 2020 3 -.0571
6 22229    1.5   1.51   1.49    1.5  130807 2020 3 -.0571
6 22230    1.5  1.522  1.482    1.5  127507 2020 3 -.0571
6 22231    1.5  1.502  1.484   1.49   81894 2020 3 -.0571
6 22232    1.5  1.504  1.488  1.494   40279 2020 3 -.0571
6 22235   1.55  1.576   1.48  1.482  317386 2020 3 -.0571
6 22236  1.506  1.506  1.484  1.498  121103 2020 3 -.0571
6 22237  1.498  1.498  1.472  1.476  181516 2020 3 -.0571
6 22238   1.48   1.49   1.44  1.448  188134 2020 3 -.0571
6 22239  1.448  1.524  1.442  1.444  442160 2020 3 -.0571
6 22242   1.45  1.566   1.45  1.552  591735 2020 3 -.0571
6 22243  1.556  2.335  1.556  2.265 5251417 2020 3 -.0571
6 22244    2.3  2.665   1.75   1.78 7251798 2020 3 -.0571
6 22245    1.8   1.89  1.722   1.73 1113992 2020 3 -.0571
6 22246  1.744  1.788  1.624  1.628 1047698 2020 3 -.0571
6 22249   1.63  1.724  1.618   1.64  720048 2020 3 -.0571
6 22250  1.642  1.698  1.588  1.606  706321 2020 3 -.0571
6 22251  1.604  1.636   1.56  1.578  656437 2020 3 -.0571
6 22252  1.572   1.58  1.552  1.552  720120 2020 3 -.0571
6 22253  1.552  1.592  1.522   1.53  395372 2020 3 -.0571
6 22256  1.592  1.592   1.51  1.516  560578 2020 3 -.0571
6 22257   1.52   1.55   1.46   1.49  732876 2020 3 -.0571
6 22258   1.49   1.49  1.452  1.452  348895 2020 3 -.0571
6 22259  1.452  1.458  1.428  1.442  486312 2020 3 -.0571
6 22260  1.442  1.618  1.412   1.45 3559569 2020 3 -.0571
6 22263   1.58   1.58   1.49   1.55 1675592 2020 3 -.0571
6 22264   1.59    1.6   1.51  1.556  875799 2020 3 -.0571
6 22265  1.562   1.63  1.562  1.588  906846 2020 3 -.0571
6 22266   1.59  1.608   1.55  1.576  437941 2020 3 -.0571
6 22267  1.574    1.9   1.52    1.9 7633764 2020 3 -.0571
6 22270  1.958  2.335   1.95   2.15 9125467 2020 3 -.0571
6 22271    2.2   2.35    2.2  2.205 3888561 2020 3 -.0571
6 22272   2.25  2.295  1.962  1.998 3593766 2020 3 -.0571
6 22277   1.95   1.98  1.772  1.884 4843412 2020 3 -.0571
6 22278  1.886   2.08  1.886  2.025 2492877 2020 3 -.0571
6 22279    1.9      2  1.818   1.89 2737873 2020 3 -.0571
6 22284   1.89  1.928    1.8  1.802 1494043 2020 3 -.0571
6 22285  1.804  2.085  1.804    1.9 2750381 2020 3 -.0571
6 22287    1.9  1.948  1.804  1.816 1782600 2020 3 -.0571
6 22288  1.816   1.85   1.71   1.74 1278867 2020 3 -.0571
6 22291   1.74  1.766   1.65  1.704  916765 2020 3 -.0571
6 22292  1.704  1.704   1.64   1.65  695137 2020 3 -.0571
6 22293   1.65  1.696   1.64  1.674  388513 2020 3 -.0571
6 22294  1.654  1.698  1.648   1.68  310787 2020 3 -.0571
6 22295   1.68   1.68  1.602  1.652  659967 2020 3 -.0571
6 22298  1.652   1.79  1.642  1.764 1749463 2020 3 -.0571
6 22299  1.764  1.848    1.7    1.8  896222 2020 3 -.0571
6 22300   1.82    1.9  1.806    1.9 1927172 2020 3 -.0571
6 22301    1.9    1.9  1.766  1.796 1781068 2020 3 -.0571
6 22302  1.836  1.878    1.7   1.74  955223 2020 3 -.0571
6 22305   1.74   1.77   1.67  1.676  605463 2020 3 -.0571
6 22306  1.692  1.726   1.64  1.662  970406 2020 3 -.0571
6 22307  1.662  1.778   1.64   1.65  983453 2020 3 -.0571
6 22308  1.652  1.678   1.57  1.598 1111780 2020 3 -.0571
6 22309   1.65  1.696  1.598  1.632  751759 2020 3 -.0571
6 22312  1.632   1.69  1.632   1.65  526294 2020 3 -.0571
6 22313  1.652  1.688   1.65   1.66  268544 2020 3 -.0571
6 22314   1.66   1.67  1.622   1.67  351139 2020 3 -.0571
6 22315   1.66  1.796    1.6  1.666 2798248 2020 3 -.0571
6 22316  1.672  1.744  1.636  1.686  690421 2020 3 -.0571
6 22319  1.654  1.686  1.642   1.65  369869 2020 3 -.0571
6 22320   1.65  1.738   1.65  1.678  690374 2020 3 -.0571
6 22321  1.676  1.676   1.61   1.64 1051194 2020 3 -.0571
6 22322  1.616   1.68  1.616   1.63  549376    . .      .
6 22323   1.63  1.652  1.626  1.634  354421    . .      .
6 22326  1.634  1.646   1.61  1.638  358732    . .      .
6 22327   1.65  1.658  1.622   1.64  475426    . .      .
6 22328   1.64   1.65    1.6   1.64  801449    . .      .
6 22329   1.64   1.64  1.588   1.59  496219    . .      .
6 22330   1.61   1.61  1.488   1.59  594959    . .      .
6 22333   1.59   1.59   1.55  1.562  476557    . .      .
6 22334   1.58  1.588    1.5    1.5  361753    . .      .
6 22335    1.5   1.58  1.494   1.56  318664    . .      .
6 22336  1.564  1.564   1.53   1.56  149451    . .      .
6 22337   1.53  1.566    1.5  1.566  545843    . .      .
6 22340  1.566  1.624  1.566    1.6  575837    . .      .
6 22341  1.618  1.632   1.59  1.624  867914    . .      .
6 22342   1.63   1.63   1.59  1.594  172840    . .      .
6 22343    1.6    1.6   1.55   1.56  252311    . .      .
6 22344   1.55  1.578   1.54  1.548  196193    . .      .
6 22347  1.548  1.574    1.5  1.518  382893    . .      .
6 22348  1.518  1.556   1.49  1.502  504994    . .      .
6 22349   1.54   1.57  1.482  1.492  582248    . .      .
6 22350  1.494   1.54  1.494  1.496  327459    . .      .
6 22351  1.494  1.546  1.486   1.51  501987    . .      .
6 22354  1.538  1.538  1.496  1.528  179604    . .      .
6 22355   1.51   1.53    1.5  1.514  281357    . .      .
6 22356  1.514  1.528    1.5  1.502  244945    . .      .
6 22357  1.528  1.528    1.5    1.5  439056    . .      .
6 22358  1.502   1.51   1.49   1.49  221452    . .      .
6 22361  1.496  1.496   1.45   1.48  488889    . .      .
6 22362    1.5    1.5   1.44   1.44  302099    . .      .
6 22363   1.43  1.464  1.394   1.45  425319    . .      .
6 22364   1.45  1.464   1.44   1.45  220003    . .      .
6 22365  1.452   1.47   1.44  1.452  297846    . .      .
6 22368  1.452  1.482   1.45   1.47  184607    . .      .
6 22369   1.48  1.486   1.41   1.42  474923    . .      .
6 22370   1.42   1.46  1.394  1.398 1312832    . .      .
6 22371  1.402   1.44    1.4  1.428  313559    . .      .
6 22376  1.428   1.43   1.39   1.39  500203    . .      .
6 22377   1.39   1.41   1.38   1.38  355734    . .      .
6 22378  1.392   1.42  1.392  1.404  158620    . .      .
6 22379   1.42  1.424    1.4  1.412  279041    . .      .
6 22382  1.412   1.42  1.402  1.404  188944    . .      .
6 22383  1.402   1.42    1.4    1.4  299901    . .      .
6 22384    1.4   1.42  1.392    1.4  203786    . .      .
6 22385  1.398   1.43  1.396   1.43  793750    . .      .
6 22386  1.472   1.53  1.472  1.478  548890    . .      .
6 22389  1.578  1.662    1.5    1.5 2507303    . .      .
6 22390  1.572  1.582  1.442   1.45  504680    . .      .
6 22391   1.45  1.492   1.42  1.434  262937    . .      .
6 22392  1.408  1.418   1.37   1.39  685555 2021 1 -.0491
6 22393  1.402   1.49    1.4  1.452  481631 2021 1 -.0491
6 22396  1.456   1.63  1.456    1.5 2671461 2021 1 -.0491
6 22397    1.6    1.6  1.504   1.53 1560942 2021 1 -.0491
6 22398   1.55  1.568    1.5   1.52 1222995 2021 1 -.0491
7 22363 59.125  60.75 59.125  60.75   73100 2020 4  .4113
7 22364   60.5 60.875     60 60.625  273212 2020 4  .4113
7 22365 60.625 61.375   60.5 61.125   39808 2020 4  .4113
7 22368 61.125  62.25 60.875  61.25   35736 2020 4  .4113
7 22369 62.375     65  62.25  64.75  110816 2020 4  .4113
7 22370  64.75   65.5 63.625   65.5  501240 2020 4  .4113
7 22371   65.5   66.5 62.625   66.5   28704 2020 4  .4113
7 22376     70     70 66.125 66.875   28292 2020 4  .4113
7 22377  66.25  67.25   65.5 66.875   33660 2020 4  .4113
7 22378 66.875 67.875  66.25   67.5   35544 2020 4  .4113
7 22379 67.375 68.125 66.375 67.875   28908 2020 4  .4113
7 22382 67.875     68     67  67.75   45396 2020 4  .4113
7 22383  67.75     68 66.125  67.75   72200 2020 4  .4113
7 22384   67.5 67.875     67   67.5  305204 2020 4  .4113
7 22385   67.5   68.5 66.625 68.125   67696 2020 4  .4113
7 22386 68.125   70.5 68.125 70.375  179364 2020 4  .4113
7 22389 70.375 70.375 69.875 70.375   57084 2020 4  .4113
7 22390   70.5   70.5   67.5  69.75  130960 2020 4  .4113
7 22391  68.75  71.25 68.625 70.875  247440 2020 4  .4113
7 22392  71.25   72.5 71.125     72   72428 2020 4  .4113
7 22393     72 72.125  71.25 72.125   27144 2020 4  .4113
7 22396 71.625 72.375 71.625 72.375   22100 2020 4  .4113
7 22397 72.375     73 71.875   72.5  298896 2020 4  .4113
7 22398  71.25 72.875 70.875  71.25   83120 2020 4  .4113
7 22399 68.125 72.875   67.5 72.375  320408 2021 1  .4038
7 22400 72.875 72.875     72  72.25   33060 2021 1  .4038
7 22403   72.5  72.75 71.875  72.75  812676 2021 1  .4038
7 22404     73 73.625 71.875 73.625  168924 2021 1  .4038
7 22405     72  73.75     71  73.75  106320 2021 1  .4038
8 22309    113    114    110    111  209930 2021 2  .6545
8 22312    112    114    112    114  138223 2021 2  .6545
8 22313    115    116    114    115  147223 2021 2  .6545
8 22314    115    119    114    118  413694 2021 2  .6545
8 22315    116    122    115    119  412732 2021 3  .6245
8 22316    122    130    122    129  922991 2021 3  .6245
8 22319    129    131    127    129  384778 2021 3  .6245
8 22320    130    130    124    126  370397 2021 3  .6245
8 22321    126    128    125    126  381815 2021 3  .6245
8 22322    126    129    125    128  223444 2021 3  .6245
8 22323    128    128    126    128  403757 2021 3  .6245
8 22326    128    130    127    129  171995 2021 3  .6245
8 22327    130    131    127    127  215647 2021 3  .6245
8 22328    128    128    121    122  360394 2021 3  .6245
8 22329    122    123    119    119  223070 2021 3  .6245
8 22330    120    123    119    122  195540 2021 3  .6245
8 22333    122    123    119    121  207398 2021 3  .6245
8 22334    122    122    117    121  314393 2021 3  .6245
8 22335    120    122    120    120  138541 2021 3  .6245
8 22336    121    126    120    123  295523 2021 3  .6245
8 22337    122    123    120    123  207007 2021 3  .6245
8 22340    124    128    122    128  433177 2021 3  .6245
8 22341    128    131    128    128  233997 2021 3  .6245
8 22342    128    130    125    126  209286 2021 3  .6245
8 22343    125    125    121    123  224817 2021 3  .6245
8 22344    123    124    120    122  225956 2021 3  .6245
8 22347    122    126    121    125  217862 2021 3  .6245
8 22348    125    126    123    126  146915 2021 3  .6245
8 22349    126    129    125    128  189075 2021 3  .6245
8 22350    129    135    129    134  761418 2021 3  .6245
8 22351    134    138    132    134  153486 2021 3  .6245
8 22354    135    136    126    128  411701 2021 3  .6245
8 22355    128    134    128    131  573206 2021 3  .6245
8 22356    131    131    126    129  316477 2021 3  .6245
8 22357    130    133    129    132  329909 2021 3  .6245
8 22358    132    135    130    133  351085 2021 3  .6245
8 22361    133    133    131    132   93670 2021 3  .6245
8 22362    131    132    126    129  333020 2021 3  .6245
8 22363    129    132    127    132  307912 2021 3  .6245
8 22364    132    134    128    130  227027 2021 3  .6245
8 22365    131    131    127    128  200763 2021 3  .6245
8 22368    128    130    127    129  136746 2021 3  .6245
8 22369    129    131    128    131  170118 2021 3  .6245
8 22370    131    134    129    130  238567 2021 3  .6245
8 22371    130    133    130    133  144981 2021 3  .6245
8 22376    134    142    133    140  609399 2021 3  .6245
8 22377    140    140    137    139  130792 2021 3  .6245
8 22378    140    142    138    139  257684 2021 3  .6245
8 22379    139    142    139    141  223561 2021 3  .6245
8 22382    141    141    138    139  172870 2021 3  .6245
8 22383    140    143    139    142  577073 2021 3  .6245
8 22384    143    144    143    143  133113 2021 3  .6245
8 22385    143    147    143    147  245329 2021 3  .6245
8 22386    147    152    147    150  317224 2021 3  .6245
8 22389    150    151    148    150  769579 2021 3  .6245
8 22390    151    153    146    147  502530 2021 3  .6245
8 22391    147    149    144    148  186054 2021 3  .6245
8 22392    148    151    148    150  179813 2021 3  .6245
8 22393    150    153    149    152  167526 2021 3  .6245
8 22396    153    154    149    149  220615 2021 3  .6245
8 22397    150    153    148    151  381701 2021 3  .6245
8 22398    151    152    148    150  298634 2021 3  .6245
8 22399    151    154    149    150  231149 2021 3  .6245
8 22400    150    151    147    148  121221 2021 3  .6245
8 22403    148    150    147    149  263405 2021 3  .6245
8 22404    148    149    143    144  270294 2021 3  .6245
8 22405    146    152    146    152  335253 2021 3  .6245
8 22406    151    153    146    149  299666 2021 3  .6245
8 22407    151    154    148    154  268215 2021 3  .6245
8 22410    155    157    150    154  521237 2021 3  .6245
8 22411    152    153    145    147  406494 2021 3  .6245
8 22412    147    148    144    146  152553 2021 3  .6245
8 22414    147    148    141    143  387640 2021 3  .6245
8 22417    144    144    140    142  313675 2021 3  .6245
8 22418    135    139    131    137  561801 2021 4  .6803
end
format %td date

[Clyde Schechter]

I'm completely confused by your data and explanation.

It looks like year and period define a calendar quarter: period is 1, 2, 3, or 4. But it turns out that there is not even one observation in the data where date falls within the quarter defined by year and period. So what's that about?

Next,

The change from period 2 to 3 would be (- 0.0571 - -0.0571) / abs(-0.0674) = .15281899

I can't make sense of that. For period 2 of 2020 the earnings for ID 6 is -.0674, not .0571. And if you did mean -0.0571, then the calculated change would be 0, right?

Please clarify.

[Jesper Eriksson]

I'm completely confused by your data and explanation.

It looks like year and period define a calendar quarter: period is 1, 2, 3, or 4. But it turns out that there is not even one observation in the data where date falls within the quarter defined by year and period. So what's that about?

Thanks Clyde for trying to help out. I´ll try to clarify.
year and period define a calender year, that is correct. 4 periods ("quarters") in one year, each period is (roughly) 3 months. However, each firm decides when their quarter starts and ends. Hence the variable name period instead of quarter. The result is periods starting on different dates for different IDs, and further that for example january, february and march (which would be considered quarter 1) does not (in many cases) correspond to period 1. Jan-march could be period 1 for one firm and period 4 for another. Or period 1 could be feb-april for one firm. However always 3 months in one period. Moreover, for a certain year, period 1 is always 3 months before period 2. Period 2 is always dates before period 3. Consider period to be a 3 month reporting period for a firm.

Next,

I can't make sense of that. For period 2 of 2020 the earnings for ID 6 is -.0674, not .0571. And if you did mean -0.0571, then the calculated change would be 0, right?

Please clarify.

My typo. Sorry. The earnings per share for ID 6, year 2020, period 2 is -.0674. The earnings per share for ID 6, year 2020, period 3 is -.0571. You are (as always) correct. The correct wording should be: ((-.0571 - -.0674) / abs( -.0674)) = .15281899
Can't believe I always make some irritating error despite proof-reading posts twice.


My problem is that I can not define the lag of period in order to calculate change between different periods. Between period 2 and 1 a certain year, but also between year 2020 period 1 and year 2019 period 4. And further for periods 2 steps apart, for example period 3 and 1. Or the change between year 2020 period 1 and year 2019 period 3. And for periods 3 steps apart and so on...

Hope I could bring some clarity to my example.

All the best,

Jesper

[Clyde Schechter]

Thanks. Now I understand where this is coming from. This should do it:

//  CREATE A SINGLE DATE VARIABLE DEFINING THE PERIODS THAT PRESERVES ORDER:
gen int pdate = yq(year, period)
format pdate %tq

//  VERIFY THAT EARNINGS ARE CONSTANT WITHIN FIRM#PERIOD
by ID pdate (earnings_Per_Share), sort: assert earnings_Per_Share[1] ///
    == earnings_Per_Share[_N]
    
//  PUT ID, PDATE, AND EARNINGS_PER_SHARE INTO A NEW FRAME
frame put ID pdate earnings_Per_Share, into(period_earnings)
frame change period_earnings
duplicates drop
xtset ID pdate, quarterly
gen rel_diff = D1.earnings_Per_Share/L1.earnings_Per_Share

//  BRING THE CALCULATED RELATIVE DIFFERENCES BACK TO ORIGINAL DATA SET
frame change default
frlink m:1 ID pdate, frame(period_earnings)
frget rel_diff, from(period_earnings)

//  CLEAN UP
drop period_earnings
frame drop period_earnings

Notes:

1. The code uses frames, so it requires version 16 or later.
2. The code assumes, and verifies, that the value of earnings_Per_Share is the same for all observations of a given ID in the same period. If that assumption is not met, code will terminate with an error message and produce no results. This constancy of earnings_Per_Share is necessary for the notion of a lagged value to even exist; so it is simply verifying that the data is in fact compatible with the requested calculation.

[Jesper Eriksson]

Thanks. Now I understand where this is coming from. This should do it:

// CREATE A SINGLE DATE VARIABLE DEFINING THE PERIODS THAT PRESERVES ORDER:
gen int pdate = yq(year, period)
format pdate %tq

// VERIFY THAT EARNINGS ARE CONSTANT WITHIN FIRM#PERIOD
by ID pdate (earnings_Per_Share), sort: assert earnings_Per_Share[1] ///
== earnings_Per_Share[_N]

// PUT ID, PDATE, AND EARNINGS_PER_SHARE INTO A NEW FRAME
frame put ID pdate earnings_Per_Share, into(period_earnings)
frame change period_earnings
duplicates drop
xtset ID pdate, quarterly
gen rel_diff = D1.earnings_Per_Share/L1.earnings_Per_Share

// BRING THE CALCULATED RELATIVE DIFFERENCES BACK TO ORIGINAL DATA SET
frame change default
frlink m:1 ID pdate, frame(period_earnings)
frget rel_diff, from(period_earnings)

// CLEAN UP
drop period_earnings
frame drop period_earnings

Notes:

1. The code uses frames, so it requires version 16 or later.
2. The code assumes, and verifies, that the value of earnings_Per_Share is the same for all observations of a given ID in the same period. If that assumption is not met, code will terminate with an error message and produce no results. This constancy of earnings_Per_Share is necessary for the notion of a lagged value to even exist; so it is simply verifying that the data is in fact compatible with the requested calculation.

Wonderful. Works like a charm. Many thanks (again) Clyde.