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