Dear Gabriel
Following my previous post, below is my attempt to solve the following problem:
Calculating a single ema on a table of data that contains the closing price of approximately 15000 securities.
As a newbie, I have tried to use your code, but am experiencing the following problem.
The ema requires the calculation of the sma.
Therefore, if I'm calculating a 12period ema, I would first have to calculate the 12 period sma on the 13th day. This is achieved by:
---Capture the averages prior to the start of the EMA interval period
select @initial_sma = AVG(case
when n<@ema_intervals then tClose
else null
end
)
FROM zpData_mod
WHERE n<@ema_intervals
However, when there are multiple securities, the above code calculates the average for n<12 for ALL securities.
I have tried to solve this by grouping
---the grouping doesn't work
---group by instrumentid
GROUP BY instrumentid
Below, I have all the code:
DROP TABLE zpData_mod
---=====Setup working table for EMAs
SELECT ROW_NUMBER() OVER (PARTITION BY InstrumentID ORDER BY tdate)n
,InstrumentID
,tDate
,tClose
,CAST (NULL AS DECIMAL(10,5)) [EMA12]
INTO zpData_mod
FROM zpData
CREATE CLUSTERED INDEX ix_n ON zpData_mod (InstrumentID, tdate)
---declare variables needed
declare @ema_intervals int, @k1 decimal(4,3)
declare @prev_instrumentid int
declare @prev_ema decimal(9,4), @initial_sma decimal(9,4)
declare @anchor int
---Setup the default intervals
set @ema_intervals =12
set @k1 = 2/(1+@ema_intervals+.000)
set @prev_instrumentid=0
---Capture the averages prior to the start of the EMA interval period
select @initial_sma = AVG(case
when n<@ema_intervals then tClose
else null
end
)
FROM zpData_mod
WHERE n<@ema_intervals
---the grouping doesn't work
---group by instrumentid
GROUP BY instrumentid
---Carry over update statement
update t1
---case statement to handle @moving_sum variable
---depending on the value of n
set
@prev_ema=case when n<@ema_intervals then null
when n=@ema_intervals then t1.tClose*@k1 +@initial_sma*(1-@k1)
when n>@ema_intervals then t1.tClose*@k1 +@prev_ema*(1-@k1)
end,
ema12=@prev_ema,
@anchor =N ---anchor so that the carryover works
FROM zpData_mod t1 with (tablockx)
option (maxdop 1)
select N, tDate
,instrumentid
,tClose
,ema12
FROM zpData_mod