Data sourcing and cleaning — what it actually means for portfolio builders

"Data is the fuel. Clean data is the premium gasoline that doesn't make your engine explode." — your slightly unhinged investment TA

You're not reading this in isolation. You've already learned about wrappers, fees, and the operational choices that let strategies scale — robo-advisors, model portfolios, and the deliciously complex world of structured products. Now ask yourself: do those decisions still matter if the data feeding your models is garbage? Welcome to Data sourcing and cleaning, the backstage crew that actually makes investing decisions perform on cue.

What is Data sourcing and cleaning?

Data sourcing and cleaning is the process of finding, acquiring, validating, and transforming raw financial and alternative data so it's accurate, consistent, and ready for analysis or automated trading systems.

• Sourcing = where you get the facts, ticks, fundamentals, sentiment, ESG scores, etc.
• Cleaning = making those facts trustworthy — resolving missing values, aligning timestamps, correcting corporate actions, normalizing formats.

Why this matters: if your robo-advisor or ETF management system uses poorly sourced/cleaned data, you pay fees and still get bad exposures. Yes, cost-efficient wrappers were a great start — but cheap wrappers + rotten data = elegantly packaged disappointment.

How does it fit into investment management workflows?

1. Research/strategy development: bad data biases backtests; survivors and look-ahead bias are sneakier than they look.
2. Portfolio construction: weighting, risk estimates, and optimization need consistent history.
3. Execution & monitoring: stale or misaligned timestamps cause slippage and execution errors.

Think of sourcing/cleaning as the plumbing between your brilliant strategy and actual market behavior. No pipes, no water.

Common data sources (and their personalities)

Pick the source that matches your use case: you don't need tick-level from an exchange for a quarterly-tilted strategy — but you do need corporate actions that actually apply to your share class.

Data quality dimensions (the checklist that judges your dataset)

• Accuracy: Is the number correct? (Yep/no/please fix)
• Completeness: Are rows/columns missing? Where and why?
• Timeliness: When does the data arrive relative to the event? (Pre/post-trade matters)
• Consistency: Same identifiers, currencies, units across files?
• Lineage: Where did this come from and what transformations were applied?

If you can’t answer these, your backtest is a house of cards.

Practical cleaning steps (a playbook)

1. Harmonize identifiers — map tickers, CUSIPs, ISINs to a canonical ID.
2. Adjust for corporate actions — splits, dividends, mergers, spin-offs change history.
3. Align timestamps — convert timezones and align to trading sessions.
4. Normalize units and currencies — make sure everything uses the same base.
5. Handle missing values — don’t impute blindly: use business rules, drop, or flag.
6. Detect outliers and errors — rule-based thresholds + visual checks.
7. Document transformations — automated change logs and provenance.

Ordered, repeatable, auditable. Like an assembly line that actually cares about quality control.

Tools & technology — the pragmatic toolkit

• SQL: bulk ETL, joins, deduping. Your backbone.
• Python + pandas: flexible cleaning, resampling, corporate-action adjustments.
• R: stats-heavy preprocessing and visualization.
• Airflow / Prefect: orchestration and scheduling ETL pipelines.
• Cloud storage & versioning (S3 + Delta Lake): snapshots and reproducibility.
• Vendor platforms: Bloomberg/Refinitiv/FactSet APIs when you need authoritative fields.

Example pandas pseudocode for aligning prices to splits and timezones:

# pseudocode
prices = load_prices('daily_ticks.csv')
actions = load_corporate_actions('actions.csv')
prices = apply_splits_dividends(prices, actions)
prices.index = prices.index.tz_convert('UTC').normalize()

Use tests for expected row counts, null fractions, and value ranges. Yes, unit tests for data.

Examples of data cleaning pain points (true horror stories)

• A fund optimized US weights but mixed up tickers for ADRs and local listings — positions doubled in exposure.
• A backtest used adjusted close for returns but unadjusted for risk metrics — volatility undercounted.
• Corporate-action feed delayed a week; rebalancing used stale share counts — corporate governance mess.

Humor aside: these are not rare. They're preventable with clear rules and monitoring.

Common mistakes in data sourcing and cleaning

1. Treating vendor data as gospel — vendors make mistakes too.
2. Ignoring provenance — no audit trail = no accountability.
3. Over-imputing missing data — creative fills create phantom alpha.
4. Not versioning datasets — hard to reproduce when models diverge.
5. Building ad hoc scripts that live on a dev machine — ops fragility kills strategies.

Quick checklist before you trust a dataset

• Are identifiers unified? ✓
• Corporate actions applied? ✓
• Timezones normalized? ✓
• Currency/unit normalized? ✓
• Lineage logged? ✓
• Alerts for data breaks? ✓

If you can’t tick most of these, budget time and money for fixing it. It’s cheaper than losing client trust or paying fees for a system that makes bad trades.

Closing — the real alpha is reliable data

Data sourcing and cleaning is boring in the same way that brakes are boring — until you need them. For investment managers, clean data reduces implementation error, makes fees and wrappers actually meaningful, and turns model portfolios and robo-advisors from cute demos into dependable products.

Key takeaways:

• Treat data infrastructure as a first-class expense — like you treated custody and compliance.
• Build repeatable, tested pipelines with provenance and alerts.
• Match data choices to business use cases: latency, cost, and coverage matter.

Go audit your datasets. Make a checklist. Ask your vendor for lineage. And when in doubt, imagine your backtest at a TED talk — only the clean data gets the mic.