Group based on two dimensions: start time & end time, and Plot
The csv file looks like this:
| Date | Strategy | StartTime | IntendedEndTime | Notional | ActualEndTime |
| 2014.07.01 | A | 9:30:00 | 16:00:00 | 80084.62 | 15:00:00 |
| 2014.07.01 | A | 11:35:00 | 15:00:00 | 17843.99 | 14:30:00 |
| 2014.07.01 | B | 12:45:00 | 14:00:00 | 60092.87 | 13:00:00 |
| 2014.07.01 | B | 15:55:00 | 16:00:00 | 80168.27 | 15:59:30 |
| 2014.07.02 | A | 9:30:00 | 16:00:00 | 60149.36 | 15:00:00 |
| 2014.07.02 | A | 11:35:00 | 15:00:00 | 59607.8 | 14:30:00 |
| 2014.07.02 | B | 12:45:00 | 13:45:00 | 78311.74 | 13:30:00 |
| 2014.07.02 | B | 15:55:00 | 16:00:00 | 2758.656 | 15:56:00 |
| 2014.07.03 | A | 9:40:00 | 16:00:00 | 60149.36 | 15:00:00 |
| 2014.07.03 | B | 11:55:00 | 15:00:00 | 59607.8 | 14:00:00 |
| 2014.07.03 | B | 12:45:00 | 13:45:00 | 78311.74 | 13:00:00 |
| 2014.07.03 | B | 15:55:00 | 16:00:00 | 2758.656 | 15:56:00 |
Goal:
Part 1:
I want to group these events by "StartTime" and "IntendedEndTime", to sum the "Notion" then divide it by total Notional to get the percentage, and also take the median "ActualEndTime" as a new column. "Date" and "Strategy" will be used as filters. The result will look like this:
| StartTime | IntendedEndTime | SumNotional | MedianActualEndTime |
| 9:30:00 | 16:00:00 | 140233.9827 | 15:00:00 |
==================================== EDIT ========================================
I posted a new workbook in which I created a hierarchy for StartTime and EndTime, so I can get the sum notional now. I can also get
sum/median ActualEndTime part if I substract ActualEndTime by #00:00:00# to get an integer, but not median (requires extract, what does it mean??).
I also don't know how to convert integer back to the hh:mm:ss format..
==================================================================================
Part 2:
Then I want to plot the new dataset like this:
The blue line is from StartTime to IntendedEndTime. The red line is from StartTime to ActualEndTime.
I have attached both workbook and the csv file. Please help.
Thank you very much!
Jul 23, 2014 6:54 AM