Calculation of returns on different dates

Question

I am trying to calculate the return of different companies at specific given dates ranging from 2007-2020 on 837 observations with a +/- 1 day of the announcement. This means that my company will announce an acquisition at e.g. 13.04.2017, and hereby i need the return of the company from 12.04.2017-14.04.2017, which I have all of the data on at the first sheet (Sheet1). As shown in the first picture.

On all my acquisitions, I will have stock data from 01.01.2007-31.12.2020 looking like this on the coming 837 different sheets. With the data I need to calculate the return of the acquiring company (return B, calculated as B3/B2) and the index (return c), hereby subtract return B from return C (which is the F column), and lastly make a return box that is made up as (F4+F5+F6 in this case). This will look like this with made up numbers:

Is this possible to do in excel, vba or R with the specific announcement date for each of the observations? Hope anyone can help me, and I hope the guidelines for posting is correct. Thanks in advance. Lastly I would like to add the return from the second picture to the first picture in column C.

Answer 1

This function relies on the sheets sharing the name of the companies listed on Sheet 1.

So for STANDEX INTERNATIONAL CORPORATION, there is a sheet called STANDEX INTERNATIONAL CORPORATION.

=SUM(INDEX(INDIRECT("'"&$A2&"'!$A:$G"),MATCH($B2,INDIRECT("'"&$A2&"'!$A:$A"),0),5),INDEX(INDIRECT("'"&$A2&"'!$A:$G"),MATCH($B2+1,INDIRECT("'"&$A2&"'!$A:$A"),0),5),INDEX(INDIRECT("'"&$A2&"'!$A:$G"),MATCH($B2-1,INDIRECT("'"&$A2&"'!$A:$A"),0),5))

Explanation
The INDIRECT function tries to evaluate text as a worksheet reference. Using concatenation (&) we can use the name in the company column to find the related sheet
INDEX MATCH finds the date (B2) from the array on the specified sheet. We then get the dates +/-1 from that date and SUM them
This only works if there is a single entry for each date on the company's individual sheets.
The 5 at the end references the 5th column (Return C)
The way it is set, you should be able to ctrl+D down Column C