pandas merge_asof: Match Trading Signals to Past Quotes

A quote recorded 20 milliseconds after a signal is closer than one recorded 100 milliseconds before it. That does not make it information the strategy could have used. When building an automated trading dataset, the nearest observation and the latest available observation answer different questions.

Use a backward time match for prior information, constrain the instrument, and impose a maximum age. Then verify what the timestamps actually mean. A correct join on the wrong clock can still supply a strategy with information from its future.

This check belongs upstream of an execution message. When preparing a Python strategy for an AlgoWay webhook workflow, retain the quote used to make the decision separately from the eventual fill. Successful delivery cannot repair a decision made from an incorrectly aligned research dataset.

Start with three quotes and two decisions

The following invented observations use milliseconds from a common origin. All rows refer to one fictional instrument, DEMO. For this example only, quote time means the time that quote became available to the decision process.

At a signal time of 1,000 ms, a nearest match selects the 1,020 ms quote: the distance is 20 ms rather than 100 ms. A backward match selects 900 ms. At a signal time of 2,000 ms, the most recent earlier quote is 1,500 ms, already 500 ms old.

Suppose this hypothetical strategy accepts quote ages of at most 200 ms. The first signal receives the 900 ms quote. The second receives no eligible quote. The 200 ms limit is an example policy, not a recommendation for a particular market or a measurement of execution latency.

Make direction, instrument and age explicit

The official pandas merge_asof reference documents backward, forward and nearest matching. Backward selects the last eligible right-side key at or before the left-side key. A by column restricts matching to the same group, while tolerance limits the key distance. Both inputs must be sorted ascending on their time keys.

import pandas as pd

signals = pd.DataFrame({
    'symbol': ['DEMO', 'DEMO'],
    'signal_ms': [1000, 2000],
})
quotes = pd.DataFrame({
    'symbol': ['DEMO', 'DEMO', 'DEMO'],
    'quote_ms': [900, 1020, 1500],
    'bid': [100.00, 100.10, 100.20],
    'ask': [100.02, 100.12, 100.22],
})

aligned = pd.merge_asof(
    signals.sort_values('signal_ms'),
    quotes.sort_values('quote_ms'),
    left_on='signal_ms',
    right_on='quote_ms',
    by='symbol',
    direction='backward',
    tolerance=200,
    allow_exact_matches=True,
)
aligned['quote_age_ms'] = aligned['signal_ms'] - aligned['quote_ms']
print(aligned[['signal_ms', 'quote_ms', 'quote_age_ms']])

Here the integer keys are milliseconds, so the tolerance is also expressed in milliseconds. With datetime keys, use compatible datetime types and an appropriate pd.Timedelta tolerance. Keep both the signal time and the matched quote time; a single retained timestamp would hide the age you are trying to audit.

The first output row has quote_ms = 900 and quote_age_ms = 100. The second has missing quote fields and a missing age. It remains a signal row, but it is not ready for a quote-dependent decision.

With several instruments, keep time globally ascending and use by='symbol' to prevent cross-instrument matches. Sorting by symbol first can restart the clock when the next symbol begins. That is not the ascending time order this join requires. If the same symbol label represents different venues or contracts in your dataset, include enough grouping keys to identify the intended instrument.

A missing quote is a decision to handle

For this example, a sensible test policy is to mark the second signal ineligible and record “no quote within age limit.” Filling its missing fields from a later row would defeat the original prior-information requirement. Carrying the old quote forward without checking its age would defeat the freshness rule.

A live strategy might wait for another observation, skip the entry, or halt quote-dependent decisions. Those are design choices with different outcomes. If it waits, the eventual decision happens at a new time and should be recorded as a new evaluation, not silently attached to the old signal timestamp.

A matched row also needs valid payload fields. A timestamp match alone says nothing about whether bid and ask are present, whether the quote is usable, or whether the market is tradable. Keep those checks separate from the join.

Equal timestamps do not prove event order

allow_exact_matches=True permits equality. Setting it to False makes a backward match strictly earlier. That flag changes which row is eligible; it does not recover the real ordering of events stamped with the same time.

Imagine a feed records only whole milliseconds and both a quote and a decision carry 1,000 ms. You need an event sequence or another reliable ordering rule to establish which happened first. If that information is unavailable, document the assumption or exclude the ambiguous equality. Do not call the result causally verified merely because the join completed.

Likewise, if several quotes for the same instrument share one timestamp, decide how duplicates are ordered or resolved before joining. Preserve the sequence field if your data provides one. An accidental file order is a poor substitute for a market-data policy.

Match on availability when reproducing a live decision

Consider a quote with exchange event time 990 ms that reaches your process at 1,030 ms. It was not available to a decision at 1,000 ms. Joining backward on exchange time alone would nevertheless admit it. This follows from the example's timing, not from any special pandas behavior.

For a replay of what your process knew, use a reliable availability timestamp and keep the original event timestamp for diagnosis. Normalize the relevant clocks and timezones. If your historical dataset has only exchange timestamps, state that limitation rather than inventing receipt times.

Finally, a quote available before a signal is not a guaranteed execution price. The order is submitted later, and fills require their own evidence. This alignment check addresses the information used to decide; chronological model validation addresses how you evaluate the resulting model. Both can be correct while the strategy still loses money.