Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Match Rows by Nearest Date with pandas merge_asof

Attach the exchange rate, price or status that applied at each transaction's date — the latest one on or before it — with pandas merge_asof. Match within groups such as currency, limit how stale a match may be, choose backward, forward or nearest, and fix the sorting and dtype errors it raises.
Joins, lookups & comparisonsDates & timespandas

Transactions happen every day, but exchange rates are published on business days, price lists change on arbitrary dates, and an employee's cost centre changes when they move team. Converting a transaction or costing an order needs the value that was in force at that moment: the latest rate on or before the transaction date. An exact merge on the date fails for every weekend transaction, and a loop that searches the rate table for each row is slow. pd.merge_asof does exactly this join. This guide, part of Merging and Joining Excel DataFrames, uses it to convert transactions with daily FX rates from a second workbook.

Each transaction takes the latest rate on or before its date USD rates are published on 2, 3 and 5 March. A transaction on 4 March takes the 3 March rate of 0.93, one on 6 March takes the 5 March rate of 0.91, and one on 9 March also takes the 5 March rate unless a tolerance of three days rules it out. 2 Mar rate 0.92 3 Mar rate 0.93 4 Mar tx 1 → 0.93 5 Mar rate 0.91 6 Mar tx 3 → 0.91 9 Mar tx 4 → 0.91, stale?

Prerequisites

Bash
pip install pandas openpyxl

Transactions in two currencies and a rate table, each in its own workbook:

Python
import pandas as pd

pd.DataFrame({
    "tx": [1, 2, 3, 4, 5],
    "date": pd.to_datetime(["2026-03-04", "2026-03-02", "2026-03-06", "2026-03-09", "2026-02-27"]),
    "currency": ["USD", "GBP", "USD", "USD", "GBP"],
    "amount": [100, 50, 80, 20, 10],
}).to_excel("transactions.xlsx", index=False)
pd.DataFrame({
    "date": pd.to_datetime(["2026-03-02", "2026-03-03", "2026-03-05", "2026-03-02", "2026-03-05"]),
    "currency": ["USD", "USD", "USD", "GBP", "GBP"],
    "rate": [0.92, 0.93, 0.91, 1.17, 1.18],
}).to_excel("fx_rates.xlsx", index=False)

tx = pd.read_excel("transactions.xlsx")
fx = pd.read_excel("fx_rates.xlsx")

Step 1 — Sort, then merge

merge_asof needs both tables sorted by the key. The transactions come in the order they were entered, so the first attempt fails:

Python
try:
    pd.merge_asof(tx, fx, on="date", by="currency")
except ValueError as exc:
    print(exc)                                   # left keys must be sorted

matched = pd.merge_asof(tx.sort_values("date"), fx.sort_values("date"),
                        on="date", by="currency", direction="backward")
matched = matched.sort_values("tx").reset_index(drop=True)
print(matched[["tx", "date", "currency", "rate"]])

by="currency" restricts each match to rates of the same currency, so a USD transaction never picks up a GBP rate even when that is the nearest row by date. direction="backward" — the default — takes the latest rate on or before the transaction date: transaction 1 on 4 March gets the 3 March USD rate, 0.93. Sorting back by tx restores the original order, which merge_asof does not preserve. If the transactions have no ID column, keep the original index with reset_index() before sorting and sort back on it afterwards.

Step 2 — Keep the date of the matched rate

The merged frame shows the rate but not which day it came from. For auditing — "why was this converted at 0.91?" — keep the rate's own date by giving it a different name and using left_on and right_on:

Python
rates = fx.rename(columns={"date": "rate_date"}).sort_values("rate_date")
matched = pd.merge_asof(tx.sort_values("date"), rates, left_on="date", right_on="rate_date",
                        by="currency").sort_values("tx").reset_index(drop=True)
matched["rate_age_days"] = (matched["date"] - matched["rate_date"]).dt.days
print(matched[["tx", "date", "rate_date", "rate", "rate_age_days"]])

Transaction 4, on 9 March, was converted with a rate four days old, and transaction 5 has no rate at all: it is dated 27 February, before the first GBP rate in the table. Both deserve attention before the numbers are used.

The matched table with the rate's own date Each transaction row shows its date, the date of the rate that was used and the rate. Transaction 1 on 4 March used the 3 March rate. Transaction 4 on 9 March used a rate from 5 March, four days old. Transaction 5 on 27 February found no rate because the table starts later. A B C D E F 1 tx date currency rate_date rate age 2 1 03-04 USD 03-03 0.93 1 3 2 03-02 GBP 03-02 1.17 0 4 3 03-06 USD 03-05 0.91 1 5 4 03-09 USD 03-05 0.91 4 6 5 02-27 GBP gold is a stale match, pink found no rate

Step 3 — Refuse stale matches with tolerance

Rates older than a few days usually mean the rate file was not updated — a missed download, a holiday feed. tolerance caps how far back a match may reach; rows beyond it get a missing value instead of a quietly stale rate:

Python
strict = pd.merge_asof(tx.sort_values("date"), fx.sort_values("date"), on="date", by="currency",
                       tolerance=pd.Timedelta(days=3)).sort_values("tx")
print(strict[["tx", "rate"]].values.tolist())
# [[1.0, 0.93], [2.0, 1.17], [3.0, 0.91], [4.0, nan], [5.0, nan]]
missing = strict.loc[strict["rate"].isna(), ["tx", "date", "currency"]]
print(missing.values.tolist())

Choose the tolerance from how the source publishes: three or four days covers weekends and single holidays for daily FX rates. Then fail the run or write the unmatched rows to an exceptions sheet, just as for an ordinary merge — see Validate a pandas Merge to Avoid Duplicate Rows.

Step 4 — Backward, forward or nearest

direction changes which neighbour counts:

Python
for direction in ["backward", "forward", "nearest"]:
    m = pd.merge_asof(tx.sort_values("date"), fx.sort_values("date"), on="date", by="currency",
                      direction=direction).sort_values("tx")
    print(direction, m["rate"].tolist())
# backward [0.93, 1.17, 0.91, 0.91, nan]
# forward [0.91, 1.17, nan, nan, 1.17]
# nearest [0.93, 1.17, 0.91, 0.91, 1.17]

backward is right for anything "in force at the time": rates, prices, statuses. forward finds the next event — the first delivery after an order, the next price review. nearest suits measurements, such as matching a sensor reading to the closest weather observation; it is rarely right for financial data, because it can use a rate published after the transaction.

Choosing the direction for merge_asof Use backward for values in force at the time, such as rates, prices and statuses. Use forward to find the next event after a date. Use nearest for measurements where either side is acceptable. Add allow_exact_matches False when the same-day value must be excluded. Which neighbouring row should each row take? in force at the time backward rates, prices, status next event forward deliveries, reviews closest measurement nearest sensors, readings strictly before exact matches off previous day's close

Step 5 — Exclude same-day matches when needed

Some rules require the previous day's value: invoices converted at the prior business day's closing rate, for example. allow_exact_matches=False skips a right row with exactly the same date:

Python
prior = pd.merge_asof(tx.sort_values("date"), fx.sort_values("date"), on="date", by="currency",
                      allow_exact_matches=False).sort_values("tx")
print(prior["rate"].tolist())     # [0.93, nan, 0.91, 0.91, nan]

Transaction 2 on 2 March no longer takes the 2 March GBP rate, and there is no earlier one, so it is unmatched. The rule belongs in the accounting policy, not in the code; confirm it before choosing.

Step 6 — Fix the dtype errors

merge_asof is strict about the key types. Dates read from different sources can have different resolutions — microseconds from one reader, seconds from another — and the merge refuses them:

Python
fx_seconds = fx.assign(date=fx["date"].astype("datetime64[s]"))
try:
    pd.merge_asof(tx.sort_values("date"), fx_seconds.sort_values("date"), on="date", by="currency")
except pd.errors.MergeError as exc:
    print(str(exc)[:40])          # incompatible merge keys [1] dtype('<M8[u
aligned = fx_seconds.assign(date=fx_seconds["date"].astype(tx["date"].dtype))
print(len(pd.merge_asof(tx.sort_values("date"), aligned.sort_values("date"), on="date", by="currency")))

Cast one side to the other's dtype. The same applies to by columns: a currency column that is text in one file and categorical in the other must be made to match. Dates stored as text in one workbook need pd.to_datetime first; see Parse Excel Dates into Python datetimes with pandas.

Step 7 — Convert and write an auditable result

With the rate and its date attached, the conversion is one multiplication. Write the rate, its date and its age next to the converted amount, and put the exceptions on their own sheet, so anyone checking a figure can see exactly how it was produced:

Python
final = pd.merge_asof(tx.sort_values("date"), rates, left_on="date", right_on="rate_date",
                      by="currency", tolerance=pd.Timedelta(days=3)).sort_values("tx")
final["amount_eur"] = (final["amount"] * final["rate"]).round(2)
final["rate_age_days"] = (final["date"] - final["rate_date"]).dt.days
ok = final["rate"].notna()
with pd.ExcelWriter("converted.xlsx", engine="openpyxl", datetime_format="yyyy-mm-dd") as writer:
    final[ok].to_excel(writer, sheet_name="Converted", index=False)
    final[~ok][["tx", "date", "currency", "amount"]].to_excel(writer, sheet_name="No rate", index=False)
print(final.loc[ok, "amount_eur"].tolist(), int((~ok).sum()))   # [93.0, 58.5, 72.8] 2

Keeping the rate date in the output is what makes the result defensible: a reviewer can compare it with the published rate for that day without rerunning anything. The "No rate" sheet tells whoever maintains the rate file exactly which currencies and dates are missing. If rounding must match an existing spreadsheet to the cent, use an Excel-compatible rounding function rather than round; see Match Excel Rounding in pandas.

Common pitfalls

  • Missing values in the key. Rows with no date cannot be placed in order; drop or fix them before merging, or the merge raises an error.
  • Duplicate dates on the right. If the rate table has two rates for the same currency and day, merge_asof takes the last one in sorted order. Deduplicate explicitly first.
  • Forgetting by. Without it, every transaction takes the latest rate of any currency.
  • Timestamps with times of day. A transaction at 09:00 does not match a rate stamped 17:00 on the same day with backward. Normalise both sides to dates when the rule is "that day's rate", or keep the times when intraday rates matter.

Conclusion

merge_asof joins each row to the latest earlier row of another table — the right tool for rates, prices and statuses that were in force at a date. Sort both tables, match within groups with by, keep the matched date for auditing, cap staleness with tolerance, pick the direction that fits the rule and align key dtypes. Report the unmatched and stale rows rather than letting them through.

Frequently asked questions

What does merge_asof do? For each row on the left, it finds the row on the right with the closest key that is on or before it by default, like a VLOOKUP with approximate match. It is the tool for joining transactions to the rate, price or status that was valid at the time.

Why do I get "left keys must be sorted"? merge_asof requires both tables to be sorted by the key column. Sort both with sort_values before merging, and restore the original order afterwards using an ID or the original index.

How do I match only within the same currency or product? Pass by="currency" (or a list of columns). Each left row is then matched only against right rows with the same values in those columns.

How do I stop a very old rate from being used? Pass tolerance, for example tolerance=pd.Timedelta(days=3). Rows whose nearest match is further away get missing values instead of a stale match, so you can report them.

More on joins, lookups & comparisons

More on joins, lookups & comparisons →