Portfolio return with cash inflow and outflow – Part II

In a previous article on this topic we have discussed this unique project I delivered to one of AlphaOverBeta’s client where he requested to display his portfolio risk metrics visually and textually by providing his broker trades report, the portfolio had complex issues, and inputs which can be reviewed in the previous article I wrote.

For reference, here are the requirements for the portfolio equity and risk calculations:

1. The portfolio is constructed of 4 sub-portfolios (sub-accounts under one major account)
2. The portfolio is traded in a number of currencies (in his case CAD, SGD, USD) in the same sub-account
3. Each sub-account has random cash inflows (cash injected into the portfolio) and outflows of cash
4. Each asset in the portfolio may pay dividends
5. An asset position may change value along the way, meaning it may be increased or reduced ( to 0 in case it’s totally closed)
6. The traded assets are from different exchanges
7. Long and short side support

We are left with issues 3,4 to solve in this part of the post-cash inflow and outflow.
Handling cash inflows and outflows is tricky since an inflow of cash increases the amount in your portfolio artificially, for example, a 100K$ portfolio that had a 20K$ cash added to it is now 120K$ portfolio but did not increase its pnl by 20% (120K$ from 100K$) so when calculating historical performance this event must be taken into calculations.

As we have stated before, the portfolio is actually a table with the trade dates in the rows and the assets invested in the columns, one specific column is dedicated to the amount of cash in the portfolio, when an asset is bought ,two columns are added to the database one for the current size of the asset and another one for the current quote of the asset, a summation of each row produces the total value of the portfolio (see the table in the previous post of this topic).

Script in play

Here is the code handling the logic above:

[python]
def ticker_performance(ticker, hq, ticker_order_history):
”’
calculate the performance of a specific symbol
:param ticker: the symbol
:param hq: historical quotes
:param ticker_order_history: buy sell,dividend history for the ticker
:return: dataframe with daily performance
”’
hq_c = hq.copy()
hq_c[‘Quantity’] = 0
# mark order dates
for order_date in ticker_order_history[‘Date’]:
hq_order_date = order_date
if not order_date in hq_c.index:
if order_date < hq_c.index[0]:
logging.debug(‘date {} not in ticker {} history, performance may be incorrect’.format(order_date.strftime((‘%Y-%m-%d’)),ticker))
ticker_order_history = ticker_order_history[ticker_order_history[‘Date’]!=order_date]
continue
hq_order_date = hq_c[order_date:].head(1).index

oh_type = ticker_order_history[ticker_order_history[‘Date’] == order_date][‘Type’].iloc[0]
if oh_type in [‘Buy’, ‘Sell’]:
hq_c.loc[hq_order_date, ‘Quantity’] = \
ticker_order_history[ticker_order_history[‘Date’] == order_date][‘Quantity’].iloc[0]

buy_sell_orders = ticker_order_history[(ticker_order_history[‘Type’]==’Buy’) | (ticker_order_history[‘Type’]==’Sell’)]
if not len(buy_sell_orders)or ((2==len(buy_sell_orders) and (1==(buy_sell_orders[‘Date’].iloc[-1]-buy_sell_orders[‘Date’].iloc[0]).days))):
return pd.DataFrame()
buy_sell_orders.reset_index(inplace=True, drop=True)
order_history = {}
one_day = datetime.timedelta(days=1)
for bsidx in range(len(buy_sell_orders)):
if bsidx:
order_history[bsidx]={‘end_date’:buy_sell_orders.iloc[bsidx][‘Date’]-one_day, ‘start_date’:buy_sell_orders.iloc[bsidx-1][‘Date’]}
order_history[len(buy_sell_orders)] = {‘start_date’:buy_sell_orders.iloc[-1][‘Date’], ‘end_date’:hq_c.index[-1]}

quantity=0
column_names=[]
for idx in order_history.keys():
start_date = order_history[idx][‘start_date’]
end_date = order_history[idx][‘end_date’]
quantity += ticker_order_history[ticker_order_history[‘Date’] == start_date][‘Quantity’].iloc[0]
if not quantity: continue
column_name = ticker+str(idx)
column_names.append(column_name)
hq_c[column_name]=0
for hq_date in hq_c[start_date : end_date].index:
hq_c.loc[hq_date, column_name] = quantity * hq_c.loc[hq_date][‘Close’]

return hq_c[column_names]
[/python]