Creating high and low balance calculations from a dataset

I have a list of currency trades, and from this I need to calculate the high and low points of the balance of each currency. I have created a simple example in the Sheet below:

https://docs.google.com/spreadsheets/d/1fxlfh-WBquyTR7wGKgHE3p2GV1zT9eSdrKmO9FJ3A8E/edit?usp=sharing

Here there are six trades involving three different currencies. Assuming that the balance of each currency is 0 before trade #1, I have manually calculated the balance high and lows in the table on the right for each of the three currencies.

How would I go about calculating these balance high and lows through a formula?

Solution 1:

try:

=INDEX(QUERY({QUERY(FLATTEN({B2:B, D2:D}), "where Col1 is not null", ), 
 MMULT(--TRANSPOSE(IF((SEQUENCE(1, COUNTA(A2:A)*2)>=SEQUENCE(COUNTA(A2:A)*2))*(
 QUERY(FLATTEN({B2:B, D2:D}), "where Col1 is not null", )=TRANSPOSE(
 QUERY(FLATTEN({B2:B, D2:D}), "where Col1 is not null", ))),
 QUERY(FLATTEN({C2:C, IF(E2:E="",,-E2:E)}), "where Col1 is not null", ), 0)),
 SEQUENCE(COUNTA(A2:A)*2)^0)}, 
 "select Col1,max(Col2),min(Col2) 
  group by Col1 
  label max(Col2)'Balance high',min(Col2)'Balance low'"))