Joining Trades to the Most Relevant Quotes with dplyr
Summary
This tutorial explains join features introduced in dplyr 1.1.0, with examples drawn from market data preparation. It first shows how to express ordinary key-based joins, then demonstrates inequality joins and rolling “closest” joins. These tools can attach the latest quote at or before each trade, optionally requiring a strictly earlier timestamp. The examples match trades and quotes by ticker and show how equality at the same timestamp changes which quote is selected.
The tutorial also illustrates shifting quote timestamps by a small buffer before matching, and using interval conditions to return every quote within a specified time range. This is useful when preparing trade and quote data for analysis, including execution and market-microstructure studies. The examples are small demonstrations rather than an evaluation of trading performance. Users still need to choose appropriate timestamp rules and buffers for their data, since join direction, equality, and interval bounds affect which observations are matched; the tutorial does not establish that any particular rule is universally correct.
Key ideas
- dplyr 1.1.0 supports expressive joins using equality and inequality conditions.
- A closest-time join can associate each trade with the nearest eligible quote for the same ticker.
- Using a strict time comparison excludes quotes timestamped exactly at the trade time.
- Timestamp buffers and interval joins allow additional control over which quotes match a trade.
- Join rules must reflect the timing conventions and research question of the underlying market data.
Tags
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.