Live forex order book charts

Order book data for 12 currency pairs, streamed into QuestDB and queried live.

Stored in QuestDB·Rendered with Grafana
LiveHit Show query on any panel for the SQL behind it

Top of book and candlestick (OHLC) chart

Recent core prices matched to the order book with an ASOF JOIN to find the level each bid would fill at, and today's 15-minute candles of the best bid.

Top-of-book Price vs Core Price - EURUSD

WITH
  p AS (
    SELECT timestamp, bid_price, bid_volume, symbol, ecn
    FROM core_price
    WHERE dateadd('m', -1, now()) < timestamp AND symbol = 'EURUSD'
    LIMIT -6
  ),
  levels AS (
    SELECT
      market_data.timestamp AS md_timestamp,
      insertion_point(bids[2], bid_volume) AS level,
      bids[1][insertion_point(bids[2], bid_volume)] AS price,
      bids[2][insertion_point(bids[2], bid_volume)] AS volume,
      p.timestamp,
      p.bid_price,
      p.bid_volume AS bid_volume,
      p.ecn
    FROM p
    ASOF JOIN market_data ON symbol
  )
SELECT
  md_timestamp AS MarketData_Time,
  CASE WHEN price IS NULL THEN NULL ELSE level END AS Level,
  Price,
  Volume,
  timestamp AS CorePrice_Time,
  bid_price AS Bid_Price,
  bid_volume1 AS Bid_Volume,
  ecn AS ECN
FROM levels;
Run in live demo

OHLC - Bids - 15m - (EURUSD)

SELECT timestamp, open, high, low, close, total_volume
FROM market_data_ohlc_15m
WHERE date_trunc('day', now()) < timestamp
  AND symbol = 'EURUSD';
Run in live demo

Market depth and bid-ask volume

Cumulative volume across the top seven levels each side, and 30-second bid and ask volume over the past hour against the best bid and offer.

Market Depth - EURUSD

WITH
  snapshot AS (
    SELECT timestamp, bids, asks
    FROM market_data
    WHERE dateadd('m', -10, now()) < timestamp AND symbol = 'EURUSD'
    ORDER BY timestamp DESC
    LIMIT 1
  ),
  bid_levels AS (
    SELECT timestamp, 'bid' AS side, bids[1,1] AS price, bids[2,1] AS volume
    FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,2], bids[2,2] FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,3], bids[2,3] FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,4], bids[2,4] FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,5], bids[2,5] FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,6], bids[2,6] FROM snapshot
    UNION ALL SELECT timestamp, 'bid', bids[1,7], bids[2,7] FROM snapshot
  ),
  ask_levels AS (
    SELECT timestamp, 'ask' AS side, asks[1,1] AS price, asks[2,1] AS volume
    FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,2], asks[2,2] FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,3], asks[2,3] FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,4], asks[2,4] FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,5], asks[2,5] FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,6], asks[2,6] FROM snapshot
    UNION ALL SELECT timestamp, 'ask', asks[1,7], asks[2,7] FROM snapshot
  ),
  bid_cum AS (
    SELECT
      timestamp,
      side,
      price,
      volume,
      sum(volume) OVER (ORDER BY price DESC) AS cum_volume
    FROM bid_levels
    WHERE price IS NOT NULL
  ),
  ask_cum AS (
    SELECT
      timestamp,
      side,
      price,
      volume,
      sum(volume) OVER (ORDER BY price ASC) AS cum_volume
    FROM ask_levels
    WHERE price IS NOT NULL
  )
SELECT * FROM bid_cum
UNION ALL
SELECT * FROM ask_cum
ORDER BY side, price;
Run in live demo

Bid vs Ask Volume (30s) - BBO (1s) - (EURUSD)

SELECT
  timestamp time,
  symbol,
  sum(bids[1,1] * bids[2,1]) AS bid,
  -1 * sum(asks[1,1] * asks[2,1]) AS ask
FROM market_data
WHERE dateadd('h', -1, now()) < timestamp
  AND symbol IN 'EURUSD'
SAMPLE BY 30s;
Run in live demo
SELECT timestamp AS Time, Symbol, Bid, Ask, (bid + ask) / 2 AS Mid
FROM "bbo_1s"
WHERE dateadd('h', -1, now()) < timestamp
  AND symbol IN 'EURUSD';
Run in live demo

Bid-ask spread, VWAP, RSI and Bollinger Bands

Spread and bid and ask volume for each of the last six seconds, and today's candles overlaid with VWAP, a 12-hour RSI and Bollinger Bands (20, 2).

Spread and Volume per second - EURUSD 

SELECT
  timestamp AS Time,
  sum(asks[1,1] - bids[1,1]) AS Spread,
  sum(bids[1,1] * bids[2,1]) AS Bid_Volume,
  sum(asks[1,1] * asks[2,1]) AS Ask_Volume
FROM market_data
WHERE dateadd('m', -1, now()) < timestamp
  AND symbol = 'EURUSD'
SAMPLE BY 1s
ORDER BY timestamp DESC
LIMIT 6;
Run in live demo

VWAP - Relative Strength Index 12h - Bollinger Bands (20,2) (EURUSD)

WITH
  sampled AS (
    SELECT
      timestamp,
      symbol,
      total_volume,
      ((open + close) / 2) * total_volume AS traded_value
    FROM market_data_ohlc_1m
    WHERE date_trunc('day', now()) < timestamp
      AND symbol IN 'EURUSD'
  ),
  cumulative AS (
    SELECT
      timestamp,
      symbol,
      sum(traded_value) OVER (ORDER BY timestamp) AS cumulative_value,
      sum(total_volume) OVER (ORDER BY timestamp) AS cumulative_volume
    FROM sampled
  ),
  vwap AS (
    SELECT timestamp, cumulative_value / cumulative_volume AS vwap
    FROM cumulative
  )
SELECT * FROM vwap;
Run in live demo
SELECT timestamp, open, high, low, close, total_volume
FROM market_data_ohlc_15m
WHERE date_trunc('day', now()) < timestamp
  AND symbol = 'EURUSD';
Run in live demo
WITH
  price_changes AS (
    SELECT
      timestamp,
      symbol,
      bid,
      bid - prev_bid price_change
    FROM (
      SELECT
        timestamp,
        symbol,
        bid,
        lag(bid) OVER (PARTITION BY symbol ORDER BY timestamp) AS prev_bid
      FROM bbo_1h
      WHERE date_trunc('day', now()) < timestamp
        AND symbol = 'EURUSD'
    )
  ),
  gains_losses AS (
    SELECT
      timestamp,
      symbol,
      bid,
      CASE WHEN price_change > 0 THEN price_change ELSE 0 END AS gain,
      CASE WHEN price_change < 0 THEN abs(price_change) ELSE 0 END AS loss
    FROM price_changes
  ),
  avg_gains_losses AS (
    SELECT
      timestamp,
      symbol,
      bid,
      avg(gain) OVER (
        PARTITION BY symbol ORDER BY timestamp
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
      ) AS avg_gain,
      avg(loss) OVER (
        PARTITION BY symbol ORDER BY timestamp
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
      ) AS avg_loss
    FROM gains_losses
  )
SELECT
  timestamp,
  Bid,
  CASE
    WHEN avg_loss = 0 THEN 100
    ELSE 100 - (100 / (1 + (avg_gain / nullif(avg_loss, 0))))
  END AS RSI_12h
FROM avg_gains_losses
ORDER BY timestamp;
Run in live demo
WITH
  stats AS (
    SELECT
      timestamp,
      close,
      avg(close) OVER (
        ORDER BY timestamp
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
      ) AS sma20,
      avg(close * close) OVER (
        ORDER BY timestamp
        ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
      ) AS avg_close_sq
    FROM market_data_ohlc_15m -- 20 periods of 15 minutes = SMA 6h
    WHERE date_trunc('day', now()) < timestamp
      AND symbol IN 'EURUSD'
  ),
  bollinger AS (
    SELECT
      timestamp,
      sma20,
      -- sqrt(avg_close_sq - (sma20 * sma20)) as stdev20,
      sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band,
      sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band
    FROM stats
    ORDER BY timestamp
  )
SELECT * FROM bollinger;
Run in live demo

Build your own on QuestDB

Every panel on this page is a SQL query against QuestDB, rendered in Grafana. Run the queries yourself in the live demo, or use our Grafana tutorials to chart your own data.

Contact usDownload QuestDB