AppliedMultiple choiceFree solution

You need the quote in force at the instant of each trade. Which join?

  1. AAn inner merge on the timestamp column
  2. Bmerge_asof with direction="backward"
  3. Cmerge_asof with direction="forward"
  4. DA cross join, then filter the result by time
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

  1. Formula
    prevailing quote=last quote at or before t\text{prevailing quote} = \text{last quote at or before } t
  2. Substitute
    merge_asof, direction backward\text{merge\_asof, direction backward}
  3. Solve
    equality join matches almost nothing\text{equality join matches almost nothing}
    Trade and quote timestamps rarely coincide.
  4. forward direction is look-ahead\text{forward direction is look-ahead}
    It matches the trade to the next quote.
  5. Answer
    Backward asof, with a tolerance\text{Backward asof, with a tolerance}

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.

Answer it in practice – your answer is marked and recorded.

More python and data for quants questions

You need the quote in force at the instant of each trade. Which join? · QuantMax