How to Calculate Technical Indicators in BigQuery using SQL — Moving Averages, RSI, MACD

Technical indicators are essential tools used by traders and analysts to gain insights into price trends, momentum, and potential reversal points in financial markets. These indicators are often calculated using historical price data and can provide valuable information for predicting stock or cryptocurrency prices and identifying overbought or oversold situations. In this blog post, I will explain how to calculate popular technical indicators in BigQuery using SQL and discuss their significance in stock market analysis.

Understanding Technical Indicators

Moving Averages (MA)

Moving Averages are one of the most commonly used indicators in financial analysis. They smooth out price data and help identify trends over a specific period. The most common moving averages are:

• MA(7): 7-day simple moving average.
• MA(30): 30-day simple moving average.
• MA(100): 100-day simple moving average.
• MA(200): 200-day simple moving average.

The Moving Average is calculated by taking the average closing price of a stock over the specified number of days.

Relative Strength Index (RSI)

The Relative Strength Index (RSI) is a momentum oscillator that measures the speed and change of price movements. It oscillates between 0 and 100 and is typically used to identify overbought and oversold conditions in a stock. RSI(24) is calculated as follows:

RSI(24) = 100 — (100 / (1 + (Average Gain / Average Loss)))

Where:

• Average Gain = (Sum of gains over 24 days) / 24
• Average Loss = (Sum of losses over 24 days) / 24
• Gain = (Closing price — Opening price) if the price increased, else 0
• Loss = (Opening price — Closing price) if the price decreased, else 0

Moving Average Convergence Divergence (MACD)

MACD is a trend-following momentum indicator that shows the relationship between two moving averages of a stock’s price. It consists of the following components:

• MACD Line: The 12-day exponential moving average (EMA) minus the 26-day EMA.
• Signal Line: A 9-day EMA of the MACD Line.
• Histogram: The MACD Line minus the Signal Line.

MACD is used to identify trend changes and potential buy/sell signals.

Bollinger Bands

Bollinger Bands consist of a middle band (MA) and two outer bands that are standard deviations away from the middle band. They expand and contract based on market volatility. Bollinger Bands can help identify overbought and oversold conditions and potential price breakouts.

Importance of Technical Indicators in Predicting Stock Prices

Technical indicators play a crucial role in stock market analysis and prediction. Here are some key reasons why they are important:

1. Trend Identification: Moving Averages help identify the direction of the prevailing trend, whether it’s bullish, bearish, or sideways.
2. Momentum Assessment: Indicators like RSI and MACD provide insights into the strength of the price momentum and potential trend reversals.
3. Support and Resistance Levels: Moving Averages and Bollinger Bands act as dynamic support and resistance levels, indicating potential turning points in the market.
4. Entry and Exit Signals: Traders use technical indicators to determine optimal entry and exit points for their trades.
5. Risk Management: Technical indicators aid in risk assessment by providing information on volatility and potential price swings.
6. Confirmation of Fundamental Analysis: Technical analysis complements fundamental analysis by validating or challenging the fundamental insights.
7. Timeframe Flexibility: Technical indicators can be used across various timeframes, making them versatile tools for both short-term and long-term traders.

Calculating Technical Indicators in BigQuery Using SQL

To calculate technical indicators in BigQuery, we use the following SQL query to create a view named dataset.daily_indicators. The view calculates moving averages (MA), RSI, MACD, and Bollinger Bands based on historical stock price data stored in the price_history_daily table.

create or replace view dataset.daily_indicators as
WITH prices AS (
  SELECT 
    stock,
    date,
    opening_price,
    high_price,
    low_price,
    closing_price,
    volume,
    AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
    AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma30,
    AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS ma100,
    AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma200,
    SUM(CASE WHEN closing_price > opening_price THEN closing_price - opening_price ELSE 0 END) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS gain,
    SUM(CASE WHEN opening_price > closing_price THEN opening_price - closing_price ELSE NULL END) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS loss,
    MAX(high_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS highest,
    MIN(low_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) AS lowest,
    (AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) - AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) AS macd
  FROM price_history_daily
),
indicators AS (
  SELECT 
    stock,
    closing_price,
    volume,
    date,
    ma7,
    ma30,
    ma100,
    ma200,
    (100 - (100 / (1 + (gain / loss)))) AS rsi,
    macd,
    (CASE WHEN STDDEV_POP(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW) = 0 THEN NULL ELSE ((closing_price - AVG(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) / STDDEV_POP(closing_price) OVER (PARTITION BY stock ORDER BY date ROWS BETWEEN 23 PRECEDING AND CURRENT ROW)) END) AS bollinger_band
  FROM prices
)
SELECT 
  stock,
  closing_price,
  volume,
  date,
  ma7,
  ma30,
  ma100,
  ma200,
  rsi,
  macd,
  bollinger_band,
  ABS(ma200 - closing_price) ma200_reach,
  ABS(ma100 - closing_price) ma100_reach,
  ABS(ma30 - closing_price) ma30_reach,
  ABS(ma7 - closing_price) ma7_reach,
FROM indicators

The prices Common Table Expression (CTE) calculates the moving averages, while the indicators CTE calculates RSI, MACD, and Bollinger Bands. The final SELECT statement combines all the calculated indicators and additional metrics for further analysis.

By utilizing this SQL query in BigQuery, analysts can efficiently calculate technical indicators for multiple stocks and use the resulting data to make informed trading decisions.

Hope you find this sql helpful and please let me your thoughts in the comments.