Looking for the correct formula to retrieve the data

[ily4everrrrr]

Here is the flow, but I don't know the correct formula.

Picture1.png
First, I have got a stock code, then a date referring to the announcement date of a circular.

Picture2.png
Then I have got many tabs in which I store the financial data in various stocks, the tab name is the stock code.

Picture3.png
I would like to get the cumulative return of T-1, T+0 (2021/11/4, referring to picture 1), T+1, total three days in the relevant tab.

Picture4.png
What formula should I put on the summary page and when I copy and paste the formula to other stocks it can give the correct answer?

Edit: Please find the attached sample workbook.
Test.xlsx

Thank you.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[ily4everrrrr]

Sorry for confusing. I attached the workbook in the thread.

[josephteh]

Try this,=LET(x,"'"&B101&"'!S",y,MATCH(D101,'1636'!A:A,0),INDIRECT(x&y)+INDIRECT(x&y-1)+INDIRECT(x&y+1))

[HansDouwe]

One way:

Please try

Please Login or Register  to view this content.

[josephteh]

Thanks to Hans' formula, revised formula =LET(x,B101&"!S",y,MATCH(D101,'1636'!A:A,0),INDIRECT(x&y)+INDIRECT(x&y-1)+INDIRECT(x&y+1)).

[ily4everrrrr]

Thanks! It worked!

Another question for the similar question (really simple, no sample workbook is required)
Q: I still want to retrieve T-1, T+0, T+1 data based on the date, but only from one single tab (named "NASDAQ" as an example).
How to amend the formula? Thanks!

[HansDouwe]

Replace
INDIRECT(B101&"!S2:S99999") by NASDAQ!S2:S9999 and
INDIRECT(B101&"!A2:A99999") by NASDAQ!A2:A9999

[ily4everrrrr]

Test(CAR 252).xlsx

This one is similar to cumulative abnormal return (CAR 3 days), but instead, it seeks to get the 252 days CAR (T0 - T+252, so actually need to retrieve 253 days data.)
I found the steps different from CAR 3 days, as it takes compounding effects into consideration, so I can't just SUM(individual stock return) - SUM(market return).
Instead, I need to get daily abnormal return, then multiply the 253 days abnormal return together.
You will find clear steps in the attached workbook. Please input the formula in F101, and it should be applicable to other stocks as well. Appreciate your help!

[HansDouwe]

this is a new question

CAR 252: Please try (all 4 steps in 1 formula):

Please Login or Register  to view this content.

[ily4everrrrr]

You're my capstone project savior! Another similar situation, no sample workbook is required, can you help with the formula to be input on F101 for the cumulative abnormal return from T-10 to T+10 (total 21 days)?
Similar to CAR(253) but idk how to amend the formula to count the T-10 days (like looking up for the 10 rows down from T0). Appreciate your help!

[ily4everrrrr]

Test(CAR21&252).xlsx

Sorry I find the CAR(253) answer is not what I expect D:
Could you help with both CAR(21) and new CAR(253) formula?
I have attached the sample workbook with the expected answer and the way to get the answer manually. Can you input the formula for CAR(21) and CAR(253) in the designated cell and the formula could be applicable to other stocks too? Thank you!

[HansDouwe]

Sorry, i calculated CAR 253 in the wrong direction:T(-252) - T(0) instead of T(0) - T(+252).
And in the end should be 1 subtracted from the result (that was missing in the given calculation steps).

Here is the corrected formula (changes are in red):

Please Login or Register  to view this content.

I also added the formula for CAR 21 to your sheet.