February 27, 2012 at 3:21 am
with t1 as (select row_number() over (order by quote_date) n
,quote_date
,close_price
from #google_stock)
select a.n
,a.quote_date
,a.close_price
,CAST(null as decimal(8,2)) [sma]
--add the close_price from 20 row prior to this one
,CAST(b.close_price as decimal(8,2)) [20_day_old_close]
into #mod_goog_data
from t1 a
left join t1 b
on a.n - 20 = b.n
i keep having issue entering this into SQLserver... is this oracle statement? keep saying Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'as'.
August 8, 2012 at 4:11 pm
Dear Gabriel
Your article is outstanding!
I would like to ask how the code would need to be altered if instead of one security, the table contained data for many securities.
This is similar to the the bank balance problem that Jeff uses in his article, where he calculates the running total for each account (in this case the SMA for each security in the EMA problem).
Can you help?
Kind Regards
Stephen Poulitsis
August 8, 2012 at 5:59 pm
quartzier (2/27/2012)
with t1 as (select row_number() over (order by quote_date) n,quote_date
,close_price
from #google_stock)
select a.n
,a.quote_date
,a.close_price
,CAST(null as decimal(8,2)) [sma]
--add the close_price from 20 row prior to this one
,CAST(b.close_price as decimal(8,2)) [20_day_old_close]
into #mod_goog_data
from t1 a
left join t1 b
on a.n - 20 = b.n
i keep having issue entering this into SQLserver... is this oracle statement? keep saying Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'as'.
I know it's and old post and you might not be around anymore but do you have a semi-colon after the last statement before the WITH?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2012 at 5:16 am
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
August 9, 2012 at 7:09 am
stephen.poulitsis (8/8/2012)
Dear GabrielYour article is outstanding!
I would like to ask how the code would need to be altered if instead of one security, the table contained data for many securities.
This is similar to the the bank balance problem that Jeff uses in his article, where he calculates the running total for each account (in this case the SMA for each security in the EMA problem).
Can you help?
Kind Regards
Stephen Poulitsis
Hi Stephen,
Because of the iterative nature of the update statement in that each row depends on the row prior to it, there's not an apparent way that comes to mind on how to do this with multiple securities and still keep it all as one update statement.
If it's possible that you can access SQL Server 2012 for this business requirement, I would implore you to learn about the new features they have added to the windowing functions using ROWS/RANGE functionality should theoretically provide a better way to accomplish this. I haven't done any work with securities in a while however, so I cannot confirm it.
August 11, 2012 at 3:51 am
Dear Gabriel
Admittedly, utilising copy-paste to the hilt, I combined your solution with the technique used by Jeff Moden in his article "Solving the Running Total and Ordinal Rank Problems" - and came up with the following solution, which seems to work correctly. If you ask me what parts of the code are actually doing, embarrassingly I probably wouldn't be able to say!
Anyway, here goes:
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 numeric(12,5)) [tCloseRunSum]
,CAST (NULL AS INT) [InstrumentRunCount]
,CAST (NULL AS numeric(12,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_ema numeric(12,5), @initial_sma numeric(12,5)
declare @anchor int
DECLARE @PrevInstrumentID INT
DECLARE @tCloseRunSum numeric(12,5)
DECLARE @InstrumentRunCountINT
---Setup the default intervals
set @ema_intervals =12
set @k1 = 2/(1+@ema_intervals+.000)
--====Update the running total and running count using for this row
--using the "Quirky Update" and a "Pseudo-Cursor".
--The order of the UPDATE is controlled by the clustered index
UPDATE zpData_mod
SET @tCloseRunSum = tCloseRunSum = CASE
WHEN InstrumentID = @PrevInstrumentID
THEN @tCloseRunSum + tClose
ELSE tClose
END,
@InstrumentRunCount = InstrumentRunCount = CASE
WHEN InstrumentID = @PrevInstrumentID
THEN @InstrumentRunCount + 1
ELSE 1
END,
@initial_sma = ema12 = case when
N=@ema_intervals
then @tCloseRunSum/@InstrumentRunCount
else
null
end,
@PrevInstrumentID = InstrumentID
FROM zpData_mod WITH (TABLOCKX)
OPTION (MAXDOP 1)
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 tCloseRunSum/InstrumentRunCount
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 *
FROM zpData_mod
It is truly a privelege to be able to communicate with you!
Thank you
Stephen
Viewing 6 posts - 76 through 80 (of 80 total)
You must be logged in to reply to this topic. Login to reply