Hello everyone,

I am very rusty with my logic and coding skills these days so hoping someone could help me get on the right track so I can calculate some statistics I am needing in excel.

Basically, what I am looking to do is calculate some statistics based on the VWAP to find out historically, on average how often does price close at each 1, 2, and 3 standard deviations above or below VWAP on any given day.

I think I understand how the basic vwap calculation works, but what I am confused about is how to implement it such that the VWAP will reset for each day of trading in excel like it normally is done.

I am pretty sure when I get that far, I could get the rest of what I need done fairly easily.


I was going to provide some links to images to show some specifics but because I have not posted here in the past, it will not allow me so I will describe my data as best as possible.

The data I am working with is hourly bars, where there are 24 bars in a day, the data is in descending order from newest to oldest, and a new day is signaled when the 17:00 hourly bar is the cell above the 15:00 hourly bar. Other than that, the data is in pretty standard format

Thanks for the help in advance. I will share the results of this study here once I am able to figure it out.

guerra503 

Hello everyone, I am very rusty with my logic and coding skills these days so hoping someone could help me get on the right track so I can calculate some statistics I am needing in excel. Basically, what I am looking to do is calculate some statistics based on the VWAP to find out historically, on average how often does price close at each 1, 2, and 3 standard deviations above or below VWAP on any given day. I think I understand how the basic vwap calculation works, but what I am confused about is how to implement it such that the VWAP will reset for each day of trading in excel like it normally is done. I am pretty sure when I get that far, I could get the rest of what I need done fairly easily. I was going to provide some links to images to show some specifics but because I have not posted here in the past, it will not allow me so I will describe my data as best as possible. The data I am working with is hourly bars, where there are 24 bars in a day, the data is in descending order from newest to oldest, and a new day is signaled when the 17:00 hourly bar is the cell above the 15:00 hourly bar. Other than that, the data is in pretty standard format Thanks for the help in advance. I will share the results of this study here once I am able to figure it out.

Have you tried to use absolute cell reference such as ...
$A1 Allows the row reference to change, but not the column reference.
A$1 Allows the column reference to change, but not the row reference.
$A$1 Allows neither the column nor the row reference to change.