Filtering out events with overlap in time

H

Henk85

Board Regular
Joined
Nov 11, 2015
Messages
112
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.

[TABLE="width: 476"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]07/12/1999[/TD]
[TD]10/26/1999[/TD]
[TD]DoubleClick Inc[/TD]
[TD]DCLK[/TD]
[/TR]
[TR]
[TD]06/14/1999[/TD]
[TD]11/26/1999[/TD]
[TD]DoubleClick Inc[/TD]
[TD]DCLK[/TD]
[/TR]
[TR]
[TD]02/01/1999[/TD]
[TD]05/21/1999[/TD]
[TD]America Online Inc[/TD]
[TD]AOL[/TD]
[/TR]
[TR]
[TD]11/24/1998[/TD]
[TD]03/17/1999[/TD]
[TD]America Online Inc[/TD]
[TD]AOL[/TD]
[/TR]
[TR]
[TD]01/19/1999[/TD]
[TD]05/28/1999[/TD]
[TD]At Home Corp[/TD]
[TD]ATHM[/TD]
[/TR]
[TR]
[TD]01/27/1999[/TD]
[TD]05/28/1999[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]04/01/1999[/TD]
[TD]07/20/1999[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]07/14/2003[/TD]
[TD]10/07/2003[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]12/23/2002[/TD]
[TD]03/19/2003[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]12/12/2001[/TD]
[TD]02/13/2002[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Henk85 said:
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.

[TABLE="width: 476"]
<tbody>[TR]
[TD]07/12/1999[/TD]
[TD]10/26/1999[/TD]
[TD]DoubleClick Inc[/TD]
[TD]DCLK[/TD]
[/TR]
[TR]
[TD]06/14/1999[/TD]
[TD]11/26/1999[/TD]
[TD]DoubleClick Inc[/TD]
[TD]DCLK[/TD]
[/TR]
[TR]
[TD]02/01/1999[/TD]
[TD]05/21/1999[/TD]
[TD]America Online Inc[/TD]
[TD]AOL[/TD]
[/TR]
[TR]
[TD]11/24/1998[/TD]
[TD]03/17/1999[/TD]
[TD]America Online Inc[/TD]
[TD]AOL[/TD]
[/TR]
[TR]
[TD]01/19/1999[/TD]
[TD]05/28/1999[/TD]
[TD]At Home Corp[/TD]
[TD]ATHM[/TD]
[/TR]
[TR]
[TD]01/27/1999[/TD]
[TD]05/28/1999[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]04/01/1999[/TD]
[TD]07/20/1999[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]07/14/2003[/TD]
[TD]10/07/2003[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]12/23/2002[/TD]
[TD]03/19/2003[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
[TR]
[TD]12/12/2001[/TD]
[TD]02/13/2002[/TD]
[TD]Yahoo! Inc[/TD]
[TD]YHOO[/TD]
[/TR]
</tbody>[/TABLE]

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

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")
 
Upvote 0
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.

[TABLE="width: 305"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]1/27/1999[/TD]
[TD="align: right"]5/28/1999[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/1999[/TD]
[TD="align: right"]7/20/1999[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7/14/2003[/TD]
[TD="align: right"]10/7/2003[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/23/2002[/TD]
[TD="align: right"]3/19/2003[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2001[/TD]
[TD="align: right"]2/13/2002[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
rayk8301 said:
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.

[TABLE="width: 305"]
<tbody>[TR]
[TD="align: right"]1/27/1999[/TD]
[TD="align: right"]5/28/1999[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/1999[/TD]
[TD="align: right"]7/20/1999[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7/14/2003[/TD]
[TD="align: right"]10/7/2003[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/23/2002[/TD]
[TD="align: right"]3/19/2003[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2001[/TD]
[TD="align: right"]2/13/2002[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Last edited:
Upvote 0
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)
 
Upvote 0
rayk8301 said:
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)
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
J
  • Question Question
Formula to SUM "On Hold" time overlaps (minus non-work time, weekends & public holidays)
Replies
5
Views
1K
fjns
F
P
  • Question Question
Correcting Data
Replies
0
Views
560
parvezs27
P
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top