The MACD (**M**oving **A**verage **C**onverenge **D**ivergence) 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.

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:

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.)

Pingback: Exponential moving average in T-SQL | Tomas LindTomas Lind