用FIFO计算股票交易的已实现和未实现市盈率

Question

我对Python非常陌生，我一直试图弄明白：如何从交易数据到我的投资组合中的数据(包括已实现和未实现的P/L? )。

到目前为止，我从代理下载交易列表，只需稍微清理一下数据。举个例子，假设我从一个数据开始，它有2只股票(ABCD和EFGH)，按股票和日期排序(顶部是最老的)，包括股票名称、数量、交易方向和价格。该设置如下所示：

ABCD 5 Buy 100
ABCD 3 Buy 90
ABCD 6 Sell 105
EFGH 4 Buy 50
EFGH 3 Sell 55
EFGH 2 Buy 53
EFGH 3 Sell 60

我想得出一个数据，其中包括股票名称、投资组合中的数量、已变现市盈率和未变现市盈率。假设ABCD的当前价格包括在其他地方(例如，在另一个数据中；假设它此时的交易价格为120 )，这是用于未变现的P/L --它将如下所示：

ABCD 2 40 60
EFGH 0 39 0

..。如此真实。ABCD的P/L为5*(105-100)+1*(105-90)，且不真实。P/L为2*(120-90)等。

queue或deque似乎是解决方案，我怀疑这是一个非常直截了当的练习，但我真的不知道如何实现它，因此任何帮助都是值得赞赏的。我发现了一个类似的问题How to calculate realized P&L of stock trades using the FIFO method?，但没有应用它的例子。

Answer 1

我不认为这将直接回答你的问题，关于排队或deque，但它肯定会给你另一个更冗长的方式做同样的。如果您的数据结构如下：

Stock  Type     Price  Quantity           TimeStamp  Stocks in Hand
TQQQ   buy   63.1200      14.0   2020-08-14 19:50:00            14.0
TQQQ   buy   71.7800      60.0   2020-08-25 13:06:41            74.0
TQQQ  sell   71.0025      74.0   2020-09-04 13:49:42             0.0
TQQQ   buy   69.0750      60.0   2020-09-04 14:00:16            60.0
TQQQ  sell   82.7900      60.0   2020-12-01 18:08:54             0.0
TQQQ   buy   82.5450       2.0   2020-12-14 22:42:22             2.0
TQQQ   buy   84.7000      40.0   2020-12-21 13:58:03            42.0
TQQQ   buy  104.0000       1.0   2021-01-25 15:35:28            43.0
TQQQ   buy   96.5000       2.0   2021-01-27 19:32:45            45.0
TQQQ   buy   91.4000       3.0   2021-01-29 20:45:38            48.0
TQQQ   buy   96.3399      13.0   2021-02-01 16:48:30            61.0
TQQQ   buy   96.3400      10.0   2021-02-01 16:50:34            71.0
TQQQ  sell  100.0000      15.0   2021-02-02 14:02:11            56.0
TQQQ  sell  102.0000      10.0   2021-02-02 16:55:15            46.0

然后使用以下方法：

def get_profit(data,stock):
 import pandas as pd
 buys = get_trans_types(mytrans[mytrans['Stock']==stock], 'buy')
 sells = get_trans_types(mytrans[mytrans['Stock']==stock], 'sell')
 profit = list()
 i = -1
 while (i < (len(sells)-1)):
 i = i + 1
 l = 0
 p = 0
 t = 0 
 r = 0
 inv = 0 
 if sells[i][3] == buys[l][3]:
  p = (sells[i][2] - buys[l][2])*sells[i][3]
  inv = buys[l][2]*sells[i][3]
  t = max(1,(sells[i][4] - buys[l][4]).days)
  r = (p / inv) * (365/t)
  profit.append([sells[i][0],sells[i][1],sells[i][2],sells[i][3],sells[i][4],sells[i][5],p,inv,t,r])
  buys.pop(l)
 elif sells[i][3] < buys[l][3]:
  p = (sells[i][2]-buys[l][2])*sells[i][3]
  inv = buys[l][2]*sells[i][3]
  t = max(1,(sells[i][4] - buys[l][4]).days)
  r = (p / inv) * (365/t)
  profit.append([sells[i][0],sells[i][1],sells[i][2],sells[i][3],sells[i][4],sells[i][5],p,inv,t,r])
  buys[l][3] = buys[l][3] - sells[i][3]
 elif sells[i][3] > buys[l][3]:
  p = (sells[i][2]-buys[l][2])*buys[l][3]
  inv = buys[l][2]*buys[l][3]
  t = max(1,(sells[i][4] - buys[l][4]).days)
  r = (p / inv) * (365/t)
  profit.append([sells[i][0],sells[i][1],sells[i][2],buys[l][3],sells[i][4],sells[i][5],p,inv,t,r])
  sells[i][3] = sells[i][3] - buys[l][3]
  buys.pop(l)
  i = i - 1
 profit = pd.DataFrame(profit, columns=['Stock','Type','Price','Quantity','TimeStamp','Stocks in Hand','Abs Profit','Init Investment','Days Invested', 'Rate of Return'])
 return(profit)

tqqqprofit = get_profit(mytrans,'TQQQ')

会给你每个卖出交易的利润和回报，除以最初在不同日期/价格购买的股票数量：

Stock  Type     Price  Quantity  ... Abs Profit  Init Investment  Days Invested  Rate of Return
TQQQ  sell   71.0025      14.0  ...   110.3550         883.6800             20        2.279082
TQQQ  sell   71.0025      60.0  ...   -46.6500        4306.8000             10       -0.395357
TQQQ  sell   82.7900      60.0  ...   822.9000        4144.5000             88        0.823541
TQQQ  sell  100.0000       2.0  ...    34.9100         165.0900             49        1.575164
TQQQ  sell  100.0000      13.0  ...   198.9000        1101.1000             43        1.533319
TQQQ  sell  102.0000      10.0  ...   173.0000         847.0000             43        1.733753