Python for Market Data, lesson 3
Dates and alignment
Trading calendars, missing days and joining series correctly.
Every market shuts on its own holidays, so two price series can have the same length and still cover different days. This lesson lines series up by date and shows what goes wrong when you don't. You need a DataFrame with dates as its index, and .loc.
By the end, you can:
Part 1. Each market keeps its own calendar
Find the days one market traded and another didn't.
Weekends are shut everywhere, but holidays differ from country to country. Take three weeks in 2026. New York was shut on Monday 7 September for Labor Day, and London on Monday 31 August for its summer bank holiday.
Example 1.1
Three weeks, two calendars
List the weekdays from Monday 24 August to Friday 11 September 2026, then drop each market's holiday. How many days did each market trade, and which days did only one of them trade?
- List the weekdays
days = pd.bdate_range("2026-08-24", "2026-09-11")→ 15Three weeks of five weekdays.
- Drop each holiday15 − 1 = 14 for each
ny = days.drop(pd.Timestamp("2026-09-07"))takes out Labor Day. London'sldndrops 31 August. - Compare the two
ny.difference(ldn)→ Mon 31 Augdifferencelists the dates in one index that aren't in the other. The other way round gives Mon 7 Sep.
Answer14 days each. New York alone traded on 31 August, and London alone on 7 September.
Count the rows and the two look the same: 14 each, same first day, same last day. The difference hides in the middle. Zip them by position and from 31 August each London price sits next to a New York price from a day earlier, until 7 September lines them back up.
Trading calendar
The days a market is open. Weekends are out everywhere, but holidays differ: New York shut on 7 September 2026 for Labor Day, and London on 31 August for its summer bank holiday. In real code, load each exchange's published calendar instead of typing holidays by hand.
Holidays aren't the only gaps. A stock can be suspended for a day, or list partway through your sample. Whatever the reason, the rule is the same: match rows by their dates, never by their position.
Your turn
New York's series has 14 rows and London's has 14 rows, over the same three weeks. Is it safe to zip them by position?
Part 2. Joining on the date
Join two series by date, and keep only the days both traded.
pandas lines things up by label, and a date is a label. Put two Series into one DataFrame and their rows match by date, whatever calendar each came from.
Example 2.1
KITE and WREN, by date
KITE trades in New York and WREN in London, each on its own calendar over the three weeks: 14 closes each. Join them by date. How many rows does the joined table have, and how many hold both prices?
- Join on the date
joined = pd.DataFrame({"KITE": kite, "WREN": wren})→ 15 rowsEvery date either Series has gets a row.
- Look at the holidays31 Aug: WREN is NaN. 7 Sep: KITE is NaN
A market that was shut has no price that day.
- Keep the days both traded
both = joined.dropna()→ 13 rowsdropna()drops every row with a NaN in it.
Answer15 rows joined, and 13 with both prices: 14 + 14 − 13 = 15.
Joining kept the union of the two calendars, every date from either side, and marked each gap with NaN. Dropping the NaN rows left the intersection, the dates both share. Neither step looked at positions, so a holiday can't shift anything.
Outer and inner join
An outer join keeps every date from either side and fills the gaps with NaN. An inner join keeps only the dates both sides have. pd.concat([kite, wren], axis=1, join="inner") does the inner join in one step.
Which you want depends on the question. To compare the two stocks day by day, use the inner join. To value a portfolio holding both on every New York day, keep New York's dates and fill London's gaps, which is the next part.
Your turn
New York traded on 250 days of a year and London on 252, and 246 of those days were the same. How many rows does the outer join have, and how many are left after dropna()?
dropna() keeps the 246 days both traded.Part 3. Filling the gaps
Fill a shut market's days with its last price, and say what that does to its returns.
Sometimes you want every day on one calendar, for example to value a portfolio on each New York day. The usual fill is forward: on a day London was shut, a London stock's price is still the last one it traded at.
Example 3.1
WREN over Easter
London was shut on Good Friday, 3 April 2026, and on Easter Monday, 6 April. New York was shut only on the Friday. WREN closed at 110.00 on Thursday 2 April, 112.20 on Tuesday 7 and 111.64 on Wednesday 8. Put WREN on New York's days, fill forward, and take returns.
- Put it on New York's days
wren.reindex(ny_days)→ NaN on the 6threindexgives the Series exactly these dates, with NaN where it has no price. - Fill forward
.ffill()→ 110.00 on the 6thThe last known price is carried into the gap.
- Take returns6th: 0%. 7th: 112.20 / 110.00 − 1 = +2.00%
The holiday looks like a flat day, and the 7th carries everything since Thursday's close.
AnswerReturns of 0%, +2.00% and −0.50% on 6, 7 and 8 April.
import pandas as pd
days = pd.bdate_range("2026-04-02", "2026-04-08")
ny_days = days.drop(pd.Timestamp("2026-04-03")) # New York: shut on Good Friday
wren = pd.Series([110.00, 112.20, 111.64],
index=pd.to_datetime(["2026-04-02", "2026-04-07", "2026-04-08"]))
t = pd.DataFrame({"raw": wren.reindex(ny_days)})
t["ffill"] = t["raw"].ffill()
t["return"] = t["ffill"].pct_change().round(4)
print(t)
# raw ffill return
# 2026-04-02 110.00 110.00 NaN
# 2026-04-06 NaN 110.00 0.000
# 2026-04-07 112.20 112.20 0.020
# 2026-04-08 111.64 111.64 -0.005A shut market really did hold its price: nobody could trade, so the last close is an honest value. But the 0% day is made up, and the news from the 6th lands in the 7th's return. Next to a New York stock, the 6th pairs a flat London day with a real New York move.
Forward fill
ffill() copies the last known value forward into the gaps. Give it a limit, such as ffill(limit=3), so a stock that stopped trading doesn't keep its last price for ever.
Never fill backward for research. bfill() would give the 6th the 7th's price, 112.20, a price nobody knew on the 6th. That's look-ahead: code that uses the future, and a backtest built on it looks better than it could ever trade.
Your turn
London is shut on Monday 31 August. After joining by date and calling ffill(), what does London's return on Tuesday 1 September cover?
Part 4. Join first, then take returns
Take returns that cover the same days for both markets, then compare them.
Now the payoff: the correlation of daily returns between KITE in New York and WREN in London. Each row compares two returns, so it only means something if both cover the same stretch of time.
Example 4.1
The row for Tuesday 8 September
New York was shut on Monday 7 September. KITE closed at 106.20 on Friday 4 and 106.30 on Tuesday 8. WREN closed at 106.82, 105.83 and 106.89 on the Friday, Monday and Tuesday. What should the row for 8 September hold?
- KITE's return106.30 / 106.20 − 1 = +0.09%
New York was shut on Monday, so KITE's return runs from Friday to Tuesday.
- WREN's, on its own days106.89 / 105.83 − 1 = +1.00%
London traded on Monday, so this covers only Monday to Tuesday: a different stretch.
- Join first, drop NaN
joined.dropna()drops 7 SeptemberKITE has no price that Monday, so for both stocks the row before Tuesday is now Friday.
- Then take WREN's return106.89 / 106.82 − 1 = +0.07%
Both returns now run from Friday's close to Tuesday's.
AnswerKITE +0.09% and WREN +0.07%, both from Friday 4 to Tuesday 8 September.
WREN fell on the Monday and rose on the Tuesday. New York only saw that Monday's news on Tuesday, all at once. Pairing KITE's two days with WREN's one compares different stretches of time. Over the three weeks, joining first gives a correlation of 0.89; zipping the same prices by position gives 0.30.
Non-synchronous returns
Two returns in one row that cover different stretches of time are non-synchronous. They make related stocks look less related. Join the prices by date, drop the days either market was shut, and only then take returns.
joined.dropna().pct_change()
The order is the trap. Take returns on each calendar first and join afterwards, and the rows still match by date, so nothing looks wrong: no error, no NaN, just returns that cover different days.
Your turn
You take each stock's returns on its own calendar, then join them and drop NaN. What's wrong with the row for Tuesday 1 September?
Where you'll meet it
Calendars aren't the only clock problem. New York trades until 4:00 p.m. Eastern time, hours after London's close, so a London close and a New York close on the same date are prices from different moments. News that breaks in New York's afternoon reaches London's prices the next day, which makes the two look related with a one-day lag. Researchers handle it with prices taken at the same moment, or by comparing returns over several days.
Joining two series with different calendars is a standard data-round task, often inside a bigger question such as a hedge ratio or a pairs trade. Say which join you used and why, and check the row count before and after.
import numpy as np
import pandas as pd
days = pd.bdate_range("2026-08-24", "2026-09-11")
rng = np.random.default_rng(18)
common = rng.normal(0, 0.01, len(days)) # news both stocks share
r = pd.DataFrame({"KITE": common + rng.normal(0, 0.005, len(days)),
"WREN": common + rng.normal(0, 0.005, len(days))},
index=days)
px = 100 * (1 + r).cumprod()
kite = px["KITE"].drop(pd.Timestamp("2026-09-07")) # New York's calendar
wren = px["WREN"].drop(pd.Timestamp("2026-08-31")) # London's calendar
# Wrong: line the two up by position, like zipping two lists
zipped = pd.DataFrame({"KITE": kite.to_numpy(), "WREN": wren.to_numpy()})
# Right: line them up by date, then keep the days both traded
joined = pd.DataFrame({"KITE": kite, "WREN": wren})
both = joined.dropna()
print(len(zipped), len(joined), len(both))
# 14 15 13
print(joined.loc["2026-09-04":"2026-09-08"].round(2))
# KITE WREN
# 2026-09-04 106.2 106.82
# 2026-09-07 NaN 105.83
# 2026-09-08 106.3 106.89
def corr(t):
return t.pct_change().corr().iloc[0, 1].round(2)
print(corr(zipped), corr(both))
# 0.3 0.89Check yourself
Three questions from across the lesson. Try each one before you look back.
1
To put a London stock on New York's days, someone fills its holiday gaps with bfill(), the next price. What's wrong?
bfill() copies the next price back into the gap, so a holiday shows a price from a day that hadn't happened yet. The returns differ too: the jump lands on the holiday instead of the day after. Fill forward.2
a and b are two calendars. a has 250 dates, a.difference(b) has 3 and b.difference(a) has 2. How many dates does b have?
b has those 247 plus its own 2: 249.3
You value a portfolio of a New York stock and a London stock on every New York trading day. Which join and fill?
What to remember
1Each market keeps its own calendar
Same length isn't same days: from 24 August to 11 September 2026, New York and London traded 14 days each, with a different Monday off.
2Joining on the date
Series put into one DataFrame join by date, an outer join of 15 rows, and
dropna()keeps the 13 days both traded, the inner join.3Filling the gaps
ffill()carries the last close into a holiday, which makes a 0% day and moves the holiday's news to the next return, whilebfill()uses the future.4Join first, then take returns
Join first, drop NaN, then take returns, so each row's two returns cover the same days: a correlation of 0.89, against 0.30 zipped.
Python for Market Data overviewSpotted a mistake? Tell us.