Solved

c# sql I need a way to calculate moving averages from a table with stock prices

Posted on 2013-01-15
6
429 Views
Last Modified: 2013-01-18
I have a table of stock price with date, symbol, close and I need moving averages.

For instance, on 1/1/2012 I will need the 20 day, 10 day, and 5 day moving average.  I was originally going to just do individual sql calls for each, but I was thinking that would be really slow.  I'd rather read all the values in first then calculate values, but I am not sure the best way.

Also, when I say 20 day moving average, I need the last 20 values, not the calendar days.  There will be no values on the weekends and holidays.  

I am not sure how to structure this, but I would like to be able to make a call like:

GetMovingAverage("IBM", 20 (day), 1/1/2012)
GetMovingAverage("IBM", 10 (day), 1/1/2012)
GetMovingAverage("GOOG", 20 (day), 2/2/2010)
0
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 38781682
Perhaps you can use linq to retrieve the latest x-numbers in your set

from m in MyTable
take 10
select m.Foo
0
 
LVL 4

Expert Comment

by:Veerabhadraiahhv
ID: 38781899
0
 
LVL 18

Expert Comment

by:deighton
ID: 38782092
here is an example for you in SQL

DECLARE @DAYS_AVERAGE int;
SET @DAYS_AVERAGE = 5;

WITH Sock_Prices_Ranked AS
(
	SELECT Company, Date, Price, row_number() OVER (PARTITION BY Company ORDER BY date) AS DateRank FROM StockPrices
)

SELECT S1.COMPANY, S1.DATE, AVG(S2.Price) AS PriceMovingAverage FROM Sock_Prices_Ranked S1	
						JOIN 
						Sock_Prices_Ranked S2
						ON S1.Company = S2.Company
						AND S2.DateRank <= S1.DateRank AND
							S2.DateRank > S1.DateRank - @DAYS_AVERAGE
						WHERE S1.DateRank >= @DAYS_AVERAGE 							
						GROUP BY S1.COMPANY,S1.DATE

Open in new window


and the table I used

/****** Object:  Table [dbo].[StockPrices]    Script Date: 01/16/2013 10:44:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StockPrices](
	[Company] [nvarchar](20) NOT NULL,
	[date] [datetime] NOT NULL,
	[price] [decimal](10, 2) NOT NULL,
 CONSTRAINT [PK_StockPrices] PRIMARY KEY CLUSTERED 
(
	[Company] ASC,
	[date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FCB00000000 AS DateTime), CAST(1500.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FCC00000000 AS DateTime), CAST(1502.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FCD00000000 AS DateTime), CAST(1509.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FCE00000000 AS DateTime), CAST(1508.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FCF00000000 AS DateTime), CAST(1499.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FD000000000 AS DateTime), CAST(1490.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FD100000000 AS DateTime), CAST(1540.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FD200000000 AS DateTime), CAST(1541.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'IBM', CAST(0x00009FD300000000 AS DateTime), CAST(1520.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FCB00000000 AS DateTime), CAST(51.72 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FCC00000000 AS DateTime), CAST(51.79 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FCD00000000 AS DateTime), CAST(52.03 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FCE00000000 AS DateTime), CAST(52.00 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FCF00000000 AS DateTime), CAST(51.69 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FD000000000 AS DateTime), CAST(51.38 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FD100000000 AS DateTime), CAST(53.10 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FD200000000 AS DateTime), CAST(53.14 AS Decimal(10, 2)))
INSERT [dbo].[StockPrices] ([Company], [date], [price]) VALUES (N'TFL', CAST(0x00009FD300000000 AS DateTime), CAST(52.41 AS Decimal(10, 2)))

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 18

Expert Comment

by:deighton
ID: 38782200
also, to select all the dates  and companies you need use the select conditions below

number of days for moving average is a variable that you could supply as a parmeter, in this case I have used 5 days

DECLARE @DAYS_AVERAGE int;
SET @DAYS_AVERAGE = 5;

WITH Sock_Prices_Ranked AS
(
	SELECT Company, Date, Price, row_number() OVER (PARTITION BY Company ORDER BY date) AS DateRank FROM StockPrices
)

SELECT S1.COMPANY, S1.DATE, AVG(S2.Price) AS PriceMovingAverage FROM Sock_Prices_Ranked S1	
						JOIN 
						Sock_Prices_Ranked S2
						ON S1.Company = S2.Company
						AND S2.DateRank <= S1.DateRank AND
							S2.DateRank > S1.DateRank - @DAYS_AVERAGE
						WHERE S1.DateRank >= @DAYS_AVERAGE 	
							--SELECT PARTICULAR COPANIES AND DATES HERE
							AND S1.Company = 'IBM' AND S1.DATE BETWEEN '2012-01-06' AND '2012-01-08' 						
						GROUP BY S1.COMPANY,S1.DATE
						
						 						

Open in new window

0
 

Author Comment

by:jackjohnson44
ID: 38784963
Thanks a lot Deighton, can you please explain that a little?  I am not understanding what is going on.

Thanks!
0
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 38786713
the with statement is a way of creating a kind of view or temporary table.  In this case I wanted to number each row, starting at 1 for the earliest date, and also I wanted to number separately for each stock, so for IBM I have these rows numbered 1,2,3,4,5,6.... by date order, and company XYZ, 1,2,3,4,5  etc

so I then have a so called 'Common Term Expression' (CTE) which I call 'Sock_Prices_Ranked'

I can then query this as if it is a regular table.  What I've done is joined it to itself, with each row joined to the previous 5 rows for the stock (inclusive of the current row).  I can then use group by to calculate the average of the previous 5 days stock prices on that day.

Finally I only show results for cases where there are 5 days of stock prices to average (so you don't get a 5 day moving average until day 5)

The query below shows the rows that are selected for each date row.

DECLARE @DAYS_AVERAGE int;
SET @DAYS_AVERAGE = 5;

WITH Sock_Prices_Ranked AS
(
	SELECT Company, Date, Price, row_number() OVER (PARTITION BY Company ORDER BY date) AS DateRank FROM StockPrices
)

SELECT S1.COMPANY, S1.DATE, s2.date as priordate, S2.Price  FROM Sock_Prices_Ranked S1	
						JOIN 
						Sock_Prices_Ranked S2
						ON S1.Company = S2.Company
						AND S2.DateRank <= S1.DateRank AND
							S2.DateRank > S1.DateRank - @DAYS_AVERAGE
						WHERE S1.DateRank >= @DAYS_AVERAGE 	
							--SELECT PARTICULAR COPANIES AND DATES HERE
							AND S1.Company = 'IBM' AND S1.DATE BETWEEN '2012-01-06' AND '2012-01-08' 						
						ORDER  BY S1.COMPANY,S1.DATE,s2.date

Open in new window

0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question