Portfolio return with cash inflow and outflow – Part I

December 20, 2019
Posted in Money Talks
December 20, 2019 admin

In a recent project I have completed for a client of AlphaOverBeta, I was requested to calculate his portfolio return under these challenging conditions:

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

That’s it!

The challenge

The input was a broker report with transaction dates and info about the transaction (asset traded, quantity, long/short, etc..), your vanilla broker report each broker generates for their clients.

So his simple request was to calculate each sub-account( basically sub-portfolio) performance , risk matrics and generate a visual report of the equity curve and the drawdown incurred in each sub-account.

So how do you approach such a challenge? Let’s divide the problem into groups,

One problem is the currency and historical quotes problem, once I have the historical quotes in place ,converted to one base currency (USD in my case) I will have a historic stream of each asset in one currency, this should solve the currency issues.

Another part of the problem is the partial open and close of the position, leading to a different amount invested in each time period and the final part of the problem is the cash inflows and outflow .

Attacking the first part was through using a source of historical quote to receive the historical quotes from, the source had to support symbols from different exchanges, and have enough history to cover the entire portfolio orders history, having high-quality data is a must in this type of projects, if the data is not high quality you find yourself handling the holes in the data instead of focusing on the more burning issues of the project, for this project we used the broker API to download the historical quote data, the data was high quality but had to be converted to one base currency, since I can’t disclose the broker for this article, I will demonstrate the feature using yahoo finance data which is a very good alternative for broker high-quality data.

Script in play

Here is the function (In Python,what else..) to fetch the data from yahoo finance and convert it to one base currency.

[python]
def fetch_historical_quotes(ticker, start_date, rates_df, currency):
”’
This module uses yfinance library to fetch histirical quotes from
Yahoo finance
https://aroussi.com/post/python-yahoo-finance
”’
try:
security = yf.Ticker(ticker)
security_history = security.history(start=start_date.strftime(‘%Y-%m-%d’), end=datetime.datetime.now().strftime(‘%Y-%m-%d’))
except:
return pd.DataFrame()
if ‘USD’ == currency: return security_history

for sec_date in security_history.index:
fx_date = sec_date if sec_date in rates_df.index else rates_df[:security_history.index[0]].index[-1]
fx_rate = rates_df.loc[fx_date][currency]
security_history.loc[sec_date,’Open’] = security_history.loc[sec_date][‘Open’]/fx_rate
security_history.loc[sec_date,’High’] = security_history.loc[sec_date][‘High’]/fx_rate
security_history.loc[sec_date,’Low’] = security_history.loc[sec_date][‘Low’]/fx_rate
security_history.loc[sec_date,’Close’] = security_history.loc[sec_date][‘Close’]/fx_rate

return security_history

[/python]

And use the code below to fetch the historical conversion rates from https://exchangeratesapi.io/

[python]

url = ‘https://api.exchangeratesapi.io/history?start_at=2014-01-01&end_at={}&base=USD’.format(datetime.datetime.now().strftime(‘%Y-%m-%d’))
response = requests.get(url)
data = response.text
parsed = json.loads(data)
rates_df = pd.DataFrame(parsed["rates"]).transpose()
rates_df.index = pd.to_datetime(rates_df.index)
rates_df.sort_index(inplace=True)

[/python]

The rates Dataframe is then sent to the fetch_historical_quotes function to be converted so that all historical quotes are in one base currency, that took care of the fetching problem, let’s move on to managing the partial open close of positions.

Managing partial open / close of positions

When managing an active portfolio, assets go in and out of it and sometimes increased or decreased in size, for example, One might buy 10 shares of GOOG then buy 10 shares in a higher price, a third move would be to reduce the amount by 15 a few days later, and then close the entire position sometime later. This change in size creates a challenge when trying to calculate the current value of a portfolio because the assets are constantly changing in price. To solve this challenge each portfolio must contain at least two column per trading date ,one for the cash deposit in the portfolio and one column for the invested assets in the portfolio , at the beginning the cash column contains the initial cash deposit into the portfolio, once an investment is made, the amount moves from the cash column( cash going out to be invested ) to the assets column, when another investment is made, the process repeats itself and another cash goes out from the cash column into the assets column, now the trick is to manage the assets column correctly, the assets column is actually not one column but multiple column, one per asset in the portfolio with additional column for the current size in the portfolio, this means that the assets column is not one column but two columns per asset, one for the current (weighted average) price and the other one is for the current size when increasing the size aggregates the amount in the column size and reducing size simply reduces the size in the column. Each line in this table format contains the corresponding date’s size and close price per asset and the daily pnl is the simple act of adding the size X value per asset thus creating a smooth equity curve per date.

Short positions

Handling short positions is a bit tricky since selling short actually increases the cash and buying the assets back reduces the cash position , to properly manage this simply mark the short position with negative values ,the math will take care of itself.

The table above displays the concept, each row is the current multiplication of the current size by the historical value(Close value in this case), the the ‘TR’ (Total Return) column is a sum of the elements in the row, the current results observed in the table is the historical return in percentage term relative to the first day of the portfolio history.

In the next post we will see how to properly manage the inflow and outflow of cash in and out of the portfolio,

Trade safely,
Alon

Leave a Reply

Your email address will not be published.