Go Premium for a chance to win a PS4. Enter to Win

x
Solved

SQL Server 2005 - Relative Strength Index (RSI)

Posted on 2011-03-11
Medium Priority
1,854 Views
Hi Experts,

I used the below code to generate the Relative Strength Index (RSI) for a certain stock over a certain period of days. The period of days is 14 days so this RSI is called RSI 14.

The code below generates one value only, because I am providing a starting date and an ending date that between them there are 14 days of trading (excluding holidays).

I want to modify this code to be used on a wider range of dates. Something like a loop that takes from the user a starting date and an ending date (other than the starting/ending mentioned in the code currently) and from the starting date to take top 14 and do the calculation, then to go to the next trading date for same stock and take last 14 dates (including current) and do the calculation, until it reaches to the ending date.

To do this, for a start, the current variables defined starting and ending dates have to be changed of course, or at least the use of them has to be changed, because they currently are used to tell the code when do the 14 days start and when do they end, but that should be actually automated. And maybe instead to use something like Top 14 so that it takes the top 14 rows for that stock ordered by date of course.

Let me know what do you think?
DECLARE @StartingDate smalldatetime
DECLARE @EndingDate smalldatetime
DECLARE @StockID INT
DECLARE @DAYS INT
DECLARE @AG FLOAT
DECLARE @AL FLOAT
DECLARE @RS FLOAT

SET @StartingDate = '20110215'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

SET @AG =(
SELECT SUM(Last-Prev)
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate BETWEEN @StartingDate AND @EndingDate) AND (Last-Prev)>0
)/@DAYS
--SELECT @AG AG
SET @AL =(
SELECT SUM(Last-Prev)
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate BETWEEN @StartingDate AND @EndingDate) AND (Last-Prev)<0
)/@DAYS
SET @RS = @AG/ABS(@AL)
SELECT 100 - (100/(1+@RS)) RSI

0
Question by:feesu
• 13
• 13

LVL 51

Expert Comment

ID: 35116076
Well, might want a calendar table to help. If you want to exclude holidays and such like then it would be easier with a calendar (of some description) to get the calendar start and end dates. Unless stock is traded every day (excluding holidays) then if it is, you could use those dates (as in top 14 disctinct dates) e.g.

DECLARE @StartingDate smalldatetime
DECLARE @EndingDate smalldatetime
DECLARE @StockID INT
DECLARE @DAYS INT
DECLARE @AG FLOAT
DECLARE @AL FLOAT
DECLARE @RS FLOAT

SET @StartingDate = '20110101'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

-- define some new variables for our "working dates" of 14 days

DECLARE @PeriodStart smalldatetime
DECLARE @PeriodEnd smalldatetime
DECLARE @PeriodDays int

-- need to "seed" the periods before we begin.

select @periodstart=min(valuedate), @periodend=max(valuedate), @Perioddays = count(*)
from (select top(@days) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate between @startingdate and @endingdate
group by valuedate) a

-- now we can loop through our periods

While @periodend <= @endingdate and @perioddays = 14
begin

SELECT @ag=SUM(case when last-prev>0 then Last-Prev else 0 end),
@al=SUM(case when last-prev<0 then Last-Prev else 0 end)
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate BETWEEN @PeriodStart AND @PeriodEnd)

-- your calcs go here (I just print them for now)
print @ag
print @al
-- maybe insert them into a temp table for the individual periods ?

select @periodstart=min(valuedate), @periodend=max(valuedate), @perioddays = count(*)
from (select top(@days) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @PeriodEnd
group by valuedate) a

end


The challenge might be what to do with the last period if less than 14 days. the above only continues if it is actually 14 days.
0

Author Comment

ID: 35117979
mark,

There is no need for a calendar table, my database table has rows for stocks on only trade days, so no worries about that, there is a column called "last" for the closing and a column called "prev" for the previous closing.

Hope that is clear?
0

LVL 51

Expert Comment

ID: 35118389
Yep, thats good. It means we use the valuedate directly from the data source to get our groups of consecutive 14 days.

So, have a quick look at the code below and see if it is looping the way you want.

Probably needs error checking and a few more "safety" functions, but from my quick little test it matches the results of your original calculations.

One thought I did have was if you are OK with the calculations themselves (ie maybe that was part of the "what do you think")

DECLARE @StartingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @EndingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @StockID INT       -- this becomes a param for the stored procedure
DECLARE @DAYS INT       -- this becomes a param for the stored procedure just in case you want 7 days or different intervals

-- lets manually "prime" those params above
SET @StartingDate = '20110101'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

DECLARE @AG FLOAT
DECLARE @AL FLOAT
DECLARE @RS FLOAT
DECLARE @RSI FLOAT

DECLARE @PeriodStart smalldatetime
DECLARE @PeriodEnd smalldatetime
DECLARE @PeriodDays int

-- we will store results in an interim table

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;
CREATE Table #tmp_RSI_Results (periodstart smalldatetime, periodend smalldatetime, days int, ag float, al float, rs float, rsi float)

-- set initial values for our first period

select @periodstart=min(valuedate), @periodend=max(valuedate), @Perioddays = count(*)
from (select top(@days) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate between @startingdate and @endingdate
group by valuedate
order by valuedate) a

While @periodend <= @endingdate and @perioddays = @days
begin

SELECT @ag=SUM(case when last-prev>0 then Last-Prev else 0 end) / @days,
@al=SUM(case when last-prev<0 then Last-Prev else 0 end) / @days
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND ValueDate BETWEEN @PeriodStart AND @PeriodEnd

SET @RS = @AG/ABS(@AL)
SET @RSI = 100 - (100/(1+@RS))

insert #tmp_RSI_Results values (@periodstart, @periodend, @days, @ag, @al, @rs, @rsi)

select @periodstart=min(valuedate), @periodend=max(valuedate), @perioddays = count(*)
from (select top(@days) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @PeriodEnd
group by valuedate
order by valuedate) a

end

-- now lets display our table of results

select * from #tmp_RSI_Results

-- and clean up

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;

0

Author Comment

ID: 35121416
Hi Mark,

Thank you for the code. However, there is a slight misunderstanding here. The code should consider the first 13 days of the stock to do the calculation, and you have got that right, but it later doesn't take the next 13 days, it should take the next day directly and consider the most recent 12 days so that the total becomes 13 days of change in price. This way it calculates the RSI for each day starting from the starting date the user inputs and until the ending date.

Please find attached an Excel representation of the formula I prepared for the same stockId, that makes it easier on you.

Thanks!
RSI-107.xls
0

LVL 51

Expert Comment

ID: 35121555
*laughing* you are right - I dont understand that rolling period. I do kinda, and realise that it isnt "blocks" of 14 days now, so that part is OK...

But why in columns F and G am I only picking up the prior 12 days (plus current = 13 days in all) and dividing by 14 days ? I seem to be missing the first date in the series.

e.g. row 15 in your spreadsheet - the first row for calcs (ie 20th Jan 2011 or the 14th day) I would calculate an RS of 2 and RSI of 66.67 not 1.5 and 60 as shown in the spreadsheet by including the 1st Jan but the spreadsheet calculation is excluding it.

Is that because "PREV" is the previous day and so we only need the 13 ?

Getting the "rolling" periods are easy enough, we simply use @periodstart rather than @periodend

0

LVL 51

Expert Comment

ID: 35131309
Hi, havent heard, so just followed the above.

Apart from a few little rounding differences (and could resolve by using decimal rather than float), it matches your spreadsheet.

DECLARE @StartingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @EndingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @StockID INT       -- this becomes a param for the stored procedure
DECLARE @DAYS INT       -- this becomes a param for the stored procedure just in case you want 7 days or different intervals

-- lets manually "prime" those params above
SET @StartingDate = '20110101'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

DECLARE @AG FLOAT
DECLARE @AL FLOAT
DECLARE @RS FLOAT
DECLARE @RSI FLOAT

DECLARE @PeriodStart smalldatetime
DECLARE @PeriodEnd smalldatetime
DECLARE @PeriodDays int

-- we will store results in an interim table

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;
CREATE Table #tmp_RSI_Results (periodstart smalldatetime, periodend smalldatetime, days int, ag float, al float, rs float, rsi float)

-- set initial values for our first period

select @periodstart=min(valuedate), @periodend=max(valuedate), @Perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @startingdate and valuedate <= @endingdate
group by valuedate
order by valuedate) a

While @periodstart <= @endingdate and @perioddays = @days -1
begin

SELECT @ag=SUM(case when last-prev>0 then Last-Prev else 0 end) / @days,
@al=SUM(case when last-prev<0 then Last-Prev else 0 end) / @days
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate BETWEEN @PeriodStart AND @PeriodEnd)

SET @RS = @AG/ABS(@AL)
SET @RSI = 100 - (100/(1+@RS))

insert #tmp_RSI_Results values (@periodstart, @periodend, @days, @ag, @al, @rs, @rsi)

select @periodstart=min(valuedate), @periodend=max(valuedate), @perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @PeriodStart
group by valuedate
order by valuedate) a

end

-- Now check the results

select * from #tmp_RSI_Results

-- and clean up

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;


0

Author Comment

ID: 35133218
Hi Mark,

Sorry for not getting back to you.

Well, we take 13 days instead of 14, because - as far as I understood from our Financial Analysts - the first change (last - prev) should always be zero, for any period you try to calculate. Coming from the fact that a change means the difference between a closing and a previous closing, when taking the RSI for a period, it shouldn't have a previous closing, so the first change is considered always zero. So it is as if you are actually taking the whole 14 days but replacing the first change (day 1) with zero, and instead of that, you take 13, much easier. But, you still consider them total of 14 days, so you divide by 14.

Hope that's clear!
0

Author Comment

ID: 35133302
Mark,

I checked the code, and it looks fine except for that it starts calculating on the 13th day of the starting date, while it should start on the 14th day but consider the recent 13 days from that 14th day, this is causing one day difference before the spreadsheet starts and one day after it ends, if you compare them.

Thanks alot!
0

LVL 51

Expert Comment

ID: 35134163
Nah, think it is doing the right thing. it looks for dates > start so excludes that first one.

But from my own testing, I had it starting from the 2011-01-01 rather than the 2011-01-03 as per the spreadsheet - sorry about that...

There was some rounding differences, but, if I change to decimal arithmetic and round the results to 9 decimal places in both the query and the spreadsheet, the results are identical.

I think the incorrect startingdate parameter was the real culprit - well depending on how many decimal places you need to round to .

DECLARE @StartingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @EndingDate smalldatetime       -- this becomes a param for the stored procedure
DECLARE @StockID INT       -- this becomes a param for the stored procedure
DECLARE @DAYS INT       -- this becomes a param for the stored procedure just in case you want 7 days or different intervals

-- lets manually "prime" those params above
SET @StartingDate = '20110103'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

-- declare local variables (note how it is now decimal)

DECLARE @AG DECIMAL(18,12)
DECLARE @AL DECIMAL(18,12)
DECLARE @RS DECIMAL(18,12)
DECLARE @RSI DECIMAL(18,12)

DECLARE @PeriodStart smalldatetime
DECLARE @PeriodEnd smalldatetime
DECLARE @PeriodDays int

-- we will store the results in an interim (temp) table

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;
CREATE Table #tmp_RSI_Results (periodstart smalldatetime, periodend smalldatetime, days int, ag decimal(18,12), al decimal(18,12), rs decimal(18,12), rsi decimal(18,12))

-- need to "prime" the period ranges before we start using...

select @periodstart=min(valuedate), @periodend=max(valuedate), @Perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @startingdate and valuedate <= @endingdate
group by valuedate
order by valuedate) a

-- now we can loop through the periods

While @periodstart <= @endingdate and @perioddays = @days -1
begin

SELECT @ag=SUM(case when last-prev>0 then Last-Prev else 0 end)*1.0 / @days,
@al=SUM(case when last-prev<0 then Last-Prev else 0 end)*1.0 / @days
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate between @PeriodStart AND @PeriodEnd)

SET @RS = @AG/ABS(@AL)
SET @RSI = 100.0 - (100.0/(1+@RS))

insert #tmp_RSI_Results values (@periodstart, @periodend, @days, @ag, @al, @rs, @rsi)

select @periodstart=min(valuedate), @periodend=max(valuedate), @perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @PeriodStart
group by valuedate
order by valuedate) a

end

select periodend as ValueDate, round(ag,9) as ag, round(al,9) as al, round(rs,9) as rs, round(rsi,9) as rsi from #tmp_RSI_Results

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;

-- for this next select - point to the spreadsheet previously sent - I saved it as C:\EE\RSI107.XLS as you can see below.
-- if it doesnt load, no worries, just change the columns in the spreadsheet itself.

Select valuedate,round(ag,9) as ag, round(al,9) as al, round(rs,9) as rs, round(rsi,9) as rsi FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\ee\rsi107.xls;HDR=Yes;imex=1', 'SELECT * FROM [sheet1\$]') as a
where ag is not null

0

Author Comment

ID: 35135307
Mark,

The date starts from 3rd of Jan because the 1st and 2nd are holidays, and there is no trade. No matter what the starting date parameter was, the actual starting date is going to be the first trading day >= the parameter.
0

LVL 51

Expert Comment

ID: 35135345
Yep, except that the @startingdate is not validated against much at all, so, asking for an earlier starting date will pick up the earlier actual date. So if you count 13days from the first date you actually land on the 19th the way it currently works.

Maybe the @startingdate should be the first date you want reported on ? and not 13 days after ?

e.g.
if I select startingdate to be 3rd Jan, then (right now) the first reported date is the 20th Jan - essentially the 3rd is missed out.

if I select startingdate to be 1st Jan, then (right now) the first reported date is the 19th Jan - essentially the third is included.

Maybe I should simply select 20th Jan as the startingdate ?

I might have misunderstood how you want the parameters to work. But it seemed to be the same as you had above.

Or we adjust the startingdate to be the first trading day >= to entered date. Again, there is no validation on that date.
0

LVL 51

Expert Comment

ID: 35135358
Have you run the code above and compared ? it does balance, it does work.

Just need to work out how the startingdate parameter is resolved.
0

Author Comment

ID: 35135359
You know what, let me get this running, and tested, and then I'll get back to you! Thanks :)
0

Author Comment

ID: 35135389
Oh yes, it is pretty much the same, but there is a first extra row and an additional extra row as mentioned before.

I have pasted the result from the executed code next to the same spreadsheet table and attached it here.
RSI-107.xls
0

LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 35135531
OK, I know why there is extra at the end. It was going "while @periodstart <= @endingdate..." and s/b @periodend not @periodstart.

It now reads : While @periodend <= @endingdate and @perioddays = @days -1

But still do not get that rogue starting row.

Please run the code below - it will print out some messages in the message tab and can you please post those messages ? :

DECLARE @StartingDate smalldatetime  -- this becomes a param for the stored procedure
DECLARE @EndingDate smalldatetime    -- this becomes a param for the stored procedure
DECLARE @StockID INT                 -- this becomes a param for the stored procedure
DECLARE @DAYS INT                    -- this becomes a param for the stored procedure just in case you want 7 days or different intervals

-- lets manually "prime" those params above
SET @StartingDate = '20110103'
SET @EndingDate = '20110309'
SET @StockID = 107
SET @DAYS = 14

-- lets now define some working parameters

DECLARE @AG DECIMAL(18,12)
DECLARE @AL DECIMAL(18,12)
DECLARE @RS DECIMAL(18,12)
DECLARE @RSI DECIMAL(18,12)

DECLARE @PeriodStart smalldatetime
DECLARE @PeriodEnd smalldatetime
DECLARE @PeriodDays int

set nocount on

-- we will store the results in an interim (temp) table

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;
CREATE Table #tmp_RSI_Results (periodstart smalldatetime, periodend smalldatetime, days int, ag decimal(18,12), al decimal(18,12), rs decimal(18,12), rsi decimal(18,12))

-- need to "prime" the period ranges before we start using...

select @periodstart=min(valuedate), @periodend=max(valuedate), @Perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @startingdate and valuedate <= @endingdate
group by valuedate
order by valuedate) a

print @startingdate
print @periodstart
print @periodend

While @periodend <= @endingdate and @perioddays = @days -1
begin

SELECT @ag=SUM(case when last-prev>0 then Last-Prev else 0 end)*1.0 / @days,
@al=SUM(case when last-prev<0 then Last-Prev else 0 end)*1.0 / @days
FROM Feed_Quotes_Details_Closing
WHERE StockID = @StockID AND (ValueDate between @PeriodStart AND @PeriodEnd)

SET @RS = @AG/ABS(@AL)
SET @RSI = 100.0 - (100.0/(1+@RS))

insert #tmp_RSI_Results values (@periodstart, @periodend, @days, @ag, @al, @rs, @rsi)

select @periodstart=min(valuedate), @periodend=max(valuedate), @perioddays = count(*)
from (select top(@days -1) valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate > @PeriodStart
group by valuedate
order by valuedate) a

end

select periodend as ValueDate, round(ag,9) as ag, round(al,9) as al, round(rs,9) as rs, round(rsi,9) as rsi from #tmp_RSI_Results

IF object_id('tempdb..#tmp_RSI_Results','U') is not null drop table #tmp_RSI_Results;

0

Author Comment

ID: 35135642
Jan  3 2011 12:00AM
Jan  4 2011 12:00AM
Jan 20 2011 12:00AM
0

LVL 51

Expert Comment

ID: 35135659
So, the row that has been written away to the temp table must be 20th

While we do have a potential problem with how startingdate parameter is set / used, I cannot see why the above would report anything for the 19th

How did the display look ? Did it stop at the 9th March ? Did it still display the 19th ?
0

Author Comment

ID: 35135703
But this is the result after changing the starting date parameter to 1st of Jan:

Jan  1 2011 12:00AM
Jan  3 2011 12:00AM
Jan 19 2011 12:00AM
0

Author Comment

ID: 35135711
With 3rd of Jan, it starts with 19th for the first ending period, with 1st of Jan it gets 20th..

But both cases stop at 9th of March..
0

LVL 51

Expert Comment

ID: 35136499
Yep, understand that the 19th will show if the startingdate is set to 1st Jan 2011 (or 2nd January, or 31st Dec 2010 etc...).

I did try to point that out above. Maybe I wasnt clear enough. It is doing what it is being told to do. But the 1st Jan should not be a legitimate startingdate either.

You say that the 1st and 2nd should not be a valid selection, but what is stopping them ? it is a parameter that the users fill in. What is stopping them now ?

So, the question for you is, will the startingdate be validated and verified as a legitimate date before use ?

There was nothing in your original code that validated startingdate, so not sure how it was happening, I had assumed it was going to be a legitimate date.

But we could do (before we set the initial @periodstart and @periodend for the first time) to make sure it is the first "legitimate" date (taken from the actual data):

set @startingdate = (select top 1 valuedate
from Feed_Quotes_Details_Closing
where StockID = @stockid and valuedate >= @startingdate
order by valuedate asc)


OR, does it make more sense for @startingdate to become the first date to be reported on ?

So, instead of requesting the 3rd Jan (to get the first reported period = 20th Jan), does it make more sense if we make it the 20th Jan and let the routine do the date manipulation to gather the appropriate data ?

0

Author Comment

ID: 35136556
Hi Mark,

My question clearly says that the code starting and ending dates have to be changed to automatically pick up the trading dates, and that they are in my example fed manually :)

On to your suggestion, yes, I think it is better that if the user wants to start calculating from 1st of Jan, means the code would go back 12 trading days. Makes more sense.

On the other hand, I have executed the recent code and displayed the result in a grid for a financial analyst, and he is fine with the output :)

Unless we find something that really makes more sense to us, current result is fine!
0

LVL 51

Expert Comment

ID: 35136645
OK, but we dont want to confuse the users if at all possible. So if there is something smart (like the set of startingdate above) or at least more intuitive for the users then think we should explore that.

Then again, you can also take control in the user interface (depending on how the users interact).

But that is your call and will leave it for now awaiting further instructions (if any).

The good thing at this point is we know the routine works so long as we are "careful" with that startingdate.

0

Author Comment

ID: 35137199
Very true!
Appreciate your help and time :)
0

LVL 51

Expert Comment

ID: 35229019
@feesu,

Wondering if this question is still needing attention, or, if you are in a position to accept a solution ?

Please let me know if there is anything else you are waiting on from me...

Cheers,
Mark
0

Author Comment

ID: 35238749
I was waiting for feedback from the users, but since it's been a while, I guess they have no comment, and even if they do, I will get back to you.

Thanks!
0

LVL 51

Expert Comment

ID: 35239620
Hope you know that we will be here ready and waiting for any feedback.

Cheers,
Mark.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month7 days, 2 hours left to enroll