Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Fri, 19 Apr 2024 05:30:20 +0000
Custom Spreadsheet Study not recalculating with changes.
View Count: 1175
| [2018-04-18 16:48:01] |
| Mooshu22 - Posts: 25 |
|
I have a signal I created that is not recalculating when changes are being made anywhere in the chartbook. I am wondering if I am asking too much of the "spreadsheet system for trading"? I am calculating this signal in four steps: #1: I have a custom spreadsheet formula I created in column W: =((E3-MIN(D3:D43))/(MAX(C3:C43)-MIN(D3:D43)))*100. #2: Then smooth this column of data to a line by using a sierra chart built in study which charts to column AF: Hull MA (HMA); setting "based on data" to the spreadsheet, "input data" to column W, length 20. #3: Repeat steps #1-#2 with slightly different settings in column X and the HMA charts to AH (similar number of rows and HMA length for the settings) #4 Next I have a column Y setup to determine if line AH is greater or less than line AF data. The data in columns AH and AF are only posting row AF3 and AH3 when changes are made and rows AF4,AF5, and so on are blank. The only way to get them back is to close the chartbook and open or right click in the spreadsheet and "recalculate all formulas." Is there a setting I am missing to get these HMA columns to recalculate? Thank you- |
| [2018-04-19 00:07:44] |
| |
|
Based upon what you are saying, it is clear that you have a circular reference which is not going to work properly. This is explained here: Chart Studies: Circular Study References When Using the Spreadsheet Study Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2018-04-19 00:07:55
|
| [2018-04-19 16:13:05] |
| Mooshu22 - Posts: 25 |
|
This is exactly the problem and makes perfect sense now. Thank you for leading me in the right direction. I've added another spreadsheet to work around the problem however, no matter where I place the additional spreadsheet in the "Studies to Graph" list the new sheet has a CalcOrder lower than the HMA data that I'm trying to signal and has the same problem. I'm assuming the best way to work around this is to calculate the HMA data using new columns in the first spreadsheet with the HMA formula, and then referencing these new columns of HMA data in a spreadsheet with a later CalcOrder. Is this line of thinking accurate? If so, this would lead me to ask if there is a spreadsheet formula posted somewhere showing how HMA is being calculated in Sierra Charts (the actual code)? Or is this the only reference?: Moving Average - Hull Thank you for all the help. EDIT: I did some more looking and found the .scss download on that page with the calculations being made in the spreadsheet page IntermediateCalculations. I can work with that from here. If you could confirm my line of thinking in this solve I would appreciate it. Thank you once again! Date Time Of Last Edit: 2018-04-19 16:19:43
|
| [2018-04-19 19:35:46] |
| |
|
Is this line of thinking accurate? In general, a study which is calculated based on Spreadsheet sheet data cannot be used within that same Spreadsheet but it can be used in another Spreadsheet which is calculated later.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing |
| [2018-04-20 16:14:56] |
| Mooshu22 - Posts: 25 |
|
Thank you for the response. I have been looking at the Moving_Average_-_Hull.148.scss reference spreadsheet on how the HMA is calculated. I am trying to replicate these HMA calculations in to my own spreadsheet system to avoid the circle reference and I can't figure out how to calculate the data for Column G, Rows 3-29. I understand how the HMA is calculated in multiple steps but I don't understand how you would get results for Column G, ROWS 3-29 if the formula calculates the data this way. I attached a picture of the spreadsheet and how each column of calculations references data. It's not until ROW 30 that you get the HMA using a 25 period length. How are the results for Rows 3-29 being calculated in an HMA study that I'm adding to my chart?
|
 HMA Question.png / V - Attached On 2018-04-20 16:14:33 UTC - Size: 43.04 KB - 234 views |
| [2018-04-20 19:01:55] |
| |
|
We did not look at this comprehensively but one thing about the spreadsheet which shows the formulas is that the rows are ascending in time and not descending as compared to when using the Spreadsheet study. Aside from that we are going to add the HULLMOVINGAVERAGE and the WEIGHTEDMOVINGAVERAGE functions to the spreadsheet component. Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2018-04-20 19:02:54
|
| [2018-04-20 19:10:38] |
| Mooshu22 - Posts: 25 |
|
Excellent thank you for the feedback. I will be looking forward to the next update including these spreadsheet functions. Is there a release schedule for these functions? Judging by the what's new page you are due soon for another update: https://www.sierrachart.com/index.php?page=doc/Whats_New.php Thank you once again. |
| [2018-04-20 23:53:21] |
| |
|
We cannot say when these functions would be available. It could be more than two months but maybe less. We just cannot say.
Sierra Chart Support - Engineering Level Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy: https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service: Sierra Chart Teton Futures Order Routing Date Time Of Last Edit: 2018-04-20 23:53:38
|
To post a message in this thread, you need to log in with your Sierra Chart account: