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]

With this data, should row 16 count as a 1 for Rebuys on the same day? It is on the same day but before the Sell.

M10Ant_1.xlsm
	B	C	D
2	Trade Execution Time	Order Type	Stock
3	3/06/2021 0:00	Buy	APPL
4	7/06/2021 0:00	Buy	APPL
5	14/06/2021 0:00	Buy	MSFT
6	15/06/2021 0:00	Buy	APPL
7	22/06/2021 0:00	Buy	MSFT
8	10/07/2021 0:00	Sell	APPL
9	16/07/2021 0:00	Buy	APPL
10	22/07/2021 0:00	Buy	APPL
11	23/07/2021 0:00	Sell	MSFT
12	26/07/2021 0:00	Buy	MSFT
13	5/11/2021 0:00	Buy	MSFT
14	6/12/2021 0:00	Sell	MSFT
15	10/12/2021 0:00	Buy	MSFT
16	11/12/2021 10:00	Buy	MSFT
17	11/12/2021 11:00	Sell	MSFT
18	11/12/2021 14:00	Buy	MSFT
19	12/12/2021 10:00	Buy	MSFT
Sheet2

 

[M10Ant]

Yes that's correct it should still count.

 

[Peter_SSs]

Is this what you want then?

M10Ant_1.xlsm
	B	C	D	E
2	Trade Execution Time	Order Type	Stock	Rebuys on same day
3	3/06/2021 0:00	Buy	APPL
4	7/06/2021 0:00	Buy	APPL
5	14/06/2021 0:00	Buy	MSFT
6	15/06/2021 0:00	Buy	APPL
7	22/06/2021 0:00	Buy	MSFT
8	10/07/2021 0:00	Sell	APPL
9	16/07/2021 0:00	Buy	APPL
10	22/07/2021 0:00	Buy	APPL
11	23/07/2021 0:00	Sell	MSFT
12	26/07/2021 0:00	Buy	MSFT
13	5/11/2021 0:00	Buy	MSFT
14	6/12/2021 0:00	Sell	MSFT
15	10/12/2021 0:00	Buy	MSFT
16	11/12/2021 10:00	Buy	MSFT	1
17	11/12/2021 11:00	Sell	MSFT
18	11/12/2021 14:00	Buy	MSFT	1
19	12/12/2021 10:00	Buy	MSFT
20	10/01/2022 0:00	Buy	MSFT
Sheet2

Cell Formulas
Range	Formula
E3:E20	E3	=IF([@[Order Type]]="Sell","",IF(COUNT(FILTER([Trade Execution Time],((INT([Trade Execution Time])=INT([@[Trade Execution Time]]))*([Stock]=[@Stock])*([Order Type]="Sell")),"")),1,""))

 

[M10Ant]

Yeah!!!!!!! That's perfect. Thank you Peter

 

[M10Ant]

Just one thing I've noticed, but completely understand if I'm pushing my luck now, so asking this very tentatively...

If there's a null value (="") in one of the [Trade Execution Time] cells, any existing tally entries across the entire "Rebuys on same day" column get deleted.
In below example, the last [Trade Execution Time] cell has a value of null (="") and this has deleted the "1" tallies in the two 11-12-21 MSFT buy entries (highlighted in yellow)

Is there any error trapping that can be done so that it doesn't delete any tallies that do apply.

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		11-12-21 11:59	Buy	MSFT		1
19		12-12-21 10:00	Buy	MSFT		1
20		10-01-22 00:00	Buy	MSFT		1
21
Sheet2

Cell Formulas
Range	Formula
E3:E21	E3	=IF([@[Order Type]]="Sell","",IF(COUNT(FILTER([Trade Execution Time],((INT([Trade Execution Time])=INT([@[Trade Execution Time]]))*([Stock]=[@Stock])*([Order Type]="Sell")),"")),1,""))
F3:F21	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,""),""))
B21	B21	=""

 

[Peter_SSs]

If there's a null value (="") in one of the [Trade Execution Time] cells ..

Are the rest of the cells in that table column also actually formulas?

Try this instead

=IF([@[Order Type]]="Sell","",IF(COUNT(FILTER([Trade Execution Time],IFERROR(((INT([Trade Execution Time])=INT([@[Trade Execution Time]]))*([Stock]=[@Stock])*([Order Type]="Sell")),0),"")),1,""))

 

[M10Ant]

Thanks very much Peter That works! Yes all the table data columns ([Trade Exec. Time], [Order Type] and [Stock]) are really formulas, getting their data from a primary source table.
I didn't show this in above examples just to keep it simple, but really they are using:

Trade Exec. Time:
=IFERROR(INDEX(tblTradeSource[Trade Exec. Time],[@[SRC Row]]),"")
Order Type:
=IFERROR(INDEX(tblTradeSource[Order Type],[@[SRC Row]]),"")
Stock:
=IFERROR(INDEX(tblTradeSource[Stock,[@[SRC Row]]),"")

It's when I dragged my filtered table down, it obviously padded out the empty rows with null values (="") as per above IFERROR function, and that's when I spotted the issue. I appreciate this is something that couldn't have been captured above.

I tried to fix the issue with the null values myself by having a FILTER function within the existing FILTER function that filtered out all non-numerical Trade Exec. Time values first, but just couldn't get it work.

It all seems okay now with the new formula - unless you can spot an issue with the above.

 

[Peter_SSs]

It all seems okay now with the new formula

Good news! Let's just stick with that for now.

 

[M10Ant]

Aye I agree! Thanks very much for all your help and time on this Peter. Especially putting up with all the back and forth from me. I can't thank you enough. It's literally saved me months - even if I could have worked out a solution for this in the end, which I very much doubt!

 

[Peter_SSs]

You are very welcome.