How to calculate Rolling Moving Avg on Load Script
Dear All,
i have found lots of people sharing different different concept to achieve rolling Moving Avg on load script.
Here is a simple way to create Rolling Moving Average on Load Script
i have Scrip code wise Weekly Close Rate table. i needs to create 5 Week rolling moving Avg on Close Rate.
Step 1.
First simple create a Scrip code , Weekly Cumulative sum of Close Rate :
load [Scrip Code],
TradingWeekSeqNo,
[Close Rate],
if( [Scrip Code]=Peek([Scrip Code]),RangeSum([Close Rate],peek([Cumm Close Rate])),[Close Rate]) as [Cumm Close Rate]
Resident tbl_Weeklytrade order by [Scrip Code],TradingWeekSeqNo
this will calculate Scrip code wise Weekly Cumulative Sum of Close Rate.
Step 2.
After that add preceding load like this
load *,
if([Scrip Code]=Peek([Scrip Code]) and TradingWeekSeqNo>5,([Cumm Close Rate]-Peek([Cumm Close Rate],-5))/5,[Cumm Close Rate]/TradingWeekSeqNo ) as Rolling ;
here what i am doing is checking when my WeekSeqNo go beyond my rolling Moving Average, i subtract current Week Cumulative Sum of Close Rate with previous 5 week Cumulative Close rate and divide by 5 else divided just cumulative Close rate by simple No of Weeks .
Whole Syntax is like below
tbl_Cumm:
load *,
if([Scrip Code]=Peek([Scrip Code]) and TradingWeekSeqNo>5,([Cumm Close Rate]-Peek([Cumm Close Rate],-5))/5,[Cumm Close Rate]/TradingWeekSeqNo ) as Rolling ;
load [Scrip Code],
TradingWeekSeqNo,
[Close Rate],
if( [Scrip Code]=Peek([Scrip Code]),RangeSum([Close Rate],peek([Cumm Close Rate])),[Close Rate]) as [Cumm Close Rate]
Resident tbl_Weeklytrade where [Scrip Code]>=500000 and [Scrip Code]<=599999 order by [Scrip Code],TradingWeekSeqNo.
