Automating End-of-Day Price Cleanup for Portfolio Backtests
If two runs of the same backtest give you two different answers, the model is rarely the culprit. The data is. You downloaded prices on a Tuesday, fixed a split by hand on a Wednesday, patched a missing quote with yesterday’s close, and converted everything to euros with whatever FX rate the browser gave you that afternoon. The optimizer did exactly what you asked — on numbers that no longer match the ones you used last month.
Automating end-of-day (EOD) price cleanup is what turns a backtest into something you can iterate on instead of something you redo. This article walks through a pipeline you set up once: fetch prices, apply consistent cleaning rules, handle corporate actions and currency conversion, cache the result, and re-run it with minimal intervention.
The Hidden Cost of Manual End-of-Day Price Cleanup
The manual loop looks harmless until you count the steps:
- Download EOD prices for every candidate from your provider’s export screen or API.
- Reconcile splits and dividends so a 4:1 split doesn’t look like a 75% drawdown.
- Align trading calendars — different exchanges, different holidays, different half-days.
- Find and remove outliers: stale quotes, fat-finger prints, zero-volume days.
- Convert currencies if your candidates are quoted in more than one.
- Repeat next week, and try to reproduce exactly what you did last week.
Two things go wrong. First, iteration slows: a hypothesis you could test in an afternoon waits for a data afternoon. Second, and worse, each manual pass is a small, undocumented decision. Drop that outlier or keep it? Backfill that gap or truncate the series? Those choices feed straight into correlation estimates, and correlation estimates are what determine portfolio weights. A handful of misaligned dates is enough to change which candidates look weakly correlated — and therefore which ones get capital.
The goal is a repeatable pipeline: raw prices in, cleaned and cached prices out, same rules every time. RSJ Portfolio ships this as a built-in price cleaning pipeline with a local cache, so the steps above stop being your job.
One-Time Setup: Configuring Your Data Pipeline
Everything in this article assumes one configuration step, done once. In RSJ Portfolio, these live on the Configuration page of the app (the Configuration chapter of the user guide covers each in detail):
- Data provider — where EOD prices come from. Supported providers are listed on the Data and currencies page.
- Local currency — the currency your portfolio and results are expressed in.
- Date range — the history used for every correlation and risk estimate.
- Cleaning settings — how the pipeline treats splits, dividends, outliers and gaps.
- Local cache — the on-disk store of cleaned prices, so later runs don’t re-fetch what you already have.
Set these before the first run, not after you’ve seen the results. It’s tempting to tune the date range once you notice that a shorter window improves the backtest — that’s how you end up curve-fitting your data window instead of your strategy.
A useful habit: write your settings down. Not because the app forgets them, but because you will, and because a result you can’t reproduce isn’t a result.
# run-manifest.yaml - keep this next to every result you keep
run: 2026-10-19_lowcorr_v3
data:
provider: <provider configured in the app>
local_currency: EUR
date_range: [2015-01-01, 2026-09-30]
cleaning: <cleaning settings as configured>
universe:
- <candidate 1>
- <candidate 2>
optimizer:
objective: minimum volatility
risk_model: <one of the eight risk models>
constraints:
max_weight: 0.10
Keep the manifest in plain text. When you revisit the run in six months, “I think I used a five-year window” is not a reproducibility strategy.
The Automated Pipeline: From Raw Prices to Backtest-Ready Data
Once configured, the flow is: fetch EOD prices → apply cleaning rules → convert currencies → cache the cleaned series → hand the result to the optimizer. You don’t drive that sequence by hand each time; you trigger a refresh, and the pipeline does the rest.
The cleaning stage is where most of the manual work disappears. Splits and dividends are adjusted so return series are continuous rather than showing artificial jumps. Outliers and missing values are handled by rule rather than by improvisation, which matters more than the specific rule — a consistent treatment you can describe beats a clever treatment you applied once. When a portfolio is quoted in several currencies, the conversion path takes the series to your configured local currency as part of the same pass.
Refreshes are incremental. Prices already in the local cache don’t need to be fetched again, so a routine update pulls the new dates and leaves the rest alone. That has a side benefit: the cache is also your fallback. If a provider is down, you keep working from the most recent cleaned prices instead of stopping.
Contrast that with the hard way — a script per provider, rewriting split handling whenever your data vendor changes its schema:
# The hard way: fragile provider glue, rewritten every few months.
import requests, pandas as pd
raw = requests.get(f"{PROVIDER}/eod", params={
"symbols": ",".join(tickers), "from": start, "to": end
}).json()
df = pd.DataFrame(raw).set_index("date").sort_index()
df = df[~df.index.duplicated()]
df = df.dropna(how="all")
# splits? dividends? currency? calendar alignment? Good luck.
That snippet isn’t a strawman — it’s what most people build first. It works right up until it silently doesn’t. The supported provider list and the multi-currency conversion path are documented on the Data and currencies page; check it before you assume your provider is covered.
Monitoring and Failure Handling
Automation without monitoring is just deferred debugging. The realistic failure modes:
- Provider outage or rate limiting — the fetch returns nothing, or returns partial history.
- Stale data — the refresh “succeeds” but the newest cached date is a week old.
- Symbol changes — a ticker is renamed or delisted, and the series stops where the old symbol ends.
- Currency conversion problems — a missing FX rate on one date quietly distorts a cross-currency series.
A ten-minute routine catches nearly all of it:
- Check freshness before you trust a run. Compare the newest date in your cached series against the last trading day you’d expect. If they differ, refresh before optimizing.
- Spot-check a sample of prices. Export the cleaned series and compare a handful of dates against the provider’s own numbers. If you keep exports in a folder, a single command tells you whether anything has gone stale:
# Anything in my export folder older than a week?
find ./exports -name "*.csv" -mtime +7 -print
- Re-run failed fetches. Most transient provider errors clear on a second attempt. Re-refresh and check the newest date again.
- Watch for discontinuities. An unexplained jump in one series usually means a corporate action the pipeline didn’t see, or a symbol that changed underneath you.
# Spot-check: find the largest day-over-day moves in the cleaned export
import pandas as pd
clean = pd.read_csv("exports/prices_clean.csv", parse_dates=["date"], index_col="date")
col = clean.columns[0]
moves = clean[col].pct_change().abs().sort_values(ascending=False)
print(moves.head(10))
Anything at the top of that list that you can’t explain from public news is worth investigating before it reaches the optimizer.
When a fetch does fail, the local cache is what keeps the afternoon productive: you backtest on the last known-good cleaned prices, then refresh when the provider recovers.
Best Practices for Reliable Automated Cleanup
- Hold the date range and cleaning settings constant across comparable runs. Changing the window between runs is the easiest way to introduce look-ahead bias without noticing, because the model always sees the window you gave it.
- Refresh on a schedule, not on impulse. Regular updates keep the cache current; ad-hoc refreshes mid-study change your inputs halfway through a comparison.
- Verify corporate actions on the names that matter. Splits and dividends are handled by the pipeline, but it’s worth confirming that a large historical drawdown in your candidate list is a real price move and not an unadjusted event.
- Use the same provider and currency settings for every candidate. Mixing sources across a universe means mixing adjustment conventions, and correlation estimates are sensitive to exactly that.
- Document the configuration with the result. RSJ Portfolio stores your settings, so repeated runs stay consistent — but the manifest you write yourself is what makes a result defensible months later.
How RSJ Portfolio Fits into Your Automation Workflow
RSJ Portfolio is a Windows desktop application that builds a statistically optimized stock portfolio from historic end-of-day prices. It analyses the return correlation between stocks and allocates capital to candidates that correlate weakly — or whose downside correlates weakly — which is how the resulting portfolio’s volatility comes down. Allocation uses modern portfolio theory, and the objective, the risk estimator and the weight constraints are all selectable.
The automation angle is straightforward: the price cleaning pipeline and local cache replace the download-adjust-align-convert loop, and the optimizer consumes the cleaned series directly. There’s no separate data engineering step between “I updated prices” and “I have weights.”
A few practical consequences:
- It runs locally. The application is installed on your machine and the cache lives there too, so your price history and holdings assumptions don’t leave the desktop.
- The methods are selectable. Minimum volatility, maximum Sharpe ratio, minimum semi variance, hierarchical risk parity and conditional value at risk are all available, with eight risk models, return models, weight constraints and exports. (If hierarchical risk parity is new to you, the plain-language explainer is the place to start.)
- Starting parameters are opinionated for a reason. The Getting started guide lists the configuration worth setting before your first run and recommended starting parameters, so you are not choosing eight risk models by vibe on day one.
Licensing is a single-user license; the Downloads page carries the Windows installer with a SHA-512 checksum published next to a versioned filename, plus the user guide in English and German.
FAQ
Which data providers does RSJ Portfolio support for end-of-day prices? RSJ Portfolio supports multiple data providers; the exact list is available on the Data and currencies page. You can configure your preferred provider in the application settings.
How does the local cache work and when is it updated? The local cache stores cleaned end-of-day prices on your machine. It is updated when you refresh data or run a new backtest, and it allows you to work offline and speed up subsequent runs.
Can I automate the cleanup for multiple currencies? Yes. RSJ Portfolio includes a conversion path for portfolios quoted in several currencies. You can set your local currency and the tool will handle conversions automatically during the cleaning process.
What happens if a data provider is temporarily unavailable? Because prices are cached locally, you can continue backtesting with the most recent cached data. When the provider is back online, you can refresh to update the cache.
Conclusion
Manual EOD cleanup is a tax you pay on every backtest, and it compounds: slower iteration, inconsistent inputs, and results you can’t reproduce. Automating it isn’t glamorous work, but it’s the difference between a pipeline you trust and a spreadsheet you hope is right.
The setup is a one-time configuration: provider, local currency, date range, cleaning settings. After that, refreshes update the cache incrementally, currency conversion happens in the same pass, and the optimizer runs on data that was cleaned the same way it was last week.
Install the Windows build, work through the Configuration and Getting started chapters once, and put the manual loop behind you. Your next backtest should be a decision about allocation — not a data project.
Related posts
- What Is Hierarchical Risk Parity? A Plain-Language Explainer
- Best Portfolio Optimization Tools in 2026: A Buyer’s Guide
- Portfolio Optimization Software Built for Individual Investors