Calculating two year moving averages based on quarterly data

[Jeff Mason]

Hi,

Please find attached my data structure.

Datastream_OPermID represents a unique Firm identifier.
Compustat_CEO_ID represents the ID of the CEO that is in place in the respective quarter CC_Quarter.
CC_FY refers to the Fiscal Year of CC_Quarter
The variable of interest, XN_Cash_Abs, denotes the compensation of a CEO in a given Fiscal Year (CC_FY). As such, it remains constant for a given Firm-CEO-Year observation.

My objective is to calculate the two-year moving average of the XN_Cash_Abs variable by Datastream_OPermID Compustat_CEO_ID including the current year.

I attempted to use -tssmooth- but encountered difficulties due to quarters being nested within Fiscal Years. Consequently, I cannot use -xtset Datastream_OPermID CC_FY.

Any help is much appreciated!

* Example generated by -dataex-. For more info, type help dataex
clear
input double Datastream_OPermID str28 Compustat_C_Name long Compustat_CEO_ID str48 Compustat_CEO_FullName int(CC_FY CC_Quarter) float XN_Cash_Abs
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 200  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 201  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 202  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 203  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 204      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 205      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 206      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 207      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 208 2907.923
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 209 2907.923
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 210 2907.923
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 216      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 217      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 218      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 219      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 220  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 221  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 222  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 223  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 224     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 225     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 226     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 227     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 228      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 229      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 230      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 231      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 232    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 233    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 234    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 235    997.5
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 200      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 201      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 202      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 203      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 204      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 205      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 206      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 207      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 208      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 209      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 210      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 211      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 212     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 213     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 214     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 215     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 216     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 217     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 218     1000
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 224      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 225      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 226      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 227      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 228      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 229      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 230      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 231      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 232  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 233  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 234  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 235  979.039
end
format %tq CC_Quarter

[Nick Cox]

I think your data structure rules out any easy use of xtset, tsset or anything dependent on those commands.

On the other hand, a two-year average of yearly values is just the average of two values, which seems to yield to other technique. This may help.

* Example generated by -dataex-. For more info, type help dataex
clear
input double Datastream_OPermID str28 Compustat_C_Name long Compustat_CEO_ID str48 Compustat_CEO_FullName int(CC_FY CC_Quarter) float XN_Cash_Abs
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 200  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 201  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 202  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2010 203  809.135
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 204      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 205      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 206      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2011 207      850
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 208 2907.923
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 209 2907.923
4295899323 "CBRE GROUP INC" 30552 "William Brett White"  2012 210 2907.923
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 216      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 217      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 218      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2014 219      875
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 220  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 221  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 222  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2015 223  1574.63
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 224     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 225     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 226     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2016 227     1490
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 228      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 229      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 230      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2017 231      990
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 232    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 233    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 234    997.5
4295899323 "CBRE GROUP INC" 31112 "Robert E. Sulentic"   2018 235    997.5
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 200      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 201      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 202      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2010 203      950
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 204      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 205      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 206      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2011 207      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 208      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 209      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 210      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2012 211      975
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 212     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 213     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 214     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2013 215     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 216     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 217     1000
4295899478 "ASSURANT INC"   31140 "Robert Brian Pollock" 2014 218     1000
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 224      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 225      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 226      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2016 227      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 228      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 229      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 230      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2017 231      955
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 232  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 233  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 234  979.039
4295899478 "ASSURANT INC"   43067 "Alan B. Colberg"      2018 235  979.039
end
format %tq CC_Quarter

bysort Datastream_OPermID Compustat_CEO_ID (CC_FY CC_Quarter) : gen wanted = (XN_Cash_Abs + XN_Cash_Abs[_n-1])/2 if CC_FY != CC_FY[_n-1]

bysort Datastream_OPermID Compustat_CEO_ID CC_FY (CC_Quarter) : replace wanted = wanted[1] 

egen tag = tag(Datastream_OPermID Compustat_CEO_ID CC_FY)

format Datastream_OPermID %10.0f 

list  Datastream_OPermID Compustat_CEO_ID CC_FY XN_Cash_Abs wanted if tag, noobs sepby(*_ID)

  +-----------------------------------------------------+
  | Datastre~D   Compus~D   CC_FY   XN_Cas~s     wanted |
  |-----------------------------------------------------|
  | 4295899323      30552    2010    809.135          . |
  | 4295899323      30552    2011        850   829.5675 |
  | 4295899323      30552    2012   2907.923   1878.962 |
  |-----------------------------------------------------|
  | 4295899323      31112    2014        875          . |
  | 4295899323      31112    2015    1574.63   1224.815 |
  | 4295899323      31112    2016       1490   1532.315 |
  | 4295899323      31112    2017        990       1240 |
  | 4295899323      31112    2018      997.5     993.75 |
  |-----------------------------------------------------|
  | 4295899478      31140    2010        950          . |
  | 4295899478      31140    2011        975      962.5 |
  | 4295899478      31140    2012        975        975 |
  | 4295899478      31140    2013       1000      987.5 |
  | 4295899478      31140    2014       1000       1000 |
  |-----------------------------------------------------|
  | 4295899478      43067    2016        955          . |
  | 4295899478      43067    2017        955        955 |
  | 4295899478      43067    2018    979.039   967.0195 |
  +-----------------------------------------------------+

.

[Nick Cox]

As there may be gaps, the code

 
 gen wanted = (XN_Cash_Abs + XN_Cash_Abs[_n-1])/2 if CC_FY != CC_FY[_n-1]

would be better as

  
 gen wanted = (XN_Cash_Abs + XN_Cash_Abs[_n-1])/2 if CC_FY == CC_FY[_n-1] + 1

[Jeff Mason]

Thanks, Nick!