SQL - Daily/weekly/monthly % Change in Price

happy1001 · Apr 8, 2015

Hi

I am using SQL Server 2014 Standard Edition x64.
Code:
I have a table that has got these 3 columns in it, showing the EOD Price Data for the symbols -

xdate
symbol
price
I need to write a query to calculate the Daily/weekly/monthly % Change in Price
Code:
Daily     % Change in Price over past 1 day
Weekly     % Change in Price over past 7 days
Monthly     % Change in Price over past 30 days
I am new to SQL. Instead of writing any complex SQL code, is there some FUNCTION available within SQL Server that can do such % change calculation easily ? I do not need the dates to be Exact, even if it is up or down, a day or two, because of weekends or other market holidays etc. that will still be fine for me.

Thanks for any help.

Regards

globalarbtrader · Apr 8, 2015

http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement

grashed · Apr 8, 2015

I would not try to do this in SQL. Can you do it, yes, but should you do it. No. Here's why: SQL is designed to retrieve data, not do calculations. Because there was a demand to do calculations in SQL, Microsoft and Oracle added it on as an afterthough. The result of that is you'll be forced to build code that is really unintuitive and complex to understand for a calculation that should be straightforward. My recommendation is to take the data and process it in something that is relatively straightforward like c# or Java. Now, sometimes when your beginning programming, you worry about doing things the "right way". Don't worry about that. Instead, do things the way that makes everything as simple as possible. And by that I mean, ask yourself this question "If I got drunk tomorrow, and I had no clue what I coded up last night, could I look at my code with blurry eyes and understand exactly what was going on ?" If you can't do that, then your code is too complex.

viktor_k67 · Apr 8, 2015

happy1001 said:
I need to write a query to calculate the Daily/weekly/monthly % Change in Price

Regards
More...

Declare @d1 as smalldatetime, @d2 as smalldatetime
--get the most recent date
set @d1 = (Select top 1 xdate from YourTableName order by xdate desc)
--get date 1 day, week or month ago, select one of the three lines below
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(DAY, -1, @d1) order by xdate desc)
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(WEEK, -1, @d1) order by xdate desc)
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(MONTH, -1, @d1) order by xdate desc)

--0.01 is used to get % in the range from0 to 100 and avout roundining if price stored in int
--
Select t1.xdate, t1.Symbol, (t2.price-t1.price)/0.01/t1.price from (
Select * from YourTableName where xdate = @d1
) t1 inner join (
Select * from YourTableName where xdate = @d2
)t2 on t1.Symbol=t2.Symbol
--to avid division by 0
where t1.price>0

happy1001 · Apr 9, 2015

globalarbtrader said:
http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement
More...

Thanks for the link globalrbtrader. I will check it out.

happy1001 · Apr 9, 2015

grashed said:
I would not try to do this in SQL. Can you do it, yes, but should you do it. No. Here's why: SQL is designed to retrieve data, not do calculations. Because there was a demand to do calculations in SQL, Microsoft and Oracle added it on as an afterthough. The result of that is you'll be forced to build code that is really unintuitive and complex to understand for a calculation that should be straightforward. My recommendation is to take the data and process it in something that is relatively straightforward like c# or Java. Now, sometimes when your beginning programming, you worry about doing things the "right way". Don't worry about that. Instead, do things the way that makes everything as simple as possible. And by that I mean, ask yourself this question "If I got drunk tomorrow, and I had no clue what I coded up last night, could I look at my code with blurry eyes and understand exactly what was going on ?" If you can't do that, then your code is too complex.
More...

Thanks for your views grashed. And I agree with it. Many other guys also say that SQL Server is not the right tool to do any complex stock market related queries work. There are other database which are specially designed for doing such work. Regarding C# or Java, I would say that I am a total noob in programming, there is it is out of my ability to do any coding in that.

Since this is a simple query, I guess there would be some way to do it with SQL Server itself. Once I am able to create a working solution, I will post it here.

Thanks and regards

happy1001 · Apr 9, 2015

viktor_k67 said:
Declare @d1 as smalldatetime, @d2 as smalldatetime
--get the most recent date
set @d1 = (Select top 1 xdate from YourTableName order by xdate desc)
--get date 1 day, week or month ago, select one of the three lines below
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(DAY, -1, @d1) order by xdate desc)
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(WEEK, -1, @d1) order by xdate desc)
Set @d2 = (Select top 1 xdate from YourTableName where xdate <= DATEADD(MONTH, -1, @d1) order by xdate desc)

--0.01 is used to get % in the range from0 to 100 and avout roundining if price stored in int
--
Select t1.xdate, t1.Symbol, (t2.price-t1.price)/0.01/t1.price from (
Select * from YourTableName where xdate = @d1
) t1 inner join (
Select * from YourTableName where xdate = @d2
)t2 on t1.Symbol=t2.Symbol
--to avid division by 0
where t1.price>0
More...

Thanks a ton viktor_k67 for providing the solution. I tried to convert your code according to my table, for doing the calculation for 1 Day % Change "DailyPCT" as follows -

Code:

  Declare @d1 as smalldatetime, @d2 as smalldatetime
--get the most recent date

set @d1 = (Select top 1 xdate from Table_1 order by xdate desc)
--get date 1 day, week or month ago, select one of the three lines below
Set @d2 = (Select top 1 xdate from Table_1 where xdate <= DATEADD(DAY, -1, @d1) order by xdate desc)


--0.01 is used to get % in the range from0 to 100 and avoid roundining if price stored in int
--
SELECT
  t1.xdate,
  t1.symbol,
  (t2.price - t1.price) / 0.01 / t1.price AS DailyPCT
FROM (SELECT
  *
FROM Table_1
WHERE xdate = @d1) t1
INNER JOIN (SELECT
  *
FROM Table_1
WHERE xdate = @d2) t2
  ON t1.symbol = t2.symbol
--to avid division by 0
WHERE t1.price > 0

But I seem to be making some mistake. I am trying to figure it out. I have never used any variable declaration method earlier.

I also created a script that will create the Sample DB and Table used in this work. Here is the script -

Code:

CREATE DATABASE ___SampleDbForTesting___1
GO

USE ___SampleDbForTesting___1
GO




CREATE TABLE ___SampleDbForTesting___1.dbo.Table_1 (
  symbol NVARCHAR(255) NULL,
  xdate DATETIME2(0) NULL,
  price FLOAT NULL
) ON [PRIMARY]
GO


--Insert data in Table_1
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-07 00:00:00.0000000', N'DLF', 142.65)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-07 00:00:00.0000000', N'UNITECH', 19.35)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-08 00:00:00.0000000', N'DLF', 149.9)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-08 00:00:00.0000000', N'UNITECH', 19.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-09 00:00:00.0000000', N'DLF', 153.45)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-09 00:00:00.0000000', N'UNITECH', 20.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-10 00:00:00.0000000', N'UNITECH', 19.35)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-10 00:00:00.0000000', N'DLF', 153.1)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-13 00:00:00.0000000', N'DLF', 147.45)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-13 00:00:00.0000000', N'UNITECH', 18.55)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-14 00:00:00.0000000', N'DLF', 105.5)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-14 00:00:00.0000000', N'UNITECH', 18.1)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-16 00:00:00.0000000', N'DLF', 110.6)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-16 00:00:00.0000000', N'UNITECH', 17.4)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-17 00:00:00.0000000', N'DLF', 111.2)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-17 00:00:00.0000000', N'UNITECH', 17.65)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-20 00:00:00.0000000', N'DLF', 115.45)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-20 00:00:00.0000000', N'UNITECH', 17.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-21 00:00:00.0000000', N'DLF', 121.5)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-21 00:00:00.0000000', N'UNITECH', 17.65)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-22 00:00:00.0000000', N'DLF', 120.4)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-22 00:00:00.0000000', N'UNITECH', 17.6)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-23 00:00:00.0000000', N'DLF', 119.95)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-23 00:00:00.0000000', N'UNITECH', 17.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-27 00:00:00.0000000', N'DLF', 110.8)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-27 00:00:00.0000000', N'UNITECH', 17.15)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-28 00:00:00.0000000', N'DLF', 111.65)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-28 00:00:00.0000000', N'UNITECH', 17.2)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-29 00:00:00.0000000', N'DLF', 118.45)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-29 00:00:00.0000000', N'UNITECH', 17.8)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-30 00:00:00.0000000', N'DLF', 124.85)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-30 00:00:00.0000000', N'UNITECH', 19.45)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-31 00:00:00.0000000', N'DLF', 125.25)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-10-31 00:00:00.0000000', N'UNITECH', 20.55)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-03 00:00:00.0000000', N'DLF', 128.8)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-03 00:00:00.0000000', N'UNITECH', 22.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-05 00:00:00.0000000', N'DLF', 127.1)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-05 00:00:00.0000000', N'UNITECH', 22.6)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-07 00:00:00.0000000', N'DLF', 134.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-07 00:00:00.0000000', N'UNITECH', 22.75)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-10 00:00:00.0000000', N'DLF', 135.95)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-10 00:00:00.0000000', N'UNITECH', 21.65)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-11 00:00:00.0000000', N'DLF', 137.4)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-11 00:00:00.0000000', N'UNITECH', 21.7)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-12 00:00:00.0000000', N'DLF', 140.05)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-12 00:00:00.0000000', N'UNITECH', 21.15)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-13 00:00:00.0000000', N'DLF', 139.15)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-13 00:00:00.0000000', N'UNITECH', 19.95)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-14 00:00:00.0000000', N'DLF', 142.25)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-14 00:00:00.0000000', N'UNITECH', 21)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-17 00:00:00.0000000', N'DLF', 140.1)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-17 00:00:00.0000000', N'UNITECH', 20.75)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-18 00:00:00.0000000', N'DLF', 139.1)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-18 00:00:00.0000000', N'UNITECH', 20.6)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-19 00:00:00.0000000', N'DLF', 141.35)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-19 00:00:00.0000000', N'UNITECH', 19.85)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-20 00:00:00.0000000', N'DLF', 138.5)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-20 00:00:00.0000000', N'UNITECH', 19.05)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-21 00:00:00.0000000', N'DLF', 140.75)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-21 00:00:00.0000000', N'UNITECH', 19.3)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-24 00:00:00.0000000', N'DLF', 147.35)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-24 00:00:00.0000000', N'UNITECH', 19.8)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-25 00:00:00.0000000', N'DLF', 141.75)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-25 00:00:00.0000000', N'UNITECH', 18.2)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-26 00:00:00.0000000', N'DLF', 152.15)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-26 00:00:00.0000000', N'UNITECH', 19)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-27 00:00:00.0000000', N'DLF', 148.95)
INSERT [Table_1] (xdate, symbol, price)
  VALUES ('2014-11-27 00:00:00.0000000', N'UNITECH', 19.55)
GO


--Now see data in Table_1
SELECT
  *
FROM Table_1
GO

Thanks again for your help. I appreciate it a lot.

With my best regards

happy1001 · Apr 9, 2015

Although I am not sure, but can we use something like this for doing the WEEKLY calculation, by selecting top 7 dates from within the table.
Code:
WHERE
  xdate >= (select min(ts) from (select distinct top 7 xdate as ts
  from [Table_1]
  order by xdate desc )
And similarly for Monthly we could use top 30 dates and so on.

In this method we might not need to use the variables.

Thanks and regards

M.ST. · Apr 9, 2015

"If you can't do that, then your code is too complex"

And/or it is very badly commented or not commented at all.

grashed said:
I would not try to do this in SQL. Can you do it, yes, but should you do it. No. Here's why: SQL is designed to retrieve data, not do calculations. Because there was a demand to do calculations in SQL, Microsoft and Oracle added it on as an afterthough. The result of that is you'll be forced to build code that is really unintuitive and complex to understand for a calculation that should be straightforward. My recommendation is to take the data and process it in something that is relatively straightforward like c# or Java. Now, sometimes when your beginning programming, you worry about doing things the "right way". Don't worry about that. Instead, do things the way that makes everything as simple as possible. And by that I mean, ask yourself this question "If I got drunk tomorrow, and I had no clue what I coded up last night, could I look at my code with blurry eyes and understand exactly what was going on ?" If you can't do that, then your code is too complex.
More...