Hello,
I'm wanting to use Excel to calculate 1-3 SD above and below the current VWAP but I'm unsure of what data needs to be be feed into the STDEV.S function in excel and wondered if anyone could help me?
Is this something that is quite straight forward or do I need to be recording a dynamic data set in excel in which to base the calculations on? If I can get my head around the maths (which is not my strong point) then I can probably program the rest if needed.
Many thanks
Calculating Standard Deviations from VWAP
-
ShaunWhite
- Posts: 10499
- Joined: Sat Sep 03, 2016 3:42 am
Std dev needs a series of values as it measures the deviation from a trend.
I find the Standard error function STEYX is uesful but it still needs a range of values. eg =STEYX(Values,Sample)
Where "Values" is a named range of Values, eg 2.0 2.1, 3.3 , 3.1, 4.6 etc
and "Sample" is the range of sample numbers eg. 1,2,3,4,5,6
Or you could write it as =STEXY (B1:B5,A1:A5)
If column A contained your 1,2,3,4,5 etc and column B contained your seies of values. (2.0 2.1, 3.3 , 3.3, 4.6 etc)
I'm afraid you will need to record a series of values. But you could play around with some example 'Values' in a sheet first to see if it's giving you the sort of numbers you'll find useful because VWAP doesn't usually move very far or fast.
I find the Standard error function STEYX is uesful but it still needs a range of values. eg =STEYX(Values,Sample)
Where "Values" is a named range of Values, eg 2.0 2.1, 3.3 , 3.1, 4.6 etc
and "Sample" is the range of sample numbers eg. 1,2,3,4,5,6
Or you could write it as =STEXY (B1:B5,A1:A5)
If column A contained your 1,2,3,4,5 etc and column B contained your seies of values. (2.0 2.1, 3.3 , 3.3, 4.6 etc)
I'm afraid you will need to record a series of values. But you could play around with some example 'Values' in a sheet first to see if it's giving you the sort of numbers you'll find useful because VWAP doesn't usually move very far or fast.