Relative Strength Index RSI in T-SQL

The relative strength index (RSI) is a calculation in TA (Technical Analysis) which measures the strength in the direction of the momentum of a stock. It compares losses to gains in closing prices under a decided time period. The measure varies between 0 and 100. 100 means there are only gains in closing prices, and 0 means there are only losses.

In theory, a stock that is rises above a certain RSI threshold, normally 70, can be considered overbought and may be a selling candidate. That is because a reversal can be expected sooner or later when a stock is overbought. Conversely, a stock that sinks below 30 in RSI can be considered oversold and may be a candidate for buying.

This blog post will show how to calculate Relative Strength Index RSI in T-SQL. It will use a time period of 14 periods. The calculation works on all versions of SQL Server.

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

The RSI indicator was developed by J. Welles Wilder in is book New Concepts in Technical Trading Systems from 1978.

The version of RSI outlined here is the same as can be found on StockCharts.com. The RSI calculation uses the average of the period gains versus the periods losses. The formula for the calculation of RSI is pretty simple:

RSI=100-(100/(1+RS))

Where RS=AvgGain/AvgLoss.

The calculation of AvgGain and AvgLoss for the first 14 periods (the seed) is a simple average:

First calculation of RSI

AvgGain14=Sum of Gains for the last 14 periods / 14
AvgLoss14=Sum of Losses for the last 14 periods / 14

The calculations following the first uses the previous value and the current gain or loss:

Subsequent calculation of RSI

AvgGain14=(Previous AvgGain14 * 13 + Current Gain) / 14
AvgLoss14=(Previous AvgLoss14 * 13 + Current Loss) / 14

Using this method, the RSI calculation gets an smoothing effect, similar to the one used in the calculation of EMA.

The complete T-SQL for the calculation is shown below. Note that the calculation, for performance reasons, uses the “running total” feature of variables which is undocumented. Check the article by Jeff Moden Solving the Running Total and Ordinal Rank Problems.

IF OBJECT_ID('tempdb..#TBL_RSI') IS NOT NULL BEGIN
    DROP TABLE #TBL_RSI
END
 
SELECT
	T0.*
	,T0.QuoteClose - T1.QuoteClose AS Gain
	,CAST(NULL AS FLOAT) AS AvgGain
	,CAST(NULL AS FLOAT) AS AvgLoss
INTO
	#TBL_RSI
FROM
	dbo.Quotes T0
LEFT OUTER JOIN
	dbo.Quotes T1
ON
	T0.StockId = T1.StockId
AND
	T0.QuoteId -1 = T1.QuoteId

CREATE UNIQUE CLUSTERED INDEX EMA9_IDX_RT ON #TBL_RSI (StockId, QuoteId)
 
IF OBJECT_ID('tempdb..#TBL_START_SUM') IS NOT NULL BEGIN
    DROP TABLE #TBL_START_SUM
END

SELECT StockId, SUM(CASE WHEN Gain >= 0 THEN Gain ELSE 0 END) AS Start_Gain_Sum, SUM(CASE WHEN Gain < 0 THEN ABS(Gain) ELSE 0 END) AS Start_Loss_Sum INTO #TBL_START_SUM FROM #TBL_RSI WHERE QuoteId <= 14 GROUP BY StockId

DECLARE @AvgGain FLOAT, @AvgLoss FLOAT

UPDATE
    T1
SET
    @AvgGain =
        CASE
            WHEN QuoteId = 14 THEN T2.Start_Gain_Sum
			WHEN QuoteId > 14 THEN @AvgGain * 13 + CASE WHEN Gain >= 0 THEN Gain ELSE 0 END
        END / 14
    ,AvgGain = @AvgGain
    ,@AvgLoss =
        CASE
            WHEN QuoteId = 14 THEN T2.Start_Loss_Sum
			WHEN QuoteId > 14 THEN @AvgLoss * 13 + CASE WHEN Gain < 0 THEN ABS(Gain) ELSE 0 END
        END / 14
    ,AvgLoss = @AvgLoss
FROM
    #TBL_RSI T1
JOIN
    #TBL_START_SUM T2
ON
    T1.StockId = T2.StockId
OPTION (MAXDOP 1)

SELECT
	StockId
	,QuoteId
	,QuoteDay
	,QuoteClose
	,Gain
	,CAST(AvgGain AS NUMERIC(10,2)) AS AvgGain
	,CAST(AvgLoss AS NUMERIC(10,2)) AS AvgLoss
	,CAST(AvgGain / AvgLoss AS NUMERIC(10,2)) AS RS
	,CAST(100 - (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2)) AS RSI
FROM
	#TBL_RSI

As you can see, I have included columns for Gain, AvgGain, AvgLoss and RS to make it easier to follow the calculation. But the goal of the script is of course the RSI column.

The script can be seen to consist of several calculations:

• Gain (or Loss) for each closing price compared to the previous. (Row 7)
• Sum of the Gains (AvgGain) and the Losses (AvgLoss) respectively. (Row 27 for initial values and Row 34 & 40 for subsequent)
• RS by the formula AvgGain / AvgLoss. (Row 62)
• RSI by the formula RSI=100-(100/(1+RS)). (Row 63)

A sample of the results is shown below:

Relative Strength Index RSI

The usage of RSI can perhaps better be illustrated with a chart. The chart below has an vertical axis with closing prices (blue line, scale to the left) and a secondary axis with RSI (orange bars, scale to the right). The red arrow shows where a overbought situation occurs. And as predicted by the RSI theory, a downward correction of prices occurs shortly after.

RSI Chart with overbought stock