Support Board
Date/Time: Thu, 27 Feb 2025 03:09:47 +0000
Post From: Spreadsheet for Trading, circular reference issue
| [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 |