Filtering out events with overlap in time

[Henk85]

Hello,

Currently I'm undertaking a study on the effect of an M&A announcement on the stock prices. However, in order to prevent confounding events, I want to filter out concurrent mergers (i.e. acquirers undertaking two or more mergers in the same period).
The relevant variables are as follows
Announcement Date
Closing Date
Acquirer ticker.

There should thus be NO acquisition by the same acquirer (i.e. having the same ticker code) in the period between the announcement date and the closing date/unconditional date.

Could you guys give me any advice on how should I construct this fomula? I reckon it's something with index, match and/or if, but I can't really get it working.

07/12/1999	10/26/1999	DoubleClick Inc	DCLK
06/14/1999	11/26/1999	DoubleClick Inc	DCLK
02/01/1999	05/21/1999	America Online Inc	AOL
11/24/1998	03/17/1999	America Online Inc	AOL
01/19/1999	05/28/1999	At Home Corp	ATHM
01/27/1999	05/28/1999	Yahoo! Inc	YHOO
04/01/1999	07/20/1999	Yahoo! Inc	YHOO
07/14/2003	10/07/2003	Yahoo! Inc	YHOO
12/23/2002	03/19/2003	Yahoo! Inc	YHOO
12/12/2001	02/13/2002	Yahoo! Inc	YHOO

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

So the first two Yahoo mergers should show a 0, the other ones should show a 1.

 

[Henk85]

Hello,

Currently I'm undertaking a study on the effect of an M&A announcement on the stock prices. However, in order to prevent confounding events, I want to filter out concurrent mergers (i.e. acquirers undertaking two or more mergers in the same period).
The relevant variables are as follows
Announcement Date
Closing Date
Acquirer ticker.

There should thus be NO acquisition by the same acquirer (i.e. having the same ticker code) in the period between the announcement date and the closing date/unconditional date.

Could you guys give me any advice on how should I construct this fomula? I reckon it's something with index, match and/or if, but I can't really get it working.

07/12/1999	10/26/1999	DoubleClick Inc	DCLK
06/14/1999	11/26/1999	DoubleClick Inc	DCLK
02/01/1999	05/21/1999	America Online Inc	AOL
11/24/1998	03/17/1999	America Online Inc	AOL
01/19/1999	05/28/1999	At Home Corp	ATHM
01/27/1999	05/28/1999	Yahoo! Inc	YHOO
04/01/1999	07/20/1999	Yahoo! Inc	YHOO
07/14/2003	10/07/2003	Yahoo! Inc	YHOO
12/23/2002	03/19/2003	Yahoo! Inc	YHOO
12/12/2001	02/13/2002	Yahoo! Inc	YHOO

<tbody>
</tbody>

So the first two Yahoo mergers should show a 0, the other ones should show a 1.

Basically I've came up with this, but it does not work

=IF(AND($C4=$C$4:$C$1943,OR($A5>$B$4:$B$1943,$B5<$A$4:$A$1943)),"1","0")

 

[rayk8301]

this one sort of worked except the first cell because it doesn't work upwards, so you'll need to adjust the first cell manually somehow.

just try to follow the logic within the formula, seems simple enough but works for the most part...you'll just need to plug the ticker in the AND function which shouldn't be that big of a deal, I just focused mostly on dates.

=IF(AND(A8>=$A$8:A8,A9<=$B$8:B8),1,0)

here is a snapshot of what I got, like I said the first one will need to be adjusted manually.

1/27/1999	5/28/1999	1
4/1/1999	7/20/1999	0
7/14/2003	10/7/2003	1
12/23/2002	3/19/2003	1
12/12/2001	2/13/2002	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Henk85]

this one sort of worked except the first cell because it doesn't work upwards, so you'll need to adjust the first cell manually somehow.

just try to follow the logic within the formula, seems simple enough but works for the most part...you'll just need to plug the ticker in the AND function which shouldn't be that big of a deal, I just focused mostly on dates.

=IF(AND(A8>=$A$8:A8,A9<=$B$8:B8),1,0)

here is a snapshot of what I got, like I said the first one will need to be adjusted manually.

1/27/1999	5/28/1999	1
4/1/1999	7/20/1999	0
7/14/2003	10/7/2003	1
12/23/2002	3/19/2003	1
12/12/2001	2/13/2002	1

<tbody>
</tbody>

Hi rayk,

Thank you for your reply.

I've now worked with =IF(AND($A5>$B$4:$B$1943,$B5<$A$4:$A$1943),1,0)

Basically this shows whether a merger's

- announcement days lies past all the completion dates in the list
- completion days lies before all the announcement dates in the list
In this case, there is no overlap for this deal (which I want to find out).
With a list of 2000+ mergers this obviously results in only zero's.

However

ONLY the overlap with the same acquirer is relevant.

I do not succeed to form a formula that uses these conditions for only the same tickers.
Do you know how I should construct this?

 

[rayk8301]

give this a shot...the third AND argument for the ticker, i want to say it should work but test it out in the bigger file you have

=IF(AND(A8>=$A$8:A8,A9<=$B$8:B8,C8=$C$8:C8),1,0)

 

[Henk85]

give this a shot...the third AND argument for the ticker, i want to say it should work but test it out in the bigger file you have

=IF(AND(A8>=$A$8:A8,A9<=$B$8:B8,C8=$C$8:C8),1,0)

Hi Rayk, it's not working unfortunately.


=IF(SUMPRODUCT(($A4<=$B$4:$B$1943)*($B4>=$A$4:$A$1943)*($C4=$C$4:$C$1943))>1,0,1)

Seems to work, but I can't really see why.

Could someone explain this to me. Have I covered all overlap with this formula?