Convert Time Series OHLC Data Frequency

[prudential]

Hi, can someone please help me convert daily Open, High, Low, Close (OHLC) data to weekly and monthly.

https://drive.google.com/open?id=1PD...N7V2ZTtb029t0N

Open = Open value on the 1st day of the 5 day range
High = Max High value for the 5 day range
Low = Min Low value for the 5 day range
Close = Close value on the 5th day of the 5 day range
Volume = Sum of volume added each day for the 5 day range.

The weekly data table can go to the right of the existing daily data table.

Thank you.

[avk]

I have create two helper columns. (Week & Month) in sheet1
Refer sheet week & month.
Using simple sumif formula you can do.

[prudential]

I have create two helper columns. (Week & Month) in sheet1
Refer sheet week & month.
Using simple sumif formula you can do.

Hi avk,

Thanks and looks good but the only column that is the sum is the volume column, the others are FIRST, MAX, MIN AND LAST values for the 5-day weekly range.

[JeteMc]

Try the following formulas for the columns on the weekly sheet.
For Opening: =INDEX(Sheet1!B$2:B$17,MATCH(AGGREGATE(15,6,(Sheet1!A$2:A$17)/(Sheet1!J$2:J$17=A2),1),Sheet1!A$2:A$17,0))
For High: =AGGREGATE(14,6,(Sheet1!C$2:C$17)/(Sheet1!J$2:J$17=A2),1)
For Low: =AGGREGATE(15,6,(Sheet1!D$2:D$17)/(Sheet1!J$2:J$17=A2),1)
For Last: =INDEX(Sheet1!E$2:E$17,MATCH(AGGREGATE(14,6,(Sheet1!A$2:A$17)/(Sheet1!J$2:J$17=A2),1),Sheet1!A$2:A$17,0))
The formulas for those columns on the monthly sheet is similar, only changing the reference to Sheet1!J$2:J$17=A2 so that it reads Sheet1!K$2:K$17=A2
Let us know if you have any questions.

[krazi]

Hi there, I also followed the solution above for aggregating daily OHLC data into weekly which works great for data that is only within the same year. However, I'd like to aggregate daily data from the same week of the same year from data which contains multiple years.

Any ideas?

Thanks in advance!

[bebo021999]

It is a 2-year thread from other OP. You can open a new thread with your own question (with a link to this thread, if needed)