Calculate Moving Average
-
I was wondering if it's possible to calculate a simple moving average (SMA) or even better an exponential moving average (EMA) using TSQL. I know I can do this with a cursor, but was wondering if it's possible using single query. Here's some DDL & desired results.
CREATE TABLE Stocks
(
TradeDate datetime NOT NULL,
TradePrice int
)
GO
INSERT INTO Stocks SELECT '1/1/2005', 10
INSERT INTO Stocks SELECT '1/2/2005', 12
INSERT INTO Stocks SELECT '1/3/2005', 13
INSERT INTO Stocks SELECT '1/4/2005', 14
INSERT INTO Stocks SELECT '1/5/2005', 11
INSERT INTO Stocks SELECT '1/8/2005', 9
INSERT INTO Stocks SELECT '1/9/2005', 8
INSERT INTO Stocks SELECT '1/10/2005', 7
INSERT INTO Stocks SELECT '1/11/2005', 7
INSERT INTO Stocks SELECT '1/15/2005', 10
INSERT INTO Stocks SELECT '1/16/2005', 9
INSERT INTO Stocks SELECT '1/17/2005', 8
GO
SELECT TradeDate, TradePrice, TenDaySMA
FROM Stocks
'1/1/2005', 10, NULL
'1/2/2005', 12, NULL
'1/3/2005', 13, NULL
'1/4/2005', 14, NULL
'1/5/2005', 11, NULL
'1/8/2005', 9, NULL
'1/9/2005', 8, NULL
'1/10/2005', 7, NULL
'1/11/2005', 7, NULL
'1/15/2005', 10, 10.1
'1/16/2005', 9, 10
'1/17/2005', 8, 9.6
David -
I quickly threw together a weekly average. Maybe this will help you figure out a ten day moving average.
CREATE TABLE #Stocks( TradeDate datetime NOT NULL,
TradePrice integer)
GO
INSERT INTO #Stocks SELECT '1/1/2005', 10
INSERT INTO #Stocks SELECT '1/2/2005', 12
INSERT INTO #Stocks SELECT '1/3/2005', 13
INSERT INTO #Stocks SELECT '1/4/2005', 14
INSERT INTO #Stocks SELECT '1/5/2005', 11
INSERT INTO #Stocks SELECT '1/8/2005', 9
INSERT INTO #Stocks SELECT '1/9/2005', 8
INSERT INTO #Stocks SELECT '1/10/2005', 7
INSERT INTO #Stocks SELECT '1/11/2005', 7
INSERT INTO #Stocks SELECT '1/15/2005', 10
INSERT INTO #Stocks SELECT '1/16/2005', 9
INSERT INTO #Stocks SELECT '1/17/2005', 8
GO
SELECT CONVERT( varchar, a.TradeDate, 101) AS TradeDate,
DATEPART( week, a.TradeDate) AS WeekNumber,
a.TradePrice,
(b.SumTradePrice / b.CountTradePrice) AS MovingWeeklyAverage
FROM #Stocks a
INNER JOIN( SELECT SUM( CONVERT( decimal(18,2), TradePrice)) AS SumTradePrice,
COUNT( TradePrice) AS CountTradePrice,
DATEPART( week, TradeDate) AS WeeklyAve
FROM #Stocks
GROUP BY DATEPART( week, TradeDate)) b
ON( DATEPART( week, a.TradeDate) = b.WeeklyAve)
ORDER BY a.TradeDate
DROP TABLE #Stocks
I wasn't born stupid - I had to study.
-
/* If you have some kind of identity it will be easy to manipulate */
/* If you want average in between dates it would be lot easier */
SET NOCOUNT ON
DECLARE @stocks TABLE
(
TradeDate datetime NOT NULL,
TradePrice NUMERIC(10, 2)
)
INSERT INTO @stocks SELECT '1/1/2005', 10
INSERT INTO @stocks SELECT '1/2/2005', 12
INSERT INTO @stocks SELECT '1/3/2005', 13
INSERT INTO @stocks SELECT '1/4/2005', 14
INSERT INTO @stocks SELECT '1/5/2005', 11
INSERT INTO @stocks SELECT '1/8/2005', 9
INSERT INTO @stocks SELECT '1/9/2005', 8
INSERT INTO @stocks SELECT '1/10/2005', 7
INSERT INTO @stocks SELECT '1/11/2005', 7
INSERT INTO @stocks SELECT '1/15/2005', 10
INSERT INTO @stocks SELECT '1/16/2005', 9
INSERT INTO @stocks SELECT '1/17/2005', 8
SELECT A.TradeDate, A.TradePrice,
MovingAverageTenDays =
CASE WHEN A.CTR <= 9 THEN NULL ELSE
(SELECT AVG(B.TradePrice )
FROM (
SELECT COUNT(*) Ctr, A.TradeDate, MIN(A.TradePrice) TradePrice
FROM
@Stocks A
JOIN
@Stocks B
ON A.TradeDate >= B.TradeDate
GROUP BY A.TradeDate) AS B
WHERE
A.Ctr - B.Ctr BETWEEN 0 AND 9)
END
FROM (
SELECT COUNT(*) Ctr, A.TradeDate, MIN(A.TradePrice) TradePrice
FROM
@Stocks A
JOIN
@Stocks B
ON A.TradeDate >= B.TradeDate
GROUP BY A.TradeDate) AS A
ORDER BY A.TradeDate
Regards,
gova -
What are the sample results for the exponential moving results?
-
To be honest I don't know what is exponential moving results. Can you explain that pls.
Regards,
gova -
SET NOCOUNT ON
DECLARE @stocks TABLE( TradeDate datetime NOT NULL,TradePrice decimal(10,2))
INSERT INTO @stocks SELECT '1/1/2005', 10
INSERT INTO @stocks SELECT '1/2/2005', 12
INSERT INTO @stocks SELECT '1/3/2005', 13
INSERT INTO @stocks SELECT '1/4/2005', 14
INSERT INTO @stocks SELECT '1/5/2005', 11
INSERT INTO @stocks SELECT '1/8/2005', 9
INSERT INTO @stocks SELECT '1/9/2005', 8
INSERT INTO @stocks SELECT '1/10/2005', 7
INSERT INTO @stocks SELECT '1/11/2005', 7
INSERT INTO @stocks SELECT '1/15/2005', 10
INSERT INTO @stocks SELECT '1/16/2005', 9
INSERT INTO @stocks SELECT '1/17/2005', 8
--For SIMPLE MOVING AVERAGE you MIGHT use this one
--returns NULL because in sample data you don't have 10 consecutive days
Select a.TradeDate,count(*),AVG(b.TradePrice), case when count(*)<10 then NULL else AVG(b.TradePrice) end
ON cast(a.tradedate as int)+10>=cast(b.tradedate as int) and cast(a.tradedate as int)<=cast(b.tradedate as int)
GROUP BY a.TradeDate
Kindest Regards,Vasc
-
Ther will never be 10 consecutive days Vasc. We have holidays when there is no trade. So we have to take available dates only.
Regards,
gova -
Very nice solution though...
-
Holidays .... that's nice to have .... : )
I knew about missing days (even from sample) data but i just wanted to point that if he picks a Certain no of days he can live with this solution (which has a nice execution plan )
For example I could say pick from last 10 days .If I don't have 6 values don't consider result.
Picking 10 records MIGHT have a draw back : You can pick really old values : ) which are irrelevant for the average.
Kindest Regards,Vasc
-
That would be a nice condition to add... and datediff (D, minDate, MaxDate) < 15?
-
Thanx for the info... but that was just a mere exemple of the condition
. -
And actually I usually use between or @Date2 whenever possible. I try to avoid calculation on fields like the pest... or whereever possible
. -
Suppose you have a table with 3 columns ISIN as internationally recognized identifier, date of the quotation, and the closing quote, you can do
select
isin, date, close,
(select avg(close)
from your_table t2
where t2.isin = t1.isin
and t2.date between dateadd(dd, -38, t1.date) and t1.date
 
as moving_averagefrom your_table t1
Presumably you put an index on (isin, date), but don't expect terrific fast performance.
Now since I do this kind of stuff every day, let me tell you, I really wouldn't do this at the server. For technical analysis, it is quite common to look at several moving average intervals. Among the most commonly used is the 10-days, 30-days, and 200-days (or even 250 days) moving average. Suppose you have a portfolio of only 30 different stocks and assume that you surely don't want to analyse only one single stock isolated, but rather compare it to either some other stock or some other benchmark like an index. Guess what the performance will look like then. Just deliver your resultset to the client, and let them do this kind of analysis.
Just my $0.02 cents anyway.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just noticed that you posted DDL.
select
TradeDate, TradePrice,
(select avg(TradePrice*1.0)
from Stocks t2
where t2.TradeDate between dateadd(dd, -10, t1.TradeDate) and t1.TradeDate
 
as moving_averagefrom Stocks t1
Generally you wouldn't create the stock price as INT. So I've added this multiplication *1.0 to force the conversion away from INT. And you need to consider what happens during holidays and banking holidays. Probably the easiest solution here, is to add artificially the last known quote via a job.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
May I suggest that you compute the AVG at "INSERT Time" and then materialize those if the Number of AVG types is Fixed
* Noel
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply