Forum Discussion
Power Query - Aggregating OHLC data
My suggestion would be to create interval numbers, e.g. with aggregation 30 minutes, 9:15 and 9:30 are interval 0, 9:45 and 10:00 are interval 1, etcetera.
Query Intervals creates a list with possible durations from which you can choose the value for parameter Interval, e.g. a list varying from 15 minutes to 2 hours:
= List.Durations(#duration(0,0,15,0),8,#duration(0,0,15,0))
Parameter Interval
Query TimeIntervals gives a table with times and interval numbers for all quarters 9:15 through 15:30:
let Source = #table(type table[Time = time],List.Zip({List.Times(#time(9,15,0),26,#duration(0,0,15,0))})), #"Added Custom" = Table.AddColumn(Source, "Interval", each Number.RoundDown(([Time] - #time(9,15,0))/Interval,0), Int64.Type) in #"Added Custom"Query IntervalStartTimes gives a table with the start times of each interval:
let Source = TimeIntervals, #"Grouped Rows" = Table.Group(Source, {"Interval"}, {{"StartTime", each List.Min([Time]), type time}}) in #"Grouped Rows"Query AggregatedDate gives the final result. Notice I grouped on Date, StartTime and Ticker (you didn't mention what to do with Ticker).
let Source = Data, #"Added Index" = Table.AddIndexColumn(Source, "OriginalSort", 0, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Time"},TimeIntervals,{"Time"},"TimeIntervals",JoinKind.LeftOuter), #"Expanded TimeIntervals" = Table.ExpandTableColumn(#"Merged Queries", "TimeIntervals", {"Interval"}, {"Interval"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded TimeIntervals",{"Interval"},IntervalStartTimes,{"Interval"},"IntervalStartTimes",JoinKind.LeftOuter), #"Expanded IntervalStartTimes" = Table.ExpandTableColumn(#"Merged Queries1", "IntervalStartTimes", {"StartTime"}, {"StartTime"}), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded IntervalStartTimes",{{"OriginalSort", Order.Ascending}})), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date", "StartTime", "Ticker"}, {{"OPEN", each List.First([OPEN]), type number}, {"HIGH", each List.Max([HIGH]), type number}, {"LOW", each List.Min([LOW]), type number}, {"CLOSE", each List.Last([CLOSE]), type number}, {"VOLUME", each List.Sum([VOLUME]), type number}}), #"Merged Columns" = Table.CombineColumns(#"Grouped Rows", {"Date", "StartTime"}, each _{0} & _{1},"Date"), #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type datetime}}) in #"Changed Type"My suggestion is to substract 9:30 instead of 9:15 and use List.Max to prevent negative values.
let Source = #table(type table[Time = time],List.Zip({List.Times(#time(9,15,0),26,#duration(0,0,15,0))})), #"Added Custom" = Table.AddColumn(Source, "Interval", each List.Max({0,Number.RoundDown(([Time] - #time(9,30,0))/Interval,0)}), Int64.Type) in #"Added Custom"
Hi MacelBeug,
I did further thinking and able to create all the steps as you suggested. I got the out put and it is working as expected. However I noticed my requirement is little changed. The only change from the previous one is I need to consider 9.15 time as only for open.
That is first interval will have 3 time and 2 time after that as usual. so if we take 00.30 interval my expectation is -
9:15 >> open of 9.15, high of 9.30 and 9.45, low of 9.30 and 9.45 and close of 9.45
9:45 >> open of 10:00, high of 10:00 and 10:15, low if 10:00 and 10:15 and close of 10:15
3:15 >> open of 3:30, High of 3:30, low of 3:30, Close of 3:30
I think in order get this we need to modify 'Interval' query to get New interval as shown in the below table. But I am not able to find a way to do it. Can you please suggest the edits required in the below code to get this?
let
Source = #table(type table[Time = time],List.Zip({List.Times(#time(9,15,0),26,#duration(0,0,15,0))})),
#"Added Custom" = Table.AddColumn(Source, "Interval", each Number.RoundDown(([Time] - #time(9,15,0))/Interval,0), Int64.Type)
in
#"Added Custom"
Thank you so much for your help!!
TimeIntervalNew Interval
| 9:15:00 AM | 0 | 0 |
| 9:30:00 AM | 0 | 0 |
| 9:45:00 AM | 1 | 0 |
| 10:00:00 AM | 1 | 1 |
| 10:15:00 AM | 2 | 1 |
| 10:30:00 AM | 2 | 2 |
| 10:45:00 AM | 3 | 2 |
| 11:00:00 AM | 3 | 3 |
| 11:15:00 AM | 4 | 3 |
| 11:30:00 AM | 4 | 4 |
| 11:45:00 AM | 5 | 4 |
| 12:00:00 PM | 5 | 5 |
| 12:15:00 PM | 6 | 5 |
| 12:30:00 PM | 6 | 6 |
| 12:45:00 PM | 7 | 6 |
| 1:00:00 PM | 7 | 7 |
| 1:15:00 PM | 8 | 7 |
| 1:30:00 PM | 8 | 8 |
| 1:45:00 PM | 9 | 8 |
| 2:00:00 PM | 9 | 9 |
| 2:15:00 PM | 10 | 9 |
| 2:30:00 PM | 10 | 10 |
| 2:45:00 PM | 11 | 10 |
| 3:00:00 PM | 11 | 11 |
| 3:15:00 PM | 12 | 11 |
| 3:30:00 PM | 12 | 12 |
My suggestion is to substract 9:30 instead of 9:15 and use List.Max to prevent negative values.
let
Source = #table(type table[Time = time],List.Zip({List.Times(#time(9,15,0),26,#duration(0,0,15,0))})),
#"Added Custom" = Table.AddColumn(Source, "Interval", each List.Max({0,Number.RoundDown(([Time] - #time(9,30,0))/Interval,0)}), Int64.Type)
in
#"Added Custom"Yes that works. Thank you.