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:
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.
[blue_box]This blog post is part of a serie about technical analysis, TA, in SQL Server. See the other posts here.[/blue_box]
Greate article.
But when I adapt to my big table, some strange errors occurs.
The same occurs when I populate your table to 10000 records.
In fact , it is all good until the record number 9585.
Can you investigate ? I have no idea.
Hi! Do you get an error message?
I ran into an issue also when I was running this type of query on a large data set. It has to do with number of times SQL Server recursive functionality will work. I converted this code to loop through the record set to get around this. I saw lind do something similar in the EMA9 page. I tried setting option ( MaxRecursion 0 ) in the code on this page but that did not help
No, but the values were all strange. Seams correct for some records, with values, but next to it others records with NULL.
The only thing that makes sense is the beginning line of the process, still line 14.
I just tried with 100 k records, and that went fine. So I don’t think it’s a problem with the number of rows.
I havent seen your data, so if I’d take a guess it would be that there is a gap in the “QuoteId” column in your data. The RSI i cumulative for the last 14 days (or whatever period you use). So if, at some point, there is no data present for the 14:th day ago, the result would be NULL.
So check if there is a gap in the numbers for the QuoteId column.
There is no gap, I used Excel to create the autonumber column to 10000, copy from there, paste on t-sql and run.
(also used Excel to create alleatory value column)
I recheck it now, still no gap.
Note that I have only StockId 1 (10000 rows).
Hmmm… did you say that row 14 were ok? And that the errors started at row 15? If so, can you send me the first 15 records?
StockId QuoteId QuoteDay QuoteClose Gain AvgGain AvgLoss RS RSI
1 1 2013-10-16 71.54 NULL NULL NULL NULL NULL
1 2 2013-10-16 58.29 -13.25 NULL NULL NULL NULL
1 3 2013-10-16 31.78 -26.51 NULL NULL NULL NULL
1 4 2013-10-16 2.77 -29.01 NULL NULL NULL NULL
1 5 2013-10-16 82.71 79.94 NULL NULL NULL NULL
1 6 2013-10-16 5.42 -77.29 NULL NULL NULL NULL
1 7 2013-10-16 87.80 82.38 NULL NULL NULL NULL
1 8 2013-10-16 38.97 -48.83 NULL NULL NULL NULL
1 9 2013-10-16 88.60 49.63 NULL NULL NULL NULL
1 10 2013-10-16 95.37 6.77 NULL NULL NULL NULL
1 11 2013-10-16 53.77 -41.60 NULL NULL NULL NULL
1 12 2013-10-16 6.60 -47.17 NULL NULL NULL NULL
1 13 2013-10-16 47.30 40.70 NULL NULL NULL NULL
1 14 2013-10-16 63.65 16.35 19.70 20.26 0.97 49.29
1 15 2013-10-16 27.28 -36.37 NULL NULL NULL NULL
1 16 2013-10-16 83.83 56.55 NULL NULL NULL NULL
1 17 2013-10-16 59.99 -23.84 18.03 16.79 1.07 51.78
1 18 2013-10-16 92.23 32.24 NULL NULL NULL NULL
1 19 2013-10-16 70.99 -21.24 NULL NULL NULL NULL
1 20 2013-10-16 25.54 -45.45 NULL NULL NULL NULL
1 21 2013-10-16 11.10 -14.44 NULL NULL NULL NULL
1 22 2013-10-16 68.20 57.10 NULL NULL NULL NULL
1 23 2013-10-16 58.11 -10.09 NULL NULL NULL NULL
1 24 2013-10-16 11.80 -46.31 NULL NULL NULL NULL
1 25 2013-10-16 29.50 17.70 NULL NULL NULL NULL
1 26 2013-10-16 30.39 0.89 NULL NULL NULL NULL
1 27 2013-10-16 31.95 1.56 NULL NULL NULL NULL
1 28 2013-10-16 98.41 66.46 NULL NULL NULL NULL
1 29 2013-10-16 28.17 -70.24 NULL NULL NULL NULL
1 30 2013-10-16 17.65 -10.52 NULL NULL NULL NULL
1 31 2013-10-16 42.42 24.77 27.55 10.90 2.53 71.64
1 32 2013-10-16 72.33 29.91 NULL NULL NULL NULL
1 33 2013-10-16 64.21 -8.12 NULL NULL NULL NULL
1 34 2013-10-16 19.59 -44.62 NULL NULL NULL NULL
1 35 2013-10-16 9.46 -10.13 NULL NULL NULL NULL
1 36 2013-10-16 91.27 81.81 NULL NULL NULL NULL
1 37 2013-10-16 79.38 -11.89 NULL NULL NULL NULL
1 38 2013-10-16 29.92 -49.46 NULL NULL NULL NULL
1 39 2013-10-16 64.63 34.71 NULL NULL NULL NULL
1 40 2013-10-16 43.10 -21.53 9.62 21.04 0.46 31.37
… and if I put select top(9854) it works !! Any number above it I receive strange results.
sorry, the misterious number is 9584
Ok, i tried with your data, and I do not get the same result as you. If I run this script:
CREATE DATABASE Gustavo
GO
USE Gustavo
GO
–drop table dbo.Quotes
CREATE TABLE dbo.Quotes
(
StockId INT NOT NULL
,QuoteId INT NOT NULL
,QuoteDay DATE NOT NULL
,QuoteClose NUMERIC(10,2) NOT NULL,
CONSTRAINT Quotes_PK PRIMARY KEY CLUSTERED (StockId,QuoteId)
);
INSERT dbo.Quotes VALUES
(1,1,’2013-10-16′,71.54),
(1,2,’2013-10-16′,58.29),
(1,3,’2013-10-16′,31.78),
(1,4,’2013-10-16′,2.77),
(1,5,’2013-10-16′,82.71),
(1,6,’2013-10-16′,5.42),
(1,7,’2013-10-16′,87.80),
(1,8,’2013-10-16′,38.97),
(1,9,’2013-10-16′,88.60),
(1,10,’2013-10-16′,95.37),
(1,11,’2013-10-16′,53.77),
(1,12,’2013-10-16′,6.60),
(1,13,’2013-10-16′,47.30),
(1,14,’2013-10-16′,63.65),
(1,15,’2013-10-16′,27.28),
(1,16,’2013-10-16′,83.83),
(1,17,’2013-10-16′,59.99),
(1,18,’2013-10-16′,92.23),
(1,19,’2013-10-16′,70.99),
(1,20,’2013-10-16′,25.54),
(1,21,’2013-10-16′,11.10),
(1,22,’2013-10-16′,68.20),
(1,23,’2013-10-16′,58.11),
(1,24,’2013-10-16′,11.80),
(1,25,’2013-10-16′,29.50),
(1,26,’2013-10-16′,30.39),
(1,27,’2013-10-16′,31.95),
(1,28,’2013-10-16′,98.41),
(1,29,’2013-10-16′,28.17),
(1,30,’2013-10-16′,17.65),
(1,31,’2013-10-16′,42.42),
(1,32,’2013-10-16′,72.33),
(1,33,’2013-10-16′,64.21),
(1,34,’2013-10-16′,19.59),
(1,35,’2013-10-16′,9.46),
(1,36,’2013-10-16′,91.27),
(1,37,’2013-10-16′,79.38),
(1,38,’2013-10-16′,29.92),
(1,39,’2013-10-16′,64.63),
(1,40,’2013-10-16′,43.10)
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 The result (first 16) is: 1 1 2013-10-16 71.54 NULL NULL NULL NULL NULL 1 2 2013-10-16 58.29 -13.25 NULL NULL NULL NULL 1 3 2013-10-16 31.78 -26.51 NULL NULL NULL NULL 1 4 2013-10-16 2.77 -29.01 NULL NULL NULL NULL 1 5 2013-10-16 82.71 79.94 NULL NULL NULL NULL 1 6 2013-10-16 5.42 -77.29 NULL NULL NULL NULL 1 7 2013-10-16 87.80 82.38 NULL NULL NULL NULL 1 8 2013-10-16 38.97 -48.83 NULL NULL NULL NULL 1 9 2013-10-16 88.60 49.63 NULL NULL NULL NULL 1 10 2013-10-16 95.37 6.77 NULL NULL NULL NULL 1 11 2013-10-16 53.77 -41.60 NULL NULL NULL NULL 1 12 2013-10-16 6.60 -47.17 NULL NULL NULL NULL 1 13 2013-10-16 47.30 40.70 NULL NULL NULL NULL 1 14 2013-10-16 63.65 16.35 19.70 20.26 0.97 49.29 1 15 2013-10-16 27.28 -36.37 18.29 21.41 0.85 46.07 1 16 2013-10-16 83.83 56.55 21.02 19.88 1.06 51.39 Can you try and run exactly the same script as above and check the results?
Divided by zero error ecncountered..!
Should be from the last SQL statement in the script where there are two “/ AvgLoss” divisors. Replace “/ AvgLoss” with “/ NULLIF(AvgLoss, 0)” if you want NULL as the result, or replace “AvgGain / AvgLoss” with ISNULL(AvgGain / NULLIF(AvgLoss,0), 0) if you want 0 as the result.
Hey Tomas, How would you handle calculating the RSI if you are updating the data every 3 or so seconds?
Hi, if you need to update that often I guess your problem would be performance related. Perhaps you could try out In-Memory OLTP. If you have the possibility that is (enough memory, later version of SQL Server).
i want to add a final element to the query above that shows a buy or sell signal when there is a divergence between price and RSI (so price hits new low but RSI hits higher low, or price hits new high but RSI hits lower high). so basically want to use two columns from the final output table above: quoteclose and RSI… to populate two new columns, one for buy signal and one for sell signal (could be binary 1 or 0 columns).
here’s what i have currently (below) to give signals, but i dont think this is the most efficient way to calculate this. i’d prefer, in the code below, to 1) not use lag, 2) not use the full formula for RSI (i.e. would rather this code: “>min(CAST(100 – (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2)))” b “>min(RSI)”. how can i build off the above to add rules based buy and sell signals? thanks a lot!
so i added the code below to your code above following the RSI calc. it’s the 2 parts of the buy signal (i.e. if both statements below = 1 then that’s a buy signal. i want to do same for sell signal but that will be easy once i see the most efficient way to produce the below output.
,(case when (lag(currfacepricebid,1) over(order by rownum))<min(currfacepricebid)
over (partition by isin order by rownum rows between 21 preceding and 2 preceding)
AND (lag(currfacepricebid,1) over(order by rownum))min(CAST(100 – (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2)))
over (partition by isin order by rownum rows between 21 preceding and 2 preceding)
AND (lag(CAST(100 – (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2)),1) over(order by rownum))<CAST(100 – (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2))
AND (lag(CAST(100 – (100 / (1 + AvgGain / AvgLoss)) AS NUMERIC(10,2)),1) over(order by rownum))<30
then 1 else 0 end) as BuyRsiSignal