Rolling Standard Deviation

building reports webintelligence
1

Hi,

I need some help in creating a rolling Standard Deviation for 20 days at one time. So for Example the date is 02/02/18 I want to get the standard Deviation of the values between 02/02/18 and 01/05/18.

I have attached a spreadsheet of how it has been done in excel i just need to calculate the column in yellow.

Thanks,
Ryan
Standar Dev.xls (84.0 KB)

RSFoster (BOB member since 2018-08-16)

2

You could do it yourself, using the Sqrt and Previous (or RelativeValue) functions. It would be a long formula, but not too unwieldy.

mikeil (BOB member since 2015-02-18)

3

Hi,

I have created this =StdDevP([price return])*Sqrt(250)

but I am having trouble getting it to do the range. I have created two variables called
start date: =RelativeDate([Clear Date];0)
20 days ahead: =RelativeDate([Clear Date];20)

any help in would be greatly appreciated.

RSFoster (BOB member since 2018-08-16)

4

I think you’ll have to do the range yourself; Webi won’t do it. Something like

sqrt ( ((A - Aaverage)^2 + (RelativeValue(A,1) - Aaverage)^2 + (RelativeValue(A,3) - Aaverage)^2 . . . . . . . . ) / 20)

where you had previously calculated Aaverage as the average of the twenty A values.

I’m not clear where the 250 came from.

mikeil (BOB member since 2015-02-18)