Getting and Cleaning Price Data

import pandas as pd
import yfinance as yf

raw = yf.download("SPY", start="2015-01-01", end="2024-12-31",
                  auto_adjust=True, progress=False)

print(raw.shape)
print(raw.columns)
print(raw.head())
print(raw.tail())

flowchart TD
    A["⬇️<br/>Download raw prices"] --> B["🔍<br/>Remove duplicates"]
    B --> C["📅<br/>Reindex to business days"]
    C --> D["🩹<br/>Fill short gaps only"]
    D --> E["🌍<br/>Normalize timezone"]
    E --> F["✅<br/>Validate prices and volume"]

Duplicates show up when you append several downloads, merge two vendors, or save a file and reload it carelessly. pandas does not warn you. A duplicated day quietly double-weights that observation in every statistic you compute later.

print(raw.index.duplicated().sum())

clean = raw[~raw.index.duplicated(keep="last")].copy()

keep="last" is usually right for price data: the most recent download is the most likely to be corrected.

Practice 1. Your file has 2,518 rows for a 10-year period and raw.index.duplicated().sum() returns 12. After deduplication, how many rows do you expect, and why might the number differ?

Click to reveal the answer

2,506 rows. Each duplicate date has at least one extra copy, so removing duplicates removes at least 12 rows, and possibly more if a date appears three times. Around 2,500 rows is a sane count for 10 years of US trading days.

Gaps: Two Very Different Problems

A calendar gap is a day the market was closed. Nothing to fix. A data gap is a day the market was open but your row is missing, because of a trading halt, a vendor outage, or a truncated download. Those are the ones that distort results, and they are invisible unless you make them visible.

idx = pd.bdate_range(clean.index.min(), clean.index.max())
clean = clean.reindex(idx)

missing = clean["Close"].isna()
print(missing.sum())
print(clean.loc[missing].head(10))

Now every missing business day is an explicit NaN you can count and look at. Two rules follow.

1. Short gaps can be filled, with a limit. A one or two day hole around a holiday is usually fine to forward fill: clean["Close"] = clean["Close"].ffill(limit=3).
2. Long gaps must not be filled. Forward filling a two-month outage invents the assumption that your position traded at a stale price the whole time. That is how backtests get beautiful results that never existed.

Also check for impossible values, since zero or negative closes are always errors:

print((clean["Close"] <= 0).sum())

Practice 2. Your cleaned frame for SPY shows 40 missing closes in one stretch in March 2020 and 6 scattered single-day holes elsewhere. What do you do?

Click to reveal the guidance

Fill the 6 isolated single days with ffill(limit=3) and log the count. For the 40-day stretch, do not fill. Either accept NaNs (and make sure your strategy code skips them rather than crashing) or trim the sample period. Write down which choice you made, because you will need to explain it in your capstone write-up.

Timezones: The Silent One-Day Shift

Daily bars are often timezone-naive, while intraday bars carry a timezone such as America/New_York. Mixing the two raises a TypeError the moment you compare or join them. Worse, a bar stamped at local midnight and one stamped at UTC midnight are 5 hours apart, which can shift a signal by a full day and quietly introduce lookahead bias.

Normalize everything the same way: convert to UTC, then drop the timezone label so the index is plain and comparable.

if clean.index.tz is not None:
    clean.index = clean.index.tz_convert("UTC")
    clean.index = clean.index.tz_localize(None)

Using UTC also sidesteps daylight saving, which makes one day a year 23 hours long and another 25.

Practice 3. You download hourly bars starting at 09:30 New York time and daily bars from a CSV. What single step makes them safe to compare?

Click to reveal the answer

Convert both indexes to UTC and then localize to naive. After that, a timestamp means the same instant in both sources and joins behave predictably.

Adjusted Prices, in One Paragraph

auto_adjust=True handles splits and dividends for you. If you get a Close column from anywhere else, scan for suspicious one-day jumps of exactly 50% or 33%, which are usually unadjusted splits. Keep in mind that adjusted history is revised retroactively whenever a new dividend is paid, so record the download date and vendor name next to every file you save.

Practical Habits Before You Trust a Price File

• Keep the raw download untouched in its own variable, and build the cleaned version as a copy. You will want to re-run cleaning with different choices.
• After every single step, print shape, head(), and tail(). Most data bugs are visible in five seconds of looking.
• Never forward fill more than a few days, and always log how many rows you filled.
• Print the number of rows, the first date, and the last date. If 10 years of data gives you 400 rows, something broke in the download.
• Treat zero volume days as a flag to inspect rather than a value to average over.
• Do not silently drop rows you do not understand. Count them, note them, then decide.

Which of these do you already do, and which one just caught you? Note it, and keep the list next to your capstone notebook.

What You Can Now Hand to the Next Step

You can download a price series, deduplicate it, put it on an explicit trading calendar, handle gaps honestly, and normalize time. Check yourself against these points:

• Duplicates are removed with duplicated(keep="last")
• Missing business days become visible NaN values via reindex
• Forward filling is capped and counted
• Timezones are converted to UTC and then dropped
• Prices are split and dividend adjusted

Review the facts below, then move on to Returns, Volatility, and Time Series Basics, where those clean prices finally become numbers you can compare: simple and log returns, and what volatility actually measures.

Setting _____ in yfinance returns prices already adjusted for splits and dividends. Duplicate timestamps are removed with clean = raw[~raw.index._____(keep="last")]. A long run of forward-filled prices is dangerous because it _____. Before comparing two price sources, convert both indexes to _____ and then drop the timezone label. Daily price bars contain only _____, so weekends and market holidays never appear.

Front: What does auto_adjust=True do in yfinance? Back: Returns prices already adjusted for splits and dividends, so corporate actions do not look like crashes.

Front: Why does a price table end up with duplicate dates? Back: Appending several downloads or merging two vendors can write the same trading day twice.

Front: How do you drop duplicate timestamps, keeping the newest row? Back: df[~df.index.duplicated(keep="last")]

Front: Why reindex prices to a business-day calendar? Back: It turns missing trading days into visible NaN values instead of silent absences.

Front: What is the danger of forward filling a long gap? Back: It assumes trades happened at a stale price, which inflates backtest performance.

Front: How much forward filling is acceptable on daily data? Back: A few days at most, always with a log of how many values were filled.

Front: What error appears when you compare a tz-aware index to a tz-naive one? Back: A TypeError, because pandas cannot align two different timezone representations.

Front: What is the standard timezone normalization for price data? Back: Convert the index to UTC, then call tz_localize(None) to get naive UTC timestamps.

Front: What should you print right after a download? Back: Shape, column names, and the head and tail rows.

Front: What does a zero or negative Close price mean? Back: A data error that must be corrected or dropped before any analysis.

Front: Why record the download date and vendor with each saved file? Back: Adjusted history is revised retroactively when new splits or dividends occur.

Front: Calendar gap versus data gap: what is the difference? Back: A calendar gap is a market holiday; a data gap is a missing row on a day the market was open.

Front: How many rows should about 10 years of US daily data contain? Back: Roughly 2,500, since there are about 252 trading days per year.