Spreadsheet: 881355d1729435610-spilled-array-trade-profit-and-loss-tradepnl_v2
Sheet: Trade PnL Backup

 | Input from Table (sorted) |  |  |  |  |  |  |  | Correct results (non-spilled array) |  |  |  |  | Spilled-array Solution |  |  | My best results (wrong)
 | All Trades | TradeDateTime | B/S | Qty | MktPrice | Charges | Value |  | Average Purchase Price | Qty accumulated | PnL ex Transaction Cost | Total PnL |  | Average Purchase Price | Qty accumulated |  | Average PP | PnL ex TK | PnL |  | Holding before Trade | Last B Date | Last B Prc | Last S Date (1) | Last S Date (2) | Startdate last Position | #B after SD | Sum B Qtys after SD | #B running | #B running +1 | #S running | #S running +1 | Next B Date (1) | Next B Date (2) | Next S Date (1) | Next S Date (2) | sum b qtys since last S | Overhang | average pp ex ov | last sell avg pp | Sum S after SD | #B Holding Future | Future B Value | Value Helper |  | [NAME] (hld b4) | pnl ex | Value
 | Amazon | 2018-05-10 17:24:00 | S | 1 | 1354.03 | 113.13 | 1354.03 |  |  |  |  |  |  |  |  |  | 957 | 397.03 | 283.9 |  | 2 | 2017-11-06 09:06:00 | 957 | 00:00:00 | 9999-12-31 00:00:00 | 2017-11-06 09:06:00 | 0 | 2 | 0 | 0 | 1 | 2 | 00:00:00 | 9999-12-31 00:00:00 | 2018-08-28 12:54:00 | 2018-08-28 12:54:00 | 0 | 0 | 957 | 0 | 0 | 0 | 0 | 0 | 957 | 957 | 397.03 | 0
 | Amazon | 2018-08-28 12:54:00 | S | 1 | 1652.55 | 192.03 | 1652.55 |  |  |  |  |  |  |  |  |  | 957 | 695.55 | 503.52 |  | 1 | 2017-11-06 09:06:00 | 957 | 2018-05-10 17:24:00 | 2018-05-10 17:24:00 | 2017-11-06 09:06:00 | 0 | 2 | 0 | 0 | 2 | 3 | 00:00:00 | 9999-12-31 00:00:00 | 2021-01-29 12:40:00 | 2021-01-29 12:40:00 | 0 | 0 | 957 | 957 | 0 | 0 | 0 | 0 | 957 | 957 | 695.55 | 0
 | Amazon | 2020-12-02 09:04:00 | B | 1 | 2670.5 | 7.73 | 2670.5 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.73 |  | 0 | 2017-11-06 09:06:00 | 957 | 2018-08-28 12:54:00 | 2018-08-28 12:54:00 | 2020-12-02 09:04:00 | 0 | 1 | 2 | 3 | 0 | 0 | 2021-05-04 10:26:00 | 2021-05-04 10:26:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 | Amazon | 2021-01-29 12:40:00 | S | 1 | 2657 | 7.75 | 2657 |  |  |  |  |  |  |  |  |  | 2670.5 | -13.5 | -21.25 |  | 1 | 2020-12-02 09:04:00 | 2670.5 | 2018-08-28 12:54:00 | 2018-08-28 12:54:00 | 2020-12-02 09:04:00 | 0 | 1 | 0 | 0 | 3 | 4 | 00:00:00 | 9999-12-31 00:00:00 | 2023-08-31 16:34:00 | 2023-08-31 16:34:00 | 1 | 0 | 2670.5 | 957 | 0 | 0 | 0 | 0 | 2670.5 | 2670.5 | -13.5 | 0
 | Amazon | 2021-05-04 10:26:00 | B | 1 | 2814 | 10.25 | 2814 |  |  |  |  |  |  |  |  |  | 0 | 0 | -10.25 |  | 0 | 2020-12-02 09:04:00 | 2670.5 | 2021-01-29 12:40:00 | 2021-01-29 12:40:00 | 2021-05-04 10:26:00 | 0 | 1 | 3 | 4 | 0 | 0 | 2021-09-30 10:53:00 | 2021-09-30 10:53:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0 | 1 | 2814 | 2814 | 0 | 0 | 0 | 2814
 | Amazon | 2021-09-30 10:53:00 | B | 1 | 2864 | 7.63 | 2864 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 1 | 2021-05-04 10:26:00 | 2814 | 2021-01-29 12:40:00 | 2021-01-29 12:40:00 | 2021-05-04 10:26:00 | 1 | 2 | 4 | 5 | 0 | 0 | 2022-06-06 00:00:00 | 2022-06-06 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0 | 1 | 2864 | 2864 | 0 | 0 | 0 | 5678
 | Amazon | 2022-06-06 00:00:00 | B | 38 | 0 | 0 | 0 |  |  |  |  |  |  |  |  |  | 0 | 0 | 0 |  | 2 | 2021-09-30 10:53:00 | 2864 | 2021-01-29 12:40:00 | 2021-01-29 12:40:00 | 2021-05-04 10:26:00 | 2 | 40 | 5 | 6 | 0 | 0 | 2023-09-01 09:00:00 | 2023-09-01 09:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 40 | -2 | 0 | 0 | 0 | 0 | 0 | 5678
 | Amazon | 2023-08-31 16:34:00 | S | 40 | 127.7 | 7.63 | 5108 |  |  |  |  |  |  |  |  |  | 141.95 | -570 | -577.63 |  | 40 | 2022-06-06 00:00:00 | 0 | 2021-01-29 12:40:00 | 2021-01-29 12:40:00 | 2021-05-04 10:26:00 | 2 | 40 | 0 | 0 | 4 | 5 | 00:00:00 | 9999-12-31 00:00:00 | 2023-09-03 09:00:00 | 2023-09-03 09:00:00 | 40 | 0 | 141.95 | 2670.5 | 0 | 0 | 0 | 0 | y | 141.95 | -570 | 5678
 | Amazon | 2023-09-01 09:00:00 | B | 30 | 50 | 7.63 | 1500 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 0 | 2022-06-06 00:00:00 | 0 | 2023-08-31 16:34:00 | 2023-08-31 16:34:00 | 2023-09-01 09:00:00 | 0 | 30 | 6 | 7 | 0 | 0 | 2023-09-02 09:00:00 | 2023-09-02 09:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0 | 30 | 1500 | 1500 | 0 | 0 | 0 | 1500
 | Amazon | 2023-09-02 09:00:00 | B | 10 | 45 | 7.63 | 450 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 30 | 2023-09-01 09:00:00 | 50 | 2023-08-31 16:34:00 | 2023-08-31 16:34:00 | 2023-09-01 09:00:00 | 1 | 40 | 7 | 8 | 0 | 0 | 2023-09-04 09:00:00 | 2023-09-04 09:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 15 | -5 | -225 | 450 | 0 | 0 | 0 | 1950
 | Amazon | 2023-09-03 09:00:00 | S | 15 | 55 | 53 | 825 |  |  |  |  |  |  |  |  |  | 48.75 | 93.75 | 40.75 |  | 40 | 2023-09-02 09:00:00 | 45 | 2023-08-31 16:34:00 | 2023-08-31 16:34:00 | 2023-09-01 09:00:00 | 1 | 40 | 0 | 0 | 5 | 6 | 00:00:00 | 9999-12-31 00:00:00 | 2023-09-05 09:00:00 | 2023-09-05 09:00:00 | 40 | 0 | 48.75 | 141.95 | 0 | 0 | 0 | 0 | y | 48.75 | 93.75 | 1950
 | Amazon | 2023-09-04 09:00:00 | B | 20 | 50 | 7.63 | 1000 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 25 | 2023-09-02 09:00:00 | 45 | 2023-09-03 09:00:00 | 2023-09-03 09:00:00 | 2023-09-01 09:00:00 | 2 | 60 | 8 | 9 | 0 | 0 | 2023-09-06 09:00:00 | 2023-09-06 09:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 30 | -10 | -500 | 1000 | 0 | 0 | 0 | 2950
 | Amazon | 2023-09-05 09:00:00 | S | 30 | 45 | 7.63 | 1350 |  |  |  |  |  |  |  |  |  | 49.16666666666666 | -125 | -132.63 |  | 45 | 2023-09-04 09:00:00 | 50 | 2023-09-03 09:00:00 | 2023-09-03 09:00:00 | 2023-09-01 09:00:00 | 2 | 60 | 0 | 0 | 6 | 7 | 00:00:00 | 9999-12-31 00:00:00 | 2023-09-07 09:00:00 | 2023-09-07 09:00:00 | 20 | 10 | x | 48.75 | 0 | 0 | 0 | 0 | x | 65.55555555555556 | -616.6666666666667 | 2950
 | Amazon | 2023-09-06 09:00:00 | B | 10 | 30 | 7 | 300 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7 |  | 15 | 2023-09-04 09:00:00 | 50 | 2023-09-05 09:00:00 | 2023-09-05 09:00:00 | 2023-09-01 09:00:00 | 3 | 70 | 9 | 10 | 0 | 0 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 25 | -15 | -450 | 300 | 0 | 0 | 0 | 3250
 | Amazon | 2023-09-07 09:00:00 | S | 2 | 60 | 20 | 120 |  |  |  |  |  |  |  |  |  | 46.42857142857143 | 27.14285714285714 | 7.142857142857139 |  | 25 | 2023-09-06 09:00:00 | 30 | 2023-09-05 09:00:00 | 2023-09-05 09:00:00 | 2023-09-01 09:00:00 | 3 | 70 | 0 | 0 | 7 | 8 | 00:00:00 | 9999-12-31 00:00:00 | 2023-09-08 09:00:00 | 2023-09-08 09:00:00 | 10 | 0 | 46.42857142857143 | 0 | 0 | 0 | 0 | 0 | y | 130 | -140 | 3250
 | Amazon | 2023-09-08 09:00:00 | S | 23 | 55 | 90 | 1265 |  |  |  |  |  |  |  |  |  | 46.42857142857143 | 197.1428571428571 | 107.1428571428571 |  | 23 | 2023-09-06 09:00:00 | 30 | 2023-09-07 09:00:00 | 2023-09-07 09:00:00 | 2023-09-01 09:00:00 | 3 | 70 | 0 | 0 | 8 | 9 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 46.42857142857143 | 46.42857142857143 | 0 | 0 | 0 | 0 | y | 141.304347826087 | -1985 | 3250
 | Bayer | 2021-01-28 17:38:00 | B | 25 | 6.85 | 10.75 | 171.25 |  |  |  |  |  |  |  |  |  | 0 | 0 | -10.75 |  | 0 | 00:00:00 | 0 | 00:00:00 | 9999-12-31 00:00:00 | 2021-01-28 17:38:00 | 0 | 25 | 1 | 2 | 0 | 0 | 2021-01-29 13:35:00 | 2021-01-29 13:35:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0 | 25 | 171.25 | 171.25 | 0 | 0 | 0 | 171.25
 | Bayer | 2021-01-29 13:35:00 | B | 400 | 10.1 | 11.57 | 4040 |  |  |  |  |  |  |  |  |  | 0 | 0 | -11.57 |  | 25 | 2021-01-28 17:38:00 | 6.85 | 00:00:00 | 9999-12-31 00:00:00 | 2021-01-28 17:38:00 | 1 | 425 | 2 | 3 | 0 | 0 | 2021-02-01 16:10:00 | 2021-02-01 16:10:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 424 | -24 | -242.4 | 4040 | 0 | 0 | 0 | 4211.25
 | Bayer | 2021-02-01 15:40:00 | S | 424 | 13.6 | 346.85 | 5766.4 |  |  |  |  |  |  |  |  |  | 9.908823529411764 | 1565.058823529412 | 1218.208823529412 |  | 425 | 2021-01-29 13:35:00 | 10.1 | 00:00:00 | 9999-12-31 00:00:00 | 2021-01-28 17:38:00 | 1 | 425 | 0 | 0 | 1 | 2 | 00:00:00 | 9999-12-31 00:00:00 | 2021-03-15 17:17:00 | 2021-03-15 17:17:00 | 0 | 0 | 9.908823529411764 | 0 | 0 | 0 | 0 | 0 | y | 9.908823529411764 | 1565.058823529412 | 4211.25
 | Bayer | 2021-02-01 16:10:00 | B | 490 | 12 | 13.65 | 5880 |  |  |  |  |  |  |  |  |  | 0 | 0 | -13.65 |  | 1 | 2021-01-29 13:35:00 | 10.1 | 2021-02-01 15:40:00 | 2021-02-01 15:40:00 | 2021-01-28 17:38:00 | 2 | 915 | 3 | 4 | 0 | 0 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 491 | -1 | -12 | 5880 | 0 | 0 | 0 | 10091.25
 | Bayer | 2021-03-15 17:17:00 | S | 491 | 11.5 | 13.38 | 5646.5 |  |  |  |  |  |  |  |  |  | 11.02868852459016 | 231.4139344262294 | 218.0339344262294 |  | 491 | 2021-02-01 16:10:00 | 12 | 2021-02-01 15:40:00 | 2021-02-01 15:40:00 | 2021-01-28 17:38:00 | 2 | 915 | 0 | 0 | 2 | 3 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 490 | 1 | x | 9.908823529411764 | 0 | 0 | 0 | 0 | x | 20.55244399185336 | -4444.75 | 10091.25
 | Nestle | 2021-07-15 16:56:00 | B | 11 | 125.58 | 7.63 | 1381.38 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 0 | 00:00:00 | 0 | 00:00:00 | 9999-12-31 00:00:00 | 2021-07-15 16:56:00 | 0 | 11 | 1 | 2 | 0 | 0 | 2022-06-02 12:10:00 | 2022-06-02 12:10:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0 | 11 | 1381.38 | 1381.38 | 0 | 0 | 0 | 1381.38
 | Nestle | 2022-06-02 12:10:00 | B | 13 | 139.78 | 7.63 | 1817.14 |  |  |  |  |  |  |  |  |  | 0 | 0 | -7.63 |  | 11 | 2021-07-15 16:56:00 | 125.58 | 00:00:00 | 9999-12-31 00:00:00 | 2021-07-15 16:56:00 | 1 | 24 | 2 | 3 | 0 | 0 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 0 | 0 | 24 | -11 | -1537.58 | 1817.14 | 0 | 0 | 0 | 3198.52
 | Nestle | 2023-03-07 10:56:00 | S | 24 | 144.58 | 79.14 | 3469.92 |  |  |  |  |  |  |  |  |  | 133.2716666666667 | 271.3999999999996 | 192.2599999999996 |  | 24 | 2022-06-02 12:10:00 | 139.78 | 00:00:00 | 9999-12-31 00:00:00 | 2021-07-15 16:56:00 | 1 | 24 | 0 | 0 | 1 | 2 | 00:00:00 | 9999-12-31 00:00:00 | 00:00:00 | 9999-12-31 00:00:00 | 0 | 0 | 133.2716666666667 | 0 | 0 | 0 | 0 | 0 | y | 133.2716666666667 | 271.3999999999996 | 3198.52