Technical Indicators — RSI, MACD, Bollinger Bands

Technical indicators are mathematical transformations of price and volume data used to generate trading signals. While fundamental analysts focus on company financials, technical analysts focus on price patterns and momentum. Three indicators dominate technical analysis: RSI (momentum), MACD (trend + momentum), and Bollinger Bands (volatility + mean reversion).

This lesson implements all three in SQL, explaining not just the code but the financial intuition behind each.

Relative Strength Index (RSI)

RSI measures the speed and magnitude of recent price changes to identify overbought or oversold conditions. It oscillates between 0 and 100:

RSI > 70: Overbought — the stock may have risen too fast and could pull back
RSI < 30: Oversold — the stock may have fallen too fast and could bounce
RSI 30-70: Neutral territory

Formula:

RS  = Average Gain / Average Loss (over N days, typically 14)
RSI = 100 - (100 / (1 + RS))

The RSI averages "up days" (positive returns) separately from "down days" (negative returns), then computes their ratio. A stock with mostly up days will have high RS and therefore high RSI.

WITH daily_returns AS (
    SELECT company_id, price_date, close_price,
        close_price - LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date) AS price_change
    FROM stock_prices
),
gains_losses AS (
    SELECT company_id, price_date, close_price, price_change,
        GREATEST(price_change, 0) AS gain,
        ABS(LEAST(price_change, 0)) AS loss
    FROM daily_returns WHERE price_change IS NOT NULL
),
avg_gl AS (
    SELECT company_id, price_date, close_price,
        AVG(gain) OVER (
            PARTITION BY company_id ORDER BY price_date
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
        ) AS avg_gain_14,
        AVG(loss) OVER (
            PARTITION BY company_id ORDER BY price_date
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
        ) AS avg_loss_14
    FROM gains_losses
),
rsi AS (
    SELECT company_id, price_date, close_price,
        ROUND(
            100.0 - (100.0 / (1 + COALESCE(NULLIF(avg_gain_14, 0), 0) / NULLIF(avg_loss_14, 0))),
        2) AS rsi_14
    FROM avg_gl
)
SELECT
    c.ticker, r.price_date, r.close_price, r.rsi_14,
    CASE
        WHEN r.rsi_14 >= 70 THEN 'Overbought - consider selling'
        WHEN r.rsi_14 <= 30 THEN 'Oversold - consider buying'
        ELSE 'Neutral'
    END AS rsi_signal
FROM rsi r
JOIN companies c ON c.company_id = r.company_id
ORDER BY c.ticker, r.price_date DESC;

What This Returns

rsi_14 for each stock on each day — the standard 14-period RSI. The rsi_signal column provides actionable context. A reading above 70 means the stock has moved up strongly in recent weeks; many traders treat this as a warning to take profits. Below 30 signals potential exhaustion of selling pressure.

Note on edge case handling: NULLIF(avg_loss_14, 0.0001) prevents division by zero when avg_loss is zero (all gains, no losses). Setting the denominator to a tiny number rather than NULL avoids a NULL RSI on very strong bull run days.

Purchase this course to unlock the full lesson.

← Previous Next →