0

I'm very new to Python and I've unsuccessfully tried to figure out: How to go from a dataframe of transactions to a dataframe of my portfolio with realised and unrealised P/L?.

So far, I download the list of transactions from my broker and just clean this dataframe a bit. As an example, lets say I am starting with a dataframe that has 2 stocks (ABCD and EFGH), is sorted by stock and date (oldest at the top) and includes stock name, the quantity, the direction of trade, and the price. The setup is something like this:

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

I would like to arrive at a dataframe including the stock name, quantity in portfolio, realised P/L and unrealised P/L. Assuming that the current price of ABCD is included somewhere else (e.g. in a separate dataframe; lets say it trades at 120 at this point in time) and this is used for unrealised P/L it would look like this:

ABCD 2 40 60
EFGH 0 39 0

... whereby real. P/L for ABCD would be 5*(105-100)+1*(105-90) and unreal. P/L would be 2*(120-90) etc.

It seems that queue or deque are the solution, and I suspect this is a pretty straight forward exercise in the end, but I really don't know how to implement it, so any help is appreciated. I found a similar question How to calculate realized P&L of stock trades using the FIFO method? but there is no example of applying it.

martineau
  • 119,623
  • 25
  • 170
  • 301
salbeikeks
  • 1
  • 2
  • An excel version is available here https://stackoverflow.com/questions/68351891/calculate-avg-price-realized-gain-unrealized-gain-via-udf-using-fifo-method – Kawalpreet Kaur Jul 27 '21 at 17:55

1 Answers1

1

I don't think this would answer your question directly about queue or deque, but it sure would give you another much wordier way of doing the same. If your data is structured as below:

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

Then using this:

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')

Would give you the profit for each sell trade and return thereof, split by the number of shares initially bought on different dates / prices:

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
Prabhjot
  • 21
  • 1
  • 3