MACD in T-SQL

The MACD (Moving Average Converenge Divergence) is a calculation in technical analysis used as a indicator of strength in a trend, or momentum in a stocks closing prices. The calculation uses different lengths of EMA (I covered the calculation of EMA in an earlier blog post here).

The MACD calculation uses the difference between a long EMA and a short EMA to create a oscillator (usually EMA12 – EMA26). The term Moving Average Converenge Divergence comes from the converging and divergence of these two moving averages. When the two moves towards each other there is convergence, and when they move away from each other, there is divergence. A divergence is commonly interpreted as a sign that the current trend is ending.

The MACD also moves around a zero line and when the MACD is above the zero line, that is used as a indicator of upward momentum (higher closing prices) since the short term EMA is above the long term EMA. And of course, if MACD is below the zero line this is an indicator of downward momentum.

Image by Kevin Ryde. Green line is MACD, red line is EMA9 of MACD.
White histogram is the difference between the two.

Further, a EMA9 is calculated for the MACD. This line is called the “Signal line”. The signal line is used in a trigger for buy and sell signals. More specifically, traders look for crossovers of the two lines. When the MACD moves over the MACD:EMA9, this is a buy signal since it indicates a upward momentum (a bullish market) of the closing prices. And when the MACD crosses under the MACD:EMA9, this is a sell signal since this is a indicator of downward momentum (bearish market).

This blog post will show how to calculate MACD in T-SQL. It works on all versions of SQL Server.

The examples use the database TAdb. A script to create TAdb can be found here.

Using a extended version of the EMA calculation, the T-SQL below calculates EMA12, EMA26 and MACD:

IF OBJECT_ID('tempdb..#TBL_EMA_LOOP') IS NOT NULL BEGIN
	DROP TABLE #TBL_EMA_LOOP
END

SELECT *, CAST(NULL AS FLOAT) AS EMA12, CAST(NULL AS FLOAT) AS EMA26 INTO #TBL_EMA_LOOP FROM dbo.Quotes

CREATE UNIQUE CLUSTERED INDEX EMA_IDX ON #TBL_EMA_LOOP (StockId, QuoteId)

DECLARE @StockId INT = 1, @QuoteId INT, @QuoteIdMax INT, @StartAvgEMA12 FLOAT, @StartAvgEMA26 FLOAT, @C_EMA12 FLOAT = 2.0 / (1 + 12), @C_EMA26 FLOAT = 2.0 / (1 + 26), @EMA12 FLOAT, @EMA26 FLOAT

WHILE @StockId <= 2 BEGIN
	SELECT @QuoteId = 1, @QuoteIdMax = MAX(QuoteId) FROM dbo.Quotes WHERE StockId = @StockId
	SELECT @StartAvgEMA12 = AVG(QuoteClose) FROM dbo.Quotes WHERE StockId = @StockId AND QuoteId <= 12
	SELECT @StartAvgEMA26 = AVG(QuoteClose) FROM dbo.Quotes WHERE StockId = @StockId AND QuoteId <= 26

	WHILE @QuoteId <= @QuoteIdMax BEGIN
		UPDATE
			T0
		SET
			EMA12 =
				CASE
					WHEN @QuoteId = 12 THEN @StartAvgEMA12
					WHEN @QuoteId > 12 THEN (T0.QuoteClose * @C_EMA12) + T1.EMA12 * (1.0 - @C_EMA12)
				END
			,EMA26 =
				CASE
					WHEN @QuoteId = 26 THEN @StartAvgEMA26
					WHEN @QuoteId > 26 THEN (T0.QuoteClose * @C_EMA26) + T1.EMA26 * (1.0 - @C_EMA26)
				END
		FROM
			#TBL_EMA_LOOP T0
		JOIN
			#TBL_EMA_LOOP T1
		ON
			T0.StockId = T1.StockId
		AND
			T0.QuoteId - 1 = T1.QuoteId
		WHERE
			T0.StockId = @StockId
		AND
			T0.QuoteId = @QuoteId

		SELECT @QuoteId = @QuoteId + 1
	END

	SELECT @StockId = @StockId + 1
END

SELECT
	StockId
	,QuoteId
	,QuoteDay
	,QuoteClose
	,CAST(EMA12 AS NUMERIC(10,2)) AS EMA12
	,CAST(EMA26 AS NUMERIC(10,2)) AS EMA26
	,CAST(EMA12 - EMA26 AS NUMERIC(10,2)) AS MACD
FROM
	#TBL_EMA_LOOP

A sample of the results look like this:

MACD Results

In this example, following the theory stated above, the stock is in a negative momentum (bearish market) up until 2010-03-03 when a crossover occurs and the stock enters a positive momentum (bullish market). And indeed, the stock price rises between 2010-03-03 and 2010-03-29. (If you do the query yourself you will see that the positive momentum lasts up until 2010-05-20 ending at a closing price of 31.78.)