RSI = 100 - ( 100 / (1 + (MA(U) / MA(D)) ) )

MA: simple or exponential moving average, depending on the kind of RSI we are interested in.

U: set of current close price - previous close price, if current close price > previous close price (0 otherwise), for each period.

D: set of previous close price - current close price, if current close price < previous close price (0 otherwise), for each period.

This method uses CROSS APPLY, to avoid iterative or cursor-based processes.

First, I create a user-defined table type called OrderedPriceSet:

CREATE TYPE dbo.OrderedPriceSet AS TABLE

(

ValueRank INT PRIMARY KEY,

ValuePrice SMALLMONEY

);

And then I wrote a table-valued user-defined function that calculates the RSI values for each row in a given table of close prices:

CREATE FUNCTION Utility.udfCutlerRSI

(

@priceSet OrderedPriceSet READONLY,

@numValues INT

)

RETURNS @RSI_table TABLE

(

ValueRank INT PRIMARY KEY,

ValuePrice SMALLMONEY

)

AS

BEGIN

-- using CROSS APPLY

-- first, generate the ordered set of differentials (close(now) - close(yesterday))

DECLARE @differentials As OrderedPriceSet;

INSERT @differentials (ValuePrice, ValueRank)

SELECT apply_ps.ValuePrice, ps.ValueRank FROM @priceSet As ps

CROSS APPLY ( SELECT (ps.ValuePrice - inner_ps.ValuePrice) As ValuePrice

FROM @priceSet As inner_ps WHERE inner_ps.ValueRank = ps.ValueRank - 1 ) As apply_ps;

-- now we need to generate the U's and D's of each @numValues period, and calculate the RSI (can be done in one shot I think)

INSERT @RSI_table (ValueRank, ValuePrice)

SELECT diffs.ValueRank,

CASE sub_calcs.Dsum

WHEN 0 THEN 100.0

ELSE (100.0 - (100.0 / (1.0 + ((sub_calcs.Usum / CONVERT(FLOAT, @numValues)) / (sub_calcs.Dsum / CONVERT(FLOAT, @numValues) ))) ))

END AS ValuePrice

FROM @differentials As diffs

CROSS APPLY ( SELECT inner_diffs.ValueRank,

SUM(CASE

WHEN last_vals.ValuePrice > 0 THEN last_vals.ValuePrice

ELSE 0

END) As Usum,

SUM(CASE

WHEN last_vals.ValuePrice < 0 THEN ABS(last_vals.ValuePrice)

ELSE 0

END) As Dsum

FROM @differentials As inner_diffs

CROSS APPLY ( SELECT TOP(@numValues) ValuePrice FROM @differentials

WHERE ValueRank <= inner_diffs.ValueRank

ORDER BY ValueRank DESC ) last_vals

WHERE inner_diffs.ValueRank = diffs.ValueRank

GROUP BY ValueRank) sub_calcs;

RETURN;

END;

Finally, I use this UDF by passing it an OrderedPriceSet of chronologically ranked Close Price values, and a number of periods for my RSI, and INSERT the output into a table variable which now contains all RSIs for each ranked row.

## No comments:

## Post a Comment