Solved

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

Posted on 2013-01-15
6
426 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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