9. Orderbook#

9.1. Synopsis#

ORDERBOOK(
  (<select_expression>),
  <time_expression>,
  ENGINE=<exchange_name>,
  PRODUCT='<product_name>',
  MODE={TOP | FULL | COLLAPSED},
  [MAX_LEVEL=<integer>]
  )

time_expression ::=
  { <timestamp> | <interval_expression> }

interval_expression ::=
  (<timestamp>, <timestamp>, <duration>)

9.2. Description#

ORDERBOOK will rebuild one or several order books based on market orders returned by the specified select expression.

The function can rebuild an accurate order book by replaying all orders in sequence. It can output either a collapsed order book (level 2), the full order book (level 3, provided the level 3 market data is available), or just the top of the book (best bid and best ask).

It is the responsibility of the select query to ensure that the data is ordered in the way events occurred in the market. When a sequence number is available for the orders, ordering the result by the sequence number is the most reliable way to achieve this.

9.3. Parameters#

select_expression: A select query that returns the orders from which the order book must be rebuilt. The order data must have data for the time range requested for the order book, and its order must match the execution sequence of the exchange. Additionally, each engine has mandatory columns needed for the proper reconstruction of the order book.
time_expression: Either a timestamp at which the order book should be rebuilt, or a time interval which specifies all order books which should be rebuilt. If a single timestamp is provided, the state of the order book at that time will be built. All order books within the range will be rebuilt if an interval is specified.
engine: Which engine the order book should use to rebuild orders. The engine specification is currently mandatory and the order book execution engine will not attempt to guess which engine matches the provided market data.
product: Which product the order book should be rebuilt for. The product’s market data must be present in the query specified as a first parameter.
mode: One of TOP, FULL, or COLLAPSED. Top means only the top of the book (best bid and ask) and is equivalent to specifying a MAX_LEVEL of 1. COLLAPSED means that orders of identical prices are merged into a single row. FULL gives the full detail of the order book, provided the input data is level 3 market data.
max_level: An optional level that specifies the number of levels to return, including the given level. 1 means the best bid and ask. 2 means the best, and second best, etc.

9.4. Exchange specific information#

9.4.1. Coinbase#

Orders are expected to be stored in two tables per instrument, one for each side.

The following columns are needed:

$timestamp, $table, type, reason, order_id, sequence, price, size, funds

9.4.2. Currenex#

Orders are expected to be stored in a single table.

The following columns are needed:

$timestamp, order_id, amount, price, event, side

9.4.3. Nasdaq#

Orders are expected to be stored in two tables per instrument, one for each side.

The following columns are needed:

$timestamp, $table, type, reference, original_reference, new_reference, shares, price

9.5. Examples#

Build the top of the book, as of now, for the pair BTC/USD, using Coinbase level 3 market data:

ORDERBOOK((SELECT * FROM "coinbase/btc_usd/orders_buy", "coinbase/btc_usd/orders_sell" ORDER BY sequence),
  now,
  engine=coinbase,
  product='btc_usd',
  mode=top)

Build the first 10 levels, as of now, for the pair ETH/USD, using Coinbase level 3 market data:

ORDERBOOK((SELECT * FROM "coinbase/eth_usd/orders_buy", "coinbase/eth_usd/orders_sell" ORDER BY sequence),
  now,
  engine=coinbase,
  product='eth_usd',
  mode=full,
  max_level=10)

Build the collapsed order book for the first 5 levels, for every minute, for the day of March 1st, 2017, for the pair ETH/USD, using Coinbase level 3 market data:

ORDERBOOK((SELECT * FROM "coinbase/eth_usd/orders_buy", "coinbase/eth_usd/orders_sell" ORDER BY sequence),
  (2017-03-01, +1day, 1min),
  engine=coinbase,
  product='eth_usd',
  mode=collapsed,
  max_level=5)