I was watching the Linda Raschke webminar last week. I was very intrigued by her time of day study; the one that showed the time that the market made day HIGH/LOW.
Just wondering what would be the easiest way to get that statistics by writing simple formula in EXCEL, if I have the OHLC data (15-min bar and daily bar) like the following in EXCEL format:
Basically, I want to categorize each trading day into 5 groups: 1hr each intraday. I want to know the frequency that the S&P made day HIGH/LOW in % term.
I did do this manually for the last 6 months and the HIGHs occured around 29% of the time during the first 2 hour of trading.
I want to automate this process and verify the stat on the last 10 years of data. Just wondering if anyone did similar studies and how on excel ?