Hello:
I am playing around with some historical trading data for a financial instrument.
The data in this spreadsheet is the price history and reflects the daily price (open, high, low and close) as it is broken down into 5 minute periods of time. Nearly every day of data will have a maximum of 81 rows of data. However, there is one day that has fewer than 81 rows due to the markets closing early for a national holiday.
For my purposes, each day is independent of the next and nothing that occurs in one day should influence the results for the next day.
During the course of each day, I am trying to ascertain how often the market traded above the high of the 5 minute opening bar and how often it traded below the low of the 5 minute opening bar. Additionally, I am trying to ascertain the highest high and lowest low during these periods.
You will see on the spreadsheet that the “POSITION” column will meander from FLAT to SHORT to LONG to SHORT, etc.
The first row of every day should and will be labeled “FLAT”. On occasion, the second and third rows might also be labeled “FLAT” in the event the market wasn’t very volatile and prices did not exceed the high or low of the first 5 minute time period.
As an example, the first direction on the excel spreadsheet shows the market trading below the opening low as designated by cell I3. Therefore, I am interested in automatically finding the lowest low at cell E9, which is 1919.5.
Soon after, the market starts rising and goes above the opening high seen at cell I12. Now I am looking to find the highest high from that period until the next time it trades below the opening low or the end of the day (whichever occurs first). In this instance, the highest high would be visible at D14, or 1929.75.
Because these periods of time will vary in length I am pretty sure I need a vba macro to sift through this data, but I am not sure and my excel skills are decent, but not that great.
I have attached a link to the excel document below:
https://app.box.com/s/1gq8475p2a3zi4hghq7i
I am playing around with some historical trading data for a financial instrument.
The data in this spreadsheet is the price history and reflects the daily price (open, high, low and close) as it is broken down into 5 minute periods of time. Nearly every day of data will have a maximum of 81 rows of data. However, there is one day that has fewer than 81 rows due to the markets closing early for a national holiday.
For my purposes, each day is independent of the next and nothing that occurs in one day should influence the results for the next day.
During the course of each day, I am trying to ascertain how often the market traded above the high of the 5 minute opening bar and how often it traded below the low of the 5 minute opening bar. Additionally, I am trying to ascertain the highest high and lowest low during these periods.
You will see on the spreadsheet that the “POSITION” column will meander from FLAT to SHORT to LONG to SHORT, etc.
The first row of every day should and will be labeled “FLAT”. On occasion, the second and third rows might also be labeled “FLAT” in the event the market wasn’t very volatile and prices did not exceed the high or low of the first 5 minute time period.
As an example, the first direction on the excel spreadsheet shows the market trading below the opening low as designated by cell I3. Therefore, I am interested in automatically finding the lowest low at cell E9, which is 1919.5.
Soon after, the market starts rising and goes above the opening high seen at cell I12. Now I am looking to find the highest high from that period until the next time it trades below the opening low or the end of the day (whichever occurs first). In this instance, the highest high would be visible at D14, or 1929.75.
Because these periods of time will vary in length I am pretty sure I need a vba macro to sift through this data, but I am not sure and my excel skills are decent, but not that great.
I have attached a link to the excel document below:
https://app.box.com/s/1gq8475p2a3zi4hghq7i
