Calculate Moving Average

Question

Calculate Moving Average

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

sql92 SSC Enthusiast Points: 114 More actions · Answer 1

I if understand you right, would feel this is redundant data. Which is not to keep. Why you want to do so?

noeld SSC Guru Points: 96590 More actions · Answer 2

Is not that is redundant is that because you only have let's say AVG for lets say 10 , 20 and 200 spans it makes sense to compute ahead of time so that when you query the data is already materialized wich will mean a blindly fast respose

* Noel

sql92 SSC Enthusiast Points: 114 More actions · Answer 3

I see, but u need another column for AVG, don't u?

noeld SSC Guru Points: 96590 More actions · Answer 4

noeld

SSC Guru

Points: 96590

June 24, 2005 at 12:56 pm

#568373

sure! you are materializing it

* Noel

sql92 SSC Enthusiast Points: 114 More actions · Answer 5

Performancewise this sounds good noeld

Thank u for explanation.

michael mchaney Grasshopper Points: 19 More actions · Answer 6

Heres so people get the idea of what exponential moving averages is all about,

http://www.stockcharts.com/education/IndicatorAnalysis/indic_movingAvg.html

I think we are looking for a moving average over a period of time, I would suppose 20 days. Calculating the simple moving average is easy, but as far as EMA,... Its a good puzzle

noeld SSC Guru Points: 96590 More actions · Answer 7

Not a big deal if you do it at INSERT time

* Noel

michael mchaney Grasshopper Points: 19 More actions · Answer 8

OK so heres where my mind is going in circles,.. any help will be appreciated.

Below is a table with the results of an exponential moving average calculation for Eastman Kodak. For the first period's exponential moving average, the simple moving average was used as the previous period's exponential moving average (yellow highlight for the 10th period). From period 11 onwards, the previous period's EMA was used. The calculation in period 11 breaks down as follows:

(C - P) = (61.33 - 63.682) = -2.352
(C - P) x K = -2.352 x .181818 = -0.4276
((C - P) x K) + P = -0.4276 + 63.682 = 63.254

Now I am wondering how, in a nutshell, some of you would go about getting this data. We would take the 10 day EMA and populate it into a detail table. I am trying to accomplish this without a cursor. Can anyone think of a way to get this with a view? I'm gonna keep stabbing at it and if I answer my own questions SO BE IT!!

BE NICE HELP A NEW-B!!!

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 9

If you will be using this information often I would create a table fo EMA and store the data in that table not a temp table.

/*

to calculate an exponential moving average.

you will need to store the exponential moving average from the previous day into a table (for the first day

you can use a simple moving average for the data)

the weight is computed by adding one to the number of days in the average

days = 200 + 1 = 201. Divide 2 by this number = 0.00995.

this number times the current price is then added to the previous EMA times

1-(2/201)

todays info price 136

previous EMA = 120

todays EMA = (136 * .00995) + (120 * .99005)=120.16

then store the new moving average in table EMA table

*/

HTH Mike