Iron Fist, Most rely on excel and don't know how to by hand solve the standard deviation equation. The method of calculating the standard deviation is two-fold. First, the variance: you find the mean, subtract the mean from every period value, find the square root of each new value, add all values together and divide by the degrees of freedom, meaning all periods sampled with the exception of one, which is left out of sample. The standard deviation is simply the square root of the variance. As per excel, and how to graph, I haven't a clue in either and it's looking like I'll not be granted one. Good luck.
Most people use stdev function in whatever program because it is simpler. And the above methodology to calculate std. dev is not correct. You do not take the square root of each new value (data pt - mean), you square each new value (of data pt - mean) (i.e. raise to power of two). Simple equation to look up if you want to do it the long way. sample variance = sum k = 1 to n (xk - uk)^2 / n-1 where xk, here is the price at time t uk is the VWAP (or average, not the same as the simple mean in this case) at each point. stdev = sqrt(sample variance) Graphing in excel is very simple. You grab the columns you want to plot, and you do insert chart (then select line chart typically). Get an excel for dummies book (seriously, they cover charts pretty well).
dtrader it looks like you have not calculated VWAP. Instead you have calculated a volume weighted moving average (VWAP(20) from your spreadsheet image). Bog standard VWAP is reset at the start of the trading session and uses all data (price and volume) for the given session, it is not windowed intra-session. Of course, variants do exist. In addition, you have calculated the standard deviation of the difference between Price and supposed VWAP (i.e., P - VWAP(20)). Just in case the OP was not aware of these details.
yeah, that could be what i was originally worried about. I used the simple equation on the 1st site I saw (which is pretty much a moving price-vol weighted avg., and that is the formula they showed for VWAP). Here I used the equation they gave sum(Price*Vol)/sum(Vol) over n periods (in my example, I used 20). Both price and volume are there. Can you explain or show your interpretation of the variation on that formula (preferably with the equation form)? Looks like it is price vol weighted to me As I mentioned, however some sites look like they vary on the interpretation, unfortunately I don't have the time to become a member and see what they are talking about.
Yep, n should be increasing (for standard VWAP that is) within the trading session, such that all price and all volume is used as the session unfolds. But in your case, it would appear that you are working with daily data (I just realised it, D'Oh), so the above does not really apply, since you are already dealing with the End Of Day VWAP value. What to do then? Good question. A month's worth might be a good starting point, so a 20 period MA of daily VWAPs could be the way to go.
The SD bands need to converge to Closing price of the first bar at beginning of each session. By definition VWAP is for each day or RTH session. The SD bands are also unlikely to be equidistant from VWAP through out the day.
I know how to calculate SD. I just use the Excel or whatever subroutine to do it cuz it's faster. That being said, when I calculate the SD of VWAP, I get this:
Think this is getting closer. Rather than use a fixed average, here it uses a running average over time. In this example, time is the total period of days I used. This is on daily data, but you would just change the data frequency to intraday to extend this to an intraday period and update each period the same way. yeah, I also took tmarket's comment to heart about equidistant issue. That was what bothered me about the previous runs. Updating each event takes care of that.
Yeah, thanks for pointing out the same reference I linked to way back on the 2nd post. The whole point of the original posters question (as I understand it) is how to execute the exact methodology, which is not too clear on this site's example. Here's the total time series divided into two time periods (I show three bands, the example shows 1st two). For intraday time series, same methodology applies, with each day being the time period to re-run each new set of running data. case closed... I think.