I have imported M1 data in a MySQL database and I would like to convert this data to M5. I have no problems with the calculations to do this but I’m not sure what will give me the correct data.
To calculate the M5 bar high, do I take the average highs of all 5 minutes or do I take the highest high from those 5 individual bars? Will the same apply to opening, close and low?
Any help is greatly appreciated! 
Hi mate
What you are asking is
“Martin, how do i do CANDLESTICK MATHEMATICS”
No… You don’t average
The “Averaging thing” since you brought it up, Applies to HA (Heiken Ashi) Candlesticks.
(let me sidetrack for a second just in case this interest’s you)
as you know you have M1 Japanese Candlestick Data
and as you know 1 M5 Candle is the result of 5 x M1 Japanese Candles
now. if you overlayed Heiken Ashi Candles over Japanese Candles
and the HA was set to HA = 5
a HA Candle would be formed like this
O = Open
H = High
L = Low
C = Close
First the HA calculates the AVERAGE of the last 5 Open Prices
the Result of Averaging the Last 5 Open prices SETS THE PRICE FOR THE OPEN OF THE HA Candle
see how that works
the High is the average of the last 5 Jap Highs
the Low is the average of the last 5 Jap Lows
the close is the average of the last 5 Jap closes
and Vouila , you have a Heiken Candle formed
NOW BACK ON TRACK
Converting M1 to M5
or Japanese Candlestick Mathematics between timeframes goes like this
YOU ARE JUST ADDING AND SUBTRACTING, that’s it (forget Averaging)
it’s as simple as this
if a candle goes up 12 pips then drop by 6 pips
12 - 6 = 6
simple as that
You’re question relates to the the OHLC of it
so… the HIGH is determined by getting THE HIGHEST HIGH
the LOW is determined by getting the LOWEST LOW
as for the Open and Close
Watch these video’s ok
it explains it easier
i could explain it, but then you would ask
but martin what if there are 4 candles or 5 or more
so watch these videos
then research more on CANDLESTICK MATHEMATICS
You’ll see pretty quickly it’s just Addition and Subtraction
this is a 3 Part series (this is part 1)
Thank you very much, this was what I was looking for. And the opening is the opening of the first bar en the close is the closing price of the last bar.
For my M5 price at 22:00 I will calculate like this:
Opening = M1 opening @ 22:00
High = M1 highest value from 22:00 to 22:04
Low = M1 lowest value from 22:00 to 22:04
Close = M1 closing value @ 22:04
Ok so you seem to have got the gist of the “How” - BUT how good is your data ?
I have 1 minute data in CSV which I am hoping to manipulate - BUT when you subtract the time of data bar 1 from the time of data bar 2, it gives a fixed result equating to 1 minute (Excel) - Pull that down the spreadsheet and you find that several times a day, the result is 2 or more times the expected result. Check the times and sure enough, there is one or more lines missing ! Manually, it is possible to insert a line and copy the result from the previous line down, but that is not terribly satisfactory. The alternative, of just using the data as received, will cause a lot of issues at the end of a number of days, [Edit since the problem will inevitably compound.]
Any ideas about creating a macro to automate this would be welcome !
However, please check your data for a few days, just to ensure you are not getting these “Missing bars” !
Yes that’s correct
THE HIGH IS THE HIGHEST HIGH
THE LOW IS THE LOWEST LOW
THE OPEN IS THE FIRST CANDLE
THE CLOSE IS THE LAST
this is assuming we have 2 Candles
I have my data from Tickstory which they got from Ducascopy. I will make sure my conversion script takes into account any missing bars.
Hi I got ine direct from Dukascopy - so you will have the problem.
I’m not sure how this works in Excell but with PHP and MySQL there shouldn’t be any problem, only if every single one of the five M1 bars are missing I wouldn’t be able to construct an M5 bar. And even then, I could construct something close enough by taking values from the surrounding M1 bars.
I will make a gap function first to see what exactly is missing. Excluding the usual weekends and holidays of course…
LOL… who doesn’t LOL
i also have all my data from Ducascopy via using Tickstory
and let me say that again, i have ALL my data
meaning… Every instrument on every time frame as far back as it’ll go (which is 2003) 
all that account for a total of like 2 weeks download and around 33.4 GB
then i have every download from TRUE FX in CSV
Every instrument as far back as it’ll go (which is 2009)
that accounts for another 40GB
I haven’t been around very long. Tickstory was what I found first.
Do you have all that in a database too or in a tool like MT4? I prefer all data in a database so I can excecute multiple strategies on multiple currency pairs with random variables. That way I can execute more tests than I could possibly run manually in a lifetime.
I’d be happy to download all your M1 data since I only got M1 usdjpy from 2009-2017 at the moment 
Hi mate
i found Tickstory first as well,
i think we all do 
Now i later wanted another source
so i came across True FX
now… with Dukoscopy Data via Tickstory lite (as you know it takes forever, Assuming your internet connection
or their connection doesn’t drop out)
I"m happy to share what i have, no problem
but, it’s not like it’s separated in M1 or M5
THIS IS TICK DATA mate
so, you can load what instrument you want and get all the ticks from the beginning
so if you want USDJPY
i store my data in folders for simplicity sake
as you can see the data starts from April 2003 - 2017
it’s a pain in the arse to download
but if you like, i’m happy to load it up to a cloud and give you a link to access you can just download it freely
it’s just folders and files
the true FX data is CSV
same thing in folders
I’m also a fan of Databases and i use MySQL (it’s cool stuff)
but i also like to store things in raw format as well
so if you’re interested let me know what you want and which instruments
and i’ll see how much i need to upload and how long it’ll take
i’ll supply you a link (i use MEGA) so nothing to install on your end
Now, as per the terms and conditions of this site, i can’t give you my email
so i’ll just have to post the link
i suppose everyone’s going to jump on board hehe
and rape the crap out of the data … huh
oh well, that’s ok
if they want to in the window that they have, SO BE IT
but let me know what you need and i’ll see what i can do
I find the thorougness of your replies amazing, especially to someone who is just new here at the forum! I actually downloaded the M1 data per half year because I couldn’t import larger .csv files. I’m not sure if the actual tick data will do me any good, I would have to import many many files and then convert those to M1 data in MySQL. I really appreciate the offer though. Thanks.
hehe
You’re welcome
and FYI, i’m not new here
I’ve been in forex for around 7 years
i just haven’t visited this site frequently
7 years i did and then i took a long break for a few years and now i’ve decided to pop back on again,
i mean, when you learn to trade properly, baby pips is not something you keep coming back to because for the most part you are just dealing with newbies
and most of them are going to fail anyway, so why would a person bother. know what i mean.
the reason why i’m thorough is because i’ve learned a lot
but thanks in any case
@Shotputty
Just a heads up RE bobs comment
as far as tick data is concerned Birts also get its from Dukoscopy and does Tickstory and as do most.
so getting Birts is pointless if you are banking on the fact that it’s going to be different data… IT WON’T BE
in saying that
I agree, as i think most do that strategy testing should not be done in Strategy Tester in MT-4.
i agree you should get a good strategy tester
i used to use Forex Tester 2 (i think there is a version 3 now) it was pretty good
I’ve done some research on Birts but never actually used it
it also seems to be pretty good FOR TESTING
but as for the data, Whether you get Birts platform, Tickstory or whatever, the data is DUKOSCOPY DATA which you already have, you have no benefit getting birts if it’s for the sake of tick data
Just something to keep in mind