使用 Python pandas 计算调整后的成本基础(股票买入/卖出的投资组合分析) [英] 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 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
例如:索引 7 行的调整后价格应为 12.948(与索引 6 行相同)而不是 12.052.此外，最后一行调整后的价格应为 0.73(与索引 2 中的行相同)，因为我正在买卖相同数量的股票.
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.
例 2:在指数 6 时，我以 12.65 的价格买入了 80 股 BPY，这使我的平均价格降至 12.94，总共 330 股(250+80).现在，我以 15.01(指数 7)卖出 100 股.我的代码将调整后的成本调整为 12.05.我需要调整后的成本是 12.94 而不是 12.05.简单地说，如果交易类型为卖出，则忽略调整价格.使用该特定股票的最后一次买入类型交易中的最后调整价格.
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.
我的代码的最后 2 行不正确.你能帮我正确计算每单位调整后的价格吗?谢谢:)
The last 2 lines of my code are not correct. Could you please help me with correctly calculating the adjusted price per unit? Thanks :)
推荐答案
如果您没有像评论那样计算销售的调整后价格，那么您可以将销售行处理为 NA 并填写同一股票的前一价值.作为您代码中的确认，您在开始计算调整后数量"时是否不需要考虑相同的库存?
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 |
这篇关于使用 Python pandas 计算调整后的成本基础(股票买入/卖出的投资组合分析)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
