Support Board
Date/Time: Tue, 26 Nov 2024 00:18:10 +0000
Calculating Price Change Since Last Alert
View Count: 366
| [2023-12-20 15:08:46] |
| pjk0225 - Posts: 33 |
|
I have a simple alert set to color the background of a subgraph when true. I'd like to then measure the percent change of price (open, last) from the start of that signal to the point at which it is no longer active. How can I reference these specific start/end points to measure their price change? Is it possible to do this using simple alerts? |
| [2023-12-20 16:31:42] |
| John - SC Support - Posts: 36286 |
|
It is not possible to do this using just the alerts. You would have to use the Spreadsheet Study to do this and either recreate your Alert condition in the spreadsheet or, if you are using the "Color Bar Based on Alert Condition", then you can reference the "Color Bar" subgraph for that study which will be a particular value when true (depending on how you setup the study). You can then use the "GETCORRESPONDINGMATCH()" along with the "ROW()" function to get the row number where the most recent value was true, then you can use that to determine the time you want. Note that you would nest these two functions like the following to get the Row number: =ROW(GETCORRESPONDINGMATCH()) You need to put in the correct parameters for the GETCORRESPONDINGMATCH function. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-16 13:45:54] |
| pjk0225 - Posts: 33 |
|
My spreadsheet formula doesn't seem to be doing anything. I first set up a spreadsheet and made an alert study to output "1" in column AB. I then tried using GetCorrespondingMatch in a spreadsheet formula study to find the most recent instance of "1" in column AB and return the Open price from column B for that row. I thought this would produce the Open value of that row on its own cell (GetCorrespondingMatch is in column AA, so I expected to see an Open price there) but column AA only displays "0" throughout the spreadsheet. Am I misunderstanding the application of this study? =GetCorrespondingMatch(AB3:AB20000, 1, 0, 1, 0, B3:B20000, 0) ^ this is what I used in the Spreadsheet Formula study, which outputs to column AA. While I'm here, if someone can help, I'm trying to achieve two things with this: 1) make a study tell me when 5 bars have passed since the last signal, and 2) measure the difference between the most recent price vs. the price corresponding to the most recent signal trigger (ex. Change of Open of Signal vs Open Now) I'd greatly appreciate any pointers or examples Date Time Of Last Edit: 2024-01-16 13:46:45
|
| [2024-01-16 15:10:21] |
| John - SC Support - Posts: 36286 |
|
Do not use columns AA and AB, unless you have increased the default Input for "Number of Formula Columns" such that columns AA and AB are now formula columns. Otherwise, AA and AB are going to be where other studies data is going to show, which will then overwrite what you have put in there. But also, these columns will not auto-fill with the formulas that you have entered. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-16 16:05:34] |
| pjk0225 - Posts: 33 |
|
Okay, I've thought about that while testing but I'm none the wiser as to what to actually do.
|
| [2024-01-16 16:33:45] |
| John - SC Support - Posts: 36286 |
|
Start by setting up your formulas starting in Column K, or the first available column if you are using columns for other purposes in the Spreadsheet Study. If you still have troubles after that, then let us know what is not working.
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-17 05:22:11] |
| pjk0225 - Posts: 33 |
|
So the formula works if I enter it manually into the spreadsheet, but it disappears if I recalculate the chart. Also, when I enter it into the "Spreadsheet Formula" study, it still outputs "0" instead of the open. The formula I'm using: =GetCorrespondingMatch(AB3:AB200000, 1, 0, 0, 0, B3:B200000, 0) EDIT: For clarification, is this study supposed to output only the most recent matching value, or should each row display its own corresponding value? Date Time Of Last Edit: 2024-01-17 05:26:48
|
| [2024-01-17 16:23:12] |
| John - SC Support - Posts: 36286 |
|
What do you mean it disappears when you recalculate the chart. Are you entering the formula in cell K3? You can not use the "Spreadsheet Formula" with the GETCORRESPONDINGMATCH() function. The Spreadsheet Formula, despite its name, uses the Alert syntax, not the spreadsheet syntax. The function only returns the first corresponding match. The parameters determine which one. We see that you are searching column AB, which is fine if that is where the data you are reviewing exists. But you then request the data be returned in column B, which is not a valid column - you would need this to be set to the column where you have entered the formula. Also, you would need to use the absolute reference for the rows, such as K$3:K$200000. And finally, if you do not add the ROW() function around the GETCORRESPONDINGMATCH(), you will not see anything. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-18 09:33:23] |
| pjk0225 - Posts: 33 |
|
I figured out how to achieve what I want using simple alerts. What do you mean it disappears when you recalculate the chart. Are you entering the formula in cell K3?
Yes, in cell K3. When I press ctrl+INS on the chart, the formula disappears. I have to press ctrl+INS each time I make an adjustment to my studies because a lot of the study cells (starting from AA) show as blanks if I don't. Pressing ctrl+INS populates them correctly. Also, you would need to use the absolute reference for the rows, such as K$3:K$200000.
I'm not comfortable referencing columns like this, because adding a new study can potentially shift the columns and affect the formulas. Isn't it possible to reference the studies directly with their study IDs (id_.sg_)?
|
| [2024-01-18 16:23:25] |
| John - SC Support - Posts: 36286 |
|
Ctrl+Ins is not a combination you would use with the Sierra Chart spreadsheets. To edit a cell, you just select it with the pointer or use the Enter key. With regards to the absolute reference, the K$3:K$2000000 is for where the results are put. So this is what you would want to use in the GETCORRESPONDINGMATCH() function for that Input. You can reference study subgraphs directly without using the cell references, which is the preferred method. Refer to the following: Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-18 17:07:53] |
| pjk0225 - Posts: 33 |
|
I need to press Ctrl+Ins on the price chart to populate my alert studies on the spreadsheet. In fact, I'm running a replay right now and the studies I have (Color Background when true, simple alerts) do not display on the chart or in the study summary window - I put the coloring studies in chart region 1 but the chart remains blank besides the price bars, and the study summary window displays 0 for their columns. If I press ctrl+ins or add the Periodic Chart Recalculation study, they magically appear. For comparison, I'm also running different "Color Background simple alert" studies based on simpler rules like "color background if RSI > 50" and these show up in real-time without issue. The study summary window display their colors appropriately. However, my other, perhaps more complicated studies act as if nothing is happening. To add more context, because this is a pressing issue for me, the studies that do not appear in real-time reference a number of different indicators simultaneously. I've judiciously gone through each one of those to ensure "Evaluate on bar close" is checked because I want the coloring to occur immediately when the next bar is opened. However, even with the Periodic recalculation study added and set to "1 second", the colors show up 1 bar too late. Here is a snippet of one of the "Color background" indicators that does not display properly unless forced: OR(
and(ID20.SG1, ID135.SG1, ID133.SG1, ID160.SG1, ID150.SG1), and(ID20.SG1, ID135.SG1, ID11.SG1, ID160.SG1, ID131.SG1), and(ID20.SG1, ID135.SG1, ID26.SG1, ID17.SG1, ID51.SG1), and(ID20.SG1, ID135.SG1, ID26.SG1, ID59.SG1, ID17.SG1), and(ID20.SG1, ID135.SG1, ID11.SG1, ID150.SG1, ID145.SG1) ) The studies themselves calculate back 200,000 bars but I only have one day's worth of data on the screen, so less than 1000 in my replay. |
| [2024-01-18 17:12:30] |
| John - SC Support - Posts: 36286 |
|
You have us a bit confused on whether the issue of studies not showing properly occurs only during replay or if it also occurs in real-time. Although you should not have this issue at all in either case. But please give us additional information on this question. If you are able to put together a relatively simple chart that shows the issue you are having, then please get us that chart by following these instructions: Support Board Posting Information: Providing Chartbook with Only a Single Chart For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-18 17:30:43] |
| pjk0225 - Posts: 33 |
|
Ok, I'll set that up for you.
|
| [2024-01-18 18:38:06] |
| pjk0225 - Posts: 33 |
|
Here is the chart. The study in question in the provided chart is id53, named [TEST ENTRY] SUMMARY - ZONE A, LONG. (My actual chart has more like this.) It is located at the top of the studies list. An example of the issue can be seen easily in replay mode. Set the date and time to 2023-12-29, 15:40:00. In my chart settings, I limited the chart data to just this one day. Open the Study Summary window. The first study column is the study in question, and the RSI studies next to it are there for comparison. My replay settings are "Calculate At Every TIck/Trade," "By Number of Trades," Unit 1. Run the replay and wait for the bar at 15:40:53 to open, then pause the replay and press Ctrl+Ins. This should highlight the previous bar's background, along with the study's column in the Study Summary window. If you right click the chart and click "Studies" and press OK to exit out, the background highlights will disappear again. You can also check how this study behaves in a spreadsheet. The study should be located in column AA. My previous experience tells me this issue will also occur with real-time data but I have been running disconnected from market data of late and have not tested this particular set of studies in real-time, so I cannot confirm that right now. But prior tests have shown me that what I see in replay mode is similar to what occurs in real-time. I want to note, I read something about Overlay-type indicators not behaving well with alerts. I don't know if it applies here, but one of my foundational studies is based on "Study Overlay - OHLC" which is set to the live chart's Open. I use this to reference the bar Open as a study, in order to use the Open value in the Divide/Ratio study. I have many simple alerts referencing it just fine even in real-time, but I thought I'd bring it up in case it is a potential problem. If you want to look at it, I placed it second on the list of studies, id323. If you intend to send the chart back to me, I'd like it sent to me privately. Thanks |
Private File |
| [2024-01-18 22:27:39] |
| John - SC Support - Posts: 36286 |
|
First, the chart you are using has some problem with it. It will not display any data for us. We had to save the studies as a "Study Collection" and then open a new chart and apply the study collection to that. In doing so, we looked at what you have setup for Study ID 53 that you reference. In particular, there is one study that it references in the Alert Condition that is in every AND() function - ID 20. Looking at that study alone, it does not show any True values for the day of data that you have the chart set to. Therefore, there will never be any True values for study 53. We also loaded 10 days of data and the same is true. We will not go deeper into how that study is setup to see what it references, so we can not do any further analysis with this as it is. Again, if you can simplify it more, that would be helpful. For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-19 06:06:49] |
| pjk0225 - Posts: 33 |
|
Did you press Ctrl+Ins? If you opened the studies in a new chart, please check that study 2 on the list, ID323 (OHLC), is set up correctly. It should reference the Open of the same chart. Nothing will work without this indicator set up correctly. For your reference, I'll attach a screenshot of my screen. I'll unhide ID20 and its underlying studies (ID39, ID42) and display them in the subgraphs directly below the chart. Their short names include their ID number. However, please note that ID20 is another one that will only show up by pressing ctrl+Ins, although ID39 and ID42 do not share this problem. ID20 references indicators that themselves reference the 'Study Overlay - OHLC' indicator, ID323 mentioned above. I'm using the OHLC study to reference not another chart but its own chart. ID39 and ID42 show no issues with this, but so I'm not sure why ID20 won't display unless Ctrl+Ins is pressed. After CTRL+INS
https://www.sierrachart.com/image.php?Image=1705644062300.pngBefore CTRL+INS - to recreate, press F6 -> OK
https://www.sierrachart.com/image.php?Image=1705644270461.png
Date Time Of Last Edit: 2024-01-19 06:07:23
|
| [2024-01-19 16:01:29] |
| John - SC Support - Posts: 36286 |
|
You have something not setup correctly, and your chart is too complicated for us to try to figure it out for you. You should NOT have to select Ctrl+Insert in order to get information to show up. If this is occurring, then you simply have something wrong in your setup. Keep in mind that if you have studies that depend on other studies, then you need to have those studies further down in the list of studies, as that is the general calculation order, although some studies are different precedence. Refer to the following: Chart Studies: Study Calculation Precedence And Related Issues For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2024-01-20 08:54:13] |
| pjk0225 - Posts: 33 |
|
Keep in mind that if you have studies that depend on other studies, then you need to have those studies further down in the list of studies
That fixed it, thanks. |
To post a message in this thread, you need to log in with your Sierra Chart account: