Matching stock price and date with announcement date (INDEX, MATCH and OFFSET formulas)

[CeliaQ]

Hi, May I know how can I use the INDEX MATCH and OFFSET formulas to match the date of stock price and stock price for each day before (day -60 to day -1) and after (day+1 to day +60) the earning announcement date.

Please find attached the data set.

Thanks in advance for any assistance you can provide.

 

[PaulF]

See if this works for you...

I completed the 1st 3 columns down to the -1 spot... Just follow the example to finish and learn Index and Match...

Respectfully,
PaulF

 

[CeliaQ]

Dear Paul, thanks so much for your prompt reply and useful formula. The formula works for the stock price. But for the date of stock price, the stock market does not trade 365 days. Hence, the given formula for the date of stock price includes non-trading days. Do you have any ideas to match with the event dates (days -60 to days -1) to the exact trading days?

I found out the following formula to match the event dates with the date of stock price from the website: =INDEX(stockprices!$A:$EC,$A6+MATCH(OFFSET(stockprices!$B$4,0,INT(COLUMN()/2)-1),stockprices!$A:$A,0),1+(COLUMN()*0)*0,5*SIN(0,5*PI()*COLUMN())^2)

However, it is not working as too many arguments have been entered for this function.

Please find attached the new dataset with manually matched stock price data with announcement dates (highlighted in yellow). I have about 2000 firms to be analysed and thus I am trying to match the data by using formula.

Thanks.

 

[Hui]

B6:
=INDEX(stockprices!$A$5:$A$1917,MATCH(B$4,stockprices!$A$5:$A$1917,0)+$A6)

C6:
=INDEX(OFFSET(stockprices!$B$5:$B$1917,,MATCH(B$3&B$4,stockprices!$B$3:$EC$3&stockprices!$B$4:$EC$4,0)),MATCH(B$4,stockprices!$A$5:$A$1917,0)+$A6) Ctrl+Shift+Enter

or see attached file:

Please check that this is correct

 

[CeliaQ]

Thanks, Hui. I got the answers. I truly appreciate all the assistances given.