September 26, 2011 at 10:22 pm
Please find the attached excel sheet
September 27, 2011 at 7:48 am
I want to calulate the 12th days Rate of change
for each company. Please find the attached excel
Formula is as below
ROC = [(Close - Close 12 periods ago) / (Close 12 periods ago)] * 100
September 27, 2011 at 7:49 am
I want to calulate the 12th days Rate of change
for each company. Please find the attached excel
Formula is as below
ROC = [(Close - Close 12 periods ago) / (Close 12 periods ago)] * 100
September 27, 2011 at 7:56 am
I want to calculate the ROC as per formula
The Rate-of-Change (ROC) indicator, which is also referred to as simply Momentum, is a pure momentum oscillator that measures the percent change in price from one period to the next. The ROC calculation compares the current price with the price "n" periods ago.
The Formula is
ROC = [(Close - Close n periods ago) / (Close n periods ago)] * 100
where n=12
PFA the attached excel
Regards,
September 27, 2011 at 9:20 am
Sorry, been busy
-- set up some sample data
DROP TABLE #ROCdata
CREATE TABLE #ROCdata (ID INT NOT NULL, Symbol_Code VARCHAR(12), Transaction_date DATE, Close_Price MONEY)
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
SELECT 1, 'Company1', '28-Apr-10', 11045.27 UNION ALL --
SELECT 2, 'Company1', '29-Apr-10', 11167.32 UNION ALL --
SELECT 3, 'Company1', '30-Apr-10', 11008.61 UNION ALL --
SELECT 4, 'Company1', '3-May-10', 11151.83 UNION ALL --
SELECT 5, 'Company1', '4-May-10', 10926.77 UNION ALL --
SELECT 6, 'Company1', '5-May-10', 10868.12 UNION ALL --
SELECT 7, 'Company1', '6-May-10', 10520.32 UNION ALL --
SELECT 8, 'Company1', '7-May-10', 10380.43 UNION ALL --
SELECT 9, 'Company1', '10-May-10', 10785.14 UNION ALL --
SELECT 10, 'Company1', '11-May-10', 10748.26 UNION ALL --
SELECT 11, 'Company1', '12-May-10', 10896.91 UNION ALL --
SELECT 12, 'Company1', '13-May-10', 10782.95 UNION ALL --
SELECT 13, 'Company1', '14-May-10', 10620.16 UNION ALL ---3.85
SELECT 14, 'Company1', '17-May-10', 10625.83 UNION ALL ---4.85
SELECT 15, 'Company1', '18-May-10', 10510.95 UNION ALL ---4.52
SELECT 16, 'Company1', '19-May-10', 10444.37 UNION ALL ---6.34
SELECT 17, 'Company1', '20-May-10', 10068.01 UNION ALL ---7.86
SELECT 18, 'Company1', '21-May-10', 10193.39 UNION ALL ---6.21
SELECT 19, 'Company1', '24-May-10', 10066.57 UNION ALL ---4.31
SELECT 20, 'Company1', '25-May-10', 10043.75 UNION ALL ---3.24
SELECT 21, 'Company2', '28-Apr-10', 110535.27 UNION ALL --
SELECT 22, 'Company2', '29-Apr-10', 114167.32 UNION ALL --
SELECT 23, 'Company2', '30-Apr-10', 151008.61 UNION ALL --
SELECT 24, 'Company2', '3-May-10', 111551.83 UNION ALL --
SELECT 25, 'Company2', '4-May-10', 10926.77 UNION ALL --
SELECT 26, 'Company2', '5-May-10', 108568.12 UNION ALL --
SELECT 27, 'Company2', '6-May-10', 10520.32 UNION ALL --
SELECT 28, 'Company2', '7-May-10', 10380.43 UNION ALL --
SELECT 29, 'Company2', '10-May-10', 10785.14 UNION ALL --
SELECT 30, 'Company2', '11-May-10', 10748.26 UNION ALL --
SELECT 31, 'Company2', '12-May-10', 10896.91 UNION ALL --
SELECT 32, 'Company2', '13-May-10', 10782.95 UNION ALL --
SELECT 33, 'Company2', '14-May-10', 106420.16 UNION ALL ---3.72
SELECT 34, 'Company2', '17-May-10', 10625.83 UNION ALL ---90.69
SELECT 35, 'Company2', '18-May-10', 10510.95 UNION ALL ---93.04
SELECT 36, 'Company2', '19-May-10', 10444.37 UNION ALL ---90.64
SELECT 37, 'Company2', '20-May-10', 10068.01 UNION ALL ---7.86
SELECT 38, 'Company2', '21-May-10', 10193.39 UNION ALL ---90.61
SELECT 39, 'Company2', '24-May-10', 10066.57 UNION ALL ---4.31
SELECT 40, 'Company2', '25-May-10', 10043.75 ---3.24
-- calculate ROC
;WITH OrderedData AS (
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,
Symbol_Code,
Transaction_date,
Close_Price
FROM #ROCdata
)
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.Close_Price,
ROC = (d1.Close_Price-d13.Close_Price)/(1.000*d13.Close_Price) * 100
FROM OrderedData d1
LEFT JOIN OrderedData d13 ON d13.Symbol_Code = d1.Symbol_Code
AND d13.seq + 12 = d1.seq
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2011 at 4:17 am
Thanks Chrism for Solution.
I have one doubt,
For the ROC Calculation , i have to select the closing price of thirteen days before, how can select the thirteen days Before closing price , if the
date one or two dates (for eg. sunday the data is not available)in between are missing.
Trying through this.
select count(*) from EOD_NSE_Stock where
Transaction_date between dateadd(day, -14, getdate()) and getdate()
October 2, 2011 at 1:09 pm
Can you fully and accurately account for the missing days? Is it all sundays? Is it only sundays?
The accuracy of the solution provided to you will reflect the accuracy of your specification.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 12:40 pm
Well it depends upon the Bank holidays (Not fixed ) and every Saturday and Sunday
I tried this way
---------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ROC_NEW]
@Symbol_Code varchar(50)
as
Declare
@Cnt int,
@crt int,
@newcnt int
IF OBJECT_ID(N'tempdb..#ROCdata', N'U') IS NOT NULL
drop table #ROCdata;
CREATE TABLE #ROCdata (ID INT NOT NULL, Symbol_Code VARCHAR(50), Transaction_date DATEtime, Close_Price Decimal(18,2))
set @Cnt=(select count(*) from EOD_NSE_Stock where
Transaction_date between dateadd(day, -13, getdate()) and getdate() and Symbol_Code=@Symbol_Code )
if (@Cnt<13)
begin
set @crt=13-@Cnt
set @newcnt=@crt+14
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
select Transaction_Id,Symbol_Code,Transaction_date,Close_Price from EOD_NSE_Stock where
Transaction_date between dateadd(day, -@newcnt, getdate()) and getdate()
and Symbol_Code=@Symbol_Code
;WITH OrderedData AS (
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,
Symbol_Code,
Transaction_date,
Close_Price
FROM #ROCdata
)
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.Close_Price,
ROC = (d1.Close_Price-d13.Close_Price)/(1.000*d13.Close_Price) * 100
FROM OrderedData d1
LEFT JOIN OrderedData d13 ON d13.Symbol_Code = d1.Symbol_Code
AND d13.seq + 12 = d1.seq
end
else if (@Cnt=13)
begin
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
select Transaction_Id,Symbol_Code,Transaction_date,Close_Price from EOD_NSE_Stock where
Transaction_date between dateadd(day, -@Cnt, getdate()) and getdate()
and Symbol_Code=@Symbol_Code
;WITH OrderedData AS (
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,
Symbol_Code,
Transaction_date,
Close_Price
FROM #ROCdata
)
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.Close_Price,
ROC = (d1.Close_Price-d13.Close_Price)/(1.000*d13.Close_Price) * 100
FROM OrderedData d1
LEFT JOIN OrderedData d13 ON d13.Symbol_Code = d1.Symbol_Code
AND d13.seq + 12 = d1.seq
end
October 3, 2011 at 12:44 pm
In the if condition i check whether am i getting the exact thirteen previous days(13 row) if count is less than 13 then i again subtracting the missing rows from 13 , and adding it to 13 to get the desired 13 rows and calcute the ROC
in else part i am checking the whether i got the 13 row if yes then calculate the ROC
Is it right path?
October 3, 2011 at 2:22 pm
sushilb (10/3/2011)
In the if condition i check whether am i getting the exact thirteen previous days(13 row) if count is less than 13 then i again subtracting the missing rows from 13 , and adding it to 13 to get the desired 13 rows and calcute the ROCin else part i am checking the whether i got the 13 row if yes then calculate the ROC
Is it right path?
Does it yield the correct results?
My solution doesn't calculate thirteen days ago, it calculates on the basis of thirteen rows ago, i.e. values are carried over missing days.
Check both solutions.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 3, 2011 at 10:38 pm
Well the condition should not on 13 rows before , it should be on thirteenths days before
as below
select ID,
Symbol_Code,
Transaction_date,
Close_Price
from EOD_NSE_Stock
where
Transaction_date between
dateadd(day, -13, getdate()) and getdate()
and
Symbol_Code=@Symbol_Code
so there might be possibilty that i may miss some rows becoz of having holiday on some days.
thats why i m checking the the if else conditionto have exact 13 days data .
October 4, 2011 at 1:56 am
sushilb (10/3/2011)
Well the condition should not on 13 rows before , it should be on thirteenths days beforeas below
select ID,
Symbol_Code,
Transaction_date,
Close_Price
from EOD_NSE_Stock
where
Transaction_date between
dateadd(day, -13, getdate()) and getdate()
and
Symbol_Code=@Symbol_Code
so there might be possibilty that i may miss some rows becoz of having holiday on some days.
thats why i m checking the the if else conditionto have exact 13 days data .
So missing days - holidays and weekends - are included in the 13 days?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2011 at 1:58 am
Yes
October 4, 2011 at 2:08 am
Sorry answer is NO
as on Holidays data is not available.
October 6, 2011 at 2:01 am
Here's an interim solution for you. It's horribly inefficient however, I'm working on a second more efficient version.
DROP TABLE #ROCdata
CREATE TABLE #ROCdata (ID INT NOT NULL, Symbol_Code VARCHAR(12), Transaction_date DATE, Close_Price MONEY)
INSERT INTO #ROCdata (ID, Symbol_Code, Transaction_date, Close_Price)
SELECT 1, 'Company1', '28-Apr-10', 11045.27 UNION ALL --
SELECT 2, 'Company1', '29-Apr-10', 11167.32 UNION ALL --
SELECT 3, 'Company1', '30-Apr-10', 11008.61 UNION ALL --
SELECT 4, 'Company1', '3-May-10', 11151.83 UNION ALL --
SELECT 5, 'Company1', '4-May-10', 10926.77 UNION ALL --
SELECT 6, 'Company1', '5-May-10', 10868.12 UNION ALL --
SELECT 7, 'Company1', '6-May-10', 10520.32 UNION ALL --
SELECT 8, 'Company1', '7-May-10', 10380.43 UNION ALL --
SELECT 9, 'Company1', '10-May-10', 10785.14 UNION ALL --
SELECT 10, 'Company1', '11-May-10', 10748.26 UNION ALL --
SELECT 11, 'Company1', '12-May-10', 10896.91 UNION ALL --
SELECT 12, 'Company1', '13-May-10', 10782.95 UNION ALL --
SELECT 13, 'Company1', '14-May-10', 10620.16 UNION ALL -- -3.85
SELECT 14, 'Company1', '17-May-10', 10625.83 UNION ALL -- -4.85
SELECT 15, 'Company1', '18-May-10', 10510.95 UNION ALL -- -4.52
SELECT 16, 'Company1', '19-May-10', 10444.37 UNION ALL -- -6.34
SELECT 17, 'Company1', '20-May-10', 10068.01 UNION ALL -- -7.86
SELECT 18, 'Company1', '21-May-10', 10193.39 UNION ALL -- -6.21
SELECT 19, 'Company1', '24-May-10', 10066.57 UNION ALL -- -4.31
SELECT 20, 'Company1', '25-May-10', 10043.75 UNION ALL -- -3.24
SELECT 21, 'Company2', '28-Apr-10', 110535.27 UNION ALL --
SELECT 22, 'Company2', '29-Apr-10', 114167.32 UNION ALL --
SELECT 23, 'Company2', '30-Apr-10', 151008.61 UNION ALL --
SELECT 24, 'Company2', '3-May-10', 111551.83 UNION ALL --
SELECT 25, 'Company2', '4-May-10', 10926.77 UNION ALL --
SELECT 26, 'Company2', '5-May-10', 108568.12 UNION ALL --
SELECT 27, 'Company2', '6-May-10', 10520.32 UNION ALL --
SELECT 28, 'Company2', '7-May-10', 10380.43 UNION ALL --
SELECT 29, 'Company2', '10-May-10', 10785.14 UNION ALL --
SELECT 30, 'Company2', '11-May-10', 10748.26 UNION ALL --
SELECT 31, 'Company2', '12-May-10', 10896.91 UNION ALL --
SELECT 32, 'Company2', '13-May-10', 10782.95 UNION ALL --
SELECT 33, 'Company2', '14-May-10', 106420.16 UNION ALL -- -3.72
SELECT 34, 'Company2', '17-May-10', 10625.83 UNION ALL -- -90.69
SELECT 35, 'Company2', '18-May-10', 10510.95 UNION ALL -- -93.04
SELECT 36, 'Company2', '19-May-10', 10444.37 UNION ALL -- -90.64
SELECT 37, 'Company2', '20-May-10', 10068.01 UNION ALL -- -7.86
SELECT 38, 'Company2', '21-May-10', 10193.39 UNION ALL -- -90.61
SELECT 39, 'Company2', '24-May-10', 10066.57 UNION ALL -- -4.31
SELECT 40, 'Company2', '25-May-10', 10043.75 -- -3.24
-- calculate ROC
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.Close_Price,
DateToCompare = DATEADD(dd,-13,d1.Transaction_date),
d13.DateChosen,
ROC = (d1.Close_Price-d13.Close_Price)/(1.000*d13.Close_Price) * 100
FROM #ROCdata d1
OUTER APPLY (
SELECT TOP 1
DateChosen = Transaction_date,
Close_Price
FROM #ROCdata
WHERE Symbol_code = d1.Symbol_Code
AND Transaction_date <= DATEADD(dd,-13,d1.Transaction_date)
ORDER BY Transaction_date DESC) d13
ORDER BY d1.Symbol_Code, d1.Transaction_date
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 61 through 75 (of 80 total)
You must be logged in to reply to this topic. Login to reply