[Other] SQL - Daily/weekly/monthly % Change in Price - Platforms and Indicators | futures io social day trading
futures io futures trading


SQL - Daily/weekly/monthly % Change in Price
Updated: Views / Replies:560 / 8
Created: by anny Attachments:0

Welcome to futures io.

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

futures io is the largest futures trading community on the planet, with over 90,000 members. At futures io, our goal has always been and always will be to create a friendly, positive, forward-thinking community where members can openly share and discuss everything the world of trading has to offer. The community is one of the friendliest you will find on any subject, with members going out of their way to help others. Some of the primary differences between futures io and other trading sites revolve around the standards of our community. Those standards include a code of conduct for our members, as well as extremely high standards that govern which partners we do business with, and which products or services we recommend to our members.

At futures io, our focus is on quality education. No hype, gimmicks, or secret sauce. The truth is: trading is hard. To succeed, you need to surround yourself with the right support system, educational content, and trading mentors Ė all of which you can find on futures io, utilizing our social trading environment.

With futures io, you can find honest trading reviews on brokers, trading rooms, indicator packages, trading strategies, and much more. Our trading review process is highly moderated to ensure that only genuine users are allowed, so you donít need to worry about fake reviews.

We are fundamentally different than most other trading sites:
  • We are here to help. Just let us know what you need.
  • We work extremely hard to keep things positive in our community.
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts.
  • We firmly believe in and encourage sharing. The holy grail is within you, we can help you find it.
  • We expect our members to participate and become a part of the community. Help yourself by helping others.

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

Reply
 
Thread Tools Search this Thread
 

SQL - Daily/weekly/monthly % Change in Price

  #1 (permalink)
Elite Member
Chandigarh India
 
Futures Experience: Intermediate
Platform: TradeStation, Multicharts
Favorite Futures: Stocks
 
Posts: 19 since Jun 2013
Thanks: 26 given, 7 received

SQL - Daily/weekly/monthly % Change in Price

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

Reply With Quote
 
  #2 (permalink)
Quick Summary
Quick Summary Post

Quick Summary is created and edited by users like you... Add FAQ's, Links and other Relevant Information by clicking the edit button in the lower right hand corner of this message.

 
  #3 (permalink)
Elite Member
Chandigarh India
 
Futures Experience: Intermediate
Platform: TradeStation, Multicharts
Favorite Futures: Stocks
 
Posts: 19 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

Reply With Quote
The following user says Thank You to anny for this post:
 
  #4 (permalink)
Administrator: Retired Backtester
 Vendor: speedytradingservers.com 
Rennes France
 
Futures Experience: Advanced
Platform: NinjaTrader
Broker/Data: IB/Kinetick
Favorite Futures: Futures
 
sam028's Avatar
 
Posts: 3,366 since Jun 2009
Thanks: 3,571 given, 3,982 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)
Reply With Quote
The following user says Thank You to sam028 for this post:
 
  #5 (permalink)
Elite Member
Texas, USA
 
Futures Experience: Intermediate
Platform: X_TRADER Pro, Custom
Broker/Data: NxCore
Favorite Futures: Futures, Spreads
 
Hulk's Avatar
 
Posts: 215 since May 2014
Thanks: 509 given, 474 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

Reply With Quote
The following 2 users say Thank You to Hulk for this post:
 
  #6 (permalink)
Elite Member
Chandigarh India
 
Futures Experience: Intermediate
Platform: TradeStation, Multicharts
Favorite Futures: Stocks
 
Posts: 19 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

Reply With Quote
 
  #7 (permalink)
Elite Member
Chandigarh India
 
Futures Experience: Intermediate
Platform: TradeStation, Multicharts
Favorite Futures: Stocks
 
Posts: 19 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.

Reply With Quote
 
  #8 (permalink)
Elite Member
Texas, USA
 
Futures Experience: Intermediate
Platform: X_TRADER Pro, Custom
Broker/Data: NxCore
Favorite Futures: Futures, Spreads
 
Hulk's Avatar
 
Posts: 215 since May 2014
Thanks: 509 given, 474 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?

Reply With Quote
The following user says Thank You to Hulk for this post:
 
  #9 (permalink)
Elite Member
Chandigarh India
 
Futures Experience: Intermediate
Platform: TradeStation, Multicharts
Favorite Futures: Stocks
 
Posts: 19 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

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

Reply



futures io > > > [Other]       SQL - Daily/weekly/monthly % Change in Price

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Upcoming Webinars and Events (4:30PM ET unless noted)

Linda Bradford Raschke: Reading The Tape

Elite only

Adam Grimes: TBA

Elite only

NinjaTrader: TBA

January

Ran Aroussi: TBA

Elite only
     

Similar Threads
Thread Thread Starter Forum Replies Last Post
Daily/weekly/monthly Reports on Market fundamentals nemeis45 Traders Hideout 2 March 28th, 2016 10:33 PM
extend weekly and monthly arnie Sierra Chart 5 September 17th, 2013 05:35 PM
CL RTH for Daily, Weekly, Monthly Charts fawn trader NinjaTrader 4 April 27th, 2011 05:13 AM
can daily&weekly indy work from monthly SMA in MA? nt6.5 mystiq NinjaTrader 4 February 27th, 2011 11:40 AM
Euro on the monthly with a peek at the weekly traderlars The Elite Circle 2 May 16th, 2010 06:45 PM


All times are GMT -4. The time now is 07:31 AM.

Copyright © 2017 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
Page generated 2017-12-11 in 0.15 seconds with 19 queries on phoenix via your IP 54.226.132.197