Blog

In an earlier post I demonstrated the calculation of moving averages in T-SQL. However, the there is one major drawback with simple moving averages. Price changes in the beginning of the period have the same importance as more recent price changes. Somehow you’d like to assign different weights to the price changes, so that the most recent changes gets the most weight.

For this purpose, a Weighted Moving Average (WMA) can be calculated. In this blog post I will show two different methods to calculate WMA, one that can be used on SQL Server 2005 and later, and the other version is for SQL Server versions earlier than 2005.

To calculate the relative weight of each price change, we need to know the position of each price change relative to the calculated day. Because of this, a window function cannot be used. It is not possible to get information from the individual rows in the window.

In the example below we will calculate weighted moving average for 9 days (WMA9). The example uses the TAdb. A script to create TAdb can be found here.

Regardless of the SQL Server version, for each row we need to access the 8 previous rows, 9 with the current row included. Those 9 rows will be the window that contains our price changes. Each row in that window will be assigned a linear weight that is increasing with the same amount for each row up until the current row.

The weight for each row will be calculated by using the window row position relative to the current row. Let’s say we want to calculate WMA for the 9:th row (quotes from TAdb StockId = 1):

1 * 30,02 = 30,02
2 * 30,33 = 60,66
3 * 30,33 = 90,99
4 * 30,44 = 121,76
5 * 30,24 = 151,20
6 * 30,27 = 181,62
7 * 29,87 = 209,09
8 * 30,00 = 240,00
9 * 30,02 = 270,18

The sum of the above is 1355,52. This is then divided by the sum of the weights, that is 1+2+3+4+5+6+7+8+9 = 45. The WMA9 for row 9 is 1355,52 / 45 = 30,12.

If you want to calculate WMA other than for 9 days, use the following T-SQL (get the GetNums2 function here) to get the divisor for the period length (for instance 45 for 9 days WMA):

SELECT I, SUM(I) OVER (ORDER BY I) FROM dbo.GetNums2 (200)

Weighted Moving Average SQL Server 2005 and later

This version uses a CTE to calculate the WMA:

;WITH CTE_QUOTES AS
	(
		SELECT
			StockId
			,QuoteId
			,QuoteDay
			,QuoteClose
		FROM
			dbo.Quotes
	)
SELECT
	T0.StockId
	,T0.QuoteId
	,T0.QuoteDay
	,T0.QuoteClose
	,CASE WHEN T0.QuoteId >= 9 THEN SUM((WMA9.QuoteId - T0.QuoteId + 9.0) * WMA9.QuoteClose) / 45.0 END AS WMA9
FROM
	CTE_QUOTES AS T0
LEFT OUTER JOIN
	CTE_QUOTES AS WMA9
ON
	T0.StockId = WMA9.StockId
AND
	WMA9.QuoteId <= T0.QuoteId AND 	WMA9.QuoteId >= T0.QuoteId - 9
GROUP BY
	T0.StockId
	,T0.QuoteId
	,T0.QuoteDay
	,T0.QuoteClose

In the results above, you can see the WMA9 for row 9 is 30,12, as calculated previously.

Weighted Moving Average before SQL Server 2005

The only difference between the SQL Server 2005 version and this one is the use of a common table expression. The pre 2005 version uses actual tables instead of CTE’s:

SELECT
	T0.StockId
	,T0.QuoteId
	,T0.QuoteDay
	,T0.QuoteClose
	,CASE WHEN T0.QuoteId >= 9 THEN SUM((WMA9.QuoteId - T0.QuoteId + 9.0) * WMA9.QuoteClose) / 45.0 END AS WMA9
FROM
	dbo.Quotes AS T0
LEFT OUTER JOIN
	dbo.Quotes AS WMA9
ON
	T0.StockId = WMA9.StockId
AND
	WMA9.QuoteId <= T0.QuoteId
AND
	WMA9.QuoteId >= T0.QuoteId - 8
group by
	T0.StockId
	,T0.QuoteId
	,T0.QuoteDay
	,T0.QuoteClose

Performance

When calculating a simple moving average and using SQL Server 2012 or later, a huge improvment in performance can be seen when using window functions compared to the alternative metohods used in older versions of SQL Server. Calculations of weighted moving averages however, can’t use windowing functions in the same way.

A comparison between the SQL Server 2005 version of WMA shows a small improvement over the versions used in older versions of SQL Server:

Because of the costly calculations involved in WMA, it might be a good idea to persist the results.

Usage

WMA are used in the same way as SMA, in trend analysis. WMA has more weight on recent price changes however.

[blue_box]This blog post is part of a serie about technical analysis, TA, in SQL Server. See the other posts here.[/blue_box]