Calculate # repurchases of a stock on same-day as a sale

[M10Ant]

I thought it best to post this separately but is a continuation of Calculate # repurchases within 30 days of last sale

In the source trade table shown on the left below, the "Rebuys within 30 days" column tallies each time a stock has been re-bought within 30 days of a previous sale of that same stock. The summary table then sums all these up in "# Rebuys within 30 days" column

This works perfectly and a big thanks to Peter_SSs and shaowu459 for providing me with the solution for this in previous post.

I've tried to tweak the "Rebuys within 30 days" formula to tally all buy trades of a stock done on the same day as a sale of that same stock, putting this in the "Rebuys on same day" helper column. I know it's not quite correct as it should be showing a "1" in the row for the buy of MSFT on 11/12/2021. Hoping someone might know how to tweak this further to get the helper column to tally correctly.

Stock Repurchases.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Trade Execution Time	Order Type	Stock	Rebuys on same day	Rebuys within 30 days		Stock	# Same-day Rebuys	# Rebuys within 30 days
3		03/06/2021	Buy	APPL				APPL	0	2
4		07/06/2021	Buy	APPL				MSFT	0	4
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		1
10		22/07/2021	Buy	APPL		1
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT		1
13		05/11/2021	Buy	MSFT
14		06/12/2021	Sell	MSFT
15		10/12/2021	Buy	MSFT		1
16		11/12/2021	Sell	MSFT
17		11/12/2021	Buy	MSFT		1
18		10/01/2022	Buy	MSFT		1
Sheet2

Cell Formulas
Range	Formula
E3:E18	E3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=1,1,""),""))
F3:F18	F3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=30,1,""),""))
I3:I4	I3	=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys on same day])
J3:J4	J3	=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys within 30 days])

 

[Peter_SSs]

Formula in E3 should be
=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet=ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=1,1,""),""))

 

[Peter_SSs]

Actually, try this shorter one in E3
=IF([@[Order Type]]="Buy",IF(COUNTIFS([Trade Execution Time],[@[Trade Execution Time]],[Stock],[@Stock],[Order Type],"Sell"),1,""),"")

 

[M10Ant]

Hi Peter. Thank you for this. I've plugged both formulas in and they do both work for those dates. I've just testing making the date column a full date-time column and putting in sample times for the buy and sell of MSFT on 11/12/21. When I do that, the tally disappears on the buy order. Is there a way to still make the buy entry tally a "1" (the buy being within 24 hours of the sell order)?

Stock Repurchases.xlsx
	A	B	C	D	E	F
1
2		Trade Execution Time	Order Type	Stock	Rebuys on same day	Rebuys within 30 days
3		03-06-21 00:00	Buy	APPL
4		07-06-21 00:00	Buy	APPL
5		14-06-21 00:00	Buy	MSFT
6		15-06-21 00:00	Buy	APPL
7		22-06-21 00:00	Buy	MSFT
8		10-07-21 00:00	Sell	APPL
9		16-07-21 00:00	Buy	APPL		1
10		22-07-21 00:00	Buy	APPL		1
11		23-07-21 00:00	Sell	MSFT
12		26-07-21 00:00	Buy	MSFT		1
13		05-11-21 00:00	Buy	MSFT
14		06-12-21 00:00	Sell	MSFT
15		10-12-21 00:00	Buy	MSFT		1
16		11-12-21 11:00	Sell	MSFT
17		11-12-21 14:00	Buy	MSFT		1
18		10-01-22 00:00	Buy	MSFT		1
19
Sheet2

Cell Formulas
Range	Formula
E3:E19	E3	=IF([@[Order Type]]="Buy",IF(COUNTIFS([Trade Execution Time],[@[Trade Execution Time]],[Stock],[@Stock],[Order Type],"Sell"),1,""),"")
F3:F19	F3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=30,1,""),""))

 

[Peter_SSs]

There has never been any mention (or sample data) of times before - only dates - so no surprise that the formulas are not working as you want.
Can you confirm that the buy order has to be after the sell order but within 24 hours?
That is a Buy at 10:00 am and a Sell at 11:00 am would not count?

 

[M10Ant]

That is a very fair point and I can't disagree with that!
The source table will already be sorted in ascending trade execution time it that helps, so the sell at 11:00 am would come after the buy at 10:00am. But in case it wasn't, yes I'd need to still count (tally) the sell at 11:00am if it was reordered and happened to appear before the buy at 10:00am row entry (on same day of course).

 

[Peter_SSs]

That did not make sense to me.

still count (tally) the sell at 11:00am if it was reordered and happened to appear before the buy at 10:00am row entry

How can a sell at 11;00 be before a buy at 10:00 am?

Let us have some clear descriptions and clear sample data and expected results.

 

[M10Ant]

Okay. Sorry for the confusion. You're right that a sell at 11:00 can't be before a buy at 10:00. Only in terms of if the source rows happened to be re-ordered somehow.

I just meant if the formula was clever enough to still work even when the source rows were reordered.
E.g. in below example, a "1" tally should still be recorded for the 11-12-21 buy for MSFT, despite it coming before the 11-12-21 sell for MSFT.

Saying that, the source data will generally always be kept in ascending order of trade execution date. So if it's easier to rely on the data being that way that's okay.

Stock Repurchases.xlsx
	B	C	D	E	F
23	Trade Execution Time	Order Type	Stock	Rebuys on same day	Rebuys within 30 days
24	03-06-21 00:00	Buy	APPL
25	07-06-21 00:00	Buy	APPL
26	14-06-21 00:00	Buy	MSFT
27	15-06-21 00:00	Buy	APPL
28	22-06-21 00:00	Buy	MSFT
29	10-07-21 00:00	Sell	APPL
30	16-07-21 00:00	Buy	APPL		1
31	22-07-21 00:00	Buy	APPL		1
32	23-07-21 00:00	Sell	MSFT
33	26-07-21 00:00	Buy	MSFT		1
34	05-11-21 00:00	Buy	MSFT
35	06-12-21 00:00	Sell	MSFT
36	10-12-21 00:00	Buy	MSFT		1
37	11-12-21 14:00	Buy	MSFT		1
38	11-12-21 11:00	Sell	MSFT
39	10-01-22 00:00	Buy	MSFT		1
40
Sheet2

Cell Formulas
Range	Formula
E24:E40	E24	=IF([@[Order Type]]="Buy",IF(COUNTIFS([Trade Execution Time],[@[Trade Execution Time]],[Stock],[@Stock],[Order Type],"Sell"),1,""),"")
F24:F40	F24	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=30,1,""),""))

 

[Peter_SSs]

Is this what you want then?

=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=1,1,""),""))

 

[M10Ant]

Almost. Sorry I think I've just clocked on to what you were trying to query with me.

It shouldn't be tallying a "1" in the row 12-12-21 10:00 buy for MSFT row in below sample. I can see that is still within 24 hours of the sell on 11-12-21 11:00 entry.
I meant for the formula only to tally a "1" for all buys of a stock done on the same day as the sell (24 hour period of the very start of that sell day until the end of that day)

Stock Repurchases.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Trade Execution Time	Order Type	Stock	Rebuys on same day	Rebuys within 30 days		Stock	# Same-day Rebuys	# Rebuys within 30 days
3		03-06-21 00:00	Buy	APPL				APPL	0	2
4		07-06-21 00:00	Buy	APPL				MSFT	2	5
5		14-06-21 00:00	Buy	MSFT
6		15-06-21 00:00	Buy	APPL
7		22-06-21 00:00	Buy	MSFT
8		10-07-21 00:00	Sell	APPL
9		16-07-21 00:00	Buy	APPL		1
10		22-07-21 00:00	Buy	APPL		1
11		23-07-21 00:00	Sell	MSFT
12		26-07-21 00:00	Buy	MSFT		1
13		05-11-21 00:00	Buy	MSFT
14		06-12-21 00:00	Sell	MSFT
15		10-12-21 00:00	Buy	MSFT		1
16		11-12-21 11:00	Sell	MSFT
17		11-12-21 14:00	Buy	MSFT	1	1
18		12-12-21 10:00	Buy	MSFT	1	1
19		10-01-22 00:00	Buy	MSFT		1
20
Sheet2

Cell Formulas
Range	Formula
E3:E20	E3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=1,1,""),""))
F3:F20	F3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=30,1,""),""))
I3:I4	I3	=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys on same day])
J3:J4	J3	=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys within 30 days])