Calculate cumulative portfolio returns in real time

Successful financial endeavors have several common denominators. Today, the biggest of those is the right tech stack for the job. Whether you want to work with crypto, stocks, bonds, options, mutual funds, or precious metals, Deephaven is the multi-tool that facilitates success.

The weighted cumulative return tells you your percent increase or decrease from your initial investment for a given set of time.

Take a calculation such as the cumulative return of a financial portfolio, for example. This "simple" calculation can be tricky to carry out without the right tools. Deephaven is ideal for any fintech project. Analyze weighted cumulative returns in both a static and dynamic context with ease.

Weighted cumulative return

The weighted cumulative return of a portfolio of assets is the aggregate return over a set time period. For a single stock, the cumulative return is:

$R_{t} = \frac{P_{t} - P_{i}}{P_{i}}$

where $R_{t}$ is the total return, $P_{t}$ is the price at the end of the time period, and $P_{i}$ is the initial price.

In portfolios with multiple stocks, each stock typically has a weight associated with it. In this blog, we'll use static weights that stay the same for the whole investment period. The formula for this calculation (the weighted cumulative return) looks like this:

$R_{t} = \sum_{k=0}^{N}w_{k}R_{t}$

Where $k$ denotes each stock, and $w_{k}$ denotes the weight associated with a given stock.

In short, the weighted cumulative return tells you your percent increase or decrease from your initial investment for a given set of time. If you initially invest $100, and after a month, have $200, your return is 100%.

Get stock data

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

tickers = ["AMD", "INTC", "MSFT", "NVDA", "TXN"]

from deephaven import read_csv

stocks_table = read_csv("https://media.githubusercontent.com/media/deephaven/examples/main/TechStockPortfolio/csv/tech_stock_portfolio.csv")

Now we have data for our 5 stocks in memory. Before we continue, let's convert the Date column into a Deephaven column of date-times.

from deephaven.time import to_datetime

stocks_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.view(formulas=adjusted_closing_prices + ["Date"])

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_table = \
    stocks_table.update(formulas=["AMD = (Adj_Close_AMD_[i] - Adj_Close_AMD_[i - 1]) / Adj_Close_AMD_[i - 1]"])

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.

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

Apply weights to the returns

Next, we have to apply weights to the percent returns. To weigh the returns, we'll simply multiply the return of each asset by its corresponding weight in the portfolio.

weight_values = [0.1, 0.4, 0.1, 0.25, 0.15]
weights = {tickers[i]: weight_values[i] for i in range(len(tickers))}

weighted_return_formulas = [f"{ticker} = {weights[ticker]} * {ticker}" for ticker in tickers]

weighted_ret_table = ret_table\
    .update(weighted_return_formulas)

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.

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

Calculate the cumulative product

Finally, calculate the cumulative returns. Group the returns, apply Deephaven's built-in cumprod method, then ungroup them.

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

Plot the results

from deephaven.plot import Figure

cumulative_returns_fig = Figure()\
    .plot_xy(series_name="Cumulative_Returns", t=cumulative_return_table, x="Date", y="CumRet")\
    .show()

Voila! Now we have cumulative portfolio returns. Deephaven is data agnostic - its query methods work on static and dynamic data in the same manner, so if you have real-time prices, the return will update in real time.

Further reading

• How to plot XY series
• How to replay table data
• drop_columns
• select
• update