Calculate Adjusted Cost Base Using Python Pandas ( Portfolio Analysis Of Stocks Buy/sell)

I am trying to do a portfolio analysis of my trading and trying to calculate the adjusted cost base price. I have tried almost everything but nothing seems to work. I am able to ca

Solution 1:

If you don't calculate the adjusted price for the sale, as you would comment, then you can process the sale line as NA and fill it in with the immediately preceding value of the same stock. As a confirmation in your code, do you not need to consider the same stock when calculating the 'Adjusted Quantity' at the beginning?

df.sort_values(['Symbol','Date','Type'], ascending=[True, True, True], inplace=True)
# your code
df['Adjusted Quantity'] = df.apply(lambda x: ((x.Type == "Buy") - (x.Type == "Sell")) * x['Quantity'], axis = 1)
df['Adjusted Quantity'] = df.groupby('Symbol')['Adjusted Quantity'].cumsum()
df['Adjusted Price Per Unit'] = df.apply(lambda x: ((x.Type == "Buy") - (x.Type == "Sell")) * x['Turnover'], axis = 1)
df['Adjusted Price Per Unit'] = df.groupby('Symbol')['Adjusted Price Per Unit'].cumsum().div(df['Adjusted Quantity'])

df.loc[df['Type'] == 'Sell',['Adjusted Price Per Unit']] = np.NaN
df.fillna(method='ffill', inplace=True)

|    | Date       | Type   | Symbol     |   Quantity |   Amount per unit |   Turnover |   Adjusted Quantity |   Adjusted Price Per Unit |
|---:|:-----------|:-------|:-----------|-----------:|------------------:|-----------:|--------------------:|--------------------------:|
|  0 | 04-23-2020 | Buy    | TSE:AC     |         75 |             18.04 |    1353    |                  75 |                   18.04   |
|  1 | 05-05-2020 | Buy    | TSE:AC     |        100 |             17.29 |    1729    |                 175 |                   17.6114 |
|  5 | 05-12-2020 | Buy    | TSE:AC     |        150 |             15.9  |    2385    |                 325 |                   16.8215 |
|  9 | 06-03-2020 | Buy    | TSE:AC     |        100 |             15.8  |    1580    |                 425 |                   16.5812 |
|  8 | 06-03-2020 | Sell   | TSE:AC     |        125 |             18.05 |    2256.25 |                 300 |                   16.5812 |
|  3 | 05-11-2020 | Buy    | TSE:BPY.UN |        200 |             13.04 |    2608    |                 200 |                   13.04   |
|  4 | 05-11-2020 | Buy    | TSE:BPY.UN |         50 |             13.06 |     653    |                 250 |                   13.044  |
|  6 | 05-12-2020 | Buy    | TSE:BPY.UN |         80 |             12.65 |    1012    |                 330 |                   12.9485 |
|  7 | 05-27-2020 | Sell   | TSE:BPY.UN |        100 |             15.01 |    1501    |                 230 |                   12.9485 |
| 10 | 06-03-2020 | Sell   | TSE:BPY.UN |        100 |             14.75 |    1475    |                 130 |                   12.9485 |
| 11 | 06-03-2020 | Sell   | TSE:BPY.UN |         50 |             14.7  |     735    |                  80 |                   12.9485 |
|  2 | 05-05-2020 | Buy    | TSE:HEXO   |       1450 |              0.73 |    1058.5  |                1450 |                    0.73   |
| 12 | 06-03-2020 | Sell   | TSE:HEXO   |       1450 |              1.07 |    1551.5  |                   0 |                    0.73   |