Calculate cumulative portfolio returns in real-time

Share:
Indexes
  1. Quick start | Deephaven
Photo by Adeolu Eletu on Unsplash

Successful financial endeavors have several common denominators. In today’s day and age, the biggest of those is the right tech stack for the job. Whether you want to work with crypto, stocks, bonds, options, mutual funds, precious metals, or even antique baseball cards, Deephaven is the multi-tool that facilitates success.

Take a simple calculation such as cumulative return of a financial portfolio for example. This “simple” calculation can be tricky to carry out without the right tools of the trade. With Deephaven, that’s not the case. Analyze weighted cumulative returns in both a static and dynamic context with ease.

Consider Deephaven the Bane to the Batman of fintech: others have merely adopted the technology. Deephaven was born in it. Molded by it. Deephaven was specifically built for this type of analysis, and so much more.

Weighted cumulative return

According to Investopedia, the definition of a cumulative return is “the total change in the investment price over a set time — an aggregate return, not an annualized one.” Luckily for us, Deephaven has the tools required to make this calculation simple and intuitive.

Create a portfolio

The first thing we need is some stocks to analyze. For this exercise, we’ll analyze 5 technology stocks: AMD, NVIDIA, Intel, Microsoft, and Texas Instruments. The stock portfolio we want is in our examples repository. We can read this portfolio directly into a table with read_csv.

tickers = ["AMD", "INTC", "MSFT", "NVDA", "TXN"]from deephaven import read_csvstocks_table = read_csv("https://media.githubusercontent.com/media/deephaven/examples/main/TechStockPortfolio/csv/tech_stock_portfolio.csv")

So we’ve got data for our 5 stocks in memory. Before we continue, let’s convert that Date column into a Deephaven column of date-times.

from deephaven.time import to_datetimestocks_table = stocks_table.update(["Date = (DateTime)to_datetime(Date + `T16:00:00 NY`)"])

Cumulative returns are typically calculated using the adjusted closing prices. We’ll discard the columns of data that aren’t the adjusted closing prices or the date.

adjusted_closing_prices = ["Adj_Close_" + ticker for ticker in tickers]stocks_table = stocks_table.select(formulas=adjusted_closing_prices + ["Date"])
stocks_table with selected columns

Calculate percent returns for each asset

Analysis of weighted cumulative returns requires the calculation of a percent change from one measurement to the next. To accomplish this, we’ll use the special row index variable to access one row at a time, as well as the previous row. Doing so for a single stock looks like this:

amd_pct_change_formula = ["AMD = (Adj_Close_AMD_[i] - Adj_Close_AMD_[i - 1]) / Adj_Close_AMD_[i - 1]"]amd_pct_change_table = stocks_table.update(formulas=amd_pct_change_formula)
amd_pct_change_table

We want to calculate this for all stocks. Python’s f-strings make this easy. Calculating this daily change will result in a null value in the first row. We can fix this using replaceIfNull.

pct_change_formulas = [f"{ticker} = (Adj_Close_{ticker}_[i] - Adj_Close_{ticker}_[i - 1]) / Adj_Close_{ticker}_[i - 1]" for ticker in tickers]
columns_to_drop = [f"Adj_Close_{ticker}" for ticker in tickers]
replace_null_formulas = [f"{ticker} = replaceIfNull({ticker}, 0.0)" for ticker in tickers]
ret_table = stocks_table.\
update(pct_change_formulas).\
drop_columns(columns_to_drop).\
update(replace_null_formulas)
ret_table

Apply weights to the returns

We have yet to apply weights to the percent returns. Doing so is simple. We just multiply the return of each asset by its corresponding weight in the portfolio.

weights = [0.1, 0.4, 0.1, 0.25, 0.25]weighted_return_formulas = [f"{ticker} = {weights[i]} * {ticker}" for i, ticker in enumerate(tickers)]weighted_ret_table = ret_table.\
update(weighted_return_formulas)
weighted_ret_table

Compute the daily returns

We now have daily percentage increases/decreases for each of our 5 stocks. We need to sum all of these values into one single column to get the daily return of the portfolio.

sum_returns_formula = ["SummedPctReturns = " + " + ".join(tickers)]summed_weighted_ret_table = weighted_ret_table.\
update(sum_returns_formula).\
drop_columns(tickers)
summed_weighted_ret_table

Calculate the cumulative product

Last but not least: calculate the cumulative returns. Group the returns, apply Deephaven’s built-in cumprod method, then ungroup them.

cumprod_formula = ["CumRet = cumprod(1 + SummedPctReturns)"]cumulative_return_table = summed_weighted_ret_table.\
group_by().\
update(cumprod_formula).\
ungroup().\
drop_columns(["SummedPctReturns"])
cumulative_return_table

Plot the results

from deephaven.plot import Figurefigure = Figure()
cumulative_returns_plot = figure.plot_xy(series_name="Cumulative Returns", t=cumulative_return_table, x="Date", y="CumRet")
cumulative_returns_fig = cumulative_returns_plot.show()
cumulative_returns_fig

Cumulative returns in real-time

This is cool, but what if we could do all of this in real-time? It turns out we can, and it’s pretty easy.

Fudging date-times

First and foremost, we don’t want to do real-time calculations once per day. That’s not exciting. Nor can anyone sit around and wait for 24 hours for things to change. Let’s pretend like these returns happen once per second by fudging the date-times with Python and Pandas.

from deephaven.time import to_datetime
from deephaven import pandas as dh_pd
from deephaven import read_csv
import pandas as pd
import datetime
tickers = ["AMD", "INTC", "MSFT", "NVDA", "TXN"]
weights = [0.1, 0.4, 0.1, 0.25, 0.25]
stocks_table = read_csv("https://media.githubusercontent.com/media/deephaven/examples/main/TechStockPortfolio/csv/tech_stock_portfolio.csv")stock_data = dh_pd.to_pandas(stocks_table.drop_columns(["Date"]))start = datetime.datetime(2022, 5, 19)
step = datetime.timedelta(seconds=1)
num_steps = len(stock_data)
datetime_strings = [""] * num_stepsfor idx in range(num_steps):
datetime_strings[idx] = start.strftime("%Y-%m-%dT%H:%M:%S NY")
start += step
stock_data["Datetime"] = datetime_stringsstocks_table = dh_pd.to_table(stock_data).update(["Datetime = (DateTime)to_datetime(Datetime)"])

Replaying the table

Now we’ve got some fudged date-times in the table. We want to play this back in real time. Deephaven has just the method for that: Replayer.

from deephaven.replay import TableReplayer
from deephaven.plot import Figure
start_datetime = to_datetime("2022-05-19T00:00:00 NY")
end_datetime = to_datetime("2022-05-19T00:21:38 NY")
stocks_replayer = TableReplayer(start_datetime, end_datetime)
stocks_replayed = stocks_replayer.add_table(stocks_table, "Datetime")

We’re not going to start the replayer yet because we want to do so and calculate the cumulative returns all at once. We can do so just like we did in a static context.

stocks_replayer.start()pct_change_formulas = [f"{ticker} = (Adj_Close_{ticker}_[i] - Adj_Close_{ticker}_[i - 1]) / Adj_Close_{ticker}_[i - 1]" for ticker in tickers]
columns_to_drop = [f"Adj_Close_{ticker}" for ticker in tickers]
replace_null_formulas = [f"{ticker} = replaceIfNull({ticker}, 0.0)" for ticker in tickers]
ret_replayed = stocks_replayed.\
update(pct_change_formulas).\
drop_columns(columns_to_drop).\
update(replace_null_formulas)
weighted_return_formulas = [f"{ticker} = {weights[i]} * {ticker}" for i, ticker in enumerate(tickers)]weighted_ret_replayed = ret_replayed.\
update(weighted_return_formulas)
sum_returns_formula = ["SummedPctReturns = " + " + ".join(tickers)]summed_weighted_ret_replayed = weighted_ret_replayed.\
update(sum_returns_formula).\
drop_columns(tickers)
cumprod_formula = ["CumRet = cumprod(1 + SummedPctReturns)"]cumulative_return_replayed = summed_weighted_ret_replayed.\
group_by().\
update(cumprod_formula).\
ungroup().\
drop_columns(["SummedPctReturns"])
figure = Figure()
cumulative_returns_plot = figure.plot_xy(series_name="Cumulative Returns", t=cumulative_return_replayed, x="Datetime", y="CumRet")
cumulative_returns_fig = cumulative_returns_plot.show()
cumulative_return_replayed
Ticking cumulative_returns_fig

Voila! Now we have real-time cumulative portfolio returns! If only these were the actual returns over ~20 minutes of investing. We’d be rich!