help defining a trade rule with excel - futures io
futures io futures trading



help defining a trade rule with excel


Discussion in Sierra Chart

Updated
      Top Posters
    1. looks_one ALFASSY with 6 posts (0 thanks)
    2. looks_two Jolew with 3 posts (3 thanks)
    3. looks_3 tomgilb with 1 posts (1 thanks)
    4. looks_4 Jigsaw Trading with 1 posts (1 thanks)
    1. trending_up 4,711 views
    2. thumb_up 5 thanks given
    3. group 3 followers
    1. forum 10 posts
    2. attach_file 0 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

help defining a trade rule with excel

(login for full post details)
  #1 (permalink)
HAIFA ISRAEL
 
 
Posts: 12 since May 2011
Thanks: 8 given, 1 received

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

Reply With Quote

Can you help answer these questions
from other members on futures io?
What blockvolume is visible? - order processing
NinjaTrader
Which kind of Blockvolume is visible? - order processing
Emini and Emicro Index
Short Interest Ratio indicator?
ThinkOrSwim
SimplerOptions
Trading Reviews and Vendors
EL/PL for FOPs?
EasyLanguage Programming
 
Best Threads (Most Thanked)
in the last 7 days on futures io
Battlestations: Show us your trading desks!
118 thanks
Big Mike in Ecuador
66 thanks
If you needed one-on-one help with any trading issue, ho …
31 thanks
Want your NinjaTrader indicator created, free?
27 thanks
Building a Crypto Mining Rig
21 thanks
 
(login for full post details)
  #3 (permalink)
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
 
Posts: 113 since Jan 2011
Thanks: 54 given, 95 received


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.

so maybe this could go into cell CONDITION1

=IF(AND(ABS(PP-LAST<.5),RESET=0),1,IF(RESET=1,0,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.

Reply With Quote
The following user says Thank You to Jolew for this post:
 
(login for full post details)
  #4 (permalink)
HAIFA ISRAEL
 
 
Posts: 12 since May 2011
Thanks: 8 given, 1 received

first of all, thank you very much for your help
what exactly is a reset cell? sorry for my lack of knowledge

Reply With Quote
 
(login for full post details)
  #5 (permalink)
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
 
Posts: 113 since Jan 2011
Thanks: 54 given, 95 received

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.

Something like

=IF(AND(ABS(PP-LAST<2),RESET=0),NOW(),IF(RESET=1,0,TIMECELL))

I guess you could also try to put the entire formula in one cell, but that will be a very long formula and will be difficult to debug.

Reply With Quote
The following user says Thank You to Jolew for this post:
 
(login for full post details)
  #6 (permalink)
HAIFA ISRAEL
 
 
Posts: 12 since May 2011
Thanks: 8 given, 1 received

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?

Reply With Quote
 
(login for full post details)
  #7 (permalink)
Site Sponsor

Web: Jigsaw Trading
AMA: Ask Me Anything
Webinars: Jigsaw Trading Webinars
Elite offer: Click here
 
 
Jigsaw Trading's Avatar
 
Posts: 2,977 since Nov 2010
Thanks: 823 given, 10,317 received

Excel is really a terrible tool for this.

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.

Visit my futures io Trade Journal Reply With Quote
The following user says Thank You to Jigsaw Trading for this post:
 
(login for full post details)
  #8 (permalink)
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
 
Posts: 113 since Jan 2011
Thanks: 54 given, 95 received

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.

Reply With Quote
The following user says Thank You to Jolew for this post:
 
(login for full post details)
  #9 (permalink)
HAIFA ISRAEL
 
 
Posts: 12 since May 2011
Thanks: 8 given, 1 received

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!!

Reply With Quote
 
(login for full post details)
  #10 (permalink)
HAIFA ISRAEL
 
 
Posts: 12 since May 2011
Thanks: 8 given, 1 received


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?

thank a lot in advance!! have a nice day!

Reply With Quote
 
(login for full post details)
  #11 (permalink)
Nashville TN USA
 
Experience: Advanced
Platform: SierraChart
Broker: Sierra Futures/SC Denali
Trading: NQ ES YM
 
Posts: 417 since Nov 2009
Thanks: 167 given, 512 received


ALFASSY View Post
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?

thank a lot in advance!! have a nice day!

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.

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


futures io Trading Community Platforms and Indicators Sierra Chart > help defining a trade rule with excel


Last Updated on June 13, 2011


Upcoming Webinars and Events
 

NinjaTrader Indicator Challenge!

Ongoing
 

Battlestations! Show us your trading desk - $1,500 in prizes!

March
 

Importance of Finding Your Own Way w/Adam Grimes

Elite only
 

Journal Challenge w/Jigsaw

April
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, +507 833-9432, info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts