what we downloaded
Equity Gold Bonds
Monday 0.01 0.005 0.002
Tuesday 0.02 NaN 0.001
Wednesday -0.01 0.007 0.003
after .dropna()
Equity Gold Bonds
Monday 0.01 0.005 0.002
Wednesday -0.01 0.007 0.003What dropna() deletes from a backtest
Python
Backtesting
Tutorial
Code
On a simulated four-asset portfolio, dropna() deletes returns that were never missing and cuts the backtest’s final value by a third.
We download prices for several assets, one row per day, turn them into returns, and clean the gaps out with dropna(). The issue is that dropna() removes the whole row, including the assets that did report. We lose returns that were never missing.
I measure what the deleted returns cost on data I simulate myself, so the true result is known in advance. dropna() raises no error message, and the backtest still returns plausible statistics. For a four-asset portfolio, the backtest built with dropna() reports a terminal wealth, the final value of 1 invested at the start, of 3.82 against a true 5.70.
The one call to dropna() makes three mistakes, which sections 1 to 3 take one at a time and then measure together on one simulated market.
1. Dropping a row deletes every asset on it
The example below has three assets in a DataFrame, and one of them has no value on Tuesday.
NoteCode: the three assets
import numpy as np
import pandas as pd
# One row per day, one column per asset, which is how a download usually arrives
demo = pd.DataFrame({"Equity": [0.010, 0.020, -0.010],
"Gold": [0.005, np.nan, 0.007], # Gold did not report on Tuesday
"Bonds": [0.002, 0.001, 0.003]},
index=["Monday", "Tuesday", "Wednesday"])
cleaned = demo.dropna()
print("what we downloaded")
print(demo.to_string())
print("\nafter .dropna()")
print(cleaned.to_string())Tuesday’s row is gone, and with it Equity’s +2.0% and Bonds’ +0.1%. Neither return was missing. DataFrame.dropna() removes the whole row when any column holds a missing value (NA), which is what the pandas documentation describes.
2. Cleaning a price series merges two days into one
I apply the same cleaning to the prices instead of the returns.
NoteCode: the cleaning applied to prices
demo_prices = pd.Series([100.0, np.nan, 105.0],
index=["Monday", "Tuesday", "Wednesday"])
# Clean, then difference: throw the gap away, then take the change
print(demo_prices.dropna().pct_change().to_string())Monday NaN
Wednesday 0.05pct_change() computes each row’s return from the row above it,
\[r_t = \frac{P_t}{P_{t-1}} - 1,\]
where \(P_t\) is the price on row \(t\) and \(P_{t-1}\) the price on the row before it. With Tuesday gone, the row before Wednesday is Monday, so Wednesday’s return is \(105/100 - 1 = 5.0\%\). The 5.0% move did end on Wednesday, and it is a two-day return in a series of one-day returns. Every later calculation over a window of days, such as a 20-day volatility, now counts that two-day return as one day. Lesson 17 covers pct_change() on a price series.
Whether merging two days into one is wrong depends on what the NA meant, and three cases look identical in a DataFrame.
The market was closed. Nothing traded anywhere, so no asset had a return that day. Merging the two days into one is correct.
The asset trades on a different calendar. Bitcoin trades at the weekend and equities do not. The return is real for bitcoin on that row, and the equities never had a return to record on that row. Deleting that row throws away bitcoin’s return.
The vendor failed to send the price. Every asset traded, and the vendor lost one of the returns. Deleting that row throws away the returns the vendor did send.
3. A weekend is not a missing day
Real data does not label which case an NA belongs to, and a return the vendor dropped is gone, so there is nothing to measure the error against. A simulation keeps that return, because I generate every price myself.
I build four simulated assets over the twenty years from January 2006 to January 2026. Equity, Bonds and Gold trade Monday to Friday. Crypto is a simulated asset that trades every day, like bitcoin, which did not exist before 2009. The dates here only decide which days are weekends. Each asset’s price grows at a set yearly rate, with random daily moves that tend to rise and fall together across the four assets.
The vendor we get the data from then drops each price with probability 4% for Gold, 1.5% for Bonds, 0.5% for Crypto and zero for Equity, independently of the prices, and keeps the first and last days. The growth rates, the sizes of the daily moves, how closely the assets move together and these probabilities are assumptions. The box below gives the price formula and the code.
NoteHow the prices and the vendor gaps are simulated
Asset \(a\)’s price on its trading day \(t\) is
\[P_{a,t} = 100\,\exp\Big(\sum_{s=1}^{t} \ell_{a,s}\Big), \qquad \ell_{a,s} = \frac{\mu_a - \frac12\sigma_a^2}{N_a} + \frac{\sigma_a}{\sqrt{N_a}}\,z_{a,s},\]
where \(\ell_{a,s}\) is the log return on day \(s\), the change in the logarithm of the price. Summing the log returns and taking the exponential, \(e\) raised to that sum, compounds them into a price that stays positive. \(\mu_a\) is the yearly drift, which sets the price’s expected growth, and \(\sigma_a\) the yearly volatility, the standard deviation of a year’s log return; both are set in ASSETS below (8% and 16% for Equity). A standard deviation measures how far values typically are from their average. \(N_a\) is the number of trading days a year, 261 for Monday to Friday and 365 for every day. Dividing by \(N_a\) and \(\sqrt{N_a}\) turns the yearly numbers into daily ones: over a year, the mean log returns add to \(\mu_a - \frac12\sigma_a^2\) and their variances, the squared standard deviations, add to \(\sigma_a^2\). The \(-\frac12\sigma_a^2\) corrects for the exponential, which turns a log return of \(+0.10\) into a 10.5% gain and a log return of \(-0.10\) into only a 9.5% loss. Without the correction, noise alone would raise the expected price; with the correction, the expected price grows by a factor of \(e^{\mu_a}\) a year. The \(z_{a,s}\) are draws from the standard normal distribution, which is bell-shaped with a mean of 0 and a standard deviation of 1. The draws are independent across days and correlated across the four assets, so the assets tend to move together, with the correlations, numbers between −1 and 1, in RHO.
SEED = 12
START = "2006-01-01"
END = "2026-01-01"
ASSETS = { # annual drift, annual volatility, native calendar, P(vendor drops a day)
"Equity": dict(mu=.08, sd=.16, cal="B", miss=.000),
"Bonds": dict(mu=.03, sd=.05, cal="B", miss=.015),
"Gold": dict(mu=.04, sd=.15, cal="B", miss=.040),
"Crypto": dict(mu=.20, sd=.45, cal="D", miss=.005)}
WEIGHTS = pd.Series({"Equity": .45, "Bonds": .30, "Gold": .15, "Crypto": .10})
# The four assets do not move independently, so their shocks are drawn with this
# correlation matrix rather than one at a time
RHO = np.array([[1, -.2, .1, .35], [-.2, 1, .15, 0], [.1, .15, 1, .1], [.35, 0, .1, 1]])
cal = pd.date_range(START, END, freq="D") # every calendar day
biz = pd.date_range(START, END, freq="B") # Monday to Friday
names = list(ASSETS)
TOTAL_YEARS = (cal[-1] - cal[0]).days / 365.25
rng = np.random.default_rng(SEED) # fixed, so the post reproduces
# A Cholesky factor is the square root of a correlation matrix. Multiplying independent
# draws by it hands back four series that have the correlations in RHO.
independent_shocks = rng.standard_normal((len(cal), 4))
correlated_shocks = pd.DataFrame(independent_shocks @ np.linalg.cholesky(RHO).T,
index=cal, columns=names)
latent = {}
true_px = {}
for asset in names:
spec = ASSETS[asset]
asset_calendar = biz if spec["cal"] == "B" else cal
per_year = len(asset_calendar) / TOTAL_YEARS # what this calendar delivers in a year
# The price is a random walk in logs, which keeps it positive. Subtracting
# half the variance leaves the average price growing at the annual drift, since
# compounding a random series would otherwise push it above that.
drift_a_day = spec["mu"]/per_year - .5*spec["sd"]**2/per_year
# .loc[rows, column] selects rows by label: this asset's trading days, in its own column
shock_a_day = spec["sd"]/np.sqrt(per_year)*correlated_shocks.loc[asset_calendar, asset]
latent[asset] = drift_a_day + shock_a_day
true_px[asset] = 100*np.exp(latent[asset].cumsum())
gap_rng = np.random.default_rng(SEED + 1) # a separate generator from the prices
obs = {} # what we download
for asset in names:
true_prices = true_px[asset]
# A draw at or above the miss probability means the vendor sent that day
reported = gap_rng.random(len(true_prices)) >= ASSETS[asset]["miss"]
reported[0] = True # never lose the ends of a series, or the
reported[-1] = True # sample would start and stop at random
for edge_day in (biz[0], biz[-1]):
if edge_day in true_prices.index:
reported[true_prices.index.get_loc(edge_day)] = True
obs[asset] = true_prices.loc[reported] # .loc keeps the days marked True
gaps = len(true_prices) - len(obs[asset])
print(f" {asset:7s} traded {len(true_prices):5,} reported {len(obs[asset]):5,} "
f"vendor gaps {gaps:4,} ({1-len(obs[asset])/len(true_prices):.2%})")
import sys
import matplotlib
python_version = sys.version.split()[0] # position 0 is the number, before the build
print(f"\n python {python_version} | numpy {np.__version__} | "
f"pandas {pd.__version__} | matplotlib {matplotlib.__version__}") Equity traded 5,219 reported 5,219 vendor gaps 0 (0.00%)
Bonds traded 5,219 reported 5,139 vendor gaps 80 (1.53%)
Gold traded 5,219 reported 5,020 vendor gaps 199 (3.81%)
Crypto traded 7,306 reported 7,269 vendor gaps 37 (0.51%)
python 3.13.1 | numpy 2.4.2 | pandas 2.3.3 | matplotlib 3.10.8The vendor lost 3.81% of Gold’s days, close to its 4% probability, and the other three assets have fewer gaps. I fix the seed and print the package versions, so with these versions you get the same numbers.
The chart below shows what dropna() does to six weeks of the simulated data.
NoteCode: the chart of the deleted days
import matplotlib.pyplot as plt
from matplotlib.patches import Patch, Rectangle
RED, TEAL, ORANGE = "#C0392B", "#17868A", "#D2822B"
CLOSED = "#b5b5b5" # a day the market was closed
MUTED = "#767676" # the annotation's connector line
# The site's chart colours: a cream background, a light grid and a pale frame, so every chart
# on the site has the same look.
BG, INK, GRID, SPINE, AXIS_TEXT = "#FCEFE3", "#1f1f1f", "#EADCCC", "#D5C6B4", "#4a4a4a"
DAY = pd.Timedelta("1D")
def style_chart(figure, axis):
"""Give a finished chart the site's cream background, light grid and pale frame."""
figure.patch.set_facecolor(BG)
axis.set_facecolor(BG)
axis.grid(True, axis="y", color=GRID, lw=1.0)
axis.set_axisbelow(True)
for side in ("top", "right"):
axis.spines[side].set_visible(False)
for side in ("left", "bottom"):
axis.spines[side].set_color(SPINE)
axis.tick_params(axis="both", length=0, colors=INK, pad=6)
axis.xaxis.label.set_color(AXIS_TEXT)
axis.yaxis.label.set_color(AXIS_TEXT)
axis.title.set_color(INK)
# Each legend entry in the colour of what it labels: a line's colour or a bar's fill.
# get_legend() returns None when the chart has no legend.
legend = axis.get_legend()
if legend is not None:
for text, handle in zip(legend.get_texts(), legend.legend_handles):
if hasattr(handle, "get_color"):
text.set_color(handle.get_color())
else:
text.set_color(handle.get_facecolor())
win = pd.date_range("2006-02-06", periods=42, freq="D") # six weeks
# One label per asset per day. A day the asset traded but that is not in obs is a day
# the vendor lost, and a day it did not trade at all is a closed market.
state_columns = {}
for asset in names:
reported_days = set(obs[asset].index)
traded_days = set(true_px[asset].index)
day_labels = []
for day in win:
if day in reported_days:
day_labels.append("reported")
elif day in traded_days:
day_labels.append("lost")
else:
day_labels.append("closed")
state_columns[asset] = day_labels
# Built by column, then transposed, so one asset is one row of the chart
state = pd.DataFrame(state_columns, index=win).T
# .dropna() keeps a day only when every asset reported on it
survives = {}
for day in win:
survives[day] = (state[day] == "reported").all()
keep = pd.Series(survives)
COLOUR = {"reported": TEAL, "closed": CLOSED, "lost": RED}
fig, ax = plt.subplots(figsize=(12.5, 4.3))
fig.patch.set_facecolor(BG) # the site's cream background, on the figure
ax.set_facecolor(BG) # and on the plotting area
for day in win[~keep.values]: # a dark bar marks every day .dropna() removes
ax.add_patch(Rectangle((day-.40*DAY, -.92), .8*DAY, .30, facecolor=INK,
edgecolor=BG, lw=1, zorder=3))
ax.text(win[0]-.75*DAY, -.77, "deleted", ha="right", va="center", fontsize=10.5,
color=INK, fontweight="bold")
for row, asset in enumerate(names):
for day in win:
ax.add_patch(Rectangle((day-.40*DAY, 3-row-.33), .8*DAY, .66,
facecolor=COLOUR[state.loc[asset, day]],
edgecolor=BG, lw=1, zorder=3))
ax.annotate("The vendor lost Bonds here, so .dropna()\ndeletes Equity, Gold and Crypto too",
xy=(pd.Timestamp("2006-02-13"), 3.40),
xytext=(pd.Timestamp("2006-02-13")+2.2*DAY, 4.15), fontsize=10.5, color=INK,
ha="left", va="bottom",
arrowprops=dict(arrowstyle="-", color=MUTED, lw=1.1,
connectionstyle="angle,angleA=0,angleB=90,rad=4"))
ax.set_xlim(win[0]-.7*DAY, win[-1]+.7*DAY)
ax.set_ylim(-1.15, 4.7)
ax.set_yticks(range(4))
ax.set_yticklabels(names[::-1], fontsize=11) # the y axis counts up, the rows run down
mondays = []
monday_labels = []
for day in win:
if day.dayofweek == 0: # 0 is Monday, 6 is Sunday
mondays.append(day)
monday_labels.append(day.strftime("%d %b"))
ax.set_xticks(mondays)
ax.set_xticklabels(monday_labels, fontsize=10)
ax.tick_params(length=0, colors=INK)
for spine in ax.spines.values():
spine.set_visible(False)
ax.set_title("What .dropna() deletes",
fontsize=13.5, fontweight="bold", pad=16, loc="left")
ax.legend(handles=[Patch(facecolor=TEAL, label="reported"),
Patch(facecolor=CLOSED, label="market closed"),
Patch(facecolor=RED, label="vendor lost it"),
Patch(facecolor=INK, label="day deleted by .dropna()")],
frameon=False, fontsize=10.5, ncol=4, loc="lower left", bbox_to_anchor=(-.005, -.30))
plt.tight_layout()
fig.savefig("dc_what_dropna_deletes.png", dpi=200, bbox_inches="tight", facecolor=BG)
plt.show()
Each cell is one asset on one day. Teal means the asset reported, grey means the market was closed, red means the vendor lost the day, and a dark bar underneath marks a day dropna() deletes.
Most of the deleted days are weekends, the third mistake. Crypto traded and the other three assets did not, so Crypto’s Saturday and Sunday returns are deleted. On a Monday-to-Friday calendar, those weekend returns belong in Crypto’s Friday-to-Monday return,
\[\frac{P_{\text{Mon}}}{P_{\text{Fri}}} - 1 = (1 + r_{\text{Sat}})(1 + r_{\text{Sun}})(1 + r_{\text{Mon}}) - 1,\]
which compounds the three days into one return. The other deleted days hold one red cell each: a single asset failed to report, and dropna() deleted the whole day.
Then I repair two of the three mistakes by working on the prices before computing returns. The second mistake, a vendor gap merged into the next day, stays, because the vendor never sent that day’s price. The first repair, forward-filling, copies each asset’s last known price over its gaps, so no day is deleted because one asset is missing. Crypto’s weekend returns are still lost when its daily returns are lined up with the business days, so the second repair computes Crypto’s returns on the valuation days, the business days on which I value the portfolio, and its Monday return covers the weekend.
The portfolio holds 45% Equity, 30% Bonds, 15% Gold and 10% Crypto, restored to those weights every day, so its return on valuation day \(t\) and its terminal wealth are
\[R_t = \sum_a w_a\, r_{a,t}, \qquad W = \prod_t (1 + R_t),\]
where \(w_a\) is asset \(a\)’s weight, \(r_{a,t}\) its return, and \(\prod_t\) multiplies the daily growth factors. A deleted day enters with \(R_t = 0\). The true result uses the prices before the vendor dropped any, and a version’s error is \(W/W^{\ast} - 1\), with \(W^{\ast}\) the true terminal wealth.
The table below also prints the compound annual growth rate, \(\text{CAGR} = W^{1/20} - 1\) over the 20 years; the volatility \(s\sqrt{N}\); the Sharpe ratio \(\sqrt{N}\,\bar R/s\), the yearly return per unit of yearly risk; and the maximum drawdown (max DD), the largest loss from an earlier peak of wealth. Here \(\bar R\) and \(s\) are the average and standard deviation of \(R_t\), and \(N = 261\) valuation days a year: over \(N\) days the average adds up \(N\) times and the standard deviation grows with \(\sqrt{N}\).
NoteCode: the truth and the three versions
def price_on_calendar(prices, dates):
"""The last known price on each of the dates asked for.
Carrying the price forward values the asset at its last observed price on days it did
not trade or did not report. After a vendor gap, the price change the vendor missed is
recorded on the next observed day.
"""
# union puts every wanted date into the index, ffill copies the last price over the
# new ones, and the second reindex keeps only the dates asked for
every_date = prices.index.union(dates)
carried = prices.reindex(every_date).ffill()
return carried.reindex(dates)
# The truth: every asset priced on the business calendar, with no vendor gaps at all
truth_columns = {}
for asset in names:
truth_columns[asset] = price_on_calendar(true_px[asset], biz).pct_change()
true_returns = pd.DataFrame(truth_columns).dropna()
# The mistake: difference the downloaded prices as they came, then drop any date
# some asset is missing from. pd.DataFrame lines the four columns up on a shared index,
# which is where the missing dates appear.
step1_columns = {}
for asset in names:
step1_columns[asset] = obs[asset].dropna().pct_change()
step1 = pd.DataFrame(step1_columns).dropna()
# First repair: each asset is differenced on its own native calendar, so no asset loses a day
# because another one was missing
step2_columns = {}
for asset in names:
if ASSETS[asset]["cal"] == "B":
step2_columns[asset] = price_on_calendar(obs[asset], biz).pct_change()
else:
crypto_daily = price_on_calendar(obs[asset], cal).pct_change()
step2_columns[asset] = crypto_daily.reindex(biz) # weekend returns are dropped here
step2 = pd.DataFrame(step2_columns).dropna()
# Second repair: every asset is valued on the business calendar, so the crypto weekend
# compounds into the Friday to Monday move instead of being thrown away
step3_columns = {}
for asset in names:
step3_columns[asset] = price_on_calendar(obs[asset], biz).pct_change()
step3 = pd.DataFrame(step3_columns).dropna()
# The calendar span every version covers, and what a year of it holds. Counting the days
# rather than assuming 252 matters here, because this calendar delivers 261 of them.
PORTFOLIO_YEARS = (true_returns.index[-1] - biz[0]).days / 365.25
PORTFOLIO_DAYS_A_YEAR = len(true_returns) / PORTFOLIO_YEARS
def stats(portfolio_returns):
"""Terminal wealth, CAGR, volatility, Sharpe ratio and maximum drawdown."""
# every version is scored on the same valuation calendar, with a zero on any day it
# deleted. That stops a series with fewer rows being annualised on a slower clock.
scored = portfolio_returns.reindex(true_returns.index).fillna(0.0)
growth = 1 + scored
terminal_wealth = float(growth.prod())
cagr = terminal_wealth**(1/PORTFOLIO_YEARS) - 1
volatility = scored.std(ddof=1)*np.sqrt(PORTFOLIO_DAYS_A_YEAR)
sharpe = scored.mean()/scored.std(ddof=1)*np.sqrt(PORTFOLIO_DAYS_A_YEAR)
# np.r_ puts a starting value of 1.00 in front of the wealth path, so the first day can
# be a drawdown too. maximum.accumulate is the running peak up to each day.
wealth = np.r_[1.0, growth.cumprod().values]
drawdown = float((wealth/np.maximum.accumulate(wealth) - 1).min())
return terminal_wealth, cagr, volatility, sharpe, drawdown
LADDER = [(".dropna() on everything", step1),
("+ stop deleting whole rows", step2),
("+ compound the weekend in", step3),
("the truth", true_returns)]
truth_terminal_wealth = stats(true_returns @ WEIGHTS)[0] # position 0 is terminal wealth
print(f"{'':32s}{'wealth':>9}{'error':>9}{'CAGR':>8}{'volatility':>12}{'Sharpe':>9}{'max DD':>9}")
for label, returns in LADDER:
total_wealth, cagr, volatility, sharpe, drawdown = stats(returns @ WEIGHTS)
if "truth" in label:
error_column = "" # the row the other three are measured on
else:
error_column = f"{total_wealth/truth_terminal_wealth-1:+.1%}"
print(f"{label:32s}{total_wealth:>8.2f}x{error_column:>9}{cagr:>8.2%}"
f"{volatility:>12.2%}{sharpe:>9.2f}{drawdown:>9.1%}")
# The weekend mistake affects Crypto, so its returns are counted on their own
crypto_all_returns = true_px["Crypto"].pct_change().dropna()
crypto_returns = crypto_all_returns.loc[crypto_all_returns.index > biz[0]]
weekend_returns = int((~crypto_returns.index.isin(biz)).sum())
survived = int(crypto_returns.index.isin(step1.index).sum())
lost_to_a_gap = len(crypto_returns) - survived - weekend_returns
print(f"\nCrypto has {len(crypto_returns):,} returns inside the portfolio's horizon "
f"and {survived:,} survive")
print(f" {weekend_returns:,} ({weekend_returns/len(crypto_returns):.1%}) fall at a weekend")
print(f" {lost_to_a_gap:,} ({lost_to_a_gap/len(crypto_returns):.1%}) fall on a business day "
f"where some asset had a vendor gap") wealth error CAGR volatility Sharpe max DD
.dropna() on everything 3.82x -32.9% 6.94% 9.61% 0.75 -19.4%
+ stop deleting whole rows 4.63x -18.9% 7.96% 9.88% 0.82 -16.3%
+ compound the weekend in 5.70x +0.0% 9.10% 10.24% 0.90 -16.4%
the truth 5.70x 9.10% 10.25% 0.90 -16.4%
Crypto has 7,304 returns inside the portfolio's horizon and 4,916 survive
2,086 (28.6%) fall at a weekend
302 (4.1%) fall on a business day where some asset had a vendor gapdropna() on everything reports 3.82 against a true 5.70, 32.9% below the truth. With the row deletion stopped, the terminal wealth is 18.9% below the truth, and with the weekend compounded in as well, within 0.05% of it. The version with both repairs also matches the truth’s CAGR of 9.10%, volatility of about 10.25%, Sharpe ratio of 0.90 and maximum drawdown of 16.4%, where dropna() on everything reports 6.94%, 9.61%, 0.75 and 19.4%. The last lines count Crypto’s returns: dropna() keeps 4,916 of 7,304, because it deletes the 2,086 dated at a weekend and 302 more on business days when some asset had a vendor gap.
The chart below draws the value of 1.00 invested under each version.
NoteCode: the chart of the wealth
STEPS = [(step1, RED, "-", ".dropna() on everything"),
(step2, ORANGE, "-", "+ stop deleting whole rows"),
(step3, TEAL, "--", "+ compound the weekend in"),
(true_returns, INK, "-", "the truth")]
# The terminal wealth is written after the label, so every label is padded to one width
LABEL_WIDTH = max(len(text) for _, _, _, text in STEPS) + 4
fig, ax = plt.subplots(figsize=(11.5, 5.6))
for returns, colour, line_style, label in STEPS:
wealth_path = (1 + returns @ WEIGHTS).cumprod()
# The dashed line is drawn on top, since it is almost exactly on the black line
ax.plot(wealth_path.index, wealth_path.values, color=colour, ls=line_style, lw=2.1,
solid_capstyle="round", zorder=5 if line_style == "--" else 3,
label=f"{label:<{LABEL_WIDTH}}{wealth_path.iloc[-1]:.2f}x")
ax.set_xlim(true_returns.index[0], true_returns.index[-1])
ax.set_ylabel("Value of 1.00 invested", fontsize=11, color=INK)
ax.set_title("One history, processed four ways", fontsize=13.5,
fontweight="bold", pad=16, loc="left")
ax.legend(frameon=False, loc="upper left", labelcolor=INK,
prop={"family": "monospace", "size": 10.5}, handlelength=2.4, labelspacing=.55)
ax.tick_params(labelsize=10)
style_chart(fig, ax)
plt.tight_layout()
fig.savefig("dc_wealth_paths.png", dpi=200, bbox_inches="tight", facecolor=BG)
plt.show()
# A day counts as recovered only when all four assets match the truth to 1e-12
day_matches = (np.abs(step3.values - true_returns.values) < 1e-12).all(axis=1)
matching_days = int(day_matches.sum())
wealth_difference = ((1 + step3 @ WEIGHTS).cumprod() / (1 + true_returns @ WEIGHTS).cumprod() - 1)
print(f"daily returns identical to the truth: {matching_days:,} of {len(step3):,}")
print(f"the wealth path runs between {wealth_difference.min():+.2%} and {wealth_difference.max():+.2%} "
f"before ending {wealth_difference.iloc[-1]:+.3%} away")
daily returns identical to the truth: 4,630 of 5,218
the wealth path runs between -0.83% and +0.49% before ending +0.048% awayThe black line is the truth, and the teal dashed line, with both repairs, is drawn on top of the black line.
So, stopping the row deletion alone is not enough. Both repairs together recover the terminal wealth. Each vendor gap is still merged into the next day, as in section 2, so only 4,630 of the 5,218 daily returns match the truth exactly, and along the way the repaired wealth is between 0.83% below and 0.49% above the true wealth.
dropna() makes three mistakes at once, so I measure each alone with the other two handled correctly.
NoteCode: each mistake alone
# Every date on which some asset traded and the vendor did not send it
missing_dates = set()
for asset in names:
traded_dates = set(true_px[asset].index)
reported_dates = set(obs[asset].index)
missing_dates = missing_dates | (traded_dates - reported_dates)
gap_days = pd.Index(sorted(missing_dates)).intersection(true_returns.index)
# Mistake 1 alone: rows deleted from a table of returns that is otherwise correct
rows_deleted = true_returns.drop(index=gap_days)
# Mistake 2 alone: the prices are repaired, so only the gap merged into the next day is left
gap_mistimed = step3
# Mistake 3 alone: no vendor gaps at all, and the crypto weekend still thrown away
weekend_columns = {}
for asset in names:
if ASSETS[asset]["cal"] == "B":
weekend_columns[asset] = price_on_calendar(true_px[asset], biz).pct_change()
else:
weekend_columns[asset] = true_px[asset].pct_change().reindex(biz)
weekend_discarded = pd.DataFrame(weekend_columns).dropna()
def on_valuation_days(portfolio_returns):
"""One clock for both sides: the truth's days, with a zero on any day that is absent."""
return portfolio_returns.reindex(true_returns.index).fillna(0.0)
# label -> (list of (naive, matching truth) pairs, periods a year, years spanned)
CASE = {}
CASE["1 rows deleted by .dropna()"] = ([(on_valuation_days(rows_deleted @ WEIGHTS),
true_returns @ WEIGHTS)],
PORTFOLIO_DAYS_A_YEAR, PORTFOLIO_YEARS)
CASE["2 a gap merged into the next day"] = ([(gap_mistimed @ WEIGHTS,
true_returns @ WEIGHTS)],
PORTFOLIO_DAYS_A_YEAR, PORTFOLIO_YEARS)
CASE["3 weekend returns discarded"] = ([(weekend_discarded @ WEIGHTS,
true_returns @ WEIGHTS)],
PORTFOLIO_DAYS_A_YEAR, PORTFOLIO_YEARS)
print(f"{len(gap_days)} of {len(true_returns):,} valuation days carried a vendor gap somewhere\n")
for case_label, (pairs, periods_a_year, years) in CASE.items():
naive, truth = pairs[0] # these three cases each hold a single pair
error = float((1+naive).prod())/float((1+truth).prod())-1
print(f" {case_label:36s}{error:>+9.2%}")302 of 5,218 valuation days carried a vendor gap somewhere
1 rows deleted by .dropna() -23.25%
2 a gap merged into the next day +0.05%
3 weekend returns discarded -17.77%Deleting the 302 gap days costs 23.25% of the terminal wealth, because each deleted day enters \(W\) with \(R_t = 0\), and in this simulated history the portfolio’s returns on those 302 dates compound to a gain of 1/(1 − 0.2325) − 1 = 30%. Discarding the weekend costs 17.77%. The gap merged into the next day changes the terminal wealth by only +0.05%, because merging moves a return to the next day without losing it, and the small differences along the path happen to cancel by the end. The three costs do not add up to the 32.9% of the three mistakes together, because each is the cost of one mistake with the other two handled correctly.
Limitations
Forward-filling a price is an assumption. Forward-filling values the asset at its last observed price. For a closed market that is the only price there is. During a vendor gap the price did move, and forward-filling records that move on the next observed day.
The size depends on the simulation. The 32.9% belongs to one portfolio, a tenth of it in an asset trading seven days a week, and to one history of vendor gaps.
Conclusion
dropna() raises no error message, and the version it produces has a Sharpe ratio of 0.75 over twenty years, an ordinary number for a weak strategy. Counting rows does not catch the mistake either, because the version that stops deleting rows has a row for every business day and is still 18.9% below the truth. A simulation run through the same pipeline catches the mistake, because the true result is known by construction.
The takeaway is that a processing step needs the same test as a trading rule: build a case where the answer is known in advance, then check that the pipeline returns it.
Read next
- Six biases that distort quant research, measured where the truth is known Six biases measured the same way, on a simulated market where the truth is known in advance.
- How to backtest a strategy A backtest end to end on simulated data with a known edge.
Disclaimer: a teaching example on simulated data throughout. The market is generated, which is the only reason the error can be measured against the truth. Not investment advice.