Help with stock data formula in excel

[locksmith55]

Hi,

I'm working with stock data in excel. In the attached picture you will see 1 min high and low prices for a stock price. I'm trying to calculate the difference between the low price and high price for each bounce. For instance, since the first low happened at 11:01 AM and the first high happened at 11:12 AM and the next low happened at 11:40 AM, we know that the price did a bounce. What I'm trying to do is to categorize these bounces more clearly by using a dynamic formula.

Another problem is that sometimes like in the picture you will have multiple lows before a high. I need the formula to be able to deal with this too.

helper columns or other ways of doing this are welcome. It doesn't have to be just 1 formula as long as it is dynamic.

Appreciate any help I can get!

 

[Peter_SSs]

Welcome to the MrExcel board!

Another problem is that sometimes like in the picture you will have multiple lows before a high. I need the formula to be able to deal with this too.

I'm wonder what "deal with this" actually means for you?

What about the sample data again, but with the expected results manually filled in so that we can see exactly what you want?
If doing so, please consider the following as most helpers are not so keen on manually typing out sample data from a picture.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[locksmith55]

Welcome to the MrExcel board!


I'm wonder what "deal with this" actually means for you?

What about the sample data again, but with the expected results manually filled in so that we can see exactly what you want?
If doing so, please consider the following as most helpers are not so keen on manually typing out sample data from a picture.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sorry about my question not being clear enough. What I mean by the formula having to be able to deal with multiple lows before a high is that the formula will still be able to detect the true low. The only way to find the true low and therefore the bounce is to look down on the time of price low and compare the time of the low with the time of the high.

For instance, on the second bounce, the two lows of 2.77 and 2.64 are made before the high at 2.95. We know these are connected to the second bounce because they are made after the first high but before the second high. The formula needs to be able to detect that both of these lows are connected to the second bounce in this instance and that 2.64 is the true low for this bounce.

The original data is 1 min tick data Open, Low, High, Close. The data provided here is just the local maxima and minimum that I have found using complicated formulas.

The expected result for the data I have provided would be 2.9, 3.08, 2.77, 2.64, 2.95, 2.53, 2.62, 2.89, 2.68, 2.46, and 2.62.
Here first bounce is from 2.9, 3.08 to 2.77. The second bounce is from 2.77, 2.64, 2.95 to 2.53.
The third bounce is from 2.53, 2.62, 2.89, 2.68 and 2.46

Stuck on this problem for over a week now using lots of IF statements in help columns but it gets too complicated too fast.

Hope this clears up any confusion!

Book1
	A	B	C	D
1	PRICE HIGH		PRICE LOW
2	3.08	11:12:00 AM	2.9	11:01:00 AM
3	2.95	12:39:00 PM	2.77	11:40:00 AM
4	2.89	2:14:00 PM	2.64	11:57:00 AM
5	2.62	2:57:00 PM	2.53	1:35:00 PM
6			2.62	2:04:00 PM
7			2.68	2:22:00 PM
8			2.46	2:47:00 PM
Sheet1

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with stock data calculation in excel
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.