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