5 minute OHLC data and question

[grdnryn]

Hello,

I have 5 minute intraday OHLC stock data.
The first row (ie the first 5 minutes of every day) set the levels that I am concerned with. I am concerned with the High(D) and low(E) from this row.
I want to know if later in the day a 5 minute period closes(F) inside the zone between the high and low, does it continue on and close above(or below) the opposite side. In other words, if the stock drops after the first 5 minutes but then rallies again and enters the opening 5 minute zone, does it continue and make a new high, the same applies for the opposite, if the stock rallies after the opening 5 minutes but then drops back into the opening 5 minute zone, does it continue to make a new low?
I hope I have been able to explain this clear enough.
I have attached some of the data concerned.

[GeoffW283]

I don't have a solution, this is more a request for clarification. First, a couple of questions:

1) There are two days worth of data in your attached example. Does the opening high/low values of interest remain the 1/2/2019 values in D2:E2 or is the 1/3/2019 analysis to use that days opening values, that is D83:E83. For now I have assumed the former, especially as nothing interesting seems to happen on 1/2/2019 (the valuation simply rises above the opening high/low and stays there all day).

What about value changes in a five minute period that are large enough to skip over the opening 5 min high/low interval? For the purpose of analysis I assume that you would treat (where BELOW, IN, ABOVE are relative to the opening high/low spread) BELOW > ABOVE the same as BELOW > IN > ABOVE. Correct?

In the attached modification to your workbook I have added column G which contains one of three values: "IN" if the close value is within the opening high/low interval, "ABOVE" if the close value is higher than the opening high/low interval or "BELOW" if it's lower.

I have manually populated column H with my best guess at what your expected result should be. Values are UP for a rising trend of interest, DOWN for a falling trend of interest, "Not of interest" where I want confirmation that a transition is not of interest to you and finally cells are left blank where there is no transition of interest.

Please confirm whether or not the expected results are correct and if not please submit an updated workbook with your expected results manually entered in column-H. Or, if I'm on completely the wrong track feel free to say so!

Geoff

[grdnryn]

Hello GeoffW283,

Thanks for the reply and input. This helps and gives me some food for thought.
To clarify some things.

1) There are two days worth of data in your attached example. Does the opening high/low values of interest remain the 1/2/2019 values in D2:E2 or is the 1/3/2019 analysis to use that days opening values, that is D83:E83. For now I have assumed the former, especially as nothing interesting seems to happen on 1/2/2019 (the valuation simply rises above the opening high/low and stays there all day).
No. Each new day will reference the opening 5 min window of that day (sorry should have clarified that, I can see how you would think that after the action in that first day of the sample data, I didn't realise that that was a bad day for the example. It would be typical to expect the data to go above a below that opening window a few times per day.)

What about value changes in a five minute period that are large enough to skip over the opening 5 min high/low interval? For the purpose of analysis I assume that you would treat (where BELOW, IN, ABOVE are relative to the opening high/low spread) BELOW > ABOVE the same as BELOW > IN > ABOVE. Correct?
Yes, this is correct, and when the value change is large enough in a 5 min interval that it goes completely from below to above and vice the versa, the result is the same, as you have assumed in column H that you manually populated.

Please confirm whether or not the expected results are correct and if not please submit an updated workbook with your expected results manually entered in column-H. Or, if I'm on completely the wrong track feel free to say so!
This is correct, how you have manually populated column H is definitely heading towards the result that I would like.

I have approximately 5 years worth of data that I what to analyse in this manner. The end result being I would like to count how often the data enters that 5 minute window and continues, and also how often it enters and then fails to continue to the other extreme.

[poweru]

In order to help, can you explain what is the purpose of your calculation ?
Are you building some technical anlaysis tool or trading signal or something similar ?

[grdnryn]

Hi poweru,

No, no TA tool nor a trading signal. I am simply trying to crunch the numbers, all I am after are the statistics of the scenario that I have described above.
Please let me know if what I am describing is not clear.

[GeoffW283]

Thanks for the clarifications.

if I take the 1/3/2019 data and use the opening high/low for that day (which is what you have confirmed I should be doing) then it's another uninteresting day - the subsequent values are all below the starting high/low.

So purely to get some interesting transitions on 1/3/2019 let's consider the 1/3/2019 data relative to 1/2/2019's starting high/low. If we can first get one day right then applying the solution to your 5 years worth of data should then be easy.

On 1/3/2019 I see 1 UPward transition, 2 DOWNward transitions and 7 instances of "Trend fails to continue" per my manual markup in col-H. Can you review col-H of the attached workbook and say whether this is what you would expect?

Thanks

[grdnryn]

Yes GeoffW283, column H is correct, and to be honest this would be suffice for my needs.
I think maybe I was making it more complex than it had to be, as I was looking for a more elegant solution in my head, but like I said I am just looking to get the stats, so something like this with a helper column will be enough.

[GeoffW283]

OK, now we know what the expected results are it's "simply" a case of creating a formula that generates the expected results.

Here's my best attempt:
in cell I4 copied down to the end:

Please Login or Register  to view this content.

This gets 9 of the 10 expected events. The one it doesn't get is cell H47 because the closing price has spent 3 x 5 minute intervals where the closing price is within the opening high/low. I could catch this case and similar cases by adding another 4 lines to the formula, but is it worth it??

There may be a more elegant/comprehensive solution out there - maybe someone else will chip in!

I have attached an update to the workbook with the above formula implemented in col-I

Hope this is useful!

[grdnryn]

Thanks Geoff,

That helps a lot. When using my real data I will need to reference the opening 5 minute range of everyday (approx every 75th row, therefore I can't just use an absolute reference when making the helper column.
What would you (or anyone) suggest for this instance?

Many thanks.

[GeoffW283]

One way that occurs would make use of the time field in column-B but I notice that there is a space character in front of each time so Excel is treating the time in col-B as text rather than actual time values. I don't know what your process is for importing your data into Excel but would it be easy to get col-B to import as proper times? If that's not easy then that's OK, there's other options.

**** Edit: Sorry, never mind! - I can make the conversion with minimal effort)


Roughly how many days worth of data will you have on a particular worksheet? I'm wondering how the performance is going to look.

[GeoffW283]

OK - here's a revised formula for helper column-G that should pick up the 9:30 am high/low values for the current day. In cell G3 and copied down to the end of your data:

Please Login or Register  to view this content.

Let us know if this works for you.

Geoff