Bollinger bands - Questdb

Calculate Bollinger Bands for volatility analysis and mean reversion trading. Bollinger Bands consist of a moving average with upper and lower bands set at a specified number of standard deviations above and below it. They help identify overbought/oversold conditions and measure market volatility.

:::tip Measuring band width To objectively measure when bands are contracting (a "squeeze"), see the Bollinger BandWidth recipe which calculates band width as a percentage and compares it to historical levels. :::

:::note Bollinger Bands can be calculated using either population standard deviation (stddev) or sample standard deviation (stddev_samp), producing slightly different results. This recipe uses stddev. :::

Solution: Calculate variance using window functions

Since standard deviation is the square root of variance, and variance is the average of squared differences from the mean, we can calculate everything in SQL using window functions. This query will compute Bollinger Bands with a 20-period simple moving average (SMA) and bands at ±2 standard deviations:

questdb-sql

WITH OHLC AS (
  SELECT
    timestamp, symbol,
      first(price) AS open,
      max(price) as high,
      min(price) as low,
      last(price) AS close,
      sum(quantity) AS volume
 FROM fx_trades
 WHERE symbol = 'EURUSD' AND timestamp IN '$yesterday'
 SAMPLE BY 15m
), stats AS (
  SELECT
    timestamp,
    close,
    AVG(close) OVER w AS sma20,
    AVG(close * close) OVER w AS avg_close_sq
  FROM OHLC
  WINDOW w AS (ORDER BY timestamp ROWS 19 PRECEDING)
)
SELECT
  timestamp,
  close,
  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;

This query:

Aggregates trades into 15-minute OHLC candles
Calculates a 20-period simple moving average of closing prices
Calculates the average of squared closing prices over the same 20-period window
Computes standard deviation using the mathematical identity: σ = √(E[X²] - E[X]²)
Adds/subtracts 2× standard deviation to create upper and lower bands

How it works

The core of the Bollinger Bands calculation is the rolling standard deviation. Please check our rolling standard deviation recipe in the cookbook for an explanation about the mathematical formula.

Adapting the parameters

Different period lengths:

sql

-- 10-period Bollinger Bands (change ROWS 19 to ROWS 9)
AVG(close) OVER w AS sma10,
AVG(close * close) OVER w AS avg_close_sq
...
WINDOW w AS (ORDER BY timestamp ROWS 9 PRECEDING)

Different band multipliers:

sql

-- 1 standard deviation bands (tighter)
sma20 + 1 * sqrt(avg_close_sq - (sma20 * sma20)) as upper_band,
sma20 - 1 * sqrt(avg_close_sq - (sma20 * sma20)) as lower_band

-- 3 standard deviation bands (wider)
sma20 + 3 * sqrt(avg_close_sq - (sma20 * sma20)) as upper_band,
sma20 - 3 * sqrt(avg_close_sq - (sma20 * sma20)) as lower_band

Different time intervals:

sql

-- 5-minute candles
SAMPLE BY 5m

-- 1-hour candles
SAMPLE BY 1h

Multiple symbols:

questdb-sql

WITH OHLC AS (
  SELECT
    timestamp, symbol,
      first(price) AS open,
      last(price) AS close,
      sum(quantity) AS volume
 FROM fx_trades
 WHERE symbol IN ('EURUSD', 'GBPUSD')
   AND timestamp IN '$yesterday'
 SAMPLE BY 15m
), stats AS (
  SELECT
    timestamp,
    symbol,
    close,
    AVG(close) OVER w AS sma20,
    AVG(close * close) OVER w AS avg_close_sq
  FROM OHLC
  WINDOW w AS (PARTITION BY symbol ORDER BY timestamp ROWS 19 PRECEDING)
)
SELECT
  timestamp,
  symbol,
  close,
  sma20,
  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 symbol, timestamp;

Note the addition of PARTITION BY symbol to calculate separate Bollinger Bands for each symbol.

:::info Related documentation