Support Board
Date/Time: Sat, 23 Nov 2024 08:30:29 +0000
Audible alert on Spreadsheet study when cell value exceeds a certain number
View Count: 169
| [2024-09-05 09:37:32] |
| trader2017 - Posts: 98 |
|
I have a calculation on a Spreadsheet System For Trading study that references data from several sheets in the spreadsheet workbook to produce a total profit number from trades on different charts. I would like to have an audible alert when the total profit number goes above a certain number, for example $100. Is that possible, I’ve read through the material on alerts but could not find information on how to accomplish that.
Date Time Of Last Edit: 2024-09-05 09:38:53
|
| [2024-09-05 14:40:27] |
| John - SC Support - Posts: 36238 |
|
You would need to reference the Profit cell in a column somewhere that is not being used for anything else (just make sure it is not beyond column BR) and put in a condition that tests the value you want and returns 0 or 1 depending on the value you want to test against. For instance, you would use the following formula to test if the Profit/Loss is more or less than 100: =$J$11 > 100 Then you can use the subgraph for that column in an alert condition somewhere. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-09-06 01:54:00] |
| trader2017 - Posts: 98 |
|
I have a sheet in the workbook that I created with the spreadsheet>add spreadsheet command called “Profit.” On that spreadsheet is where I reference all the other sheets’ data, for example: =Sheet21!$J$10. That Profit spreadsheet has all the calculations and is not linked to a chart directly the way Sheet21, Sheet22, Sheet23 etc. is, so all the cells are blank until I start to reference data or add formulas to the cells. From there is where I’m trying to determine when a cell value is over $100. The cell I’m using for the total is $A$6 so using your example I wrote =$A$6 > 100 in cell $I$6 on the Profit spreadsheet. Assuming that cell $A$6 is $101 then the $I$6 cell will have a returned value of 1. I don’t understand what you mean by: “Then you can use the subgraph for that column in an alert condition somewhere.” How do I reference the returned value of 1 in cell $I$6 from my Profit spreadsheet to set an audible alert that will continue to play as long as the value is over $100. Thank you for your help. |
| [2024-09-06 14:02:07] |
| John - SC Support - Posts: 36238 |
|
It sounds like you have at one of the Spreadsheet Studies on a chart. You would need to put test formula in that sheet and then reference back to the "Profit" sheet, such as =Profit!$A$6. Whatever column you put that into on the sheet will have a corresponding subgraph. For instance, for Column K the subgraph is Subgraph 1. If you look at the Subgraph tab of the Spreadsheet Study Settings, you will see these listed out. You can then reference that subgraph in the Alert condition by entering a formula like the following: =ID1.SG1 > 100 Where ID1 is the ID of the Spreadsheet study and SG1 is the first subgraph (in this case, column K). Refer to the following and the section below it: Study/Chart Alerts And Scanning: Referencing Study Subgraphs For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-09-07 06:51:12] |
| trader2017 - Posts: 98 |
|
Skip to Edit 3 if you don't have time to read all of this. I kept it here if anyone else is trying to accomplish the same thing. --------------------------------------------------------------------------------------------------------------------------------------------- I’m trying to get this to work, I read through the documentation you referenced. So far, I have part of it working. The charts I’m interested in totaling profits from are =Sheet21!$J$10 =Sheet22!$J$10 =Sheet23!$J$10. My “profit” spreadsheet references those sheets in the workbook and calculates the total profit from each of them in cell $A$6. In Sheet21 at $K$1 which is above the words “Buy Entry” I wrote: =profit!$A$6 and it returned the profit from $A$6 when I ran a simulation. So far, so good. In the Spreadsheet System for Trading study on Chart 21 it has an ID number of 99. I selected the Settings button. One of the Input Names is Column K Alert (In:30) and I set that to Alert 10. Not sure if I had to do that or not. Then I went to the Alerts Tab and wrote: =ID99.SG1>100. I selected the Alert sound as Alert 10 checked the Enabled box, tried to select the Reset Alert Condition on New Bar but after I selected the OK button and then went back to see if it stayed checked, the Enabled checkbox was still checked but the Reset Alert Condition on New Bar went back to unchecked status for some reason, it kept unchecking itself when I tried again. In simulation there was no audible alert of Alert 10 when the profit reached 100 and stayed over 100. One confusing thing is that ID99.SG1 refers to the Buy Entry (SG1) in the Subgraph tab in the Study Settings for ID99. I thought maybe the Input Name Column K Alert which had in parentheses In:30 meant that I should substitute SG1 with SG30 but that didn’t work either. I'm not running an automated trading system with this study, I'm only including it to be able to calculate the total profit from different charts. What am I doing incorrectly? Edit 1: I think in your previous replies, you may have been thinking that I was using the Spreadsheet study. I’m using the Spreadsheet System for Trading study. I just added the Spreadsheet study and noticed that column K does not say Buy Entry. Am I supposed to be using column O in some way for the Spreadsheet System for Trading study? Edit 2: If I place =profit!$A$6>100 in the Spreadsheet System for Trading study on Sheet 21 in $O$3 and then take one of my other studies such as Daily OHLC and add to the alert tab: =ID99.SG5=1 (which is column O's subgraph) then I will get an audible alert and also the Reset Alert Condition on New Bar will stay checked so the alert will keep playing for each subsequent bar. The only problem is that as soon as I save the chart studies as a template the formula =profit!$A$6>100 disappears from $O$3. I don't know how to enter a formula in Sheet 21 column O and have it stay there constantly. Edit 3: I got it to work!!! From reading other questions about data disappearing from cells one of the answers from Sierra Chart was the following: It sounds like the Formula Source Sheet Number input with the Spreadsheet study is not set correctly. This is documented here: https://www.sierrachart.com/index.php?page=doc/doc_SpreadsheetStudyInputs.html#FormulaSourceSheetNumber So I kept everything the same that I wrote about above but added =profit!$A$6>100 to $O$3 on Sheet 1 even though I’m not using Sheet 1 for the charts that I’m trying to total the profit from. Sheet 1 automatically copied that formula to all the other sheets in the workbook. Now the alert plays in simulation and the formula does not disappear when saving the spreadsheet to a study template. Thanks again for your help. Just wanted to mention while I’m writing that I really appreciate Sierra Chart, it’s my favorite trading application of all the ones I've worked with, and the support is excellent too. Lisa Date Time Of Last Edit: 2024-09-07 10:39:39
|
To post a message in this thread, you need to log in with your Sierra Chart account: