quantlab.acquisition.wrds.nbbo_bars_sql#

The SQL statement that resamples TAQ NBBO records into bars on the WRDS server.

ADR 0027: over the slow link to WRDS, downloading every NBBO record and resampling locally takes weeks for a decade of S&P 500 data, so the resampling runs in SQL and only bars cross the network. NbboBarsQuery builds that statement for one day table and one batch of symbols. It reproduces quantlab.dataset.nbbo.resample.NbboResampler step by step:

  1. Order. row_number() OVER () numbers the rows in the server’s scan order before anything else touches them, the same assumption the tick path makes with wrds_row_ord. Records are ordered by time (with time_m_nano from 2018 on), then by that number, which before 2018 is the only way to order records sharing a microsecond.

  2. Normalise. A null or NaN price nulls its side and that side’s size.

  3. Filter. The rules of NbboFilterPolicy that are enabled, in its priority order (non-positive price, condition, crossed, locked); a dropped record is ignored completely and counted under its first matching rule. Records after the session close are dropped too.

  4. Bars. Bar k covers (open + (k-1)d, open + kd]; a record at or before the open falls in bar 0, the seed. The snapshot of bar k is the last kept record at or before its end, so records sharing an instant collapse to the last one and a bar with no update carries the quote in force. n_updates counts every kept record inside bar k, tied ones included; n_ambiguous_ties counts those of them whose instant holds more than one distinct (bid, bid size, ask, ask size) state, so identical duplicates count zero.

  5. Time weights. After ties collapse, each quote is in force from its instant (the seed from the open) to the next quote or the close. Each such span is cut at the bar edges; tw_spread, tw_bid_size and tw_ask_size of bar k are the sum of value times duration over the pieces in bar k where the value is defined, divided by the sum of their durations, and NULL when the value was never defined in the bar.

  6. Grid. Every (ticker, k) for k = 1..N is returned for every ticker with a record that day, also when all its records were dropped, so the result has exactly tickers x N rows. page_rows, the row count the server computed, is on every row, so the client can tell a truncated transfer from a complete one.

  7. Filter counts. The FILTER_STATS_COUNTS of each ticker over all its records of the day, as NbboResampler.resample_with_stats counts them, are set on the ticker’s bar 1 row and NULL on its other rows.

Steps 4 and 5 share one ordering of the kept records by ticker, time and scan order: a record is the last at its instant when the next record is later, and the last in its bar when the next record is in a later bar, so the server sorts the day’s records once. Only records sharing their instant with a neighbour are grouped by instant for n_ambiguous_ties, and only quotes in force across a bar edge are split into pieces per bar.

Times are compared as nanoseconds since midnight on TAQ’s time_m clock (New York); the client computes the session bounds from the XNYS calendar and passes them in, so the server needs no calendar. The bar labels are computed by the client from k. mid, spread, spread_bps and imbalance are derived by the client from the snapshot. An instant holds several states when, in any of the four columns, its records hold more than one distinct value or mix null and non-null ones; values are compared as numbers, never through their text form.

Every value enters the statement as a psycopg2.sql.Literal and every name as a psycopg2.sql.Identifier; nothing is pasted in as text. The statement is always restricted by the (sym_root, sym_suffix) pairs condition of WrdsSession.where_clause, so it never scans a whole day table.

Classes

NbboBarsQuery

The server-side resampling statement for one day table and one symbol batch.