Login Page - Create Account

Support Board


Date/Time: Tue, 18 Mar 2025 21:33:28 +0000



Post From: How to Specify an Event that Happened During a Prior Time Range in Spreadsheets

[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