Support Board
Date/Time: Mon, 25 Nov 2024 18:35:57 +0000
Question about a spreadsheet study that references time and draws a line
View Count: 328
| [2024-02-14 23:34:12] |
| trader2017 - Posts: 98 |
|
I’m trying to have a spreadsheet study draw a line at the high of each hour, for example a line from 18:00 to 18:59:59 and a new line from 19:00 to 19:59:59. I know there is a high low extended study for that but because I might change the timeframes, I was trying to accomplish this in a spreadsheet study. I found this on another comment to identify the time: =FRACTIME(MROUND(A3,1/86400))=TIMEVALUE("18:00:00") I wrote that formula in the K3 cell and it worked to identify the row with 18:00:00 by returning the number 1 in the column. Now I’m trying to figure out how to get a line drawn from the high of 18:00 all the way to 18:59:59 In cell L3 I wrote the following to place a point on the high + .50 and that worked. =IF(K3=1, ID0.SG2@3 +.50, 0 ) Now the difficult part, I can’t figure out how to get the point to extend across until the next hour begins. I can reference the previous row, but the previous row is line 2 and the following formula causes a syntax error. =IF(K3<>1, L3[-1] , 0) Thank you for any assistance someone can provide. Date Time Of Last Edit: 2024-02-14 23:34:45
|
| [2024-02-15 00:28:34] |
| trader2017 - Posts: 98 |
|
In case anyone else is trying to do something similar, here is what worked. The previous row didn’t make sense in this context, I don’t work with spreadsheet studies very often and the chronological order is most recent first so there is no need to reference the previous row, the next row is what you need to carry forward a value. I still have to figure out if I can add a value to the 18:00 number by having it reference a static number like 60 minutes to increase it by so I can copy and paste these into new columns and not have to change the numbers manually. For example (18:00:00 + (60 *1)), (18:00:00 + (60 *2)), etc. CELL K3 =FRACTIME(MROUND(A3,1/86400))=TIMEVALUE("18:00:00") CELL L3 =IF(K3=1,ID0.SG2@3+.50,0) CELL M3 =IF(L3=0,M4,L3) Date Time Of Last Edit: 2024-02-15 00:31:10
|
| [2024-02-15 14:24:08] |
| Sawtooth - Posts: 4120 |
|
Try this in K3: =IF(FRACTIME(MROUND(A3,1/86400))=TIMEVALUE("18:00:00"),C3,IF(FRACTIME(MROUND(A3,1/86400))>=TIMEVALUE("18:59:59"),0,K4)) The previous row didn’t make sense in this context
The previous row is the one you want to make persistent.The persistent value is released with a second IF statement. Date Time Of Last Edit: 2024-02-15 14:57:47
|
| [2024-02-18 09:09:37] |
| trader2017 - Posts: 98 |
|
I just saw your reply now, thank you for writing back. Your post to someone else is where I copied the original code I posted about time. I was trying to get a horizontal line drawn across various time frames and thought that a spreadsheet study might be easier than manually entering the different times in multiple copies of the High/Low for Time Period - Extended study. But it didn’t take long to realize that it would probably be quicker to just take the time to add them all manually instead of trying to figure out how to write the code for it, so that’s what I ended up doing. Thank you though for taking the time to write the code you posted. I will keep this in mind if I ever try again with another idea in the future. |
To post a message in this thread, you need to log in with your Sierra Chart account: