Reports

This formula it's a bit long, but it works:

=IF(C2>0;"-"; LET(
  operation_ticker;B2; operation_date;A2; operation_shares;C2; operation_amount;D2;
  tickers;$B$2:$B; dates;$A$2:$A; shares;$C$2:$C; amounts;$D$2:$D;
  previous_sales; SUMPRODUCT((dates<operation_date)*(tickers=operation_ticker)*(shares<0)*shares);
  data; SORT(FILTER(HSTACK(dates; tickers; shares; amounts); (dates<operation_date)*(tickers=operation_ticker)*(shares>0)); 1; 1);
  accumulated_shares; SCAN(0; INDEX(data;;3); LAMBDA(x; y; x+y));
  sales_shares; MAP(accumulated_shares; INDEX(data;;3); LAMBDA(k; i; MAX(0; MIN(-previous_sales-operation_shares; k) - MAX(-previous_sales; k - i))));
  ABS(operation_amount) - SUMPRODUCT(sales_shares; ArrayFormula(ABS(INDEX(data;;4))/INDEX(data;;3)))
))

Google sheets example: https://docs.google.com/spreadsheets/d/1l1RzVFQiap-XtvWKmkB9Uo0HAmhoviujL7YzoX3KH9Y/edit?usp=sharing

Reference: https://sudapollismo.substack.com/p/fifo-capital-gains-excel-formula

79595155