Dynamically scanning subsequent cells after a condition is met

[john132]

Hello,

I would like to conduct this study but need some expertise from those who know excel. I have very limited knowledge on how to do the functions and macros. I seek your guidance.

Essentially what I have done is if certain conditions are met, an arrow shows on my charting program for either buy or sell. I have extracted the data into excel, in particular, the OHLC (Open, high low and close) of each bar, and the price at which the arrow shows up. I've filtered out (with very limited knowledge in IF, then statements) to post true or false when an up (buy) or down (sell) arrow was printed.

So for now lets assume we are only talking about buy arrows / signals. If a buy arrow condition is true, I want to evaluate if the subsequent highest value of the bars after the arrow printed is 9 units higher than the high of the bar where the arrow was produced. Or is the highest value of the bars following the arrow lower (by 6 units) than the high of the bar at when the arrow was formed

So here's an example. (Cell A309 ->) At 10:08 am on April 16, 2018, a bar has the following O: 6671.25 H: 6673 Low: 6670.25 Close: 6671.75, the up arrow was printed on this bar when price was 6670.75. Now what I want to do is check for the following: does the price/value following 10:08 am bar go up for at least 9 or more points / units or does it at any point go 6 points below 6670.75 before it moves 9 points from the high of my signal bar. Whichever is hit first.

Edit: (Column AI, checks if there was an up arrow printed and if so, then print True), Now what I want to do is scan the subsequent rows if D310 and onwards when subtracted from $D$309 >=9 , or does it at any point go 6 points below D309 before the difference between the two cells is >=9.

The other thing is, if there was only one up arrow that was produced then it's easy to subtract and test for one or the other condition. But because other rows may have a condition that is true for up arrow condition and other rows print true when down arrows is printed under column AI. I don't know how to dynamically scan the subsequent rows after it is determined what arrow was printed. For example Row 181 meets the condition where the down arrow was printed. Now i want to scan the subsequent rows to check if the low of E182 subtracted from the low of $E$181 <=-9 or does at any point before the difference is -9, the difference becomes +6.

Once it determines that the difference is -9 or +6. I want it to i guess stop "scanning" until the next arrow prints and then we scan the subsequent rows below until one or the other condition is hit. and so forth.

I am attaching the Excel file, but since it is about 43 mb, I am providing a dropbox link to the file. But I am not able to post any kinds of links images or videos until I post a few times. I don't know how else to attach the file in the interim as the file is too large.

https://www.dropbox.com/s/gynkns8wyr...test.xlsm?dl=0

How can I achieve the desired goal?

[jeffreybrown]

Hello John & Welcome to the Forum,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...

Also...

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FlameRetired]

Withdrawn by FR.

[john132]

Hello here is the dropbox file / link. https:// http://www.dropbox.com/s/gynkns8wyr1...test.xlsm?dl=0

[john132]

Here are two images that explain what I want to achieve in excel, based on how it is in my charting program.

One scenario is for down arrows, and the other one is for up arrows. For down arrows we want to take the difference using the low of every subsequent bar, and for up arrows we want to take the difference using the high of every bar thereafter.

Down Arrow Scenario.png


Up Arrow Scenario.png

[john132]

So I have been trying to fiddle around with this and work with it. What I've able to make progress on so far (Or that's what i think I am doing lol), I've created a column to say that if a buy arrow condition is true, take the value from the high of the bar (D14432) and add + 9 to that value. I called this column (Column AO), my target for buy column. Then I created another column (Column AP) that I call, stoploss for buy, in which if a buy arrow condition is true then I take the high of that bar and subtract 5.5 from that value.

So to keep it simple, a column tells me yes buy arrow was printed and it prints "true" in excel. Another column right beside this condition column, lists the target price. The column next to this one lists the stop price. Now this is where I am really stuck. So lets use an example. Row 14432, arrow appears, target 6505, stoploss 6490.5. How do I check the rows below 14432 to see whether column D ( which has the high of every bar) is equal or greater than the target price (AO14432). OR whether the rows below 14432 is <= the stoploss price (AP14432).

Once either of the condition is met, it should i guess stop "checking", until the next arrow is displayed and the process is repeated.