Loading Price Data: CSV Files and Free APIs, Adjusted versus Unadjusted Prices, Missing Values and Time Zones

What you will learn

• How to read a CSV file into a DataFrame with proper dates, and the arguments that fix the usual import problems.
• What free data APIs give you, what their licences and defaults mean, and how to cache downloads.
• How split and dividend adjustment factors are calculated and why a backtest needs adjusted prices but a live order needs unadjusted ones.
• How to find and treat missing values, and how to localise and convert time zones without shifting your bars.

Explanation

CSV files. A CSV (comma-separated values) file is plain text with one row per bar. The key call is:

df = pd.read_csv("data/raw/xyz.csv", parse_dates=["Date"], index_col="Date")

parse_dates converts the text column to timestamps and index_col makes it the index. Common fixes: sep=";" and decimal="," for German exports, dayfirst=True for dates like 03.04.2025, and date_format="%d.%m.%Y" when parsing is wrong. After loading, run df = df.sort_index() (some exports are newest-first), check df.index.is_unique, and lowercase the column names with df.columns = df.columns.str.lower().

Free APIs. An API (application programming interface) lets code request data from a server. For a first project the yfinance library (Yahoo Finance data) is the usual choice: yf.download("SAP.DE", start="2015-01-01"); it is meant for personal research and accuracy is not guaranteed. One default matters: as of 2026 auto_adjust=True, so open, high, low and close are already adjusted for splits and dividends and there is no "Adj Close" column; with auto_adjust=False you get split-adjusted OHLC plus an "Adj Close" that also includes dividends. Alternatives with free tiers include Stooq (CSV downloads), Alpha Vantage and Tiingo (registration and API key required, strict request limits); terms change, so check the current conditions, and lesson 12 covers key handling and rate limits. Whatever the source, save the raw download once to data/raw/ (CSV or Parquet) and work from the file; vendors revise history, and limits are scarce.

Adjusted versus unadjusted. The Systematic Trading course explained why splits and dividends distort backtests; here is how the correction works. For an n-for-1 split, every earlier price is divided by n (and volume multiplied by n). For a cash dividend D paid with ex-date on day t, the standard method multiplies all prices before day t by the factor 1 − D ÷ C, where C is the close on the last day before the ex-date. Adjustments compound backwards. Consequences: adjusted history changes with every new dividend, and percentage returns are correct while euro price levels are not. Rule: backtest on adjusted data, place real orders at unadjusted prices, and never mix the two in one calculation.

Missing values. pandas marks gaps as NaN. Find them with df.isna().sum(). Forward-filling a handful of price gaps with df["close"].ffill() is acceptable, but never forward-fill volume, and never interpolate prices, because interpolation uses the future value and plants look-ahead bias (lesson 7). Otherwise drop rows with df.dropna(subset=["close"]) and record how many. Note that the old fillna(method="ffill") syntax has been deprecated since pandas 2.1 and should not be used in new code; write .ffill().

Time zones. Daily bars rarely need them; intraday data always does. tz_localize("Europe/Berlin") attaches a zone to a naive index without changing clock times; tz_convert("America/New_York") changes the clock times. Localising UTC data to Berlin by mistake silently shifts every bar by one or two hours, so check the vendor's documentation. Daylight-saving dates differ between Europe and the US, which is when mishandled timestamps misalign instruments.

Worked example

Hypothetical example: a share closes at €50.00 on Monday. A dividend of €1.00 has its ex-date on Tuesday, when the share closes at €49.50. Fees and taxes ignored.

Adjustment factor = 1 − 1.00 ÷ 50.00 = 0.98. All prices before Tuesday are multiplied by 0.98: Monday's close becomes 50 × 0.98 = €49.00; a close of €48.00 from the previous week becomes 48 × 0.98 = €47.04.

Return across the ex-date: unadjusted (49.50 − 50.00) ÷ 50.00 = −1.0%, which wrongly looks like a loss. Adjusted (49.50 − 49.00) ÷ 49.00 = +1.02%, which correctly reflects €1.00 of cash received against a €0.50 price loss (net +€0.50 on €50; the small difference arises because the adjusted base is €49).

Now a 2-for-1 split: raw closes 100, 102, 104 before and 52, 53 after. Dividing the pre-split prices by 2 gives 50, 51, 52, 52, 53, a continuous series. Without it, pct_change() reports a −50% return on split day, a crash that never happened.

Common mistake

Practice activity

On paper: a share has closes of €90.00 and €93.00 on two days, then a 3-for-1 split, then closes of €31.00 and €30.38 (hypothetical). On the day of the €30.38 close a dividend of €0.60 has its ex-date, and the close before it was €31.00. (a) Write the split-adjusted values of the two pre-split closes. (b) Compute the dividend adjustment factor and apply it to all three closes before the ex-date. (c) State the raw and the fully adjusted return from the €31.00 close to the €30.38 close.

Key takeaways

• Load with parse_dates and index_col, then sort, check uniqueness and standardise column names.
• Download once, save raw data locally, and read the API's licence and its adjustment defaults (yfinance: auto_adjust=True as of 2026).
• Split factor 1 ÷ n, dividend factor 1 − D ÷ C, applied backwards; backtest on adjusted prices, trade on unadjusted ones.
• Forward-fill a few price gaps at most, never interpolate, never forward-fill volume, and localise time zones once with the vendor's actual zone.

Next step

Lesson 5 turns prices into returns: simple versus log returns, cumulative returns, annualised return and volatility, and rolling windows.