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