For weeks I am trying to define the last thing in my automated trading system.
I use sierra chart and my trading system is based on excel.
My strategy is based on market tension and is trying to predict market corrections.
There is a rule that if the price came 0.25 - 0.5 points from a pivot point, didn't touch the pivot and a market correction at the size of 2 points or more occurred I will not go into position using this specific pivot point. But if after the correction - the price got 5 points away from the pivot and 15 minutes have passed since the correction took place I can use the pivot again.
How can I define something like this? : \
can macros help me? How?
Thank you very much in advance

Can you help answer these questions from other members on futures io?

I'm no expert, but here are some thoughts on how to approach...

I think you need each condition to be persistent and able to be reset.

Lets say your pivot point cell is named PP and your reset cell is named RESET and last price cell is named LAST, and your condition for closer than .5 is named CONDITION1.

This cell would then go to 1 or True once price came within .5 and would stay that way until reset by changing RESET cell to 1.

You could do something similar for all your other conditions and the reset condition and make a formula for your trade entry based on the whole series of conditions.

Hope that helps.

The following user says Thank You to Jolew for this post:

I meant just another cell that you use to reset the condition. In this case, the cell could contain a formula which was true if price was greater than 5 points away for more than 15 minutes.

You can just use regular cell references, I just wrote the formula with named cells for clarity. See workbook explorer for using defined names.

I just realized you need a way to capture the time to compare for 15 minutes.

You can probably just make a cell that puts the current time if within a certain range of the PP.

Again thank you for your help! I really appreciate that!
my problem is how to define the time sequence of the occurrences
i want to go into position when the market price touch the pivot point
how can i write a function that become true if the market price touch the pivot point and if there was a market correction as i explained on my first message.
meaning: right now the price touch the pivot point, before that the price was 5 points away from the pivot point, before them both the price was 0.25 -0.5 points away from the pivot point.
how do i define in the function what came first to what?

Go to Ninja web page, find a broker partner, ask for a free trial.

Then, for zero cost you will have a tool that will allow you to backtest your rules.

Note - I am 41 and have been programming since I was 13. Excel will take much, much longer to test anything like this and the output of the testing results will be very poor. Also, by the nature of how you'll load the data, testing that involves smaller targets is virtually useless.

The following user says Thank You to Jigsaw Trading for this post:

When you write your conditions, you can check for the previous condition. Here is an example of how it might work. Not sure if these are the exact conditions, but something like this. Each condition would be a separate cell.

Condition 1:

Price within .5 of PP and condition 4 false

Condition 2:

Price > .25 from PP and Condition 1 True and condition 4 false

Condition 3:

Price > 2 from PP and Condition 1 and 2 true and condition 4 false

Condition 4:

Price > 5 from PP for 15 minutes and Condition 1,2, and 3 True. This should reset all the conditions back to initial state.

Dionysus,

This excel method is a prototyping tool with Sierra. Just enter a formula and go. There is also a C++ interface. They both backtest easily. Actually, either for trying things out quickly or for someone that doesn't program it is a pretty cool feature.

His problem here is defining the same logic that is needed to program this. I don't see how Excel makes it any more difficult.

The following user says Thank You to Jolew for this post:

hi jolew!
first of all thanks a lot for your help!!!!!!!!!!!!!
I put this trading rule aside for the last weeks and now i am back on it.
i used something very simillar to what you suggested using excel state lock.
this is what i have done: AC3 is my pivot point. i used cell H5,H6,H8 - all are non advancing cells.

H5 - will become and remain true when the market came 0.5 points close to the pivot. and H8 is it's reset cell.
=AND(OR(E3=AC3+0.5,H5=TRUE),H8=FALSE)

H6 - will become and remain true if the market price equals the pivot point plus 2.5 points and if H5 is true. and H8 is it's reset cell.
=AND(OR(AND(H5=TRUE,E3=AC3+2.5),H6=TRUE),H8=FALSE)

H8 - is the reset cell for H5 and H6 will reset H5 and H6 to false if the market will equal the pivot point plus 5 points

=E3=AC3+5

this 3 formulas gives me the series of events. my LAST problem is: i need to be able to check if from the moment H5 first become true until i will make an entry(E3=AC3) 15 minutes have passed.
my time values are in column A. i was able to get the time but i can't seem to manage to lock the time cell. is there a way to lock the time cell to this specific time? or is it only able to lock a cell to "TRUE"?
Is there a way you can think of that will help me get time?

Thanks a lot for your help!! it is really apreciated!!

Hi again
I used the match index function to get the time when the market price first touched the pivot +0.5 and it worked but i have a little question.

this is what i have done:ac3 is the pivot
y3 =AND($J$41-INT($J$41)>TIMEVALUE("16:30:00"),OR(CROSSFROMABOVE(E3:E4,AC3+0.5),CROSSFROMBELOW(E3:E4,AC3+0.5)))
h13=INDEX(A3:A30,MATCH(TRUE,Y3:Y30,FALSE),1)
a30 because i use 15 min bars chart and i only need the last 6 hours

h13 gave me the time of the last crossover in the range(a12). but i want the time of the first crossover in the range(a18). how can i get that??

another question: because i use 15 min bars chart, when h13 gives me a time i don't know where inside that 15 minutes it happened. how can i get a more accurate time??? my idea is opening another chart of 1 minute bars and then my offset will be less then a minute, which is acceptable. do you have a better idea?

Since SC worksheets are in descending order, try using a match_type of -1, not FALSE.
h13=INDEX(A3:A30,MATCH(TRUE,Y3:Y30,-1),1)
This should give you the first crossover and not the last crossover.

Your idea of using another chart of a shorter timeframe, 1 min as you suggested, is the only way I know to get the time inside the 15 min bar. J41 updates with every price tick but I don't know any way to capture it at the moment another cell goes TRUE.

I don't think locking the state can lock a value. I've tried to do this without success. If you figure it out, I'd be interested to see your solution.

The following user says Thank You to tomgilb for this post: