Support Board
Date/Time: Mon, 25 Nov 2024 05:33:15 +0000
Spreadsheet, formula including all session bars
View Count: 566
| [2024-02-18 13:09:47] |
| User440951 - Posts: 50 |
|
Hi, I would like to create a formula in a spreadsheet that gives me an alert when in the session, 10:00:00 p.m. to 9:59:59 p.m. for me, the price has touched two very specific levels. To keep it simple in this request, 1 will be true and 0 false. I tried this : =IF(AND(CROSSOVER(ID0.SG4, ID2.SG1), CROSSOVER(ID0.SG4, ID2.SG2)), 1, 0) Or this =IF(AND(AND(FRACTIME(J41)>=TIME(22, 00, 00, 750), FRACTIME(J41)<TIME(21, 59, 59, 250)), CROSSOVER(ID0.SG4, ID2.SG1), CROSSOVER(ID0.SG4, ID2.SG2)), 1, 0) But it always returns false to me even though the conditions are met in the sessions. I can't find the solution, could you help me? |
| [2024-02-18 14:20:37] |
| Sawtooth - Posts: 4120 |
|
In spreadsheet syntax, the CROSSOVER function requires a range, and a reference to the row#: =CROSSOVER(ID0.SG4@3:ID0.SG4@4, ID2.SG1@3:ID2.SG1@4) https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#CROSSOVER_Function Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study |
| [2024-02-18 14:34:24] |
| User440951 - Posts: 50 |
|
Thanks for your return. But in my case how can I integrate all the bars of the session? Those past and those to come? |
| [2024-02-18 14:56:49] |
| User440951 - Posts: 50 |
|
Thanks for your feedback. In fact, it is a spreadsheet formula but not just an alert and therefore it seems to me possible to use the IF function. I took 1 and 0 as true and false values to simplify my request, but in fact what I want to do is when the price has not touched two pivot points R1 and R2 (only one is possible) for example, it draws me a line at X% of point R1, but if the price touched R1 AND R2 it draws me a line at Y% of R1 and this in the session only. For the next session everything is reset to 0. |
| [2024-02-18 21:20:24] |
| Sawtooth - Posts: 4120 |
|
=IF() is not a supported function
The IF function is supported in both Alert syntax and spreadsheet syntax.what I want to do is when the price has not touched two pivot points R1 and R2 (only one is possible) for example, it draws me a line at X% of point R1, but if the price touched R1 AND R2 it draws me a line at Y% of R1 and this in the session only.
- You would need to create a persistent variable when price has not yet touched R1 and R2.- Then release it and create another persistent variable when price has touched both R1 and R2. - Once the formulas return values, use another spreadsheet column to limit their display to the session times. Here are some formula examples of persistent variables: Spreadsheet Example Formulas and Usage: Persistent and Incrementing Variables Notice that one IF starts the persistence, and a second IF releases it. |
| [2024-03-24 18:15:54] |
| User440951 - Posts: 50 |
|
I worked on the subject, it was complicated but I think something happened. I was able to create my persistent variables in columns in this form : =IF(CROSSFROMABOVE(ID0.SG3@3:ID0.SG3@4,ID2.SG3@3:ID2.SG3@4),1,AQ4) And I was able to modify my plots when the conditions are true with this formula in another column : =IF(AQ3=1,(ID2.SG13@3-((ID2.SG13@3-ID2.SG3@3)*0.236)),(ID2.SG13@3-((ID2.SG13@3-L3)*0.236))) On the other hand, I have concerns about persistent variables which set me to true when it should be false. I think it comes from the fact that it doesn't take into account the session only. You say "- Once the formulas return values, use another spreadsheet column to limit their display to the session times" How can I do this ? Is this a formula that will impact all persistent variables ? |
| [2024-03-24 20:11:35] |
| Sawtooth - Posts: 4120 |
|
This is the basic idea to limit display to session times: =AND(FRACTIME(A3)>=TIME(9,30,0),FRACTIME(A3)<TIME(16,00,0)) You could put this in a spare Formula Column, e.g. AR3, and reference that column in each of your line formulas, like this: =IF(AND(AQ3=1,AR3=1),(ID2.SG13@3-((ID2.SG13@3-ID2.SG3@3)*0.236)),(ID2.SG13@3-((ID2.SG13@3-L3)*0.236))) |
| [2024-03-24 21:05:34] |
| User440951 - Posts: 50 |
|
I'm going to test this. Thank you so much !
|
| [2024-03-24 21:30:33] |
| User440951 - Posts: 50 |
|
One more little thing, I hear my session from 10 p.m. until 9:59 p.m. the next day. should I put this into a formula ? : =AND(FRACTIME(A3)>=TIME(22,00,0),FRACTIME(A3)<TIME(21,59,59)) |
| [2024-03-24 22:59:03] |
| Sawtooth - Posts: 4120 |
|
If you cross Midnight, use OR instead of AND: =OR(FRACTIME(A3)>=TIME(22,00,0),FRACTIME(A3)<TIME(21,59,59)) |
To post a message in this thread, you need to log in with your Sierra Chart account: