Bid-ask spread

The bid-ask spread is the difference between the best ask (lowest sell price) and best bid (highest buy price). It represents the cost of immediately executing a round-trip trade and is a key measure of market liquidity.

Problem​

You want to measure market liquidity and transaction costs. Narrow spreads indicate liquid markets with low trading costs, while wide spreads suggest illiquidity or market stress.

Solution​

Calculate bid-ask spread metricsDemo this query
DECLARE
  @symbol := 'EURUSD',
  @lookback := '$now - 1h..$now'

SELECT
  timestamp,
  symbol,
  round(bid_price, 5) AS bid,
  round(ask_price, 5) AS ask,
  round(ask_price - bid_price, 6) AS spread_absolute,
  round((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000, 2) AS spread_bps,
  round((bid_price + ask_price) / 2, 5) AS mid_price
FROM core_price
WHERE symbol = @symbol
  AND timestamp IN @lookback
ORDER BY timestamp;

The query calculates:

  • Absolute spread: ask - bid
  • Spread in basis points: spread / mid_price × 10,000
  • Mid price: (bid + ask) / 2

Aggregated spread analysis​

Average spread by time periodDemo this query
DECLARE
  @symbol := 'EURUSD',
  @lookback := '$now - 1d..$now'

SELECT
  timestamp,
  symbol,
  round(avg((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS avg_spread_bps,
  round(min((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS min_spread_bps,
  round(max((ask_price - bid_price) / ((bid_price + ask_price) / 2) * 10000), 2) AS max_spread_bps,
  count() AS quote_count
FROM core_price
WHERE symbol = @symbol
  AND timestamp IN @lookback
SAMPLE BY 1h
ORDER BY timestamp;

Interpreting results​

  • Tight spread: Highly liquid market conditions and lower immediate transaction costs
  • Wide spread: Illiquid or volatile period, higher transaction costs
  • Spread widening: Often precedes or accompanies volatility
  • Intraday patterns: Spreads typically widen during off-hours and narrow during active sessions
Spread conventions (venue-dependent)

Typical spreads vary a lot by venue, instrument, and session:

  • FX majors: often around sub-pip to a few pips in liquid hours (roughly sub-1 to several bps)
  • FX minors/emerging pairs: typically wider than majors
  • Crypto: can range from tight to very wide depending on pair and exchange
  • Equities: often quoted in ticks/cents rather than bps

Treat these as rough guidelines, not fixed thresholds.

Related documentation