How to aggregate multiple transactions to show complete trades in one row in Google Sheets?

Question

I have a table in Google Sheets that logs individual transactions for various stock symbols, structured as follows:

I want to aggregate these transactions to show complete trades in a summarized format like this:

Each row in the output represents a complete trade, aggregated from multiple transactions. For each trade, I need to calculate the average entry time, average exit time, total quantity, average entry price, and average exit price.

Here is the sample document

I tried these solutions given by @player0. It's working perfectly. The only catch is that it combines these two WMT trades into one WMT trade and it doesn't show the actual size. Other than that, it's working really well.

How to combine data from multiple rows into a single row based on numbers and certain texts in Google Sheets?

Any assistance on how to achieve this in Google Sheets would be greatly appreciated!

Answer 1

I was able to figure out how to separate these two trades using your previous answers

...so I skipped this step and just added Total Quantity as per your request and also optimized for speed, removed dependency for RT helper and shortened the 2 years old formula to make it more readable

see:

={SPLIT("Date♦AV Entry time♦AV Exit Time♦Symbol♦Total Quantity♦AV Entry Price♦AV Exit Price", "♦");
 INDEX(LET(
 a, A2:INDEX(A:A, MATCH(, 0/(A:A<>""))),  
 b, OFFSET(a,,1), 
 c, OFFSET(b,,1), 
 d, OFFSET(c,,1), 
 e, OFFSET(d,,1), 
 r, ROW(a),
 g, MMULT(1*TRANSPOSE(IF((TOROW(r)>=r)*(c&a = TRANSPOSE(c&a)), d, 0)), r^0), 
 p, (d>0)*(g>0), 
 n, (d<0)*(g<1), 
 q, IF((d>0)*COUNTIFS(a&c&p, a&c&p, r, "<="&r), {b, e, d}, ), 
 w, IF((d<0)*COUNTIFS(a&c&n, a&c&n, r, "<="&r), {b, e}, ), 
 u, SPLIT(UNIQUE(c)&"×0♦0", "♦"), 
 z, IF(d>0, COUNTIFS(c&p, c&p, r, "<="&r), ), x, COUNTIFS(c, c, r, "<="&r),
 m, a&c&VLOOKUP(r, IF(IF(a="",,IF(VLOOKUP(c&"×"&x-1, {u; c&"×"&x, g}, 2, )<>0,,z))<>"", 
                  {r, IF(a="",,IF(VLOOKUP(c&"×"&x-1, {u; c&"×"&x, p}, 2, )<>0,,z))}), 2), 
 QUERY(QUERY({a, c, q, w, m}, 
 "select Col1,avg(Col3),avg(Col6),Col2,sum(Col5),avg(Col4),avg(Col7) 
  where Col1 is not null group by Col1,Col2,Col8"), "offset 1", )))}

spreadsheet demo