Simple google sheet trade summary from Tradingview .csv file

Question

I have this raw data in this format:

I want to add a column (or more than one if it’s not possible to find a single formula to complete the job) that matches each open trade by instrument and chronological order with the related closing trade.

For example, row 21 opens a short position on MESH24, 1 lot, at 4762 (column H). The next action is row 20, where the short is covered at 4760, resulting in a 2-point profit. In this case, the rows are subsequent, but they could be distributed differently, like for example row 5 that opens a short that is closed in row 2.

---

I'm sorry: I posted in a hurry and I did not know I had to elaborate more. It's two weeks I am trying to find a formula with a simple logic: since the rows are in chronological order, you always encounter first the opening trade. For instance, if you come across a buy on symbol X, the formula must then locate a sell on the same symbol at a later time.

Following the FIFO criteria, the first opposite order you find for the same instrument will close your initial open position, irrespective of any other open positions on the same or different instruments.

After numerous tries the best I could come with is this: "=IF(AND(B2="Buy", B3="Sell", A2=A3), D2-D3, IF(AND(B2="Sell", B3="Buy", A2=A3), D3-D2, ""))" as a helper column for these data: .

All is ok if the opening and closing rows defining a trade are adjacent, but if they are not the formula does not work because check for the closing order only on subsequent rows.

Answer 1

Here's one approach you may test out:

=map(A2:A,B2:B,D2:D,lambda(Σ,Λ,Ξ,if(isodd(countif(A2:Σ,Σ))+(Σ=""),,if(Λ="Buy",-1,1)*((Ξ-xlookup(Σ,A2:offset(Σ,-1,),D2:offset(Ξ,-1,),,,-1))*switch(mid(Σ,6,3),"MES",4,"MNQ",4,"MGC",10,"M6E",10000,1)))))