Event study

**Niek** · 03-11-2010, 08:32 AM

Hi everyone!

For my master thesis on mergers and acquisitions I'm doing an event study. This is a technique in which you look if an event (in this case the announcement of an acquisition) influences stock prices. Now, in Excel 2007 I have a worksheet which contains daily stock prices for about 200 firms for all trading dates between 1994/03/09 and 2010/03/01. This is off course a rather sizable database and I don't need all this information. What I do need is for every firm the stock prices for D(-30) to D(30) and for D(-300) to D(-51), with D(0) being the event date, D(-X) being X trading days before and D(X) X trading days after the event date. Note that each firm has a different event date. The event dates are stored in a different worksheet. I could do this manually, but you can see this would be a lot of work! I was wandering if anyone could help me out with automatizing this procedure. I know it might sound complicated, so I'll try to sum thins up once again:

What I have is the following:

Worksheet1 which contains the daily stock prices. The top row contains the names of the firms and the left column the dates (note that these are trading dates, so I can not for example just subtract 30 days from the event date). All the other sells contain the corresponding stock prices.
Worksheet2 contains in the first column the same names of firms and in the second column a corresponding event date.

And what I need is this:

Worksheet3 which should contain in the top row the names of the firms. Each firm should cover two columns: one for the dates and the next one for the corresponding stock price. So the second row contains the headers: date-price-date-price-... The first column will contain the number of the day, i.e. 30-29-...-[-29]-[-30] and [-51]-[-50]-...[-299]-[-300]
Off course, this can be don very easily, but the hard part is filling in the stock prices. This is where some help would come in very handy!

I hope I explained my problem more or less clearly. If not, don't hesitate to say so!

Thanks in advance for reading this and maybe helping me out a bit!

Grtz,
Niek

**rwgrietveld** · 03-11-2010, 10:02 AM

Niek,

I generally get the idea and I think it can be done, but please upload as Sample dummy with about 100 rows (both sheets)

We'll make it work for D(-5), D(+5), but this range is adjusted easily later on.

**Niek** · 03-11-2010, 11:37 AM

Hello rwgrietveld,

I had to make some minor adjustments. Since one firm can be involved in multiple deals, we can not work with the firm's names, so I created a unique code to work with. I also included the event date in the same sheet that holds the stock prices. So I've created a small sample file (see attachment) with one sheet that includes the info I have and a second sheet which gives an idea of what I'm trying to get.

Grtz,
Niek

FYI: the event dates in the sample are not the right ones, since I had to adjust them so they would fall in the range of dates included in the sample file.

**rwgrietveld** · 03-11-2010, 06:23 PM

Put this in Sheet2 B4 and drag right, down

HTML Code:

=INDEX(Sheet1!$A:$K,$A4+MATCH(OFFSET(Sheet1!$B$3,0,INT(COLUMN()/2)-1),Sheet1!$A:$A,-1),1+(COLUMN()-1)*0.5*SIN(0.5*PI()*COLUMN())^2)

maybe a little explination is required

The INT(column/2) is because you go from one columns spread into two columns

The 1+(COLUMN()-1)*0.5*SIN(0.5*PI()*COLUMN())^2) is just and alternating sequence 1,2,1,3,1,4,1,5,1,6, etc. as you need to keep looking in Column A for the date and the stock prices in 2,3,4,5, etc.

**Niek** · 03-12-2010, 02:57 AM

Thanks a lot Ricardo! This is exactly what I needed!

**robinelias** · 05-12-2010, 09:09 AM

Hi All,

This topic is very usefull for my event study aswell! The only question I got is how you 'easy ajust' the code so you can go t - 300 and t + 150 for example, from t = 0 as the event date?

I'm not so good in excel, so I hope this question is not annoying or anything...

Thanks in advanced.

Robin

**Niek** · 05-12-2010, 10:52 AM

Hi Robin,

It's been a while since I did it, but if I remember correctly you don't have to change a thing to the formula. Make column A ranging from -300 to +150 instead of -5 to +5 and use the same formula.

I hope it works!

Grtz,
Niek

**ivanplakotias** · 09-25-2010, 11:19 PM

Hi guys!

I'm writing my thesis as well and I have the same problem with Niek. I used the formula and is actually working, but only for a range of 120 rows, while I want to use it for my entire sample (2300 rows). The estimation period is rather short (-80.+40) but the limit with the rows is really an obstacle.
I've tried several things until now, in order to adjust the range of the rows but nothing works....Please help!!!

Thank you in advance
Ivan

**micheilleyevans** · 09-25-2010, 11:33 PM

these is a usual problem and sometime it can break our neck on analyzing but when times goes by i found a software that could help us on this onenote
this tool could help us ..get your self and relax it's a free software..

**DonkeyOte** · 12-26-2010, 05:22 AM

dobdob, please do not post questions in the threads of other members.
Please create your own thread, if you feel another thread is particular relevant to your question provide a link to it within your own post.

All that being said I am not entirely sure that an Excel Forum is best suited to your question.