Running P&L
- To: mathgroup at smc.vnet.net
- Subject: [mg119456] Running P&L
- From: Thomas <zanzibarlector at googlemail.com>
- Date: Sat, 4 Jun 2011 06:22:13 -0400 (EDT)
Dear Experts,
Could you please help me with the calculation of running Profit & Loss
Input:
A list with buy (value=1) or sell (value=2) indicator, price, number
of items, cumulative number of items
data = {{BUYSELL, PRICE, ITEMS, CUMITEMS}, {1, 67.24, 1, 1}, {1,
67.21,
4, 5}, {1, 67.21, 1, 6}, {2, 67.27, 5, 1}, {1, 67.27, 1, 2}, {1,
67.26, 1, 3}, {1, 67.24, 2, 5}, {1, 67.24, 1, 6}, {2, 67.24, 2,
4}, {1, 67.26, 1, 5}, {1, 67.26, 2, 7}, {1, 67.26, 1, 8}, {2,
67.26, 8, 0}, {1, 67.26, 2, 2}}
As output I need two additional columns: profitloss from the
transaction and cumulative profitloss
output = {{BUYSELL, PRICE, NBRITEMS, CUMITEMS, PROFITLOSS,
PROFITLOSSCUM}, {1, 67.24, 1, 1, 0, 0}, {1, 67.21, 4, 5, 0, 0}, {1,
67.21, 1, 6, 0, 0}, {2, 67.27, 5, 1, 0.27, 0.27}, {1, 67.27, 1, 2,
0, 0.27}, {1, 67.26, 1, 3, 0, 0.27}, {1, 67.24, 2, 5, 0,
0.27}, {1, 67.24, 1, 6, 0, 0.27}, {2, 67.24, 2, 4, 0, 0.27}, {1,
67.26, 1, 5, 0, 0.27}, {1, 67.26, 2, 7, 0, 0.27}, {1, 67.26, 1, 8,
0, 0.27}, {2, 67.26, 8, 0, 0.06, 0.33}, {1, 67.26, 2, 2, 0, 0.33}}
The profit or loss should be calculated as first-in-first-out (FIFO).
>From my example:
In my first four transactions:
buy 1 team at 67.24
buy 4 items at 67.21
buy 1 items at 67.21
sell 5 items at 67.27
So at transaction #4 I am selling 5 items. My profit is calculated as
5*67.27-(1*67.24+4*67.21) = 0.27 profit
My CUMITEMS is at 1 now
At transaction nbr nine I am selling 2 items at 67.24 but previously I
bought one at 67.21 and another one at 67.27, so my profit from those
transactions is zero.
Thanks,
Thomas