Event study formula

**Findus89** · 11-21-2012, 05:09 AM

Hi,

I am writing my bachelors thesis in finance and I am investigating market reaktions to dividend changes with an event study. I have about 120 different companys with a total of about 550 event dates and daily stock- and index returns. I would need a lot of help with how I can do the event studuy without having to manually look up and select all the dates and corresponding stock- and indexreturns for all the events one by one, since this would take forever. Preferably I would like a drag formula or something similar, my Excel skills are not very high...

Please ask specifying questions if I didn't make myself clear. My estimation window is 120 days and the event window for-5 to +5 days from the event at day 0.

Thank you in advance for you time and help.

**TheExcelphile** · 11-21-2012, 05:14 AM

Hi, welcome to the forum!

Could you upload a sample sheet so that we may help you better?

Click on Go Advanced below this thread and then on 'Manage Attachments'.

**Findus89** · 11-21-2012, 05:28 AM

This is how the spreadsheet I have right now looks like. I could'nt upload the whole file because it was too large, so I cut it of after a few companies.

**TheExcelphile** · 11-21-2012, 05:34 AM

OK, got the file, but i still can't understand what you want! Could you rephrase the question with reference to the file?

**Findus89** · 11-21-2012, 06:09 AM

I uploaded an updated version of the sample file. In Sheet2 there is an example of what I need in the end for all the events, but here just for one event. You can see the date of the event under the company name in Sheet1.

I don't need all the financial calculations, just a way to pick out the appropriate values (stock returns and index returns) for the appropriate dates.
The appropriate dates are the estimation window -125 to -6 (for the Intercept, Slope, R^2 and Standard Error), and the event window -5 to +5 (for E(R), AR, CAR and AR t-test), with the event date mentioned under the company name as day 0.

Hope I could explain well enough.

Edit: I don't need to have the specific values for Intercept, Slope, R^2 and Standard Error either.

**TheExcelphile** · 11-21-2012, 07:04 AM

Hi!

I've updated the formulas in the cells shaded blue.

See if this works for you.

Don't forget to click the little star to the left of this post if you feel I helped!