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

M10Ant

New Member
Joined
Dec 30, 2021
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJ
1
2Trade Execution TimeOrder TypeStockRebuys on same dayRebuys within 30 daysStock# Same-day Rebuys# Rebuys within 30 days
303/06/2021BuyAPPL  APPL02
407/06/2021BuyAPPL  MSFT04
514/06/2021BuyMSFT  
615/06/2021BuyAPPL  
722/06/2021BuyMSFT  
810/07/2021SellAPPL  
916/07/2021BuyAPPL 1
1022/07/2021BuyAPPL 1
1123/07/2021SellMSFT  
1226/07/2021BuyMSFT 1
1305/11/2021BuyMSFT  
1406/12/2021SellMSFT  
1510/12/2021BuyMSFT 1
1611/12/2021SellMSFT  
1711/12/2021BuyMSFT 1
1810/01/2022BuyMSFT 1
Sheet2
Cell Formulas
RangeFormula
E3:E18E3=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:F18F3=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:I4I3=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys on same day])
J3:J4J3=SUMIF(tblTradeOrders[Stock],[@Stock],tblTradeOrders[Rebuys within 30 days])
 
Last edited:
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
BCD
2Trade Execution TimeOrder TypeStock
33/06/2021 0:00BuyAPPL
47/06/2021 0:00BuyAPPL
514/06/2021 0:00BuyMSFT
615/06/2021 0:00BuyAPPL
722/06/2021 0:00BuyMSFT
810/07/2021 0:00SellAPPL
916/07/2021 0:00BuyAPPL
1022/07/2021 0:00BuyAPPL
1123/07/2021 0:00SellMSFT
1226/07/2021 0:00BuyMSFT
135/11/2021 0:00BuyMSFT
146/12/2021 0:00SellMSFT
1510/12/2021 0:00BuyMSFT
1611/12/2021 10:00BuyMSFT
1711/12/2021 11:00SellMSFT
1811/12/2021 14:00BuyMSFT
1912/12/2021 10:00BuyMSFT
Sheet2
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Is this what you want then?

M10Ant_1.xlsm
BCDE
2Trade Execution TimeOrder TypeStockRebuys on same day
33/06/2021 0:00BuyAPPL 
47/06/2021 0:00BuyAPPL 
514/06/2021 0:00BuyMSFT 
615/06/2021 0:00BuyAPPL 
722/06/2021 0:00BuyMSFT 
810/07/2021 0:00SellAPPL 
916/07/2021 0:00BuyAPPL 
1022/07/2021 0:00BuyAPPL 
1123/07/2021 0:00SellMSFT 
1226/07/2021 0:00BuyMSFT 
135/11/2021 0:00BuyMSFT 
146/12/2021 0:00SellMSFT 
1510/12/2021 0:00BuyMSFT 
1611/12/2021 10:00BuyMSFT1
1711/12/2021 11:00SellMSFT 
1811/12/2021 14:00BuyMSFT1
1912/12/2021 10:00BuyMSFT 
2010/01/2022 0:00BuyMSFT 
Sheet2
Cell Formulas
RangeFormula
E3:E20E3=IF([@[Order Type]]="Sell","",IF(COUNT(FILTER([Trade Execution Time],((INT([Trade Execution Time])=INT([@[Trade Execution Time]]))*([Stock]=[@Stock])*([Order Type]="Sell")),"")),1,""))
 
Upvote 0
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
ABCDEF
1
2Trade Execution TimeOrder TypeStockRebuys on same dayRebuys within 30 days
303-06-21 00:00BuyAPPL  
407-06-21 00:00BuyAPPL  
514-06-21 00:00BuyMSFT  
615-06-21 00:00BuyAPPL  
722-06-21 00:00BuyMSFT  
810-07-21 00:00SellAPPL  
916-07-21 00:00BuyAPPL 1
1022-07-21 00:00BuyAPPL 1
1123-07-21 00:00SellMSFT  
1226-07-21 00:00BuyMSFT 1
1305-11-21 00:00BuyMSFT  
1406-12-21 00:00SellMSFT  
1510-12-21 00:00BuyMSFT 1
1611-12-21 11:00SellMSFT  
1711-12-21 14:00BuyMSFT 1
1811-12-21 11:59BuyMSFT 1
1912-12-21 10:00BuyMSFT 1
2010-01-22 00:00BuyMSFT 1
21   
Sheet2
Cell Formulas
RangeFormula
E3:E21E3=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:F21F3=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,""),""))
B21B21=""
 
Upvote 0
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

Excel Formula:
=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,""))
 
Upvote 0
Solution
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top