Hi Cameron:
First of all, I want to compliment you on how well you laid out your problem. It makes me want to help you.
Anyway, I came up with a different approach that I think would handle multiple & overlapping events. It creates a column or variable for each event that gives the number of trading days relative to the event. This way you can change your windows easily. I think the .do file could be cleaned up & made more efficient but I do think it works. Voila...
Eric
============================================================================
. /* event_dates.do */
.
. version 9.0
.
. clear
.
. use http://www-personal.umich.edu/~chooper/stata/returns
.
. sort permno ticker date
.
. save temp1, replace
file temp1.dta saved
.
. use http://www-personal.umich.edu/~chooper/stata/event_dates, clear
.
. sort permno ticker date
.
. merge permno ticker date using temp1
.
. keep if _merge == 2 | _merge == 3
(0 observations deleted)
.
. sort permno ticker date
.
. by permno ticker: gen no = _n
.
. save temp2, replace
file temp2.dta saved
.
. keep if _merge == 3
(496 observations deleted)
.
. by permno: gen evt = _n
.
. keep permno no evt
.
. reshape wide no, i(permno) j(evt)
(note: j = 1 2 3 4 5)
Data long -> wide
-----------------------------------------------------------------------------
Number of obs. 8 -> 2
Number of variables 3 -> 6
j variable (5 values) evt -> (dropped)
xij variables:
no -> no1 no2 ... no5
-----------------------------------------------------------------------------
.
. save temp3, replace
file temp3.dta saved
.
. use temp2, clear
.
. drop _merge
.
. merge permno using temp3
variable permno does not uniquely identify observations in the master data
.
. rename no n
.
. foreach var of varlist no* {
2. replace `var' = n - `var'
3. }
(502 real changes made)
(502 real changes made)
(503 real changes made)
(252 real changes made)
(252 real changes made)
.
.
. drop _merge
.
. list in 1/15
+-------------------------------------------------------------------------------+
| permno ticker date ret n no1 no2 no3 no4 no5 |
|-------------------------------------------------------------------------------|
1. | 10107 MSFT 03 Jan 00 -.001606 1 -1 -47 -119 -179 -250 |
2. | 10107 MSFT 04 Jan 00 -.0337802 2 0 -46 -118 -178 -249 |
3. | 10107 MSFT 05 Jan 00 .0105438 3 1 -45 -117 -177 -248 |
4. | 10107 MSFT 06 Jan 00 -.0334981 4 2 -44 -116 -176 -247 |
5. | 10107 MSFT 07 Jan 00 .0130682 5 3 -43 -115 -175 -246 |
|-------------------------------------------------------------------------------|
6. | 10107 MSFT 10 Jan 00 .0072911 6 4 -42 -114 -174 -245 |
7. | 10107 MSFT 11 Jan 00 -.0256125 7 5 -41 -113 -173 -244 |
8. | 10107 MSFT 12 Jan 00 -.0325714 8 6 -40 -112 -172 -243 |
9. | 10107 MSFT 13 Jan 00 .0189014 9 7 -39 -111 -171 -242 |
10. | 10107 MSFT 14 Jan 00 .0411594 10 8 -38 -110 -170 -241 |
|-------------------------------------------------------------------------------|
11. | 10107 MSFT 18 Jan 00 .0272829 11 9 -37 -109 -169 -240 |
12. | 10107 MSFT 19 Jan 00 -.0720867 12 10 -36 -108 -168 -239 |
13. | 10107 MSFT 20 Jan 00 -.0093458 13 11 -35 -107 -167 -238 |
14. | 10107 MSFT 21 Jan 00 -.0212264 14 12 -34 -106 -166 -237 |
15. | 10107 MSFT 24 Jan 00 -.0240964 15 13 -33 -105 -165 -236 |
+-------------------------------------------------------------------------------+
.
===============================================================================
>Dear list,
>
>I want to merge event dates with a series of daily stock returns and subsequently define various event windows. What seemed like a straight forward problem is actually proving surprisingly difficult (for me anyway).
>
>I have made up some illustrative data. Suppose I have a file containing daily returns for a couple of firms like this:
>
>
>. version
>version 8.2
>. use http://www-personal.umich.edu/~chooper/stata/returns
>. list in 1/10
>
> +-----------------------------------------+
> | permno ticker date ret |
> |-----------------------------------------|
> 1. | 10107 MSFT 03 Jan 00 -.001606 |
> 2. | 10107 MSFT 04 Jan 00 -.0337802 |
> 3. | 10107 MSFT 05 Jan 00 .0105438 |
> 4. | 10107 MSFT 06 Jan 00 -.0334981 |
> 5. | 10107 MSFT 07 Jan 00 .0130682 |
> |-----------------------------------------|
> 6. | 10107 MSFT 10 Jan 00 .0072911 |
> 7. | 10107 MSFT 11 Jan 00 -.0256125 |
> 8. | 10107 MSFT 12 Jan 00 -.0325714 |
> 9. | 10107 MSFT 13 Jan 00 .0189014 |
> 10. | 10107 MSFT 14 Jan 00 .0411594 |
> +-----------------------------------------+
>
>I also have a file of event dates:
>
>. use http://www-personal.umich.edu/~chooper/stata/event_dates
>. list
>
> +-----------------------------+
> | permno ticker date |
> |-----------------------------|
> 1. | 10107 MSFT 04 Jan 00 |
> 2. | 10107 MSFT 10 Mar 00 |
> 3. | 10107 MSFT 22 Jun 00 |
> 4. | 10107 MSFT 18 Sep 00 |
> 5. | 10107 MSFT 28 Dec 00 |
> |-----------------------------|
> 6. | 12490 IBM 12 Jan 00 |
> 7. | 12490 IBM 07 Apr 00 |
> 8. | 12490 IBM 25 Jul 00 |
> +-----------------------------+
>
>Merging is straightforward. However I also want to create a set of 3 event windows for each event date: pre-event, event, and post-event. Define the event date as 0 then
>
>pre-event = days -7 to -3
>event = days -2 to +2
>post-event = days +3 to + 7
>
>** These are trading dates not calendar dates (each observation is a trading date). **
>
>I thought this would be easy, but I just can't seem to make it happen. Please note that in practice I have a sample containing daily data for thousands of firms, many of which have over 10 years of data. I also have 4 event dates per firm-year. This means that the method shown at
>
>http://dss.princeton.edu/online_help/analysis/multiple_event_dates.htm
>
>which creates a duplicate set of observations for each event date / company combination is impractical. The resulting file is simply too large.
>
>Here is a mocked up file showing what I am aiming for. I have shown data around one event_date. The variable TYPE is defined as (0 = non-event, 1 = event, 2 = pre and 3 = post.
>
>. use http://www-personal.umich.edu/~chooper/stata/desired_result
>. list in 39/57
>
>
>+-------------------------------------------------------------------+
> | permno ticker date event_d~e ret type
>indx |
>
>|-------------------------------------------------------------------|
> 39. | 10107 MSFT 28 Feb 00 . .0027379 0
>. |
> 40. | 10107 MSFT 29 Feb 00 . -.0238908 0
>. |
> 41. | 10107 MSFT 01 Mar 00 . .0160839 2
>-7 |
> 42. | 10107 MSFT 02 Mar 00 . .0282175 2
>-6 |
> 43. | 10107 MSFT 03 Mar 00 . .0294511 2
>-5 |
>
>|-------------------------------------------------------------------|
> 44. | 10107 MSFT 06 Mar 00 . -.0572172 2
>-4 |
> 45. | 10107 MSFT 07 Mar 00 . .0248276 2
>-3 |
> 46. | 10107 MSFT 08 Mar 00 . .0289367 1
>-2 |
> 47. | 10107 MSFT 09 Mar 00 . .0464356 1
>-1 |
> 48. | 10107 MSFT 10 Mar 00 10 Mar 00 .01 1
>0 |
>
>|-------------------------------------------------------------------|
> 49. | 10107 MSFT 13 Mar 00 . -.029703 1
>1 |
> 50. | 10107 MSFT 14 Mar 00 . -.0293367 1
>2 |
> 51. | 10107 MSFT 15 Mar 00 . .0026281 3
>3 |
> 52. | 10107 MSFT 16 Mar 00 . 0 3
>4 |
> 53. | 10107 MSFT 17 Mar 00 . .0419397 3
>5 |
>
>|-------------------------------------------------------------------|
> 54. | 10107 MSFT 20 Mar 00 . -.0201258 3
>6 |
> 55. | 10107 MSFT 21 Mar 00 . .055199 3
>7 |
> 56. | 10107 MSFT 22 Mar 00 . .0048662 0
>. |
> 57. | 10107 MSFT 23 Mar 00 . .0835351 0
>. |
>
>+-------------------------------------------------------------------+
>
>Any suggestions greatly appreciated.
>
>Cameron
>
>*
>* For searches and help try:
>* http://www.stata.com/support/faqs/res/findit.html
>* http://www.stata.com/support/statalist/faq
>* http://www.ats.ucla.edu/stat/stata/
--
===================================================
Eric G. Wruck
Econalytics
2535 Sherwood Road
Columbus, OH 43209
ph: 614.231.5034
cell: 614.330.8846
eFax: 614.573.6639
eMail: [email protected]
website: http://www.econalytics.com
====================================================
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
