Home >> Support Board

Support Board

Date/Time: Fri, 03 May 2024 20:43:02 +0000

How to Post a Message for Support

[Programming Help] - Overlaying Pivot Points

View Count: 1473

[2021-08-11 00:33:12] #25 [Go To First Post]
Botan626 - Posts: 294
It will show pivots for the range bar, if it's last on a chart with the date for the previous day. How can I fully recalculate spreadsheet? It shows obsolete numbers in columns K-U even after chart reload and recalculate. Date Time Of Last Edit: 2021-08-11 00:37:06


02.Cht - Attached On 2021-08-11 00:32:57 UTC - Size: 12.92 KB - 176 views Pivots.scss - Attached On 2021-08-11 00:33:06 UTC - Size: 32.13 KB - 158 views

[2021-08-11 00:55:03] #26 [Go To First Post]
Sawtooth - Posts: 3993
I don't see any spurious numbers in the latest files. Reload and Recalculate, or just Recalculate, should clear any spurious numbers. Is there a reason you are only loading a few days of data? Load 100 days and see what happens.

[2021-08-11 01:00:15] #27 [Go To First Post]
Botan626 - Posts: 294
Reload and Recalculate, or just Recalculate, should clear any spurious numbers. This doesn't help. http://www.sierrachart.com/image.php?Image=1628643406763.png Is there a reason you are only loading a few days of data? Load 100 days and see what happens. Set 'Days To Load' to 100: http://www.sierrachart.com/image.php?Image=1628643542562.png This is not right?

[2021-08-11 01:09:20] #28 [Go To First Post]
Botan626 - Posts: 294
After I restarted SC, spreadsheet fully updated: http://www.sierrachart.com/image.php?Image=1628644049352.png Date Time Of Last Edit: 2021-08-11 01:11:27

[2021-08-11 01:43:48] #29 [Go To First Post]
Sawtooth - Posts: 3993
I don't see any spurious numbers.

[2021-08-11 11:46:13] #30 [Go To First Post]
Botan626 - Posts: 294
Is it ok, that column K doesn't start counting from 1 on this screenshot http://www.sierrachart.com/image.php?Image=1628643406763.png ? Even so, pivots are plotted correctly on the chart, thanks a lot for your help and explanations, tomgilb. But is it possible to make pivots not plotted at all, if destination chart doesn't have yet a bar for the current day? Date Time Of Last Edit: 2021-08-11 11:51:35

[2021-08-11 12:21:09] #31 [Go To First Post]
Botan626 - Posts: 294
But is it possible to make pivots not plotted at all, if destination chart doesn't have yet a bar for the current day? I think I got it. I put in M3: =AND(L3=$K$3, TODAY()=INT(A3)) And modified formula in O3 to: =IF($M3=1,ID3.SG12@3,0) Is it a correct approach? Date Time Of Last Edit: 2021-08-11 12:25:18

[2021-08-11 14:46:54] #32 [Go To First Post]
Sawtooth - Posts: 3993
Is it ok, that column K doesn't start counting from 1 on this screenshot It starts counting at 1 at some row. Scroll the sheet down to see where. It's not important where it starts counting because all that is needed to find the current day is a value in $K$3 that is equal to the max of column K in L3. But is it possible to make pivots not plotted at all, if destination chart doesn't have yet a bar for the current day? Your approach would only work in real time. It would not work in Replay Chart or backtesting because TODAY() is real time today, not chart time today. If you never need to do a Replay Chart, your approach is the simplest.

[2021-08-11 14:58:39] #33 [Go To First Post]
Botan626 - Posts: 294
because TODAY() is real time today, not chart time today But if a chart time today (you mean the To: date in the Date Range right?) is not a real time today, then I don't need to see pivots from current real time today on the destination chart. If you never need to do a Replay Chart, your approach is the simplest. How can this be done better? Date Time Of Last Edit: 2021-08-11 15:44:43

[2021-08-11 19:27:05] #34 [Go To First Post]
Sawtooth - Posts: 3993
But if a chart time today is not a real time today then I don't need to see pivots from current real time today on the destination chart. Chart time today is the same as real time today if the chart is in real time. If the chart is in Replay, then it is no longer tied to real time. The TODAY function is looking at your computer clock, not Sierra Chart. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#TODAY_Function How can this be done better? If you always use the chart in real time, your approach is the best.

[2021-08-11 20:01:06] #35 [Go To First Post]
Botan626 - Posts: 294
If you always use the chart in real time, your approach is the best. I'm very pleased to hear that from you, thanks. I started to see #CREF! error in some cells in L column. Do you know, what could be a reason for that? Date Time Of Last Edit: 2021-08-11 20:33:27

[2021-08-11 21:27:13] #36 [Go To First Post]
Sawtooth - Posts: 3993
I started to see #CREF! error in some cells in L column. Do you know, what could be a reason for that? It is seeing a circular reference somewhere. If it is, I would expect it to be in all cells in that column. Focus a cell with #CREF and click on Details in the upper right corner of the sheet.

[2021-08-12 10:47:39] #37 [Go To First Post]
Botan626 - Posts: 294
Strangely, I don't see it anymore. When I saw it the last time, I pressed 'Recalculate All Formulas' button and it was gone. I have a Q about formula in L3 from post #21: =MAX(K3:OFFSET(K3,$J$30-2,0)) Why is it $J$30-2 ? Doesn't K3 count as 1 cell with row 3, so we need to give it 997 offset to get the last value in the column, if J30=1000? Date Time Of Last Edit: 2021-08-12 11:02:42

[2021-08-12 14:58:09] #38 [Go To First Post]
Sawtooth - Posts: 3993
Doesn't K3 count as 1 cell with row 3, so we need to give it 997 offset to get the last value in the column, if J30=1000? J30 is the number of chart bars that the spreadsheet sees, where each chart bar is a row, and the current bar is in row 3. If J30 is 1000, then there are 1002 rows in the spreadsheet, because of the header rows of 1 & 2. The idea to subtract 2 from J30 is to compensate for the header rows, so really the -2 is not accurate, as you point out. It should actually be -1 to get the last row, which is 1002 if J30 is 1000. The offset that this OFFSET uses in this situation is not critical. The goal is to count any number of days covered by the sheet. This would be a better L3 formula, as it only looks at the existing rows of the sheet: =MAX(K3:OFFSET(K3,$J$30-ROW()+2,0)) Actually, after taking a closer look at this, you don't even need the formula in L3, for this day count setup. Your M3 formula could be: =AND(K3=$K$3,TODAY()=INT(A3)) In fact, you could eliminate L3 and M3 and use this in O3: =IF(AND(K3=$K$3,TODAY()=INT(A3)),ID3.SG12@3,0) Date Time Of Last Edit: 2021-08-12 19:50:13

[2021-08-13 17:58:59] #39 [Go To First Post]
Botan626 - Posts: 294
Thank you, Tom. I finally managed to plot pivots for a specific day on the destination chart. After many tries I decided to output pivots data from source chart to spreadsheet, this way I always have a correct date for pivots I want to see on the destination chart, which helps to avoid plotting lines, if there are no bars on the destination chart with date, matching pivots date from the source chart. The problem was, that in case of 0-60-0 destination chart Study/Price Overlay study plots lines at 00:00:00 time of desired date, but in case of range bars it plot lines at the last bar of the previous day, if it exists on the chart, and if not, then it plots lines at any next date bar. I have 'Fill Blanks With Last Value' set to No. Please have a look at what I've done in the attached files. What do you think about this approach? Is it efficient? Any drawbacks? Also, I have another 2 questions: 1. Why you told me to use Dash draw style in post #17 and not Line draw style? 2. The K3 formula in post #21. =IF(INT(A3)>INT(A4),K4+1,K4) You said, that: This finds the date change and increments the previous count starting at the first date in column A. Why do we need to do that? Date Time Of Last Edit: 2021-08-13 18:04:21


02.Cht - Attached On 2021-08-13 17:58:45 UTC - Size: 13.14 KB - 154 views Pivots.scss - Attached On 2021-08-13 17:58:53 UTC - Size: 49.37 KB - 143 views

[2021-08-13 20:16:59] #40 [Go To First Post]
Sawtooth - Posts: 3993
What do you think about this approach? Is it efficient? Any drawbacks? - It is an innovative approach that works for your specific need. - I don't see any inefficiencies, but you could remove the overlay study from the range chart. - The only drawback is that your solution meets a narrow need, but it leaves no room for any mods. This is moot if you never need to change. 1. Why you told me to use Dash draw style in post #17 and not Line draw style? The Dash draw style draws an horizontal line with no connecting line, if the value were to change. Since you are only displaying a single day's pivots, there would be no connecting line, so the Line draw style would work the same in your particular case. Why do we need to do that? You need the K3 formula to increment the day count of all the days in the spreadsheet. It doesn't matter how many days there are in the count. To find all of today's rows we compare the max count in column K with only the latest value in column K ($K$3). The current day is in every row where $K$3 equals the max count in column K.

[2021-08-13 21:40:23] #41 [Go To First Post]
Botan626 - Posts: 294
I don't see any inefficiencies, but you could remove the overlay study from the range chart. Right, missed it. the Line draw style would work the same in your particular case When draw style is Dash, pivots cover the whole width of corresponding bars, I changed draw style of PP to Line, you can see the difference in the attached files. To find all of today's rows we compare the max count in column K with only the latest value in column K ($K$3). Looks like I don't need the K3 formula, since I do this: AND(INTDATE(Sheet2!$A$3)=TODAY()=INTDATE(A3)) Am I wrong? I found a drawback. The pivots on the destination chart don't update, until I press Recalculate. In the attached files in the source chart you can switch from 'Use Number Of Days To Load' to 'Use Date Range' back and forth and see, that pivots on the destination chart won't update, though values in the spreadsheet update correctly. I noticed, that calculation time for Spreadsheet Study on the source chart was e.g. 42ms and on the destination chart 34ms (visible in Studies To Graph after study name). Probably that is the reason. I removed the overlay study from the destination chart and now it updates faster, than the source chart. Do you know, how this could be resolved? Date Time Of Last Edit: 2021-08-13 21:41:53


02.Cht - Attached On 2021-08-13 21:40:08 UTC - Size: 12.75 KB - 159 views Pivots.scss - Attached On 2021-08-13 21:40:18 UTC - Size: 40.24 KB - 146 views

[2021-08-13 22:43:40] #42 [Go To First Post]
Botan626 - Posts: 294
I don't get it. The Spreadsheet shows correct values. Why the chart won't update automatically to match the current spreadsheet values? E.g. if I put a plain number in the K column and draw the K line on the chart, it's value updates every time I update number in the K column. Date Time Of Last Edit: 2021-08-13 22:50:37

[2021-08-14 00:36:27] #43 [Go To First Post]
Sawtooth - Posts: 3993
When draw style is Dash, pivots cover the whole width of corresponding bars, I changed draw style of PP to Line, you can see the difference in the attached files. The Dash draw style draws a dash from the left edge to the right edge of the bar's width. The Line draw style draws a line from the center of the bar's width to the center of the adjacent bar's width center. Because of the, Dash is the preferred draw style for pivots. Looks like I don't need the K3 formula, since I do this: AND(INTDATE(Sheet2!$A$3)=TODAY()=INTDATE(A3)) Am I wrong? That should work. Why the chart won't update automatically to match the current spreadsheet values? Probably a calculation precedence issue. Make sure the studies are in calc order in the Studies to Graph list. You could add the Chart Recalculation - Periodic study.

[2021-08-14 08:26:35] #44 [Go To First Post]
Botan626 - Posts: 294
Make sure the studies are in calc order in the Studies to Graph list. I have only 1 Spreadsheet Study on the destination chart, on the source chart Spreadsheet Study is the 2nd in calculation order. You could add the Chart Recalculation - Periodic study. I don't need to recalculate chart every N seconds, I need it to recalculate every time spreadsheet updates.

[2021-08-14 13:50:49] #45 [Go To First Post]
Sawtooth - Posts: 3993
Maybe your innovative approach won't work because of some esoteric reliant functionality between spreadsheet sheets that is not present. Although you can reference another sheet, it is not recommended to do so when using data from other charts. Referencing Other Charts in Spreadsheet Study Formulas: Referencing Other Charts in Spreadsheet Study Formulas It is still not clear to me why you need to only display 1 day on your source chart. You have deviated from my original suggestion, which was: - to populate the source chart with many days of pivots. - overlay the pivots to the lower time frame charts using Fill Blanks With Last Value. - filter the day to display with the spreadsheet study formulas. I know you have run into some problems with this, and it is probably due: - to differences in dates between charts because of session times, or with historical charts' settlement times. - to the ln:12 and ln:13 settings in the Study/Price Overlay study. A date is a date so it's just a matter of aligning them within the limitations of Sierra Chart. I haven't chased every issue you've brought up because I'm trying to stay out of the weeds in this venue. I don't have the big picture of what you want to do, something I would require if you were to hire me to create something like this. I've helped you more than I do for most, and at some point what I offer for free crosses into what I offer for a fee.

[2021-08-14 16:16:04] #46 [Go To First Post]
Botan626 - Posts: 294
I really don't understand, why you call this approach innovative. I just output data from source chart to sheet #2, then use that data on sheet #1 to calculate pivot formulas. I think it's as simple, as it can be. Although you can reference another sheet, it is not recommended to do so when using data from other charts. Yes, but in the end Spreadsheet produces correct pivot values, it's the chart, that doesn't update automatically. I asked SC support about this in the new thread: Chart doesn't update with new spreadsheet values It is still not clear to me why you need to only display 1 day on your source chart. Because I need to see pivots on the destination chart for 1 day only, so I consider plotting pivots for multiple days on the source chart as excessive. I found a way to solve this problem. Now I use only date data from sheet #2 and I get pivots data from overlay study on the destination chart. Pivots are plotted correctly for desired day on the destination chart with time-based or range bars without chart recalculating, using any Days-Mins-Secs Per Bar setting on the source chart. I attached the files, in case you or anybody else would like to see them. Thank you, Tom, for your help, explanations and inputs. Date Time Of Last Edit: 2021-08-15 11:52:00


02.Cht - Attached On 2021-08-15 11:51:48 UTC - Size: 13.29 KB - 136 views Pivots.scss - Attached On 2021-08-15 11:51:54 UTC - Size: 44.1 KB - 138 views

[2021-08-14 19:16:14] #47 [Go To First Post]
User90125 - Posts: 715
I haven't chased every issue you've brought up because I'm trying to stay out of the weeds in this venue. I don't have the big picture of what you want to do, something I would require if you were to hire me to create something like this. I've helped you more than I do for most, and at some point what I offer for free crosses into what I offer for a fee. Kudos to Tom for being very generous with his time and knowledge on addressing the OP's issue for FREE. BTW, Tom is a real trader who actually does SC spreadsheet programming for a living, so the OP may want to contact him offline for a PAID consult that would address the nuances accordingly. https://www.sawtoothtrade.com/ https://www.linkedin.com/in/tom-gilbert-92497b1a Date Time Of Last Edit: 2021-08-14 19:25:48

[2021-08-14 21:13:54] #48 [Go To First Post]
Botan626 - Posts: 294
the OP may want to contact him offline for a PAID consult that would address the nuances accordingly OP said in the post, preceding your post, that the task, described in the 1st post, is accomplished, with Tom's help and guidance. OP also attached chartbook and spreadsheet in the same post, that satisfy the 1st post task, which you can take and use for FREE. Moreover, if you were more attentive, you'd have noticed, that Tom already offered OP to solve the 1st post task for a fee in the post #10, in case of OP's complete unwillingness to use his own brain, but, as you might see, OP did choose a different way. Date Time Of Last Edit: 2021-08-14 21:19:01

[2021-08-15 22:27:14] #49 [Go To First Post]
User90125 - Posts: 715
TL;DR hoping someone else got something out of this long and wieldy discussion. By the sounds of it, looks like the OP did use his brain (as he put it), after all.

To post a message in this thread, you need to log in with your Sierra Chart account: