Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sun, 12 May 2024 16:08:59 +0000
[Programming Help] - Backtesting issue. Time based indicator/ multiple charts and sheets.
View Count: 446
| [2020-10-06 03:10:52] |
| User339484 - Posts: 71 |
|
3 charts 3 Spreadsheets. 1. Main chart. 2. VIX historical + Spreadsheet 3. It grabs the last price (not settlement price) by making a time line at 16:59:59 on the second chart and gets the close. When the spreadsheet shows 100 for time line it knows to take the last price at E3 and write it down or post a 0. I then have a reference that takes MOSTRECENTNONZEROVALUE so basically I have a reference of the last price of vix from the previous day when I call Sheet3!S3, it updates each day after market close. 3. /ES similar to VIX + Spreadsheet 2. It grabs the last price (not settlement price) by making a time line at 16:59:59 on the second chart and gets the close. When the spreadsheet shows 100 for time line it knows to take the last price at E3 and write it down or post a 0. I then have a reference that takes MOSTRECENTNONZEROVALUE so basically I have a reference of the last price of ES from the previous day when I call Sheet2!S3, it updates each day after market clos. Back to the main chart. This spreadsheet pulls Sheet3!S3 and Sheet2!S3 and does calculations to get 1 Standard Deviation. This then does the math and writes cells for -1 deviation and +1 deviation. When I go to backtest this and try to use the deviations, it is only using the most recent deviation that is written. So if I test a month ago, its still using todays deviations for some reason. Is something I am trying to do here too complicated for spreadsheet style? Thanks! |
| [2020-10-06 12:58:26] |
| Sawtooth - Posts: 3995 |
|
It is recommended that you use one of the overlay studies to reference values on other sheets, instead of directly referencing the other sheets. Referencing Other Charts in Spreadsheet Study Formulas: Referencing Other Charts in Spreadsheet Study Formulas There is an easier way to find the previous close of a symbol, using the Spreadsheet Formula study, with a formula like this: =IF(ID1.SG1=100,C,ID2.SG1[-1]) - where ID1 is the Time Line study, and ID2 is the Spreadsheet Formula study. This will get the Close price at the time line and make it persistent until the following day. Then overlay this on the main chart using the Study/Price Overlay study. Then reference the overlay study in formulas on the main chart's spreadsheet study. You won't need to use spreadsheet studies on charts 2 and 3. |
To post a message in this thread, you need to log in with your Sierra Chart account: