9. Orderbook#

9.1. Synopsis#

ORDERBOOK(
  (<select_expression>),
  <time_expression>,

  [ OFFSETS=(<duration> [, ...]), ]

  ENGINE=<exchange_name>,

  PRODUCT='<product_name>',

  MODE={TOP | FULL | COLLAPSED},

  [ { MAX_LEVEL=<integer> | LEVEL_ABS_DELTA=<float> | LEVEL_REL_DELTA=<float> } ]

  )

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 sequentially. 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 occur 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 to reconstruct the order book properly.

time_expression

Either a timestamp at which the order book should be rebuilt, a list of timestamps, or a time interval specifying all order books that should be rebuilt. If a timestamp, or list of timestamps is provided, the state of the order book at that time (or each time) will be built. All order books within the range will be rebuilt with the provided interval (duration) if an interval is specified.

engine

Which engine the order book should use to rebuild orders. The engine specification is 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.

9.4. Controlling the levels#

When working on the full order book, it is commonly wanted not to display the full depth of the order book. There are three ways to control the depth:

  • By level: MAX_LEVEL followed by a strictly positive integer. Only the levels to this value (included) will be displayed. Thus, MAX_LEVEL=1 is equivalent to MODE=TOP.

  • By absolute value: LEVEL_ABS_DELTA followed by a strictly positive floating value. Only the values whose prices are of a difference no greater than the given delta will be displayed. Example: A value of 10.0, with a middle price of 100.0, means no price deviating by more than 10.0 from the middle will be shown. In our example, levels with prices between 90.0 and 110.0 (inclusive) will be shown.

  • By relative value: LEVEL_REV_DELTA followed by a strictly positive floating value. Only the values whose prices are of a difference no greater than the given ratio will be displayed. A value of 2.0, with a middle price of 100.0, means no price deviating by more than double, and half of the middle will be shown. In our example, levels with prices between 50.0 and 200.0 (inclusive) will be shown.

When no level maximum is displayed, every available price is shown.

9.5. Building points with offsets#

Sometimes you want to inspect specific points in time, with offsets around these points. The query provide a flexible way to build those points with the optional OFFSETS parameter.

The parameter takes a list of positive and negative time offsets, for example:

ORDERBOOK((SELECT $timestamp, order_id, price, amount, account_id, event, side FROM "currenex/otr2y"),
  (2021-01-01T10:00, 2021-01-02T13:00),
  OFFSETS=(-1h, +1h),
  ENGINE=currenex,
  product='otr.2y',
  MODE=COLLAPSED);

Will generate orderbooks for the following points in time:

  • January 1st, 2021 at 09:00 UTC

  • January 1st, 2021 at 10:00 UTC

  • January 1st, 2021 at 11:00 UTC

  • January 2nd, 2021 at 12:00 UTC

  • January 2nd, 2021 at 13:00 UTC

  • January 2nd, 2021 at 14:00 UTC

9.6. Exchange specific information#

9.6.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.6.2. Currenex#

Orders are expected to be stored in a single table.

The following columns are needed:

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

In case the account ID must be displayed as well, here is how it should be included:

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

9.6.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.7. 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)

Build the full order book for every level on the day of January 1st 2021, for Currenex data, for 2 years treasuries, including the account ID:

ORDERBOOK((SELECT $timestamp, order_id, price, amount, account_id, event, side FROM "currenex/otr2y"),
  2021-01-01,
  ENGINE=currenex,
  product='otr.2y',
  MODE=FULL);

Build the top of the book every hour, between 09:00 and 17:00 UTC, for January 1st 2021, for Currenex data, for 2 years treasuries, including the account ID:

ORDERBOOK((SELECT $timestamp, order_id, price, amount, account_id, event, side FROM "currenex/otr2y"),
  (2021-01-01T00:09:00, 2021-01-02T17:00:00, 1h),
  ENGINE=currenex,
  product='otr.2y',
  MODE=TOP);

Build a collapsed order book, showing only the first two levels, for April 2nd 2024 at 23:00 UTC, April 3rd 2024 at 00:00 UTC, and April 3rd 2024 at 02:00 UTC, for Currenex data, for 2 years treasuries, including the account ID:

ORDERBOOK ((SELECT $timestamp, account_id, order_id, price, amount, event, side FROM "currenex/otr2y"),
  (2023-04-02T23:00:00, 2023-04-03, 2023-04-03T01:00:00, 2023-04-03T02:00:00),
  engine=currenex,
  product='otr.2y',
  MODE=COLLAPSED,
  MAX_LEVEL=1);