Calculate # repurchases within 30 days of last sale

[M10Ant]

Hoping someone might be able to help as currently at a loss on working out a formula for this

I need a formula that will count the number of times a stock has been bought within 30 days of the last sale of that stock.

In the below example, my summary table on the right should be recording a count of "2" for APPL stock and "1" for MSFT stock.

For the APPL stock, the count of 2 would come from the two Buy orders on 16/06/2021 and 22/07/2021, both being within 30 days of the last sale of APPL stock on 10/07/2021.
For the MFST stock, the count of 1 would come from the Buy order on 26/07/2021, being within 30 days of the last sale of MFST stock on 23/07/2021

No of repurchases within 30 days of last disposal.xlsx
	A	B	C	D	E	F	G
1
2		Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
3		03/06/2021	Buy	APPL		APPL
4		07/06/2021	Buy	APPL		MSFT
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL
10		22/07/2021	Buy	APPL
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT
13		05/11/2021	Buy	MSFT
Sheet1

I think I might need to use COUNTIFS or MAX, but so far have only worked out how to count the number of trades for each different type of stock, not a count of all buy orders within 30 days of the last sale (per each type of stock).
I'm also running Excel 365 so have access to the new MINIFS, MAXIFS functions. I just can't work out how to solve this currently.


Thanks in advance.

 

[shaowu459]

try this:

工作簿1.xlsx
	A	B	C	D	E	F
1	Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
2	2021-6-3	Buy	APPL		APPL	2
3	2021-6-7	Buy	APPL		MSFT	1
4	2021-6-14	Buy	MSFT
5	2021-6-15	Buy	APPL
6	2021-6-22	Buy	MSFT
7	2021-7-10	Sell	APPL
8	2021-7-16	Buy	APPL
9	2021-7-22	Buy	APPL
10	2021-7-23	Sell	MSFT
11	2021-7-26	Buy	MSFT
12	2021-11-5	Buy	MSFT
13
14	I need a formula that will count the number of times a stock has been bought within 30 days of the last sale of that stock.
15
16
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=LET(s,MAXIFS(A:A,C:C,E2,B:B,"Sell"),COUNTIFS(C:C,E2,B:B,"Buy",A:A,"<="&30+s,A:A,">"&s))

 

[Peter_SSs]

.. same thing but applying it to your two apparent formal table structures

22 07 21.xlsm
	A	B	C	D	E	F	G
1
2		Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
3		3/06/2021	Buy	APPL		APPL	2
4		7/06/2021	Buy	APPL		MSFT	1
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL
10		22/07/2021	Buy	APPL
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT
13		5/11/2021	Buy	MSFT
14
Bought

Cell Formulas
Range	Formula
G3:G4	G3	=LET(tet,Table1[Trade Execution Time],stk,Table1[Stock],ot,Table1[Order Type],s,MAXIFS(tet,stk,[@Stock],ot,"Sell"),COUNTIFS(stk,[@Stock],ot,"Buy",tet,"<="&30+s,tet,">"&s))

 

[M10Ant]

try this:

工作簿1.xlsx
	A	B	C	D	E	F
1	Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
2	2021-6-3	Buy	APPL		APPL	2
3	2021-6-7	Buy	APPL		MSFT	1
4	2021-6-14	Buy	MSFT
5	2021-6-15	Buy	APPL
6	2021-6-22	Buy	MSFT
7	2021-7-10	Sell	APPL
8	2021-7-16	Buy	APPL
9	2021-7-22	Buy	APPL
10	2021-7-23	Sell	MSFT
11	2021-7-26	Buy	MSFT
12	2021-11-5	Buy	MSFT
13
14	I need a formula that will count the number of times a stock has been bought within 30 days of the last sale of that stock.
15
16
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=LET(s,MAXIFS(A:A,C:C,E2,B:B,"Sell"),COUNTIFS(C:C,E2,B:B,"Buy",A:A,"<="&30+s,A:A,">"&s))

Hi shaowu459. Thank you very much for this. I've plugged in your formula and converted to structured table references and it all works perfectly

 

[M10Ant]

.. same thing but applying it to your two apparent formal table structures

22 07 21.xlsm
	A	B	C	D	E	F	G
1
2		Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
3		3/06/2021	Buy	APPL		APPL	2
4		7/06/2021	Buy	APPL		MSFT	1
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL
10		22/07/2021	Buy	APPL
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT
13		5/11/2021	Buy	MSFT
14
Bought

Cell Formulas
Range	Formula
G3:G4	G3	=LET(tet,Table1[Trade Execution Time],stk,Table1[Stock],ot,Table1[Order Type],s,MAXIFS(tet,stk,[@Stock],ot,"Sell"),COUNTIFS(stk,[@Stock],ot,"Buy",tet,"<="&30+s,tet,">"&s))

Hi Peter. Thank you very much for the formula also That gives me the same expected result as well. I've never come across "LET" or the "tet", "stk" "ot" before. I can possibly work out the rest but wondered if you could give me a brief explanation of what they are doing please.

 

[Peter_SSs]

The LET function effectively allows you to introduce variables into your formula. This can make the formula shorter and more efficient than repeating the same calculations or references over and over in the formula. For example if you just plugged the relevant structured references into @shaowu459's formula, you would have come up with a much longer formula than mine.

There is nothing special about "tet", "stk" or "ot', they are just variable names that I chose (initials from the table column headings but they could just as easily have been "x", "y" and "z") to make repeated references to those table columns shorter.

More information about LET here: LET function

 

[M10Ant]

The LET function effectively allows you to introduce variables into your formula. This can make the formula shorter and more efficient than repeating the same calculations or references over and over in the formula. For example if you just plugged the relevant structured references into @shaowu459's formula, you would have come up with a much longer formula than mine.

There is nothing special about "tet", "stk" or "ot', they are just variable names that I chose (initials from the table column headings but they could just as easily have been "x", "y" and "z") to make repeated references to those table columns shorter.

More information about LET here: LET function

Ah thank you Peter. I should have picked that up regards the variable names! That makes sense now.

 

[M10Ant]

If either of you knew how and could advise, how would the formula need to be amended to restrict the returned "counts" to those found within a tax year (for me in the UK that would be start date 6th April one year, and 5th April the next).

 

[M10Ant]

Just checking further, I don't think the formula is quite right yet, as when adding another sell and buy order for MFST, it should increase the count to a total of 2.
One buy within 30 days after sell on 23/07/2021 and one buy within 30 days after sell on 06/12/2021

No of repurchases within 30 days of last disposal.xlsx
	A	B	C	D	E	F	G
1
2		Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
3		03/06/2021	Buy	APPL		APPL	2
4		07/06/2021	Buy	APPL		MSFT	1
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL
10		22/07/2021	Buy	APPL
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT
13		05/11/2021	Buy	MSFT
14		06/12/2021	Sell	MSFT
15		10/12/2021	Buy	MSFT
16
Sheet1

Cell Formulas
Range	Formula
G3:G4	G3	=LET(tet,tblTrades[Trade Execution Time],stk,tblTrades[Stock],ot,tblTrades[Order Type],s,MAXIFS(tet,stk,[@Stock],ot,"Sell"),COUNTIFS(stk,[@Stock],ot,"Buy",tet,"<="&30+s,tet,">"&s))

 

[M10Ant]

Sorry I appreciate I may not have explained the problem fully initially.
I'm needing a formula that records the number of buys of the same stock that has occurred within 30 days following a sell order for that stock prior, and for any time this has occurred.