Now we could progress along, scrolling through excel copying and pasting formulas. But this is time consuming. And at the end of the day this is excel. It’s designed to do the hard work for you. So we’re going to take advantage of the filter button
Select and left click column B (Time). Select the data tab and left click the Filter button in Sort and filter. A filter (down arrow) button will now appear in the header cell.
Left click on that and a dropdown menu will appear. We are going to filter all results other than that of end of day or 23:00hrs. Deselect “select all” the select 23:00 only. Click Ok
Now the spreadsheet only display rows containing the time 23:00. This corresponds to the row we need our formulas to be applied in to work. You should see this.
Now go to cell H25 and select from H25 to L25. Do this left clicking and hold the left button in H25 and scroll across to L25. Release. This selects our formulas.
Next move the mouse button of the bottom right corner of the selected cells, a solid sight symbol will appear. Left click, hold and scroll down to the end of your data. Release. Excel will now automatically copy all formulas to our worksheet.
Finally, in the Sort & Filter pane left click on the Clear button. This will cancel the filter option returning the worksheet back to the default view. As you can see we have now analysis all our data, 1586 days worth.
Now to interpret our findings. Again excel has the tools to take the hard work out of this task. But first will need to active an add-in in excel. Select File - Options - Add-ins. At the bottom of the window ensure Excel add-ins is selected the left click go.
The final stage of our journey. Lets interpret our findings. The Data Analysis ToolPak has a histogram function. This will display frequency and cumulative percent in both tabular form and graphic chart. Perfect for our needs.
The data we are interested in is what time the high/low formed by. Column L. Use the filter button and filter out times except hour 23:00 again as this is the rows that contain our data. Select column L by the rick click and select copy from the dropdown menu
Open a new worksheet and in cell A1 paste our data. Next select cell B2 and type in Bin. This will represent each value excel will work with. Then from cell B3 to B26 type the values 1….24. Your worksheet should look like this
The histogram data window will now appear. We need to input the variables. Input range. Select the input pane, a sub window will pop up. Scroll to cell A2, left click , hold and scroll down to the last row of data and release. The sub window will copy the range. Deselect the sub window.
Select the bin range. Again a sub window will open. Select cells C3 to C26. Deselect the sub window.
In the output options select Output Range and click on the select button. Click on cell G2. This is where our results will be displayed. Finally select Cumulative Percentage and Chart Output for our visual display. Click OK when complete
We can see from our data that by 12:00hr East European time (10am British time) there is a 79.5% chance the high or low of the day being formed on the GBPUSD pair.
Now, one last word. We’re here to answer questions. But if you can’t demonstrate your work then it might not be the answer your looking for
@_bob
ok… so i’m sitting here, I read this line hehe
and i’m like … HERE WE BLOODY GO he’s got a glass of milo ready in front of him, he’s got a backup prep’d in the fridge if he runs out of the first one
and he’s gonna teach the newbies how to trade
HERE WE BLOODY GO
BOB’S COMPREHENSIVE COURSE ON FOREX TRADING FOR DUMMIES(SORRY… NEWBIES)
OK, i’m gonna read this as well (Let me make a cup of coffee first,)
be cool
or they could ask Martin, Really Politely and Martin might be a nice guy and upload it to a cloud server that is a lot more reliable and quicker to download then dukoscopy hehe
then martin will give them a link to the direct folder they need to download from , SO THEY DON’T NEED TO INSTALL ANYTHING (as would be the case with something like dropbox or icloud) this would be MEGA that i’m using.
Because Martin has an internet connection of 115Mbps and it took him around 2 Weeks to DOWNLOAD EVERYTHING , Every Instrument, the Entire Time Frame … EVERYTHING
Actually NO mate
Specifically speaking…
it is text based data that can be used by ANY SPREADSHEET or ANY DATABASE
so, it’s not just excel, it can be Calc it can be MySQL, … whatever
Any spreadsheet or Database can import this file type and use it
Thank you very much for this step! I have marked this educational thread! Although, I am not a newbie, but I am sure there will be so many educational stuff‘s!
Wow, what a ‘piece’ - must be important as there is a shed load of data here and Bob has taken the time and trouble to share and provide the process. Will save, read and try and digest. Thank you for sharing a valuable resource/insight!
I downloaded a load of 1 minute data from dukascopy, back to 2003 and I find there are some minutes missing (Data issues, not weekends etc)
I’m looking to write a macro probably, to put in new lines and duplicate previous lines, until the data returns (usually 1-4 minutes) - do you have ay issues with missing data from your source ?
Thanks @Jacqui. When we talk of hard work this is what we talk about. But like our trading it is personal therefor subjective. We each have to tailor our research to our own needs. Hopefully this well help the chosen few on their way.[quote=“Falstaff, post:22, topic:135938”]
do you have ay issues with missing data from your source
[/quote]
Can’t comment to much on this bro. Yes I have also noticed this missing data and there is even examples of here
and a chart here as well
In my observation data errors occur more in the earlier years than recent history. Statistically speaking IMO it’s an error of less than 0.1% making it insignificant to our results. But then most of my work is done on the EURUSD so I haven’t examined the data to much with other pairs.
Also IMO it isn’t really worth investigating back much bast 2011. Again this is my view, I believe the world markets changed after 2008. New technologies, algo trading, and HFT entered. Therefor data analysis any further back is probably not reflect of future market conditions.
Thanks my friend. You left a gold standard yourself, pity no-one has been active there for a while.
he’s got a glass of milo ready in front of him, he’s got a backup prep’d in the fridge if he runs out of the first one