Home >> Support Board

Support Board

Date/Time: Sun, 12 May 2024 08:41:47 +0000

How to Post a Message for Support

[Programming Help] - Spreadsheet study: Direct A3 result alignment between two sheets

View Count: 513

[2020-10-13 08:34:19] #1 [Go To First Post]
User185210 - Posts: 59
Hello, I would very much appreciate some help with the following formula in a study spreadsheet There are two charts: #1 "15 min" , #2 "1 tick range", a study spreadsheet and I would like to have the sheet#1 results correctly aligned and computed on each sheet #2 rows. Now I use this formula in the begining of all formulas in sheet#2 but it only works for the last 5 bars... "= IF(A3>=SHEET1!A3,SHEET1!A$3, IF(SHEET1!A4<=A3<SHEET1!A3,SHEET1!A$4, IF(SHEET1!A5<=A3<SHEET1!A4,SHEET1!A$5, IF(SHEET1!A6<=A3<SHEET1!A5,SHEET1!A$6, IF(SHEET1!A7<=A3<SHEET1!A6,SHEET1!A$7, NOVALUE)))))" ...and I would like to have it for an unlimited number of bars! How could that be done? Thanks a lot for any feedback, Cheers,Paul

[2020-10-13 11:48:39] #2 [Go To First Post]
Sawtooth - Posts: 3995
If I understand you correctly, this might work for you: =FLOOR(A3,15/1440) This will return the DateTime of the 15min bar corresponding to each of the 1TickRange bars.

[2020-10-13 13:03:21] #3 [Go To First Post]
User185210 - Posts: 59
Thank you, FLOOR(A3,15/1440) is the first part of the solution. This example to illustrate my idea: on sheet#1 15min chart: ------------------------------------------ sheet#1 A3 14:15:00 returned value of O3 is: 4 A4 14:00:00 returned value of O3 is: 15 A5 13:45:00 returned value of O3 is: 27 A6 13:30:00 returned value of O3 is: 5 A7 13:15:00 returned value of O3 is: 11 ... -------------------------------------------- What I would like to have extracted on sheet#2 1TickRange chart is: sheet#2 --------------------- A3 14:18:55 O3=4 (returned value from SHEET1 O3) A4 14:16:28 O4=4 (returned value from SHEET1 O3) A5 14:15:22 O5=4 (returned value from SHEET1 O3) A6 14:08:21 O6=15 (returned value from SHEET1 O4) A4 14:04:37 O7=15 (returned value from SHEET1 O4) A7 14:00:52 O8=15 (returned value from SHEET1 O4) A8 13:58:22 O9=27 (returned value from SHEET1 O5) A9 13:47:12 O10=27 (returned value from SHEET1 O5) A10 13:45:55 O11=27 (returned value from SHEET1 O5) ... --------------------- with "FLOOR(A3,15/1440)",it improves the original formula = IF(FLOOR(A3,15/1440)=SHEET1!A$3,SHEET1!O$3, IF(FLOOR(A3,15/1440)=SHEET1!A$4,SHEET1!O$4, IF(FLOOR(A3,15/1440)=SHEET1!A$5,SHEET1!O$5, IF(FLOOR(A3,15/1440)=SHEET1!A$6,SHEET1!O$6, IF(FLOOR(A3,15/1440)=SHEET1!A$7,SHEET1!O$7,NOVALUE))))) but it still needs me to adjust it for each bar individualy and I would like to have an automatic formula that could return all the results from sheet#1 to sheet#2 and not only the ones for which I specificaly put "O$3,O$4,O$5,O$6"... Is there a way? thank you

[2020-10-13 13:34:11] #4 [Go To First Post]
Sawtooth - Posts: 3995
Try this: On the 15min chart: - Add the Spreadsheet Formula study, with a formula like this in the Formula field: =ID1.SG5 where ID1 is the spreadsheet study, and SG5 is column O. On the 1TickRange chart: - Add the Study/Price Overlay study, set the Study to Overlay to the Spreadsheet Formula study on the 15min chart. - On Sheet2 of the spreadsheet, in a Formula Column, use a formula like this: =ID2.SG1@3 where ID2 is the Study/Price Overlay study.

[2020-10-13 15:55:38] #5 [Go To First Post]
User185210 - Posts: 59
The concept works but the precision is no longer there (compared with my first solution!) Even with the matching method: "Containing Match" selected!It's either one raw ahead or one behind! Could you suggest another way? Thank you, Paul

[2020-10-13 17:42:03] #6 [Go To First Post]
Sawtooth - Posts: 3995
When using the Study/Price Overlay study on non-time-based bars, the Data Copy Mode needs to be set to 'Earliest...' Auto Trade System Back Testing: Performing Back Testing on a Trading System That Uses Multiple Charts But that doesn't seem to directly solve the issue. Try this: - In the Study/Price Overlay study on the 1TickRange chart: Set the Data Copy Mode to 'Earliest...' Set the Bar Time Matching Method to 'Containing...' - Offset the overlaid value on Sheet2 by using this formula instead: =ID2.SG1@4 where ID2 is the Study/Price Overlay study.

[2020-10-13 17:59:06] #7 [Go To First Post]
User185210 - Posts: 59
Great! It works! Thank you so much, Cheers, Paul

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