Help converting OHLC fx data from 1 min to 5 minutes or higher time frame

Hello all, great forum here. I have found many solutions to minor excel/VBA issues over the last few years. But I've never been stumped until now.

I have a several million lines of Forex data (not all in the same Workbook thankfully) in minute format and need to convert the minute data to 5 minute (or higher timeframes - up to Weekly). I found a converter that someone else has developed. It works in taking every five lines of minutes and combining into a single line of data but it doesn't respect the timestamps in the date column.

I am a novice when it comes to VBA (I just keep staring at the code) so if someone can take a look at the code and fix it so the DateTime is in YYYY-MM-DD HH:MM so each row should have the beginning time of YYYY-MM-DD HH:00 and then YYYY-MM-DD HH:05. There are many missing minutes, especially at the beginning of my data so I do not expect or desire to have a row for every 5 minute period, just when there is actual data.

I have attached the converter and some of my sample data for you to see. Thanks, in advance, for any and all help.

Patrick

No, OHLC refers to market data: Open, High, Low and Close of a period.

Open would be the first B column value. High would be the highest value of the 5 rows of column C, Low would be the lowest value of the five rows being combined of column D. And close would be the value of the final of the 5 rows in column E.

With there being some rows missing from each five minute period there may not always be 5 rows to combine into a single five minute OHLC row. We also call these bars or candles because they display this data on price charts. Thanks.

Yes, true. Trading in AUDUSD was sporatic back in 2000. These should just get their own 5 minute row unless there happens to be more rows in the same five minute period.

The attached DataConverter.xls does work to combine every five rows into one with the open from the first row, the close from the last 5th row and the high and low from whichever row those are in. I does work, but it doesn't honor the time stamp and organize the start of the five minute row at the top of the hour and so on.

Yes, straightforward except how does one modify the VBA in DataConverter.xls to get the date-time to start at the start of every hour and every 5 min period thereafter???

Wow, this looks good. Thank you so much. P

OK, what was the bug?

OK, after looking at it, it does do what I want, however, I forgot to be specific. I was hoping you'd modify the file DataConverter I attached because it had a place to input the file name and worksheet and specify the number of minutes. I just realized you say you started the week on Monday when it should start on Sundays. I see the line dBeg = Int(dMin) - (Weekday(dMin) + 5) Mod 7 ' start weekly on Monday; but looking up the function shows if it is omitted it defaults to Sunday which for the Weekly is perfect. Do you concur?

Now for the hard part. I needed 240 and 1440 minutes. Yes I know that is a day but that is how we do it with forex. It trades all night and starts on Sunday here at 17:00 EST (its Monday morning in Asia). I was able to add the 240 and 1440 to the Case and to the dropdown menu, but it would be best to get these rows to start at 17:00 eastern on Sundays and go from there. Any ideas?

Thanks for adding the table. I clearly had a few bad data points.

Here is my file

Oops attachment too big

This attachment

Yea, it looks like the sunday is missing on the weekly

Sorry, I have been swamped the last couple of days and not feeling well on top of it. I really appreciate your help.

None the Sundays show up on the weekly worksheet.

I have attached a couple of screenshots showing daily data on Sunday Sept 12th, 2004 and on the weekly screenshot it doesn't even have a Sept 12th. It just starts on the 13th.
As do all the other weeks.

This is because my data is Eastern US time (Sundays at 17:00 in NY are Monday mornings in Asia; which is when the trading starts each week).

So the weekly and 240 min data should each start at 17:00 on Sundays NY time.

Anyway, I hope all is well with you.

PatrickA

I was thinking of a simple VBA that change all the source data by adding 16 hours to each date. But then I thought roll the Input data forward 16 hours (to Melborne time). Then create the output and roll the Output data back to NY time. Also, seems like the Input data should be rolled back to NY time to create a new Output and so all the series are synced. What do you think? it might be the easiest thing.

Yea, that is what I had to do. It works but is tedious to copy the date columns back and forth and then copy back into the Input sheet.

BTW, your tool is quite fragile because of all the named spaces and the header. The code breaks if I don't copy by rows.

How do I get the data for 10 min?