I have a file containing the following columns:
Date | Time | Open | High | Low | Close | Volume
There is one row every 5 minutes between the hours of 7:35 and 11:15 (when the bar closes). The file contains data going back until January 2011.
I would like to create a new pivot tablee that summarizes each day in the following manner:
Date | Open | High | Time H | Low | Time W | Close | Volume
Where there is one row per trading day.
Open = opening value of first bar of the day
High = highest high of the day
Time H = the time when the highest high occurred
Low = the lowest low of the day
Time W = the time when the lowest low occurred
Close = the close of the last bar of the day
Volume = the sum of the entire day's volume
I tried creating a pivot table and was able to generate a table of dates with the highest highs, lowest lows and sum of volume but I have not been able to generate any time information. I hope this explanation is clear. Any help would be greatly appreciated. I have included a small file including a few days worth of data if that helps. The second sheet contains my attempt at creating the pivot table.
I also should mention that I'm running excel mac 2011 on os 10.9.1. I would greatly appreciate any help. I'm new to pivot tables which is sad because I've been using excel for years.
Bookmarks