Python and data for quants
You need the quote in force at the instant of each trade. Which join?
- AAn inner merge on the timestamp column
- B
merge_asofwithdirection="backward" - C
merge_asofwithdirection="forward" - DA cross join, then filter the result by time
Try it
Pick an answer
Next free question: Starting from the bracket [0,5], how many bisection steps are needed to locate…
Show the answer and worked solution
Answer: B – merge_asof with direction="backward"
You want the most recent quote at or before each trade, which is exactly what merge_asof with direction="backward" does. An equality join on timestamps returns almost nothing, because trade and quote times rarely coincide to the nanosecond. The forward direction matches each trade to the next quote, which is look-ahead written out in code and is the option to be careful about. A cross join then filter is correct and quadratic, so on a day of tick data it will not finish. Add a tolerance so a trade is never matched to a quote hours old.
Worked solution
- Formula
- Substitute
- SolveTrade and quote timestamps rarely coincide.
- It matches the trade to the next quote.
- Answer
Sanity check. A cross join and filter is correct and quadratic, so on a day of tick data it will not finish.
- A. Trade and quote timestamps almost never coincide exactly, so this returns nearly nothing.
- B. Correct – it matches each trade to the most recent quote at or before it, which is the definition of the prevailing quote.
- C. That matches to the next quote, which is look-ahead written out explicitly.
- D. Correct in principle and quadratic in practice; on tick data it will not finish.
Takeaway: merge_asof backward is the "what did I know then" join.
Report an issue with this question
Learn the method
Of the kind reported at
More python and data for quants questions
- A backtest computes pnl = signal * returns, where both are indexed by day. What is wrong?Applied · Free solution
- You build a ten-year backtest on the stocks currently in an index.Advanced · Premium
- Building one-minute bars, part 3 of 3Advanced · Premium
- The New York equity open is 9:30 local time.Foundation · Premium
- Building one-minute bars, part 1 of 3Foundation · Premium
- A $100 stock goes ex-dividend by $2.Applied · Premium