SQL - Daily/weekly/monthly % Change in Price

April 8th, 2015, 06:17 AM

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

April 9th, 2015, 10:54 AM

Here is the script that will create the Sample DB and Table used in this work.

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

Regards

April 9th, 2015, 04:30 PM

That's insert values in a table, I don't see the "query to calculate the Daily/weekly/monthly % Change in Price", no?

April 9th, 2015, 05:03 PM

anny

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

I havent run these so please fix any syntax issues but this should get you what you want.

This gives you the current day's price and the previous days price in the same row.

Code

select xdate, 
         symbol, 
         price, 
         lag(price) over (order by xdate) as "lastclose" 
from Table_1 
where symbol = '<your symbol>'

This gives you the current day's price and the price at the beginning of the week in the same row.

Code

select xdate, 
         symbol, 
         price, 
         first_value(price) over (partition by DATEPART(WEEK, xdate) order by xdate) as "weekbeginprice" 
from Table_1 
where symbol = '<your symbol>'

This gives you the current day's price and the price at the beginning of the month in the same row.

Code

select xdate, 
          symbol, 
          price, 
          first_value(price) over (partition by DATEPART(month, xdate) order by xdate) as "monthbeginprice" 
from Table_1 
where symbol = '<your symbol>'

For further reference: https://msdn.microsoft.com/en-us/library/hh213234.aspx

April 9th, 2015, 06:31 PM

Hulk

I havent run these so please fix any syntax issues but this should get you what you want.

This gives you the current day's price and the previous days price in the same row.

Code

select xdate, 
         symbol, 
         price, 
         lag(price) over (order by xdate) as "lastclose" 
from Table_1 
where symbol = '<your symbol>'

This gives you the current day's price and the price at the beginning of the week in the same row.

Code

select xdate, 
         symbol, 
         price, 
         first_value(price) over (partition by DATEPART(WEEK, xdate) order by xdate) as "weekbeginprice" 
from Table_1 
where symbol = '<your symbol>'

This gives you the current day's price and the price at the beginning of the month in the same row.

Code

select xdate, 
          symbol, 
          price, 
          first_value(price) over (partition by DATEPART(month, xdate) order by xdate) as "monthbeginprice" 
from Table_1 
where symbol = '<your symbol>'

For further reference: https://msdn.microsoft.com/en-us/library/hh213234.aspx

Thank you so much for your help Hulk. Your code works correctly for giving the previous values for the day, week and month respectively.

There is one issue though. When I put just one symbol in the where condition as this -
WHERE symbol IN ('dlf')
then it works fine.

but if I put both the symbols from that table into the statement like this -
WHERE symbol IN ('dlf', 'unitech')
then it just shows the output for first symbol only,

Here is the script -

Code

                            
SELECT

  xdate,

  symbol,

  price,

  LAG(price) OVER (ORDER BY xdate) AS "lastclose"

FROM Table_1

WHERE symbol IN ('dlf')







SELECT

  xdate,

  symbol,

  price,

  FIRST_VALUE(price) OVER (PARTITION BY DATEPART(WEEK, xdate) ORDER BY xdate) AS "weekbeginprice"

FROM Table_1

WHERE symbol IN ('dlf')





SELECT

  xdate,

  symbol,

  price,

  FIRST_VALUE(price) OVER (PARTITION BY DATEPART(MONTH, xdate) ORDER BY xdate) AS "monthbeginprice"

FROM Table_1

WHERE symbol IN ('dlf')





-- WHERE symbol IN ('dlf', 'unitech')

And I was wondering if there is any Direct FUNCTION available in SQL Server that can do the % Change Calculations directly. Is there no such function available in it ? Microsoft has such a big library of Functions for the Excel, but SQL Server seems to have very less functions available for the Stock Market Guys.

Thanks again for your help.

With my best regards

April 9th, 2015, 06:32 PM

sam028

That's insert values in a table, I don't see the "query to calculate the Daily/weekly/monthly % Change in Price", no?

You are correct sam. That script just creates the test table.

April 9th, 2015, 06:43 PM

anny

There is one issue though. When I put just one symbol in the where condition as this -
WHERE symbol IN ('dlf')
then it works fine.

but if I put both the symbols from that table into the statement like this -
WHERE symbol IN ('dlf', 'unitech')
then it just shows the output for first symbol only,
With my best regards

You can change the "order by xdate" clause to "order by symbol, xdate" and the partition by clause to "partition by symbol, DATEPART..." if you want to run the query for multiple symbols at the same time.

anny

And I was wondering if there is any Direct FUNCTION available in SQL Server that can do the % Change Calculations directly. Is there no such function available in it ? Microsoft has such a big library of Functions for the Excel, but SQL Server seems to have very less functions available for the Stock Market Guys.

Thanks again for your help.

With my best regards

I have no idea. But now that you have the correct data/SQL query, the calculation of % change should be simple enough. Why are you still looking for a function?

April 9th, 2015, 07:58 PM

Hulk

You can change the "order by xdate" clause to "order by symbol, xdate" and the partition by clause to "partition by symbol, DATEPART..." if you want to run the query for multiple symbols at the same time.

I have no idea. But now that you have the correct data/SQL query, the calculation of % change should be simple enough. Why are you still looking for a function?

Thanks for the clarification Hulk. I will do those changes in the script.

I am looking for taking help from Functions because I have a lot more work to do in this direction. And if I will need to write the special codes for doing all these basic calculations for the Stock Market Analysis work, then that would make it extremely time consuming and complex. While doing some searching in this regards, I have found one software - XLeratorDB - Financial Analytics Package, which seems to have some tools in this regards.

If anyone is familiar about anything that helps in doing stock market related analysis easily on a stock market database that is available in SQL Server or My-SQL etc. then please suggest the same. Because that would have a lot of such % changes type of stuff and some technical analysis calculations per-built and one will not have to write the custom codes for the same.

Thanks a lot for your help

SQL - Daily/weekly/monthly % Change in Price

Discussion in Platforms and Indicators

SQL - Daily/weekly/monthly % Change in Price