How to examine data
So now we have some data it’s time to examine it. IMO the best way is to work through an example.
Lets us ask the following question;
BY WHAT TIME DOES THE HIGH OR THE LOW OF THE DAY FORM ON THE GBPUSD PAIR SINCE 2012?
Step One - generate and open a csv file for the 1hr chart
The analysis you wish to preform will determine what timeframe of data you will need to produce. For our example we will work in the 1 hr chart although a 15min, 30min or 4 hr csv. could be used. Generate this file, find and open it in excel. Save as under a different name. You’ll get something that looks like this
To the more experience, we know what each column represent. However to a beginner it pays to add some headings
Step Two - Add Headings
Right click on row 1, from the drop down list click insert. This will add a row above in which we will add our headings into. From column A to G they are , Date, Time, Open, High, Low, Close and Tick Volume.
Your spreadsheet will now look like this.
Excel hint - in the View tab is a function call Freeze panes. Click on this and choose Freeze Top Row. This will allow you to scroll down through he spreadsheet while retaining your headers.
Step Three - Select your data range.
Now I have data going back to the 5/5/2003. I don’t need all this data. I only want from Jan 2012. Scroll down to Jan 2012, left click in column A (date), hold and drag across to column G (volume) then while still holding scroll down to the bottom of the spreadsheet. This will start the sheet scrolling down until you get the last row of data. Release the mouse button . You have now selected the data range you wish to examine. Right click anywhere in the selected area and copy.
Step Four - Create a new worksheet
To effectively work with our data it is best to add a new work sheet. Left click on the add new worksheet button will add a new sheet to the file. In cell A1, right click and from the drop down menu select paste. Your data will now be pasted there. As in step two, add your headers and freeze the top row. Next we are going to add the follow headers for our analysis, High, H Time, Low, L Time, High/Low. To answer our question we need to following pieces of information, the high of the day and what time it occurred, the low of the day and what time it occurred and which one happened first.
Step Five - data analysis.
For the 1hr chart naturally enough there are 24 rows of data collected. One for each hour. These 24 rows will form our array for each days analysis. The first days data goes from Row2 to Row25, The next day Row26 to Row49. And so on. As Row 25 represent the last data for the day we’ll do our calculations here. Select column H25 by left clicking on it. With cell H25 selected go to the Formulas tab, left click on More functions, hover over statistical in the drop down menu until a second dropdown menu opens, hover over that list and scroll down to MAX function.
The MAX function selects the highest valve in a range . Left click on MAX and the following Function Argument window will appear. It wants to know the range of data to examine. In our case it’s the highs of the data. Column D has the highs so we need to select from D2 to D25. Type in it into the field as such D2:D25. Once you’ve done this click OK and the high of the day will be displayed. In this case 1.55429
Knowing the high of the day is one thing but we need to know what time this occurred by. We need to match the high with time in column B. This time select cell I25. In the formula Tab, select Lookup & Reference, and select Match in the dropdown menu. This formula returns the relative position in the array of the value that matches the reference value (in this case the high). It’s important to understand this because the timeframe with determine that relative value. Lucky for us, on the 1hr chart the relative position represents time bar closed. When clicked on you’ll get the following window displayed
In the Lookup_value type H25. H25 contains the value of the day high we previously found. Next in Lookup_array, type D2:D25. This is the range we used that contains the high value. Finally in the Match_type, type 0. 0 represent exact match. Click OK and the position will be display. In our case 10.
We now know that the High of the day was formed between 11am and 12 noon. Next we are going to repeat but this time for the days low. Select cell J25. This time we want to find the low of the day. We’ll use the MIN formula found in More Functions - statistical. The low of each hour is in column E, cells E2 to E25. Type E2:E25 into the Number Field and press ok. Our return value is 1.54671.
Next we determine time the same way as we did with the high. Except this time the Lookup_value will be J25, the days low and the Lookup_array will be E2:E25. Our result the low of the day was formed between 5pm(17:00) and 6pm(18:00)
We now know the times the days high and low formed. The high formed before 12:00 and the low at 18:00. The high time being the smaller of the values was formed first. Therefore to tell which formed first we choose the MIN value. Select cell L25. From the formulas tab select More Functions - Statistical - MIN. In Number1 type I25 (the cell containing the high time. In Number2 type L25 (the cell containing the low time) then press OK. This will return the lower value. And there we have it. The high/ low for the day was formed at 12:00. We have analysis the first day’s data.
1585 to go.
What’s that you say, another 1585 times wow, that is going to take like forever. Chill, relax. This is excel. We’ll do it the quick way using the Filter function.
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