SQL does not come with a lot of analytical functions, and if a user wants to perform analytical calculations in the database one has to write a large number of stored procedures or functions. This is a simple way to create an exponential moving average, that can be used for a number of purposes. This implementation focuses on market data.
Exponential Moving Averages – What is it?
An Exponential Moving Averages commonly referred to as EMA or sometimes the EWMA, Exponentially Weighted Moving Average, is a moving average where the weights of the most recent data point are more important than a distant data point. In a simple moving average, all observations have the same weight. EMA is popular in financial applications where it can be noted that Risk Metrics is using a set of EMA in order to calculate ‘value-at-risk’.
It also is rather common in technical analysis applications as it is perceived to be less lagging than ordinary moving averages. Compared to the simple moving average (SMA), an EMA also has one parameter. While the SMA has the lookback window as the single parameter, an EMA has a smoothing factor as the main parameter. The smoothing factor can be easily converted into a ‘half-life’ factor that refers to the number of days.
The formula for EMA is especially efficient for computational calculations since it can be completed using the prior EMA value and the new information. Here the framework is financial data, where we are calculating the EMA on a series consisting of closing prices.
Sample Implementation using Cursors
Below is a code sample used for implementing the EMA in T-SQL, using SQL Server 2008, but it should be compatible with prior versions of SQL Server as well. Please note that the function depends on the Length parameter that is equal to the half-life. This is converted in the smoothing factor in the first line. While the implementation uses cursors, one could also implement this is a cleaner way using inner joins. However, for large datasets, those are likely to both time-consuming and memory intensive.
SET @smoothing= (2/(@length +1))
SET @signalname = @signalname + convert(varchar(15), convert(int, @length))
SET @cursor = CURSOR FOR SELECT A.[date], A.[close] FROM dbo.[Market-data] A
WHERE A.market =@market and A.date between @Startdate and @Enddate
ORDER BY A.date OPEN @cursor
FETCH NEXT From @cursor INTO @date, @close
WHILE @@FETCH_STATUS = 0
BEGIN
IF @expaverage is null
SET @expaverage = @close
ELSE
SET @expaverage = @expaverage + @smoothing * ( @close - @expaverage)
INSERT INTO @Result (date, [close], avg, signal)
SELECT @date, @close, @expaverage, @signalname
FETCH NEXT From @cursor INTO @date, @close