Estimation/Event Window

[Sophia Roemer]

Hello I want to create Estimation and Event Windows. However, i have multiple firms over multiple years with several events for each firm within the multiple years. I want to create event and estimation windows and want to know if there is an easy way. For example I have an event "MPS" and want to create a variable that counts from -3 up to 3 in the next variable is there an easy code to write this with?
Kind regards,
Sophia

[Benno Schoenberger]

There's probably a simple solution here, but without sample data, it's unlikely anyone will bother to help. Maybe take a look at https://www.statalist.org/forums/help#stata

[Sophia Roemer]

Thanks for the answer! This is my sample data. To elaborate again on my problem, I have multiple firms that are identified with the variable Ticker. These firms have daily returns over multiple years. Furthermore, I have several events within each year over several year (MPS variable). I want to build event_windows and estimation_windows around the MPS variable. However, since I have multiple events for each firm and over several timeframes I am not really sure how to build it as I am sorting my variables with Ticker, Date.
Therefore, I am trying to build an event window around MPS, from -3, 2, 1, 0, 1, 2, 3 as a variable and estimation window as variable from -10 until 10.

RD SGA Year Scope1 Date MPS event_window new_var
612 2170 2010 10700 04jan2010
612 2170 2010 10700 05jan2010
612 2170 2010 10700 06jan2010
612 2170 2010 10700 07jan2010
612 2170 2010 10700 08jan2010
612 2170 2010 10700 11jan2010
612 2170 2010 10700 12jan2010
612 2170 2010 10700 13jan2010
612 2170 2010 10700 14jan2010
612 2170 2010 10700 15jan2010
612 2170 2010 10700 19jan2010
612 2170 2010 10700 20jan2010
612 2170 2010 10700 21jan2010
612 2170 2010 10700 22jan2010 -3
612 2170 2010 10700 25jan2010 -2
612 2170 2010 10700 26jan2010 -1
612 2170 2010 10700 27jan2010 .00658228 0
612 2170 2010 10700 28jan2010 1
612 2170 2010 10700 29jan2010 2
612 2170 2010 10700 01feb2010 3
612 2170 2010 10700 02feb2010
612 2170 2010 10700 03feb2010

I hope this makes it more clear what I am aiming for.

RD SGA Year Scope1 Date MPS event_window new_var
766.1 2076.4 2016 1880 05jul2016
766.1 2076.4 2016 1880 06jul2016
766.1 2076.4 2016 1880 07jul2016
766.1 2076.4 2016 1880 08jul2016
766.1 2076.4 2016 1880 11jul2016
766.1 2076.4 2016 1880 12jul2016
766.1 2076.4 2016 1880 13jul2016 -10
766.1 2076.4 2016 1880 14jul2016 -9
766.1 2076.4 2016 1880 15jul2016 -8
766.1 2076.4 2016 1880 18jul2016 -7
766.1 2076.4 2016 1880 19jul2016 -6
766.1 2076.4 2016 1880 20jul2016 -5
766.1 2076.4 2016 1880 21jul2016 -4
766.1 2076.4 2016 1880 22jul2016 -3 -3
766.1 2076.4 2016 1880 25jul2016 -2 -2
766.1 2076.4 2016 1880 26jul2016 -1 -1
766.1 2076.4 2016 1880 27jul2016 -.01017547 0 0
766.1 2076.4 2016 1880 28jul2016 1 1
766.1 2076.4 2016 1880 29jul2016 2 2
766.1 2076.4 2016 1880 01aug2016 3 3
766.1 2076.4 2016 1880 02aug2016 4
766.1 2076.4 2016 1880 03aug2016 5
766.1 2076.4 2016 1880 04aug2016 6
766.1 2076.4 2016 1880 05aug2016 7
766.1 2076.4 2016 1880 08aug2016 8
766.1 2076.4 2016 1880 09aug2016 9
766.1 2076.4 2016 1880 10aug2016 10
766.1 2076.4 2016 1880 11aug2016
766.1 2076.4 2016 1880 12aug2016
766.1 2076.4 2016 1880 15aug2016
766.1 2076.4 2016 1880 16aug2016

[Benno Schoenberger]

Hi Sophia,

Since you still haven't been able to provide easy-to-use sample data, I have to refer you again to the Statalist FAQs, especially section 12.2. Since your "sample data" does not contain any obvious id variables and all other variables except date and MPS appear to be constant, you will most likely not be able to apply the following code to your data, as it probably still contains variables and properties that you haven't shown us yet. However, the following lines of code will hopefully generate the two variables you want the way you wanted.

* Example generated by -dataex-. For more info,    type help dataex
clear
input double(RD SGA) int(Year Scope1) str9 Date    double MPS
612   2170 2010 10700 "04jan2010"          .
612   2170 2010 10700 "05jan2010"          .
612   2170 2010 10700 "06jan2010"          .
612   2170 2010 10700 "07jan2010"          .
612   2170 2010 10700 "08jan2010"          .
612   2170 2010 10700 "11jan2010"          .
612   2170 2010 10700 "12jan2010"          .
612   2170 2010 10700 "13jan2010"          .
612   2170 2010 10700 "14jan2010"          .
612   2170 2010 10700 "15jan2010"          .
612   2170 2010 10700 "19jan2010"          .
612   2170 2010 10700 "20jan2010"          .
612   2170 2010 10700 "21jan2010"          .
612   2170 2010 10700 "22jan2010"          .
612   2170 2010 10700 "25jan2010"          .
612   2170 2010 10700 "26jan2010"          .
612   2170 2010 10700 "27jan2010"  .00658228
612   2170 2010 10700 "28jan2010"          .
612   2170 2010 10700 "29jan2010"          .
612   2170 2010 10700 "01feb2010"          .
612   2170 2010 10700 "02feb2010"          .
612   2170 2010 10700 "03feb2010"          .
766.1 2076.4 2016  1880 "05jul2016"          .
766.1 2076.4 2016  1880 "06jul2016"          .
766.1 2076.4 2016  1880 "07jul2016"          .
766.1 2076.4 2016  1880 "08jul2016"          .
766.1 2076.4 2016  1880 "11jul2016"          .
766.1 2076.4 2016  1880 "12jul2016"          .
766.1 2076.4 2016  1880 "13jul2016"          .
766.1 2076.4 2016  1880 "14jul2016"          .
766.1 2076.4 2016  1880 "15jul2016"          .
766.1 2076.4 2016  1880 "18jul2016"          .
766.1 2076.4 2016  1880 "19jul2016"          .
766.1 2076.4 2016  1880 "20jul2016"          .
766.1 2076.4 2016  1880 "21jul2016"          .
766.1 2076.4 2016  1880 "22jul2016"          .
766.1 2076.4 2016  1880 "25jul2016"          .
766.1 2076.4 2016  1880 "26jul2016"          .
766.1 2076.4 2016  1880 "27jul2016" -.01017547
766.1 2076.4 2016  1880 "28jul2016"          .
766.1 2076.4 2016  1880 "29jul2016"          .
766.1 2076.4 2016  1880 "01aug2016"          .
766.1 2076.4 2016  1880 "02aug2016"          .
766.1 2076.4 2016  1880 "03aug2016"          .
766.1 2076.4 2016  1880 "04aug2016"          .
766.1 2076.4 2016  1880 "05aug2016"          .
766.1 2076.4 2016  1880 "08aug2016"          .
766.1 2076.4 2016  1880 "09aug2016"          .
766.1 2076.4 2016  1880 "10aug2016"          .
766.1 2076.4 2016  1880 "11aug2016"          .
766.1 2076.4 2016  1880 "12aug2016"          .
766.1 2076.4 2016  1880 "15aug2016"          .
766.1 2076.4 2016  1880 "16aug2016"          .
end

gen date = daily(Date,"DMY")        // generate Stata-date variable
format date %td

egen id = group(RD)                // gen a kind of an id
order id
bysort id (date): gen a = _n        // numbering all dates per id
clonevar b =a if !missing(MPS)        // setting starting point for calculation
by id: egen c = max(b)                // generate c as constant maximum of b
drop b

local settings event|3 estimation|10
foreach el in `settings' {
    gettoken setting rest : el, parse("|")
    gettoken delim range : rest, parse("|")
    bysort id (date): gen `setting'_window = a-c if inrange(a-c,-`range',`range')
}
drop a c

All the best,
Benno

[Sophia Roemer]

Sorry I hope this makes it clearer, I have Ticker as my firm ID.


Ticker Year Scope1 IntradayReturn Date ln_Scope1 MPS week_IntradayReturn event_window new_var
ADSK 2017 2270 -1.9599991 17jul2017 7.727535 -9.5457612 -7
ADSK 2017 2270 1.0299988 18jul2017 7.727535 -9.5457612 -6
ADSK 2017 2270 1.75 19jul2017 7.727535 -9.5457612 -5
ADSK 2017 2270 .19000244 20jul2017 7.727535 -9.5457612 -4
ADSK 2017 2270 .33999634 21jul2017 7.727535 -9.5457612 -3 -3
ADSK 2017 2270 1.2200012 24jul2017 7.727535 -63.655854 -2 -2
ADSK 2017 2270 1.3499985 25jul2017 7.727535 -63.655854 -1 -1
ADSK 2017 2270 1.5800018 26jul2017 7.727535 -.00784203 -63.655854 0 0
ADSK 2017 2270 -3.4500046 27jul2017 7.727535 -63.655854 1 1
ADSK 2017 2270 .90000153 28jul2017 7.727535 -63.655854 2 2
ADSK 2017 2270 -.86000061 31jul2017 7.727535 -1.1427881 3 3
ADSK 2017 2270 .11000061 01aug2017 7.727535 -1.1427881 4
ADSK 2017 2270 -1.9500046 02aug2017 7.727535 -1.1427881 5
ADSK 2017 2270 -.06999969 03aug2017 7.727535 -1.1427881 6
ADSK 2017 2270 .04000092 04aug2017 7.727535 -1.1427881 7
ADSK 2017 2270 1.1500015 07aug2017 7.727535 -.90731823 8
ADSK 2017 2270 -1.0299988 08aug2017 7.727535 -.90731823 9
ADSK 2017 2270 -.24000549 09aug2017 7.727535 -.90731823 10
ADSK 2017 2270 -1.5799942 10aug2017 7.727535 -.90731823
ADSK 2017 2270 2.2600021 11aug2017 7.727535 -.90731823
ADSK 2017 2270 -.29999542 14aug2017 7.727535 -3.8244253
ADSK 2017 2270 .78000641 15aug2017 7.727535 -3.8244253
ADSK 2017 2270 .12999725 16aug2017 7.727535 -3.8244253
ADSK 2017 2270 -2.1800003 17aug2017 7.727535 -3.8244253
ADSK 2017 2270 .69999695 18aug2017 7.727535 -3.8244253
ADSK 2017 2270 1.3799973 21aug2017 7.727535 1.9913416
ADSK 2017 2270 .70000458 22aug2017 7.727535 1.9913416
ADSK 2017 2270 -.26000214 23aug2017 7.727535 1.9913416
ADSK 2017 2270 .02999878 24aug2017 7.727535 1.9913416
ADSK 2017 2270 -.02999878 25aug2017 7.727535 1.9913416
ADSK 2017 2270 -3.5799942 28aug2017 7.727535 -3.9886718
ADSK 2017 2270 1.6199951 29aug2017 7.727535 -3.9886718
ADSK 2017 2270 2.0200043 30aug2017 7.727535 -3.9886718
ADSK 2017 2270 .83000183 31aug2017 7.727535 -3.9886718
ADSK 2017 2270 -.91999817 01sep2017 7.727535 -3.9886718
ADSK 2017 2270 .30999756 05sep2017 7.727535 -.40955053
ADSK 2017 2270 -.61000061 06sep2017 7.727535 -.40955053 -10
ADSK 2017 2270 -.08999634 07sep2017 7.727535 -.40955053 -9
ADSK 2017 2270 .27000427 08sep2017 7.727535 -.40955053 -8
ADSK 2017 2270 3.5 11sep2017 7.727535 -2.7307089 -7
ADSK 2017 2270 -.18999481 12sep2017 7.727535 -2.7307089 -6
ADSK 2017 2270 .13999939 13sep2017 7.727535 -2.7307089 -5
ADSK 2017 2270 -.15000153 14sep2017 7.727535 -2.7307089 -4
ADSK 2017 2270 -1.4900055 15sep2017 7.727535 -2.7307089 -3 -3
ADSK 2017 2270 .09999847 18sep2017 7.727535 -.88364141 -2 -2
ADSK 2017 2270 -.68999481 19sep2017 7.727535 -.88364141 -1 -1
ADSK 2017 2270 -.33999634 20sep2017 7.727535 .02976498 -.88364141 0 0
ADSK 2017 2270 -.52999878 21sep2017 7.727535 -.88364141 1 1
ADSK 2017 2270 .09999847 22sep2017 7.727535 -.88364141 2 2
ADSK 2017 2270 -1.8499985 25sep2017 7.727535 -6.0319551 3 3
ADSK 2017 2270 .02000427 26sep2017 7.727535 -6.0319551 4
ADSK 2017 2270 1.75 27sep2017 7.727535 -6.0319551 5
ADSK 2017 2270 .04999542 28sep2017 7.727535 -6.0319551 6
ADSK 2017 2270 1.0100021 29sep2017 7.727535 -6.0319551 7
ADSK 2017 2270 1.7779999 02oct2017 7.727535 41.427215 8
ADSK 2017 2270 1.2399979 03oct2017 7.727535 41.427215 9
ADSK 2017 2270 .26000214 04oct2017 7.727535 41.427215 10
ADSK 2017 2270 2.0400009 05oct2017 7.727535 41.427215
ADSK 2017 2270 .77999878 06oct2017 7.727535 41.427215
ADSK 2017 2270 .33999634 09oct2017 7.727535 2.5122308
ADSK 2017 2270 -.16999817 10oct2017 7.727535 2.5122308
ADSK 2017 2270 1.5500031 11oct2017 7.727535 2.5122308
ADSK 2017 2270 .43999481 12oct2017 7.727535 2.5122308
ADSK 2017 2270 -.13999939 13oct2017 7.727535 2.5122308
ADSK 2017 2270 -.49000549 16oct2017 7.727535 -.53671963
ADSK 2017 2270 -.62999725 17oct2017 7.727535 -.53671963
ADSK 2017 2270 .04000092 18oct2017 7.727535 -.53671963 -10
ADSK 2017 2270 .29000092 19oct2017 7.727535 -.53671963 -9
ADSK 2017 2270 .8299942 20oct2017 7.727535 -.53671963 -8
ADSK 2017 2270 -2 23oct2017 7.727535 -17.39928 -7
ADSK 2017 2270 .33000183 24oct2017 7.727535 -17.39928 -6
ADSK 2017 2270 .94000244 25oct2017 7.727535 -17.39928 -5
ADSK 2017 2270 1.1399994 26oct2017 7.727535 -17.39928 -4
ADSK 2017 2270 1.9700012 27oct2017 7.727535 -17.39928 -3 -3
ADSK 2017 2270 .87000275 30oct2017 7.727535 -.86003945 -2 -2
ADSK 2017 2270 .68000031 31oct2017 7.727535 -.86003945 -1 -1
ADSK 2017 2270 -.84999847 01nov2017 7.727535 -.0055105 -.86003945 0 0
ADSK 2017 2270 -.77999878 02nov2017 7.727535 -.86003945 1 1
ADSK 2017 2270 .34999847 03nov2017 7.727535 -.86003945 2 2
ADSK 2017 2270 -1.1800003 06nov2017 7.727535 -1.0367415 3 3
ADSK 2017 2270 -.86000061 07nov2017 7.727535 -1.0367415 4
ADSK 2017 2270 .34999847 08nov2017 7.727535 -1.0367415 5
ADSK 2017 2270 -.5 09nov2017 7.727535 -1.0367415 6
ADSK 2017 2270 1.1600037 10nov2017 7.727535 -1.0367415 7
ADSK 2017 2270 .30000305 13nov2017 7.727535 1.255505 8
ADSK 2017 2270 -.52999878 14nov2017 7.727535 1.255505 9
ADSK 2017 2270 .1499939 15nov2017 7.727535 1.255505 10
ADSK 2017 2270 2.2099991 16nov2017 7.727535 1.255505
ADSK 2017 2270 0 17nov2017 7.727535 1.255505
ADSK 2017 2270 -1.2300034 20nov2017 7.727535 -1.7622826
ADSK 2017 2270 .58000183 21nov2017 7.727535 -1.7622826
ADSK 2017 2270 -.23999786 22nov2017 7.727535 -1.7622826
ADSK 2017 2270 1.7600021 24nov2017 7.727535 -1.7622826
ADSK 2017 2270 .05000305 27nov2017 7.727535 -.41474851
ADSK 2017 2270 -.80999756 28nov2017 7.727535 -.41474851
ADSK 2017 2270 -4.6900024 29nov2017 7.727535 -.41474851 -10
ADSK 2017 2270 .05999756 30nov2017 7.727535 -.41474851 -9
ADSK 2017 2270 -1.75 01dec2017 7.727535 -.41474851 -8
ADSK 2017 2270 -.05000305 04dec2017 7.727535 3.2279038 -7
ADSK 2017 2270 -1.3200073 05dec2017 7.727535 3.2279038 -6
ADSK 2017 2270 1.1899948 06dec2017 7.727535 3.2279038 -5
ADSK 2017 2270 3.4100037 07dec2017 7.727535 3.2279038 -4
ADSK 2017 2270 -2.4399948 08dec2017 7.727535 3.2279038 -3 -3
ADSK 2017 2270 -.04000092 11dec2017 7.727535 -1.0359315 -2 -2
ADSK 2017 2270 .01000214 12dec2017 7.727535 -1.0359315 -1 -1
ADSK 2017 2270 -1.0100021 13dec2017 7.727535 -.0095095 -1.0359315 0 0
ADSK 2017 2270 .30000305 14dec2017 7.727535 -1.0359315 1 1
ADSK 2017 2270 1.8499985 15dec2017 7.727535 -1.0359315 2 2
ADSK 2017 2270 -1.1800003 18dec2017 7.727535 -1.0000092 3 3
ADSK 2017 2270 -1.8499985 19dec2017 7.727535 -1.0000092 4
ADSK 2017 2270 -.98999786 20dec2017 7.727535 -1.0000092 5
ADSK 2017 2270 -1.1500015 21dec2017 7.727535 -1.0000092 6
ADSK 2017 2270 -.95999908 22dec2017 7.727535 -1.0000092 7
ADSK 2017 2270 .3500061 26dec2017 7.727535 1.4210695 8
ADSK 2017 2270 .61000061 27dec2017 7.727535 1.4210695 9
ADSK 2017 2270 .40999603 28dec2017 7.727535 1.4210695 10
ADSK 2017 2270 -.20999908 29dec2017 7.727535 1.4210695
AEE 2010 67068709 -.27000046 04jan2010 18.02123 -.60139135
AEE 2010 67068709 -.12000084 05jan2010 18.02123 -.60139135
AEE 2010 67068709 -.15000153 06jan2010 18.02123 -.60139135
AEE 2010 67068709 -.26999855 07jan2010 18.02123 -.60139135
AEE 2010 67068709 -.20999908 08jan2010 18.02123 .4090957
AEE 2010 67068709 .18000031 11jan2010 18.02123 .4090957
AEE 2010 67068709 .05000114 12jan2010 18.02123 .4090957 -10
AEE 2010 67068709 .1799984 13jan2010 18.02123 .4090957 -9
AEE 2010 67068709 .21999931 14jan2010 18.02123 .4090957 -8
AEE 2010 67068709 -.06999969 15jan2010 18.02123 -.7450263 -7
AEE 2010 67068709 .31999969 19jan2010 18.02123 -.7450263 -6
AEE 2010 67068709 -.32999992 20jan2010 18.02123 -.7450263 -5
AEE 2010 67068709 -.68999863 21jan2010 18.02123 -.7450263 -4
AEE 2010 67068709 -.79999924 22jan2010 18.02123 -.82178526 -3 -3
AEE 2010 67068709 -.10000038 25jan2010 18.02123 -.82178526 -2 -2
AEE 2010 67068709 .11999893 26jan2010 18.02123 -.82178526 -1 -1
AEE 2010 67068709 .03999901 27jan2010 18.02123 .00658228 -.82178526 0 0
AEE 2010 67068709 -.14999962 28jan2010 18.02123 -.82178526 1 1
AEE 2010 67068709 -.31000137 29jan2010 18.02123 -.44805072 2 2
AEE 2010 67068709 -.10000038 01feb2010 18.02123 -.44805072 3 3
AEE 2010 67068709 .29000092 02feb2010 18.02123 -.44805072 4
AEE 2010 67068709 .30000114 03feb2010 18.02123 -.44805072 5
AEE 2010 67068709 -.47000122 04feb2010 18.02123 -.44805072 6
AEE 2010 67068709 -.13999939 05feb2010 18.02123 -.65824961 7
AEE 2010 67068709 -.56999969 08feb2010 18.02123 -.65824961 8
AEE 2010 67068709 .00999832 09feb2010 18.02123 -.65824961 9
AEE 2010 67068709 -.25 10feb2010 18.02123 -.65824961 10
AEE 2010 67068709 .21999931 11feb2010 18.02123 -.65824961
AEE 2010 67068709 .10000038 12feb2010 18.02123 1.019819
AEE 2010 67068709 .32999992 16feb2010 18.02123 1.019819
AEE 2010 67068709 .18000031 17feb2010 18.02123 1.019819
AEE 2010 67068709 .17000008 18feb2010 18.02123 1.019819
AEE 2010 67068709 .02999878 19feb2010 18.02123 -.49273587
AEE 2010 67068709 -.38999939 22feb2010 18.02123 -.49273587
AEE 2010 67068709 -.12999916 23feb2010 18.02123 -.49273587
AEE 2010 67068709 -.20000076 24feb2010 18.02123 -.49273587
AEE 2010 67068709 .15999985 25feb2010 18.02123 -.49273587
AEE 2010 67068709 -.41000175 26feb2010 18.02123 .06483267
AEE 2010 67068709 .22000122 01mar2010 18.02123 .06483267
AEE 2010 67068709 .15999985 02mar2010 18.02123 .06483267 -10
AEE 2010 67068709 .09000015 03mar2010 18.02123 .06483267 -9
AEE 2010 67068709 .17000008 04mar2010 18.02123 .06483267 -8
AEE 2010 67068709 .20000076 05mar2010 18.02123 .70758333 -7
AEE 2010 67068709 .05999947 08mar2010 18.02123 .70758333 -6
AEE 2010 67068709 .12999916 09mar2010 18.02123 .70758333 -5
AEE 2010 67068709 -.01000023 10mar2010 18.02123 .70758333 -4
AEE 2010 67068709 .20000076 11mar2010 18.02123 .70758333 -3 -3
AEE 2010 67068709 -.30999947 12mar2010 18.02123 .07712684 -2 -2
AEE 2010 67068709 -.03000069 15mar2010 18.02123 .07712684 -1 -1
AEE 2010 67068709 .04999924 16mar2010 18.02123 -.02836595 .07712684 0 0
AEE 2010 67068709 .17000008 17mar2010 18.02123 .07712684 1 1
AEE 2010 67068709 .30999947 18mar2010 18.02123 .07712684 2 2
AEE 2010 67068709 .01000023 19mar2010 18.02123 -.48969384 3 3
AEE 2010 67068709 -.13000107 22mar2010 18.02123 -.48969384 4
AEE 2010 67068709 .08999825 23mar2010 18.02123 -.48969384 5
AEE 2010 67068709 -.26000023 24mar2010 18.02123 -.48969384 6
AEE 2010 67068709 -.27999878 25mar2010 18.02123 -.48969384 7
AEE 2010 67068709 .1799984 26mar2010 18.02123 .87284937 8
AEE 2010 67068709 .19000053 29mar2010 18.02123 .87284937 9
AEE 2010 67068709 .10000038 30mar2010 18.02123 .87284937 10