Spilled Array Trade Profit and Loss

Hi everyone,

I want to use spilled array formulas to calculate PnL per trade based on a table containing all trades and the relevant data.

The page "Ledger" contains the raw data.

The page "Trade PnL" contains:

1. The alphabetically sorted spilled arrays.
2. The results I'd expect calculated manually i.e. without spilled arrays.
3. My experiments and what I've got so far.

As you'll see, it gets trickier when there are multiple buys end sells before a position is finally closed (Marked yellow in the sheet).

Maybe I'm making it too complicated, maybe I'm incompetent, who knows :D

Thank you very much for your help and ideas.

I use basket averages to calculate the PnL.

Best,
calle

Hi,
you could use just one formula in "Trade PnL" cell B4.

Formula:

Please Login or Register  to view this content.

Hi,
I reviewed again your workbook and my formula previously suggested would cause too much trouble, so my new formula is just for the column B.

Formula:

Please Login or Register  to view this content.

For the calculation of the 'Average Purchase Price' I needed to create a new column 'Qty accumulated'.

Average Purchase Price formula:

Formula:

Please Login or Register  to view this content.

Qty accumulated formula:

Formula:

Please Login or Register  to view this content.

I'm sending a file with the formula, but my version is Portuguese and it may not be translated to English even though when I opened your file it was translate to Portuguese formulas.

Tks for the information AliGW!