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 calculate the adjusted quantity but not able to get the adjusted buy price Could anyone please help?
Here is the Sample trade log raw data
import pandas as pd
import numpy as np
raw_data = {'Date': ['04-23-2020', '05-05-2020', '05-05-2020', '05-11-2020', '05-11-2020',
'05-12-2020', '05-12-2020', '05-27-2020', '06-03-2020', '06-03-2020',
'06-03-2020', '06-03-2020', '06-03-2020'],
'Type': ['Buy', 'Buy', 'Buy', 'Buy', 'Buy', 'Buy', 'Buy',
'Sell', 'Sell', 'Sell', 'Buy', 'Sell', 'Sell'],
'Symbol': ['TSE:AC', 'TSE:AC', 'TSE:HEXO', 'TSE:BPY.UN', 'TSE:BPY.UN',
'TSE:BPY.UN', 'TSE:AC', 'TSE:BPY.UN', 'TSE:AC', 'TSE:BPY.UN',
'TSE:AC', 'TSE:BPY.UN', 'TSE:HEXO'],
'Quantity': [75, 100, 1450, 200, 50, 80, 150, 100, 125, 100, 100, 50, 1450],
'Amount per unit': [18.04, 17.29, 0.73, 13.04, 13.06, 12.65, 15.9, 15.01,
18.05, 14.75, 15.8, 14.7, 1.07],
'Turnover': [1353, 1729, 1058.5, 2608, 653, 1012, 2385, 1501, 2256.25, 1475, 1580, 735, 1551.5],
}
df = pd.DataFrame (raw_data, columns = ['Date','Type','Symbol','Quantity','Amount per unit', 'Turnover']).sort_values(['Date','Symbol']).reset_index(drop = True)
I am able to get the Adjusted quantity without any issues but I am not able to get the correct Adjusted Price Per Unit. The condition here is if I sell a stock, my Adjusted Price Per Unit should not change and remain the same as the last adjusted price when the buy was made for that stock.
#to calculate adjusted quantity. this works as expected
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()
#section where I am having problem. Works good until I reach the row where sell was made
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'])
Running this code will result in the following
For eg:, the adjusted price for the row at index 7 should be 12.948 (same as row at index 6) instead of 12.052. Also, the last row adjusted price should be 0.73 (same as row at index 2) since I am buying and selling same amount of shares of the stock.
Eg 2: At index 6, I bought 80 shares of BPY at 12.65 which brought my average price down to 12.94 for a total of 330 shares(250+80). Now, I’m selling 100 shares at 15.01(index 7). My code brings it adjusted cost to 12.05. What I need my adjusted cost to be 12.94 instead of 12.05. Simply put, ignore adjusting the price if the transaction type is Sell. Use the last adjusted price in the last buy type transaction for that particular stock.
The last 2 lines of my code are not correct. Could you please help me with correctly calculating the adjusted price per unit? Thanks :)
Advertisement
Answer
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 |