max sum values of a time series per day

[ckmoied01]

Hi........I have historical data of every minute for the last 20 years...........and want to get the maximum and lowest of each hour

The data is tagged with time stamp in column B in the format e.g. 13:00.....13:59 ..... 14:00.....14:59 and so on

what is the best formula to get the max and min for each hour of the timeseries data

Thanks
ckm

 

[ckmoied01]

To be more precise.............i need 4 values out of the time series from each hour

Starting value of the Hour.......so at 00:00
Maximum value of the hour........so the max of 60 x 1 minute values
Minimum value of the hour........so the min of 60 x 1 minute values
Closing value of the hour .....so at 00:59

this goes on everay day with a date defined in column A

one small trouble is that the historical data is not very clean..............some of the minute values are missing in each hour............so some hours have 55 entries............some have 59 and so on............so the formula must read the start and end of the hour...........rather a fixed offset of rows

 

[kvsrinivasamurthy]

A sample file may help.

 

[ckmoied01]

finding some trouble to load the file..................but here is a sample of the data from columns A to G

A is date, B is time, C to F is Open, High, Low and Close of each minute........and G is to be ignored

I need for each hour...........the Open Price so col C at XX:00
High Price .........so the max of col D for the hour between xx:00 and xx:59
Min Price........so the min of col E between xx:00 and xx:59
Close price ............so the Col F at xx:59

as already mentioned...............some hours have less than 60 entries so offset function will not work here...........formula must read the timestamp

02.01.2011	18:00	1418.15	1418.88	1417.93	1418.88	118
02.01.2011	18:01	1418.98	1419.33	1418.83	1418.93	96
02.01.2011	18:02	1419.03	1419.13	1418.63	1418.63	61
02.01.2011	18:03	1418.58	1418.58	1417.15	1417.15	144
02.01.2011	18:04	1417.18	1417.58	1416.93	1417.58	91
02.01.2011	18:05	1417.55	1417.83	1417.33	1417.58	98
02.01.2011	18:06	1417.60	1418.13	1417.55	1418.03	74
02.01.2011	18:07	1417.95	1417.95	1417.48	1417.53	53
02.01.2011	18:08	1417.50	1417.58	1417.43	1417.58	23
02.01.2011	18:09	1417.65	1418.25	1417.65	1418.05	81
02.01.2011	18:10	1418.08	1418.48	1418.08	1418.15	74
02.01.2011	18:11	1418.13	1418.23	1418.08	1418.08	26
02.01.2011	18:12	1418.05	1418.15	1418.05	1418.13	13
02.01.2011	18:13	1418.15	1418.38	1418.13	1418.33	33
02.01.2011	18:14	1418.35	1418.43	1418.35	1418.43	9
02.01.2011	18:15	1418.33	1418.43	1418.23	1418.38	36
02.01.2011	18:16	1418.35	1418.35	1418.13	1418.18	28
02.01.2011	18:17	1418.15	1418.15	1418.03	1418.03	13
02.01.2011	18:18	1417.98	1417.98	1417.83	1417.83	16
02.01.2011	18:19	1417.85	1417.90	1417.63	1417.80	50
02.01.2011	18:20	1417.83	1418.23	1417.83	1418.08	56
02.01.2011	18:21	1418.15	1418.23	1417.98	1417.98	34
02.01.2011	18:22	1417.95	1417.95	1417.93	1417.93	3
02.01.2011	18:23	1417.95	1417.95	1417.70	1417.88	44
02.01.2011	18:24	1417.90	1417.93	1417.78	1417.83	24
02.01.2011	18:25	1417.73	1417.73	1417.28	1417.28	46
02.01.2011	18:26	1417.35	1417.83	1417.35	1417.68	64
02.01.2011	18:27	1417.65	1417.68	1417.58	1417.58	14
02.01.2011	18:28	1417.55	1417.58	1417.38	1417.38	24
02.01.2011	18:29	1417.45	1417.58	1417.45	1417.48	24
02.01.2011	18:30	1417.45	1417.50	1417.43	1417.48	12
02.01.2011	18:32	1417.50	1417.95	1417.50	1417.95	46
02.01.2011	18:33	1417.98	1417.98	1417.98	1417.98	1
02.01.2011	18:34	1417.93	1417.93	1417.43	1417.43	51
02.01.2011	18:35	1417.40	1417.40	1417.23	1417.23	18
02.01.2011	18:37	1417.18	1417.18	1416.83	1416.83	36
02.01.2011	18:38	1416.73	1416.73	1416.73	1416.73	1
02.01.2011	18:39	1416.78	1416.78	1416.78	1416.78	1
02.01.2011	18:40	1416.83	1417.03	1416.83	1417.03	21
02.01.2011	18:41	1417.18	1417.38	1417.13	1417.13	46
02.01.2011	18:42	1417.08	1417.63	1417.08	1417.63	56
02.01.2011	18:43	1417.58	1417.88	1417.58	1417.63	56
02.01.2011	18:44	1417.53	1417.53	1417.28	1417.28	26
02.01.2011	18:45	1417.33	1417.33	1417.33	1417.33	1
02.01.2011	18:47	1417.38	1417.38	1417.38	1417.38	1
02.01.2011	18:48	1417.48	1417.73	1417.48	1417.73	26
02.01.2011	18:49	1417.78	1418.08	1417.78	1418.03	36
02.01.2011	18:50	1418.38	1418.38	1418.08	1418.18	41
02.01.2011	18:51	1418.08	1418.08	1418.03	1418.03	6
02.01.2011	18:54	1418.00	1418.00	1417.83	1417.83	18
02.01.2011	18:55	1417.80	1417.80	1417.30	1417.30	51
02.01.2011	18:56	1417.28	1417.28	1417.13	1417.13	16
02.01.2011	18:57	1417.10	1417.10	1417.03	1417.03	8
02.01.2011	18:58	1417.00	1417.00	1417.00	1417.00	1
02.01.2011	18:59	1416.98	1416.98	1416.98	1416.98	1
02.01.2011	19:00	1416.83	1416.93	1416.83	1416.88	16
02.01.2011	19:01	1416.83	1416.88	1416.63	1416.63	31
02.01.2011	19:02	1416.60	1416.60	1415.63	1415.73	108
02.01.2011	19:03	1415.78	1415.98	1415.58	1415.73	76
02.01.2011	19:04	1415.58	1415.58	1415.38	1415.48	31
02.01.2011	19:05	1415.58	1415.58	1415.48	1415.53	16
02.01.2011	19:06	1415.58	1415.63	1415.48	1415.48	21
02.01.2011	19:07	1415.38	1415.38	1415.18	1415.18	21
02.01.2011	19:08	1415.33	1415.33	1415.18	1415.18	16
02.01.2011	19:09	1415.33	1415.33	1414.98	1415.08	46
02.01.2011	19:10	1414.88	1414.98	1414.38	1414.38	71
02.01.2011	19:11	1414.08	1414.18	1413.98	1414.13	36
02.01.2011	19:12	1414.23	1414.53	1414.13	1414.28	76
02.01.2011	19:13	1414.48	1415.08	1414.48	1414.93	76
02.01.2011	19:14	1415.03	1415.43	1415.03	1415.23	61
02.01.2011	19:15	1415.38	1415.53	1415.08	1415.43	86
02.01.2011	19:16	1415.23	1415.23	1414.78	1414.83	51
02.01.2011	19:17	1414.78	1414.88	1414.73	1414.83	26
02.01.2011	19:18	1414.93	1414.93	1414.88	1414.88	6
02.01.2011	19:19	1415.23	1415.23	1415.18	1415.23	11
02.01.2011	19:20	1414.83	1415.53	1414.83	1415.53	71
02.01.2011	19:21	1415.58	1415.78	1415.38	1415.48	71
02.01.2011	19:22	1415.63	1415.63	1415.13	1415.13	51
02.01.2011	19:23	1415.18	1415.63	1415.13	1415.63	56
02.01.2011	19:24	1415.53	1415.63	1415.43	1415.43	31
02.01.2011	19:25	1415.38	1415.53	1415.33	1415.53	26
02.01.2011	19:26	1415.48	1416.08	1415.48	1416.08	61
02.01.2011	19:27	1416.13	1416.93	1416.13	1416.93	81
02.01.2011	19:28	1417.08	1417.08	1416.73	1416.78	41
02.01.2011	19:29	1416.68	1416.68	1416.58	1416.60	13
02.01.2011	19:30	1416.63	1416.75	1416.63	1416.73	15
02.01.2011	19:31	1416.68	1416.68	1416.20	1416.28	57
02.01.2011	19:32	1416.30	1416.40	1416.15	1416.30	51
02.01.2011	19:33	1416.23	1416.35	1416.15	1416.28	36
02.01.2011	19:34	1416.33	1416.35	1416.20	1416.28	26
02.01.2011	19:35	1416.30	1416.33	1416.15	1416.33	34
02.01.2011	19:36	1416.35	1416.70	1416.35	1416.70	36
02.01.2011	19:37	1416.68	1416.75	1416.35	1416.65	78
02.01.2011	19:38	1416.53	1416.53	1416.43	1416.48	16
02.01.2011	19:39	1416.33	1416.48	1416.28	1416.33	41
02.01.2011	19:40	1416.43	1416.43	1416.13	1416.18	36
02.01.2011	19:41	1416.13	1416.18	1416.08	1416.13	21
02.01.2011	19:42	1416.18	1416.33	1416.13	1416.18	41
02.01.2011	19:43	1416.28	1416.28	1416.13	1416.18	21
02.01.2011	19:44	1416.08	1416.18	1416.08	1416.18	11
02.01.2011	19:45	1416.13	1416.18	1416.13	1416.18	6
02.01.2011	19:46	1416.08	1416.18	1416.03	1416.03	26
02.01.2011	19:47	1415.98	1416.18	1415.98	1416.13	26
02.01.2011	19:48	1416.18	1416.18	1416.13	1416.13	6
02.01.2011	19:49	1415.98	1415.98	1415.98	1415.98	1
02.01.2011	19:51	1415.93	1415.93	1415.88	1415.88	6
02.01.2011	19:52	1415.78	1415.78	1415.48	1415.48	31
02.01.2011	19:53	1415.38	1415.38	1415.03	1415.03	36
02.01.2011	19:54	1414.98	1414.98	1414.85	1414.88	17
02.01.2011	19:55	1414.95	1415.00	1414.88	1414.95	25
02.01.2011	19:56	1414.98	1414.98	1414.75	1414.78	27
02.01.2011	19:57	1414.95	1415.45	1414.95	1415.45	51
02.01.2011	19:58	1415.43	1415.68	1415.13	1415.58	96
02.01.2011	19:59	1415.63	1415.88	1415.63	1415.88	26

 

[kvsrinivasamurthy]

Try. In H2. Change the ranges as per your file. This is for the sample data in A2:G115.

=LET(tm,B2:B115,op,C2:C115,hi,D2:D115,lo,E2:E115,cl,F2:F115,Rop,MAP(tm,op,LAMBDA(xa,ya,IF(MINUTE(xa)=0,ya,""))),Rmx,MAP(tm,hi,LAMBDA(xb,yb,IF(MINUTE(xb)=0,MAX(IF(HOUR(tm)=HOUR(xb),hi,"")),""))),Rmn,MAP(tm,lo,LAMBDA(xc,yc,IF(MINUTE(xc)=0,MIN(IF(HOUR(tm)=HOUR(xc),lo,"")),""))),Rcl,MAP(tm,cl,LAMBDA(xd,yd,IF(MINUTE(xd)=0,INDEX(cl,MAX(IF(HOUR(tm)=HOUR(xd),ROW(cl),""))-ROW($F$1)),""))),HSTACK(Rop,Rmx,Rmn,Rcl))

H1:K1

OPEN

MAX

MIN

CLOSE

 

[ckmoied01]

dear can you pls share the excel file where you could have this formula worked..............my excel is in german version and is not accepting the function of LET

 

[kvsrinivasamurthy]

I don't know about German version. Function works satisfactorily.