Login Page - Create Account

Support Board


Date/Time: Tue, 18 Mar 2025 22:13:30 +0000



[User Discussion] - How to Specify an Event that Happened During a Prior Time Range in Spreadsheets

View Count: 341

[2022-09-25 02:31:25]
bradyfans - Posts: 16
I'm trying to figure out how to initiate a trade in spreadsheet for trading system with one of the conditions being that price broke an indicator's subgraph during a certain time period (0830-1000, 0900-1000, etc.)

I have figured out how to initiate a trade only during certain hours, but I'm not sure how to state a previous time condition (using VWAP as an example):

=AND(price opened below VWAP at 0830, price crossed VWAP after 0830, price is currently above VWAP, an inside bar or bearish engulfing pattern prints, current time is 1000-1200)

Most of this I can figure out. It's the part that specifies an occurrence that happened during a previous time period that I don't know how to express. Whether it's just stating price broke the line (crossedfrombelow) or is simply above the line, I don't know how to specify that this happened between the open and 10 am, but that I don't want to open a trade until a later time period.

Any help is welcome. Thanks!
[2022-09-25 04:03:18]
Sawtooth - Posts: 4186
You'll need to use Formula Columns to create persistent TRUEs of price below VWAP at 08:30, and price above VWAP after 08:30.

At 08:30, in P3:
=IF(AND(E3<ID1.SG1@3,FRACTIME($J$41)>=TIME(8,30,0),FRACTIME($J$41)<TIME(8,31,0)),1,IF(INT(A3)>INT(A4),0,P4))
where ID1 is the VWAP study.
This finds where the Close price is less than VWAP between 08:30 and 08:31, and if it's TRUE, it makes it persistent until the date changes)

Between 08:30 and 10:00, in Q3:
=IF(AND(E3>ID1.SG1@3,FRACTIME($J$41)>TIME(8,30,0),FRACTIME($J$41)<=TIME(10,0,0)),1,IF(INT(A3)>INT(A4),0,Q4))
This finds where the Close price is greater than VWAP between 08:30 and 10:00, and if it's TRUE, it makes it persistent until the date changes)

Then reference P3, Q3 in your entry formula:
=AND(P3,Q3,E3>ID1.SG1@3,FRACTIME($J$41)>=TIME(10,0,0),FRACTIME($J$41)<TIME(12,0,0),PricePatternCondition)

These are untested examples; feel free to tweak them.

Here are some other examples of persistent variables:
Spreadsheet Example Formulas and Usage: Persistent and Incrementing Variables

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account