Support Board
Date/Time: Sat, 15 Mar 2025 08:09:45 +0000
[Programming Help] - SPREADSHEETS - save last ES price to column if $tick-nyse hits 0
View Count: 697
| [2022-06-18 17:07:47] |
| SSGill - Posts: 36 |
|
Hello; I am trying to record in a column, (so I may display on my ES chart) the last price when the Tick last was equal or crossed over 0. When I get a true condition within a bar, it returns to false, when Tick last column information is over or under the 0 line in that same bar, because it happens so fast. So unless at when the bar ended, and tick was at 0, is the only time I would get this true condition. I tried to use crossover function, but same thing. New Tick Last prices coming in, overwrite the condition. Can you tell me how in spreadsheets if and when Tick Last crossed over the 0 line, from under or above, I can track the ES price at that instance? I have the information from both instruments (ES and Tick) in my spreadsheet by using the overlay study. So for example, I am using minute bars. if Tick last makes a crossover over/under 0, in column X I save the ES price in that instance, but before the bar is finished, if the crossover condition goes back to false, I won't lose my ES price that I recorded, before the bar closes? |
| [2022-06-18 22:07:58] |
| Sawtooth - Posts: 4183 |
|
Can you tell me how in spreadsheets if and when Tick Last crossed over the 0 line, from under or above, I can track the ES price at that instance?
It's not possible with a spreadsheet study to make an intrabar value persistent.It is only possible to lock an intrabar TRUE, using this method: https://www.sawtoothtrade.com/example-9.html Spreadsheet Studies Special Tasks: Locking the State of a One Time Condition |
| [2022-07-10 14:38:22] |
| SSGill - Posts: 36 |
|
Hello; I am trying to save the first instance when last TICK crosses over 0 for each 1 minute bar. I have tried the above instructions and used =AND(OR(E3=10,K3>0),H3=FALSE) the formula recommended. Please see the attached screenshot. So my formula is as follows: once the condition comes true in L3, turn the 0 to a 1. =AND(h$3=false,or(crossfromabove(aq3:aq4,0), l3>0)) Then I need to record the instance of last price (e3) upon L3 becoming true. =if(m3>0,m3,if(l3=1,e3,0)) both columns work on the first row, however the same value gets carried forward for each cell instead of L3, M3 resetting itself to 0. I have included a screenshot, and the workbook for your reference. The issue is in the TandP_ES.scss worksheet. Any other methods or solutions to achieve the same result would be greatly appreciated. I hope, when I get this resolved to display a dot on the ES price bar, at the price in column M on the chart. However, once the value goes true for the first minute I run the spreadsheet, the value carries over for each subsequent row, and will not reset itself. |
 Screenshot 2022-07-10 101102.png / V - Attached On 2022-07-10 14:35:39 UTC - Size: 64.16 KB - 124 views  Tick and Price divergence.Cht - Attached On 2022-07-10 14:36:54 UTC - Size: 13.71 KB - 280 views Private File |
| [2022-07-10 21:11:51] |
| Sawtooth - Posts: 4183 |
|
Locking an intrabar TRUE cannot produce an intrabar value when it locked. It could only mark the bar that it occurred within, to then get a OHLC price. But if that locked TRUE gets reset, that marked bar would lose its mark. So this would have a limited usefulness, e.g. for an entry or exit only while locked. Also, getting a TRUE to lock and unlock automatically properly can be tricky. |
| [2022-07-10 22:57:54] |
| SSGill - Posts: 36 |
|
Tom; Another workaround that I have thought about, is that I can use a second bar for the ES, and overlay a one second chart of the TICK, then record the last ES price when there is crossover on TICk at the 0 line. Because I am doing it second by second, I have a much more accurate record when Tick hit 0. Then I could average the ES price results for that minute (since the crossover may occur on multiple rows for that minute). I can't figure out how to take this average ES price for respective minute in my second chart over to my one minute ES chart. I referenced the documentation, and could not see how to reference another spreadsheet study. My goal would be to find the information on the one second chart worksheet that I averaged the crossover prices back to the respective minute of the one minute sheet. Is this possible? |
| [2022-07-11 01:04:42] |
| Sawtooth - Posts: 4183 |
|
I referenced the documentation, and could not see how to reference another spreadsheet study. My goal would be to find the information on the one second chart worksheet that I averaged the crossover prices back to the respective minute of the one minute sheet. Is this possible?
You would use the Study/Price Overlay study on the 1 minute chart to overlay the Spreadsheet Study study from the 1 second chart.Referencing Other Charts in Spreadsheet Study Formulas: Referencing Other Charts in Spreadsheet Study Formulas However, the spreadsheet study on the 1 second chart would need too many rows to be of much use overlaid on the 1 minute chart. IOW, a very large Number of Rows will likely cause some lag. |
| [2022-07-14 14:44:43] |
| SSGill - Posts: 36 |
|
Tom; Thanks for your post; I did not realize this study was there. Found some very helpful youtube videos on the feature too. |
To post a message in this thread, you need to log in with your Sierra Chart account: