Support Board
Date/Time: Mon, 25 Nov 2024 08:42:41 +0000
bars since alert
View Count: 339
| [2024-03-20 04:13:29] |
| User486817 - Posts: 83 |
|
hello, i'm trying to count the number of bars since an event using the following instructions here: Spreadsheet Example Formulas and Usage: Formula to Count Bars Since An Event here is what i have done: 1. added spreadsheet study to chart 2. input the new spreadsheet name as per these instructions #5: Using the Spreadsheet Study 3. i currently have a .scss file added to my chartbook with the corresponding spreadsheet study on my chart. now, going back to Spreadsheet Example Formulas and Usage: Formula to Count Bars Since An Event the instructions state: "The following formula will count the number of bars since a Buy Entry event occurred (cell K3). This will also stop counting if a Sell Entry event occurs (cell M3). This could be used to cancel an unfilled Order at some number of bars." therefore, in cell K3 i am to input the buy event formula and in cell M3 i am to input the sell event formula. on my chart at ID16 i have the color bar based on alert condition study, which is my buy alert. ID17 is my sell alert. i have copied and pasted IF(ID16.SG1<>0, 1, 0) into cell K3, representing if the buy formula is true. i also input =IF(ID17.SG1<>0, 1, 0) into cell M3, representing the sell alert. these formulas were accepted with no error. i went on to the next step and copied the formula as-is from here: Spreadsheet Example Formulas and Usage: Formula to Count Bars Since An Event to count the number of bars from the buy alert, i input =IF(M3, 0,IF(OR(K3, P4 > 0), P4 + 1, P4)) into cell P3. to count bars from the sell alert, i entred =IF(K3, 0,IF(OR(M3, Q4 > 0), Q4 + 1, Q4)) into cell Q3. both columns P and Q return #VALUE! error outputs. can you please help by showing me what i have done wrong? Date Time Of Last Edit: 2024-03-20 04:15:11
|
| [2024-03-20 15:47:41] |
| John - SC Support - Posts: 36238 |
|
We are not having any problems with the formulas as you have them in this post. Did you copy and paste the formulas from your spreadsheet? Most likely there is an extra parenthesis or something along those lines in your spreadsheet. Refer to the following to help find the error: Working with Spreadsheets: Viewing Formula Expression Tree For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-21 05:25:07] |
| User486817 - Posts: 83 |
|
hello john, first, i managed to correct the syntax issue per your instructions -- now, the K and M columns both display '1' all the way down. the P and Q columns both show '0' all the way down so i think that there is still something wrong with what i'm doing. in short, what i'm trying to accomplish is a count of bars since a buy alert/sell alert. i'm trying to print the actual number of bars since a buy alert below the the bar and likewise the number of bars since a sell alert above the bar using "value on high/low." when a buy alert comes in, the sell alert count stops and the buy alert count starts. currently, the spreadsheet study output on the chart displays only "1" when i display K (SG1) using Value on Low. so there is a disconnect someplace. i have not changed any spreadsheet study settings as i didn't see any that seemed to apply, but i could be wrong. i appreciate your help with this issue if you can, but if this is outside of the scope of support help i totally understand and have no hard feelings as you have helped me many times. thanks for your time John. |
| [2024-03-21 15:49:43] |
| John - SC Support - Posts: 36238 |
|
first, i managed to correct the syntax issue per your instructions -- now, the K and M columns both display '1' all the way down. the P and Q columns both show '0' all the way down so i think that there is still something wrong with what i'm doing.
Yes, something is not right here. Cells K and M should have your conditions for Buy Entry and Sell Entry, but they also need to be exclusive from each other, as you can not have a buy and sell at the same time. Per your earlier statements, you say you have the following: - K3: IF(ID16.SG1<>0, 1, 0) - M3: IF(ID17.SG1<>0, 1, 0) Are these in any way setup such that when ID16 is true ID17 is not true and vice versa? You really need to have something in these to have these becoming 0 when you no longer want the position open. currently, the spreadsheet study output on the chart displays only "1" when i display K (SG1) using Value on Low. so there is a disconnect someplace. i have not changed any spreadsheet study settings as i didn't see any that seemed to apply, but i could be wrong.
The count is in cells P and Q. So you would want to display those values. But you need to get your K and M cells working in opposite directions of each other first. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-03-22 01:41:26] |
| User486817 - Posts: 83 |
|
hello john, that was a very helpful and clear explanation. i will figure out how to code that and go from there. thanks so much for your time! kindest regards, |
To post a message in this thread, you need to log in with your Sierra Chart account: