Published on

February 1, 2010

Calculating Moving Averages with SQL Server

In the world of financial markets, moving averages are a popular tool used in technical analysis. They help identify trends and provide insights into the overall direction of a stock or market. In this article, we will explore how to efficiently calculate moving averages using T-SQL in SQL Server.

Simple Moving Average (SMA)

The simple moving average (SMA) is a commonly used moving average in financial markets. It calculates the average price of a stock or market over a specific period of time. For example, a 20-day SMA calculates the average price of the last 20 trading days.

To calculate the SMA efficiently, we can use a join with a group by clause. This allows us to filter the data and calculate the average for each day. Here is an example:

SELECT a.quote_date, a.close_price, AVG(b.close_price) AS [20_day_sma]
FROM #google_stock a
JOIN #google_stock b ON a.quote_date >= b.quote_date AND b.quote_date >= DATEADD(dd,-20,a.quote_date)
GROUP BY a.quote_date, a.close_price
ORDER BY a.quote_date

This query joins the #google_stock table with itself, filtering the data to include only the previous 20 trading days. It then calculates the average close price for each day.

However, this solution has a limitation. It includes weekends and holidays in the calculation, which may result in fewer than 20 trading days. To address this, we can add a number column to the table and use it to filter the data. Here is the modified code:

SELECT a.quote_date, a.close_price, 
    CAST(AVG(CASE WHEN a.n < 20 THEN NULL ELSE b.close_price END) AS decimal(8,2)) AS [20_day_sma]
FROM #mod_goog_data a
JOIN #mod_goog_data b ON b.n <= a.n AND b.n > a.n - 20
GROUP BY a.quote_date, a.close_price
ORDER BY a.quote_date

This modified query filters the data using the row number field, ensuring that only the previous 20 trading days are included in the calculation. It also handles cases where there are fewer than 20 records prior to the current row.

Exponential Moving Average (EMA)

The exponential moving average (EMA) is another commonly used moving average in financial markets. It puts more weight on recent data and less on older data. The EMA can be calculated using the previous day’s EMA and the current close price.

To calculate the EMA efficiently, we can use a similar strategy as the SMA. We first calculate the initial averages for the specified periods (e.g., 12-day EMA, 26-day EMA). Then, we use a carryover update statement to calculate the EMA for each day. Here is an example:

DECLARE @ema_1_intervals INT, @ema_2_intervals INT, @K1 DECIMAL(4,3), @K2 DECIMAL(4,3)
DECLARE @prev_ema_1 DECIMAL(8,2), @prev_ema_2 DECIMAL(8,2), @initial_sma_1 DECIMAL(8,2), @initial_sma_2 DECIMAL(8,2)

SET @ema_1_intervals = 12
SET @ema_2_intervals = 26
SET @K1 = 2 / (1 + @ema_1_intervals + .000)
SET @K2 = 2 / (1 + @ema_2_intervals + .000)

SELECT @initial_sma_1 = AVG(CASE WHEN n < @ema_1_intervals THEN close_price ELSE NULL END),
       @initial_sma_2 = AVG(CASE WHEN n < @ema_2_intervals THEN close_price ELSE NULL END)
FROM #mod_goog_data
WHERE n < @ema_1_intervals OR n < @ema_2_intervals

UPDATE t1
SET @prev_ema_1 = CASE
        WHEN n < @ema_1_intervals THEN NULL
        WHEN n = @ema_1_intervals THEN t1.close_price * @K1 + @initial_sma_1 * (1 - @K1)
        WHEN n > @ema_1_intervals THEN t1.close_price * @K1 + @prev_ema_1 * (1 - @K1)
    END,
    @prev_ema_2 = CASE
        WHEN n < @ema_2_intervals THEN NULL
        WHEN n = @ema_2_intervals THEN t1.close_price * @K2 + @initial_sma_2 * (1 - @K2)
        WHEN n > @ema_2_intervals THEN t1.close_price * @K2 + @prev_ema_2 * (1 - @K2)
    END
FROM #mod_goog_data t1
OPTION (MAXDOP 1)

SELECT quote_date, close_price, ema12, ema26, ema12 - ema26 AS macd
FROM #mod_goog_data

This code calculates the 12-day and 26-day EMA for each day, as well as the MACD (the difference between the two EMAs). It uses a carryover update statement to calculate the EMA values based on the previous day’s EMA and the current close price.

With these efficient solutions, you can easily calculate moving averages with financial market data in SQL Server. Whether you need to analyze stock prices, monthly income, or power consumption, these techniques will help you identify trends and make informed decisions.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.