I am trying to create a day trading worksheet that I populate each night (and in the morning) to prepare for the next trading day.
There are two items that I am stuck on where I am attempting to use nested IF ANDs. I'm sure there has to be a better way and I have been beating my head against the wall trying to use VLOOKUP but I haven't been successful.
The first issue is to determine if there is a gap between the current day's open and the prior day's close. I believe I have this working (with IF ANDs) but if there's a better way then I'd like to change it.
There are multiple zones associated with the prior day's close (did we close up or down) and where the current day opens (above the high, above the close but below the high, above the open but below the close, above the low but below the open, below the open, etc... and each zone has an associated probability associated with closing that gap.
The second challenge is to depict three prior day's candles and break each of the candles down into their Open, High, Low, Close and then by 1/8s.
I input the Open, High, Low, and Close of a prior candle manually and then it down in 1/8's. I'm trying to create a table where the numbers are sorted in descending order and depict the labels associated with each of those prices in the table next to the number (High, Open, 7/8, 3/4, 5/8, 1/3, Close, etc...) to get a numerical representation of where the current price falls within the prior candle.
I have worked many hours on this and was hoping to share my worksheet with somebody so I can get some pointers.
There are two items that I am stuck on where I am attempting to use nested IF ANDs. I'm sure there has to be a better way and I have been beating my head against the wall trying to use VLOOKUP but I haven't been successful.
The first issue is to determine if there is a gap between the current day's open and the prior day's close. I believe I have this working (with IF ANDs) but if there's a better way then I'd like to change it.
There are multiple zones associated with the prior day's close (did we close up or down) and where the current day opens (above the high, above the close but below the high, above the open but below the close, above the low but below the open, below the open, etc... and each zone has an associated probability associated with closing that gap.
The second challenge is to depict three prior day's candles and break each of the candles down into their Open, High, Low, Close and then by 1/8s.
I input the Open, High, Low, and Close of a prior candle manually and then it down in 1/8's. I'm trying to create a table where the numbers are sorted in descending order and depict the labels associated with each of those prices in the table next to the number (High, Open, 7/8, 3/4, 5/8, 1/3, Close, etc...) to get a numerical representation of where the current price falls within the prior candle.
I have worked many hours on this and was hoping to share my worksheet with somebody so I can get some pointers.
