Solved

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

Posted on 2013-01-15
6
423 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now