Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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

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
jackjohnson44
Asked:
jackjohnson44
1 Solution
 
DhaestCommented:
Perhaps you can use linq to retrieve the latest x-numbers in your set

from m in MyTable
take 10
select m.Foo
0
 
VeerabhadraiahhvCommented:
0
 
deightonCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
deightonCommented:
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
 
jackjohnson44Author Commented:
Thanks a lot Deighton, can you please explain that a little?  I am not understanding what is going on.

Thanks!
0
 
deightonCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now