Question about cycles

[Lisa Tara Smith]

Best users,

For my thesis I'm partly using a paper by Shue and Townsend, that is on how stock option pay influences executive risk taking.
The most important thing is that they detect the predicted multi-year cycles of how stock options are being paid, which is also what I want to do.
They state that companies have same number cycles and same value cycles and they are on those if the executive either has the same number of options in 2 or more consecutive years or the same value of options in 2 or more consecutive years. They also state that once the companies are on a cycle, they do not change.
So, up to now I have found the actual realized cycles. Which is found with

gen actualnumber=.
bysort expercomp: replace actualnumber=1 if f.option_number==option_number

gen actualvalue=.
bysort expercomp: replace actualvalue=1 if f.option_value==option_value

which get's me this

However, I now want to conduct the predicted cycles.
So, I want to construct a variable that will start from the first year of the realized cycle (and will be noted as 1) and then every first year of the predicted cycle (also noted as 1).

which I want it to look like this, but this I just did by hand, which is not doable to do for every observation I have. Also, the cycle length per company is different so I also need to detect that firstly I'm guessing.

I would really appreciate your help.
Let me know if anything is not clear.

Thank you in advance.

Best,
Lisa

[Ken Chui]

So, I want to construct a variable that will start from the first year of the realized cycle (and will be noted as 1) and then every first year of the predicted cycle (also noted as 1).

which I want it to look like this, but this I just did by hand, which is not doable to do for every observation I have. Also, the cycle length per company is different so I also need to detect that firstly I'm guessing.

The above is unclear. The picture just showed the desired outcome without stating any rules. I'd have guessed it's adding alternate 1 and missing to the sequence. But then the part about "the cycle length per company is different" adds another level of confusion. It'd great if you can provide some logical rule to make the decision in the prenumber with minimal assumption on our common knowledge in finance.

[Lisa Tara Smith]

Best Ken,

Sorry for being unclear. Hope I can explain better.
You are right about in this case, for this id, that it would alternate 1 and missing to the sequence, starting from 2011 since that is the first year of the actual cycle, which is then also the first year of the predicted cycle.
And then since I found that for this id there was a cycle of 2 years, then the assumption is that they use a cycle of 2 years for all the years. So, therefore you get the alternation between 1 and missing.

However, it could be that I found that a company has a cycle of 3 years or more. So then I still want that the first year of the actual cycle is 1, but after that I want it to have 2 years missing and then the fourth year a value of 1 again, since then the new predicted cycle would start. That would be like so:

Since, in this case there is a cycle of 3 years (3 consecutive years of 7000 options being rewarded to the executive).

So, what I would like is a way to detect the cycles easily. So, if the company has had a cycle (minimal of 2 consecutive years the same number of options rewarded) once in all the years they were public.
Then use these cycles to get the predicted cycles and predict the first year of the new cycle.


Is this more clear?
Sorry if it is confusing.

[Andrew Musau]

Please review FAQ Advice #12 and familiarize yourself with using dataex to present data examples for you future posts. Below, I code your wanted variable 1/0 as opposed to 1/missing, but if you must have missing values, just change this in the final line of the code.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(expercomp fyear option_number)
2414 2010  135
2414 2011   80
2414 2012   80
2414 2013   17
2414 2014   17
2414 2015    0
2414 2016 26.5
2414 2017   22
2414 2018    0
2414 2019   27
2472 2010 7000
2472 2011 7000
2472 2012 7000
2472 2013 9250
2472 2014 3000
2472 2015 2250
2472 2016    0
2472 2017    0
2472 2018    0
2472 2019    0
end

*FILLIN MISSING YEARS
xtset expercomp fyear
tsfill

*IDENTIFY FIRST CYCLE & GEN CYCLE VAR
bys expercomp (fyear): gen cycle= sum(sum(option_number==option_number[_n+1] & !missing(option_number)))==1
bys expercomp (fyear): replace cycle= cycle[_n-1]+1 if cycle[_n-1]>=1 & option_number== option_number[_n-1]

*LENGTH OF CYCLES
bys expercomp (fyear): egen length=max(cycle) if sum(cycle)>0

*GEN WANTED
gen wanted= length if cycle==1
bys expercomp (fyear): replace wanted= wanted[_n-1]+1 if _n>1 & missing(wanted)
replace wanted= cond(mod(wanted, length)==0, 1,cond(missing(length), ., 0))

Res.:

. l, sepby(expercomp)

     +-------------------------------------------------------+
     | experc~p   fyear   option~r   cycle   length   wanted |
     |-------------------------------------------------------|
  1. |     2414    2010        135       0        .        . |
  2. |     2414    2011         80       1        2        1 |
  3. |     2414    2012         80       2        2        0 |
  4. |     2414    2013         17       0        2        1 |
  5. |     2414    2014         17       0        2        0 |
  6. |     2414    2015          0       0        2        1 |
  7. |     2414    2016       26.5       0        2        0 |
  8. |     2414    2017         22       0        2        1 |
  9. |     2414    2018          0       0        2        0 |
 10. |     2414    2019         27       0        2        1 |
     |-------------------------------------------------------|
 11. |     2472    2010       7000       1        3        1 |
 12. |     2472    2011       7000       2        3        0 |
 13. |     2472    2012       7000       3        3        0 |
 14. |     2472    2013       9250       0        3        1 |
 15. |     2472    2014       3000       0        3        0 |
 16. |     2472    2015       2250       0        3        0 |
 17. |     2472    2016          0       0        3        1 |
 18. |     2472    2017          0       0        3        0 |
 19. |     2472    2018          0       0        3        0 |
 20. |     2472    2019          0       0        3        1 |
     +-------------------------------------------------------+

.

[Lisa Tara Smith]

Best Andrew,

I will look into FAQ Advice 12, sorry I didn't do so before.

Thank you so much for your help! This is exactly what I needed.
Now since I have the same number option cycles, I also have to look into the same value option cycles.
This is a bit different than the number option cycles, because for these cycles it does not have to be the exact same value, but needs to be within 3%.
So, in the range of 3% below the value or 3% above.

So, for the same number cycles I needed the same number of options for consecutive years.
But now for the same value cycles, I need the value of the next year to be within either 3% above of the value of the previous year and 3% below the value of the previous year.
I thought I could maybe use: if inrange {f.option_value, option_value/1.03, option_value*1.03}
But I'm not sure how to use it in the same code you provided, such that it works the same.

Would really appreciate your help some more.

[Andrew Musau]

I cannot follow this. The best way to illustrate is to create an example by hand on how the wanted variable should look like as you did in #1 and #3, explaining by referencing the example. Be sure to use dataex when posting the example.

[Lisa Tara Smith]

input double(expercomp fyear option_value) float(vcycle vpredfirst)
79 2010 4217.489 1 1
79 2011 4280.375 0 0
79 2012 0 0 1
79 2013 0 0 0
79 2014 1954.997 1 1
79 2015 1899.996 0 0
79 2016 2423.997 1 1
79 2017 2565.005 0 0
79 2018 2999.999 1 1
79 2019 2999.996 0 0
111 2010 9164.925 0 0
111 2011 2193.374 1 1
111 2012 2159.907 0 0
111 2013 2079.871 0 0
111 2014 2535.762 1 1
111 2015 2563.088 0 0
111 2016 1573.15 0 0
111 2017 3200.989 0 1
111 2018 0 0 0
581 2010 5917.38 1 1
581 2011 5847.83 0 0
581 2012 4833.9 0 1
581 2013 5332.8 1 0
581 2014 5350.925 1 1
581 2015 5350.302 1 0
581 2016 5351.73 1 1
581 2017 5350.848 1 0
581 2018 5351.06 1 1
581 2019 5350.324 1 0
end
[/CODE]

Hope I did this right.
But this is what I would like.
So to find the cycle first: from expercomp 79 year 2010 the value is 4217.489 and to find the cycle the next year the option value should be in between 4094,6495(=4217.489/1.03) and 4344,0137(=4217.489*1.03), which is the case since the next year the value is 4280.375.

And then for 581 year 2014 we also put a vcycle=1 because the next year the value is within the 3% below and 3% above again.
because 5350.302 from year 2015 is in between 5195,0728(=5350.925/1.03) and 5511,45275(=5350.925*1.03).
And then from year 2016 5351.73 is again in the range 5350.302/1.03 and 5350.302*1.03. So, always compared to the previous year.
But for this id, we already found that there was a cycle of 2 yers in 2010 and 2011, so then I want the predicted cycle to be 2 years for all the years following.

Hopefully this makes sense.

[Andrew Musau]

There should be an error in your calculations for firm 111. Clearly, a change from 2159 to 2079 exceeds 3% in absolute terms.

. di abs(2159-2079)/2079*100
3.8480038

You can change 2079 to 2179 so that this change is within 3%.

. di abs(2159-2179)/2179*100
.91785223

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(expercomp fyear option_value) float(vcycle vpredfirst)
 79 2010 4217.489 1 1
 79 2011 4280.375 0 0
 79 2012        0 0 1
 79 2013        0 0 0
 79 2014 1954.997 1 1
 79 2015 1899.996 0 0
 79 2016 2423.997 1 1
 79 2017 2565.005 0 0
 79 2018 2999.999 1 1
 79 2019 2999.996 0 0
111 2010 9164.925 0 0
111 2011 2193.374 1 1
111 2012 2159.907 0 0
111 2013 2179.871 0 0
111 2014 2535.762 1 1
111 2015 2563.088 0 0
111 2016  1573.15 0 0
111 2017 3200.989 0 1
111 2018        0 0 0
581 2010  5917.38 1 1
581 2011  5847.83 0 0
581 2012   4833.9 0 1
581 2013   5332.8 1 0
581 2014 5350.925 1 1
581 2015 5350.302 1 0
581 2016  5351.73 1 1
581 2017 5350.848 1 0
581 2018  5351.06 1 1
581 2019 5350.324 1 0
end

bys expercomp (fyear): gen run= sum(sum(abs(option_value- option_value[_n+1])/ option_value <=0.03))==1
bys expercomp (fyear): replace run= run[_n-1]+1 if run[_n-1]>=1 & abs(option_value- option_value[_n-1])/ option_value[_n-1] <=0.03
bys expercomp (fyear): egen length= max(run)
bys expercomp (fyear): replace run=run[_n-1]+1 if !run
gen wanted= length if run==1
bys expercomp (fyear): replace wanted = wanted[_n-1]+1 if _n>1 & missing(wanted)
replace wanted= !mod(wanted, length)

Res.:

. l, sepby(expercomp)

     +-------------------------------------------------------------------------+
     | experc~p   fyear   option~e   vcycle   vpredf~t   run   length   wanted |
     |-------------------------------------------------------------------------|
  1. |       79    2010   4217.489        1          1     1        2        1 |
  2. |       79    2011   4280.375        0          0     2        2        0 |
  3. |       79    2012          0        0          1     3        2        1 |
  4. |       79    2013          0        0          0     4        2        0 |
  5. |       79    2014   1954.997        1          1     5        2        1 |
  6. |       79    2015   1899.996        0          0     6        2        0 |
  7. |       79    2016   2423.997        1          1     7        2        1 |
  8. |       79    2017   2565.005        0          0     8        2        0 |
  9. |       79    2018   2999.999        1          1     9        2        1 |
 10. |       79    2019   2999.996        0          0    10        2        0 |
     |-------------------------------------------------------------------------|
 11. |      111    2010   9164.925        0          0     .        3        0 |
 12. |      111    2011   2193.374        1          1     1        3        1 |
 13. |      111    2012   2159.907        0          0     2        3        0 |
 14. |      111    2013   2179.871        0          0     3        3        0 |
 15. |      111    2014   2535.762        1          1     4        3        1 |
 16. |      111    2015   2563.088        0          0     5        3        0 |
 17. |      111    2016    1573.15        0          0     6        3        0 |
 18. |      111    2017   3200.989        0          1     7        3        1 |
 19. |      111    2018          0        0          0     8        3        0 |
     |-------------------------------------------------------------------------|
 20. |      581    2010    5917.38        1          1     1        2        1 |
 21. |      581    2011    5847.83        0          0     2        2        0 |
 22. |      581    2012     4833.9        0          1     3        2        1 |
 23. |      581    2013     5332.8        1          0     4        2        0 |
 24. |      581    2014   5350.925        1          1     5        2        1 |
 25. |      581    2015   5350.302        1          0     6        2        0 |
 26. |      581    2016    5351.73        1          1     7        2        1 |
 27. |      581    2017   5350.848        1          0     8        2        0 |
 28. |      581    2018    5351.06        1          1     9        2        1 |
 29. |      581    2019   5350.324        1          0    10        2        0 |
     +-------------------------------------------------------------------------+

.

[Lisa Tara Smith]

Thank you so much Andrew.
Your code worked perfectly.
Appreciate it very much!