SQL - Daily/weekly/monthly % Change in Price - futures io
futures io futures trading



SQL - Daily/weekly/monthly % Change in Price


Discussion in Platforms and Indicators

Updated
      Top Posters
    1. looks_one anny with 5 posts (2 thanks)
    2. looks_two Hulk with 2 posts (3 thanks)
    3. looks_3 Quick Summary with 1 posts (0 thanks)
    4. looks_4 sam028 with 1 posts (1 thanks)
    1. trending_up 951 views
    2. thumb_up 6 thanks given
    3. group 2 followers
    1. forum 8 posts
    2. attach_file 0 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

SQL - Daily/weekly/monthly % Change in Price

(login for full post details)
  #1 (permalink)
Chandigarh India
 
Experience: Intermediate
Platform: TradeStation, Multicharts
Trading: Stocks
 
Posts: 20 since Jun 2013
Thanks: 26 given, 7 received

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

Started this thread Reply With Quote

Journal Challenge February 2021 results (so far):
Competing for $1500 in prizes from Topstep
looks_oneSBtrader82 's Trading Journalby SBtrader82
(170 thanks from 31 posts)
looks_twoJust BEING a Trader: Letting Go!!by iqgod
(116 thanks from 33 posts)
looks_3Wisdom is Emptinessby Mtype
(68 thanks from 25 posts)
looks_4Deetee’s DAX Trading Journal (time based)by Deetee
(31 thanks from 17 posts)
looks_5Journal for peanuts1956by peanuts1956
(23 thanks from 13 posts)
 
 
(login for full post details)
  #3 (permalink)
Chandigarh India
 
Experience: Intermediate
Platform: TradeStation, Multicharts
Trading: Stocks
 
Posts: 20 since Jun 2013
Thanks: 26 given, 7 received


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(255NULL,
  
xdate DATETIME2(0NULL,
  
price FLOAT NULL
ON [PRIMARY]
GO


--Insert data in Table_1
SET DATEFORMAT ymd
SET ARITHABORT
ANSI_PADDINGANSI_WARNINGSCONCAT_NULL_YIELDS_NULLQUOTED_IDENTIFIERANSI_NULLSNOCOUNT ON
SET NUMERIC_ROUNDABORT
IMPLICIT_TRANSACTIONSXACT_ABORT OFF
GO

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

Started this thread Reply With Quote
The following user says Thank You to anny for this post:
 
(login for full post details)
  #4 (permalink)
Site Moderator
 
 
sam028's Avatar
 
Posts: 3,665 since Jun 2009
Thanks: 3,786 given, 4,494 received

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

Success requires no deodorant! (Sun Tzu)
Follow me on Twitter Reply With Quote
The following user says Thank You to sam028 for this post:
 
(login for full post details)
  #5 (permalink)
Texas, USA
 
Experience: Advanced
Platform: TT Pro, Custom
Broker: dxFeed
Trading: Futures, Spreads
 
Hulk's Avatar
 
Posts: 249 since May 2014
Thanks: 596 given, 665 received


anny View Post
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

Visit my futures io Trade Journal Reply With Quote
The following 2 users say Thank You to Hulk for this post:
 
(login for full post details)
  #6 (permalink)
Chandigarh India
 
Experience: Intermediate
Platform: TradeStation, Multicharts
Trading: Stocks
 
Posts: 20 since Jun 2013
Thanks: 26 given, 7 received


Hulk View Post
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(priceOVER (ORDER BY xdate) AS "lastclose"
FROM Table_1
WHERE symbol IN 
('dlf')



SELECT
  xdate
,
  
symbol,
  
price,
  
FIRST_VALUE(priceOVER (PARTITION BY DATEPART(WEEKxdateORDER BY xdate) AS "weekbeginprice"
FROM Table_1
WHERE symbol IN 
('dlf')


SELECT
  xdate
,
  
symbol,
  
price,
  
FIRST_VALUE(priceOVER (PARTITION BY DATEPART(MONTHxdateORDER 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

Started this thread Reply With Quote
 
(login for full post details)
  #7 (permalink)
Chandigarh India
 
Experience: Intermediate
Platform: TradeStation, Multicharts
Trading: Stocks
 
Posts: 20 since Jun 2013
Thanks: 26 given, 7 received


sam028 View Post
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.

Started this thread Reply With Quote
 
(login for full post details)
  #8 (permalink)
Texas, USA
 
Experience: Advanced
Platform: TT Pro, Custom
Broker: dxFeed
Trading: Futures, Spreads
 
Hulk's Avatar
 
Posts: 249 since May 2014
Thanks: 596 given, 665 received


anny View Post
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 View Post
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?

Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to Hulk for this post:
 
(login for full post details)
  #9 (permalink)
Chandigarh India
 
Experience: Intermediate
Platform: TradeStation, Multicharts
Trading: Stocks
 
Posts: 20 since Jun 2013
Thanks: 26 given, 7 received


Hulk View Post
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

Started this thread Reply With Quote
The following user says Thank You to anny for this post:


futures io Trading Community Platforms and Indicators > [Other]       SQL - Daily/weekly/monthly % Change in Price


Last Updated on April 9, 2015


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
 

Journal Challenge w/$1500 prizes from Topstep!

February
 

Battlestations! Show us your trading desk - $1,500 in prizes!

March
 

Call Option Buying: The New Pain Trade? w/Carley Garner

Elite only
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts