Support Board
Support Board
Search Board
Control Panel
View My Posts / Threads
Direct MessagesDate/Time: Sun, 28 Apr 2024 20:33:27 +0000
[Programming Help] - Spreadsheet for Trading, circular reference issue
View Count: 749
| [2021-06-13 15:39:50] |
| toews&kane - Posts: 209 |
|
Hi there, Technically I am trying to do back testing for basic rules that should hold for simple strategies around the market profile. Ex. for an open inside value day it should be viable to buy/sell out of value. The TPO chart values I look at like previous day VAH, VAL, that show in the compact tools value window I can't really get out of the box, so I am having to do this I am using 30 min charts: 1. generate the decimal values for date and time 2 Take the current date minus one, add the time to grab preciously yesterday's closing VAH and VAL. To do this I am using VLOOKUP like vlookup(INT(A3)-1+0.6458333362170379,A:AC,28) Where A3 is of course the current row date and time, turn it to a date, go back to yesterday, and add the exact time 1600est using that time right now for close just for simplicity sake. A:AC, 28, I am having to search these columns because column 28 is what holds my VAH for example. It was working last night, and this morning I put in probably like 3-4 hours on this, then I got a circular reference error. Then four of my columns lit up with Circular reference error, is this the VLOOKUP that's causing this? Would love some help here. I can't really use the index function either because I am not looking for an exact match, I need to the pull the value from yesterday. Recap: formula for previous vah ex.: =vlookup(INT(A3)-1+0.6458333362170379,A:AC,28) formula to check if it's open within value: =IF(AND(FRACTIME(A3)=0.39583333621703787, AND(B3<X3, B3>Y3)), "1",0) 3. is it easy to have these previous day values like VAH and VAL out of box in the spreadsheet? Cheers |
| [2021-06-14 03:52:05] |
| Sawtooth - Posts: 3980 |
|
Some ideas: - You can use TIMEVALUE("16:00:00") to get the decimal value of 16:00:00 - Both value area lines studies have Reference Days Back options to get yesterday's values. - You can use a formula like this to bring yesterday's VAH or VAL at 16:00 to row 3, e.g. in cell R3: =IF(AND(FRACTIME(A3)>=TIMEVALUE("16:00:00"),FRACTIME(A4)<TIMEVALUE("16:00:00")),ID1.SG2@3,R4) where ID1.SG2 is the VAH. Note: So that you can insert/delete columns and rearrange studies in the Studies to Graph list without dislocating references, it's best to use this method to reference a study's output: Working with Spreadsheets: References to Study Subgraph Columns when using the Spreadsheet Study - You could use the GETCORRESPONDINGMATCH function instead of VLOOKUP, because it has a Nearest option. https://www.sierrachart.com/index.php?page=doc/SpreadsheetFunctions.html#GetCorrespondingMatch_Function - To avoid a #CREF error, you can include the referenced cell's formula in the referencing formula, instead of a direct cell reference. Yes, resolving these can be tricky and cumbersome. - If the result is boolean (T/F) you don't need to use the IF function: =AND(FRACTIME(A3)=0.39583333621703787, B3<X3, B3>Y3) |
| [2021-06-14 04:53:25] |
| toews&kane - Posts: 209 |
|
true, thanks for the tips will try that and report back
|
To post a message in this thread, you need to log in with your Sierra Chart account: