?
Solved

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

Posted on 2013-01-15
6
Medium Priority
?
430 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 to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 
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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

762 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