NexusFi: Find Your Edge


Home Menu

 





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 Quick Summary with 1 posts (0 thanks)
    4. looks_4 Sawtooth with 1 posts (1 thanks)
    1. trending_up 6,221 views
    2. thumb_up 5 thanks given
    3. group 3 followers
    1. forum 10 posts
    2. attach_file 0 attachments




 
Search this Thread

help defining a trade rule with excel

  #1 (permalink)
ALFASSY
HAIFA ISRAEL
 
Posts: 12 since May 2011
Thanks Given: 8
Thanks Received: 1

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 NexusFi?
Build trailing stop for micro index(s)
Psychology and Money Management
The space time continuum and the dynamics of a financial …
Emini and Emicro Index
Deepmoney LLM
Elite Quantitative GenAI/LLM
NexusFi Journal Challenge - April 2024
Feedback and Announcements
Exit Strategy
NinjaTrader
 
Best Threads (Most Thanked)
in the last 7 days on NexusFi
Get funded firms 2023/2024 - Any recommendations or word …
59 thanks
Funded Trader platforms
36 thanks
NexusFi site changelog and issues/problem reporting
25 thanks
GFIs1 1 DAX trade per day journal
19 thanks
The Program
18 thanks
  #3 (permalink)
 Jolew 
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
Posts: 113 since Jan 2011
Thanks Given: 54
Thanks Received: 97


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
Thanked by:
  #4 (permalink)
ALFASSY
HAIFA ISRAEL
 
Posts: 12 since May 2011
Thanks Given: 8
Thanks Received: 1

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
  #5 (permalink)
 Jolew 
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
Posts: 113 since Jan 2011
Thanks Given: 54
Thanks Received: 97

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
Thanked by:
  #6 (permalink)
ALFASSY
HAIFA ISRAEL
 
Posts: 12 since May 2011
Thanks Given: 8
Thanks Received: 1

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
  #7 (permalink)
 
Jigsaw Trading's Avatar
 Jigsaw Trading  Jigsaw Trading is an official Site Sponsor
 
Posts: 2,988 since Nov 2010
Thanks Given: 831
Thanks Received: 10,393

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 NexusFi Trade Journal Reply With Quote
Thanked by:
  #8 (permalink)
 Jolew 
San Jose, CA
 
Experience: Intermediate
Platform: Sierra Chart
Broker: IB
Trading: Futures
Posts: 113 since Jan 2011
Thanks Given: 54
Thanks Received: 97

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
Thanked by:
  #9 (permalink)
ALFASSY
HAIFA ISRAEL
 
Posts: 12 since May 2011
Thanks Given: 8
Thanks Received: 1

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
  #10 (permalink)
ALFASSY
HAIFA ISRAEL
 
Posts: 12 since May 2011
Thanks Given: 8
Thanks Received: 1


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




Last Updated on June 13, 2011


© 2024 NexusFi™, s.a., All Rights Reserved.
Av Ricardo J. Alfaro, Century Tower, Panama City, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada)
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.
About Us - Contact Us - Site Rules, Acceptable Use, and Terms and Conditions - Privacy Policy - Downloads - Top
no new posts