Solved: Re: Power Query - Aggregating OHLC data

hnsbhat · ‎11-30-2017

Hello, I have below sample stock data for 15 min time interval for multiple dates which has open, high, low, close and volume. I need to convert 15 mins data into data for other time frames like 30 mins/1 hour/2 hours etc.

Open = Open of first time of the interval
High = Maximum high in the interval
Low = Minimum low in the interval
Close = Close of last time interval
Volume = Sum of volume

One more thing to consider is each day starts at 9:15 and ends at 15:30, so time interval we choose should start from 9:15 and end at 3:30. for eg. for 30 mins it should be 9:15,9:45,10:15....15:15,15:30 and next day again 9:15,9:45...
It is ok to have separate code for each interval (like 30 mins, 1 hours etc), but it would be great if it is possible to have them as parameters.

I thought of using Group By option, but the requiment complicated and beyond my expertise currently. Can anybody see if there is a way to achieve this in powerquery or powerpivot? Thank you in advance for any help here!

Date Ticker OPEN HIGH LOW CLOSE VOLUME
11/28/2017 9:15 ACC 1,698.05 1,698.05 1,698.05 1,698.05 603.00
11/28/2017 9:30 ACC 1,698.05 1,698.80 1,686.40 1,688.75 34,603.00
11/28/2017 9:45 ACC 1,688.85 1,689.95 1,681.70 1,681.80 25,225.00
11/28/2017 10:00 ACC 1,682.10 1,688.00 1,681.15 1,687.55 33,698.00
11/28/2017 10:15 ACC 1,687.55 1,693.95 1,685.70 1,688.00 32,960.00
11/28/2017 10:30 ACC 1,688.55 1,689.60 1,685.50 1,688.00 4,412.00
11/28/2017 10:45 ACC 1,688.00 1,688.00 1,682.80 1,685.00 14,118.00
11/28/2017 11:00 ACC 1,685.50 1,687.00 1,684.10 1,684.45 7,128.00
11/28/2017 11:15 ACC 1,684.45 1,686.50 1,683.90 1,684.30 5,397.00
11/28/2017 11:30 ACC 1,683.85 1,686.00 1,683.05 1,684.00 3,296.00
11/28/2017 11:45 ACC 1,684.00 1,686.15 1,683.50 1,685.00 6,168.00
11/28/2017 12:00 ACC 1,685.10 1,686.90 1,684.00 1,684.05 8,639.00
11/28/2017 12:15 ACC 1,684.00 1,685.00 1,682.00 1,683.50 3,588.00
11/28/2017 12:30 ACC 1,684.10 1,687.85 1,683.50 1,687.20 3,426.00
11/28/2017 12:45 ACC 1,688.40 1,691.60 1,685.15 1,685.15 8,409.00
11/28/2017 13:00 ACC 1,685.15 1,687.50 1,684.00 1,686.80 1,710.00
11/28/2017 13:15 ACC 1,686.75 1,688.00 1,682.00 1,685.00 17,527.00
11/28/2017 13:30 ACC 1,685.50 1,687.00 1,684.20 1,684.20 3,075.00
11/28/2017 13:45 ACC 1,684.15 1,685.00 1,683.10 1,683.35 4,064.00
11/28/2017 14:00 ACC 1,683.35 1,685.00 1,682.10 1,682.10 3,945.00
11/28/2017 14:15 ACC 1,681.35 1,681.60 1,677.90 1,680.45 14,090.00
11/28/2017 14:30 ACC 1,680.10 1,680.85 1,677.00 1,680.00 6,385.00
11/28/2017 14:45 ACC 1,680.15 1,684.20 1,680.15 1,681.55 4,481.00
11/28/2017 15:00 ACC 1,681.85 1,683.00 1,678.70 1,682.95 9,549.00
11/28/2017 15:15 ACC 1,682.90 1,684.00 1,678.65 1,683.20 15,549.00
11/28/2017 15:30 ACC 1,683.30 1,684.80 1,677.00 1,680.00 22,844.00
11/29/2017 9:15 ACC 1,680.90 1,680.90 1,680.90 1,680.90 324.00
11/29/2017 9:30 ACC 1,681.05 1,685.35 1,681.05 1,682.65 18,689.00
11/29/2017 9:45 ACC 1,682.65 1,682.65 1,678.00 1,681.55 10,533.00
11/29/2017 10:00 ACC 1,681.55 1,685.35 1,680.80 1,685.10 6,939.00
11/29/2017 10:15 ACC 1,685.10 1,691.30 1,683.45 1,685.10 15,100.00
11/29/2017 10:30 ACC 1,685.05 1,690.50 1,683.20 1,690.40 26,420.00
11/29/2017 10:45 ACC 1,690.40 1,695.40 1,690.10 1,694.50 22,717.00
11/29/2017 11:00 ACC 1,694.90 1,694.90 1,689.65 1,689.65 15,507.00
11/29/2017 11:15 ACC 1,689.65 1,690.00 1,686.30 1,686.80 8,253.00
11/29/2017 11:30 ACC 1,686.80 1,692.00 1,685.20 1,685.20 8,551.00
11/29/2017 11:45 ACC 1,685.30 1,690.45 1,684.00 1,684.60 11,708.00
11/29/2017 12:00 ACC 1,684.85 1,686.25 1,681.50 1,682.45 10,020.00
11/29/2017 12:15 ACC 1,682.50 1,682.55 1,680.80 1,682.15 3,269.00
11/29/2017 12:30 ACC 1,681.00 1,682.40 1,680.35 1,681.10 4,335.00
11/29/2017 12:45 ACC 1,680.70 1,681.30 1,680.10 1,681.30 3,437.00
11/29/2017 13:00 ACC 1,681.30 1,681.30 1,680.05 1,680.50 3,614.00
11/29/2017 13:15 ACC 1,680.90 1,680.95 1,679.55 1,680.70 3,954.00
11/29/2017 13:30 ACC 1,680.95 1,684.15 1,680.70 1,681.20 5,008.00
11/29/2017 13:45 ACC 1,683.50 1,683.50 1,681.15 1,682.90 1,718.00
11/29/2017 14:00 ACC 1,682.90 1,684.90 1,680.00 1,681.80 3,411.00
11/29/2017 14:15 ACC 1,681.70 1,681.70 1,678.35 1,679.00 2,507.00
11/29/2017 14:30 ACC 1,678.65 1,680.10 1,675.30 1,676.30 5,408.00
11/29/2017 14:45 ACC 1,677.00 1,682.35 1,675.00 1,679.05 9,814.00
11/29/2017 15:00 ACC 1,679.00 1,682.75 1,678.25 1,680.95 13,174.00
11/29/2017 15:15 ACC 1,681.20 1,685.65 1,680.80 1,681.65 19,223.00
11/29/2017 15:30 ACC 1,682.50 1,683.05 1,675.00 1,678.00 21,110.00

Sample Expected Result:

Date	Ticker	OPEN	HIGH	LOW	CLOSE	VOLUME
11/28/2017 9:15	ACC	1,698.05	1,698.80	1,686.40	1,688.75	35,206.00
11/28/2017 9:45	ACC	1,688.85	1,689.95	1,681.15	1,687.55	58,923.00
….
11/28/2017 14:45	ACC	1,680.15	1,684.20	1,678.70	1,682.95	14,030.00
11/28/2017 15:15	ACC	1,682.90	1,684.80	1,677.00	1,680.00	38,393.00
11/29/2017 9:15	ACC	1,680.90	1,685.35	1,680.90	1,682.65	19,013.00

MarcelBeug · ‎11-30-2017

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"

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug · ‎12-01-2017

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"

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug · ‎11-30-2017

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"

Specializing in Power Query Formula Language (M)

hnsbhat · ‎11-30-2017

Thanks for the reply MacelBeug. The steps you have mentioned seems to be beyond my expertize, just started learing powerquery and not able to determine where to start in your solution :). Is it possible to attach the output in an excel file, other-wise I think I need little more detailed steps to follow your solution. Thank you!

MarcelBeug · ‎11-30-2017

Hopefully a Power BI Desktop file is also fine.

A walkthrough the various queries (no comments, only background music):

Specializing in Power Query Formula Language (M)

hnsbhat · ‎11-30-2017

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

MarcelBeug · ‎12-01-2017

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"

Specializing in Power Query Formula Language (M)

hnsbhat · ‎12-01-2017

Yes that works. Thank you.

Power Query - Aggregating OHLC data

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly