Myroslav Gusyak

Find MAX in different periods

Recently, I get an interesting task. We have a database like that:

DateTime Close
28.09.2009 9:31 50.74
28.09.2009 9:32 50.81
28.09.2009 9:33 50.8
28.09.2009 9:34 50.78
29.09.2009 9:31 50.84
29.09.2009 9:32 50.8
29.09.2009 9:33 50.81
There are a lot of days for few years. We need for every minute find the chances of a particular minute in the day being the highest (lowest) within the day. This must be done in Excel.

So we need to find the percent of days from total days where price was MAX. I want to do it without VBA code. So, first of all, we need to restructure our database. I need to get the structure like that.

28.09.2009 29.09.2009
9:31 50.74 50.84
9:32 50.81 50.8
9:33 50.8 50.81
9:34 50.78

Sure, we could use copy/paste and do it manually for each day. However, using Excel’s PivotTables feature, we can avoid this tedious work (especially if we have hundreds of thousands of lines)  and let Excel do this work for us. A PivotTable is nothing more than a different, flexible view at your data. A PivotTable combines the best of subtotals, sorting, filtering and rearranging to allow us to see the data in a new view.

Also, when we work with date format we have a very useful feature in PivotTable – grouping. Grouping data in a PivotTable can help you show a subset of data to analyze by different time period. For example, you may want to group an unwieldy list of dates or times (date and time fields in the PivotTable) into quarters and months or even minutes and seconds.

In this view we can easy calculate MAX and even count days where we have max value for each minute. We just create the same table and put formula:

28.09.2009 29.09.2009
9:31 =IF(G3=MAX(G$3:G$6);1;0) 1
9:32 1 0
9:33 0 0
9:34 0 0

We search MAX value of the day and if found assign 1, else – 0. Now we can easy calculate the chance of a particular minute in the day being the highest within the day. Just sum by row and divided by the number of days.

Time Chance
9:31 50%
9:32 50%
9:33 0%
9:34 0%

As a result, I got a graph: