Automatic Candle Timeframe Calculation?

[Clearwell_T]

Hi All,

Does anyone know of an elegant formula that could take a look at a list of imported candle times (mostly regular, modulo extended hours slippages-see example below), and figure out the likely original timeframe that was being used on the Trading View chart that they were generated from?

ie: interpolate and guess that a list like this is actually a 30s timeframe:

14/02/2025 14:20:00.000
14/02/2025 14:20:30.000
14/02/2025 14:21:00.000
14/02/2025 14:22:00.000
14/02/2025 14:22:30.000
14/02/2025 14:24:00.000
14/02/2025 14:24:30.000
14/02/2025 14:26:00.000
14/02/2025 14:27:30.000
14/02/2025 14:28:00.000
14/02/2025 14:28:30.000
14/02/2025 14:29:00.000
14/02/2025 14:29:30.000
14/02/2025 14:30:00.000
14/02/2025 14:30:30.000
14/02/2025 14:31:00.000
14/02/2025 14:31:30.000

Thanks.

CT

[joshua4]

I worked with Gemini a little on this. Assuming that your values are actual numeric time values in A1 down to A17, you could use =MODE((A2:A17-A1:A16)*86400) This results in an answer of 30. The idea is to get the mode of the differences of the consecutive values. The large number is just seconds per day. You could also find the average of the difference then pick the closest time frame actually in use in trading…here that would again be 30 seconds, since 44 seconds is the average, and that narrowly is closer to 30 seconds than to 60 seconds. It depends on what you mean by “guess”, I suppose, and what your data look like to start with.

[Clearwell_T]

Hey @joshua4,

Thanks very much for this. It’s a really brilliant function and use of it in a formula that I hadn’t heard of, and that ChatGPT never suggested.

A quick question you may know the answer to in relation to the formula you’ve provided. Is it possible to use dynamic named ranges as the cell ranges in this kind of formula??

I guess it woud require some kind of “high-wire, death-defying” formula gymnastics to derive a copy of the DNR in question, in order to have the evaluation of it’s contents start one cell later.

Thanks again for your help. Much appreciated.

Regards.

CT

[joshua4]

If I understand you correctly, then it’s pretty straightforward. I even considered expanding the formula to demonstrate that, but was running short on time.

The trick would be to use the OFFSET function and rely on the default mechanisms of Calc to expand named ranges. This requires a little understanding of Calc ‘hygiene’ that you may have, or you may never have thought about.

In general, if you create a named range (Ctrl+F3 or just select and name in box at upper left) Calc will automatically expand that range so long as you insert into it by clicking on the row number (far left) of a row in the range and using “Insert row above” for all but the first row, or “Insert row below” for all but the last row. Makes sense, really.

But if you want to insert many rows, that would be laborious. Fortunately, the same semantics play out by clicking on any cell in the range and using right-click Insert (or Ctrl+[Numpad+]). Here you can chose Entire Row and set the number of rows to insert in a small dialogue box that even allows use of tab to move around, etc…

So, set up a named range of at least two rows in a column (select cells and enter in range name box at top left, or use Ctrl+F3…let’s call this range Times) and put something like END in the last row. Now you can add n rows by going to the last row of the range (Ctrl+Down from anywhere above) and Ctrl+[Numpad+]. Add n rows, then paste in n datapoints into those new rows.

To have the MODE formula keep working, use the name of the named range (Times) in that formula, trimming it and sliding it by using OFFSET.

=MODE((OFFSET(Times,1,0,ROWS(Times)-2,COLUMNS(Times))-OFFSET(Times,0,0,ROWS(Times)-2,COLUMNS(Times)))*86400)

The -2 is really -1 for setting up the differences (as we slide the region one row) and -1 for ignoring our END marker.

Unfortunately, even using Excel with the modern UNIQUE and FILTER functions, I didn’t succeed in getting the MODE function to ignore 0s, where a person could just mark nearly an entire column as the named range and be done with it. Someone may have an idea how, but simply OFFSETting the virtual ranges produced by the spill functions didn’t seem to work.

[Clearwell_T]

Hi again @joshua4,

Sorry for the time delay getting back to you. I’m in the UK.

Thanks so much for your help. You’ve opened up a whole new world for me in my spreadsheeting journey with these functions. It’s one thing to read about these functions in the user guide, and a whole other thing to learn them through use in an ACTUAL formula that’s needed, so thanks to you for that.

I came up with my own (sort of) creative solution. I took a look at the format of this original formula you provided: =MODE((A2:A17-A1:A16)*86400) and decided I could substitute into the two cell ranges my original dynamic named range (TSList_CandleTimes), together with a modified version starting one cell later (TSList_CandleTimesOffset).

So I ended up with this formula:
=MODE((TSList_CandleTimesOffset - TSList_CandleTimes)*86400) which worked to return this essentially correct number from my dataset: 60.0000001955777, then with the help of ChatGPT, I added the extra bits to round off the number like this: =ROUND(MODE((TSList_CandleTimesOffset - TSList_CandleTimes)*86400),0) and ChatGPT also suggested array-entering. It all seems to be working really well!

Any potential pitfalls you can spot with this approach??

Thanks again for all your help. Much appreciated.

Regards.

CT

PS: I definitely will be experimenting with adding OFFSET into my repertoire ASAP.

[joshua4]

Just test for insertion at the end of the list, if that is a question. If you insert on the very last cell of a range, the range may not expand as a named range (as mentioned above). So, if you have two distinct ranges ending on different cells, when inserting at the very end you could “miss” automatic expansion for TSList_CandleTimes even if it engages on TSList_CandleTimesOffset.

Good point on ROUND. If you need whole numbers, you’ve got to use ROUND. You can’t even add up the change in a change drawer in Excel without ROUND. Any fractional amount that isn’t binary might show up as “junk.” I don’t know how Excel implements MODE, but if you think about it, if there are several different 60’s, like 60.00000034 and 60.00000023, etc., then how does it come to a single 60 something conclusion for the MODE? Would it be safer to use MODE(ROUND(…)) rather than (ROUND(MODE(…)) if you new you would always be seeking whole-second intervals?

[Clearwell_T]

Hi @joshua4,

Thanks for the heads up with testing the end insertion.
On all the dynamic named ranges I’ve created in my sheet so far, I’ve defined them all to the “end stop” of 1048576. I think that’s the maximum limit isn’t it?
I was simply assuming that any additions “inside” my range cell numbers 21:1048576 and 22:1048576 would mean both updated regardless, but I can see now that the offset between the two may make that not so.
I’m a little afraid to experiment too heavily in this arena at this stage, as my whole sheet is a little bit creaky around the edges whilst I learn how to make a sheet maximally robust from the ground up (painfully aware that I’m a newb, and a little knowledge is a dangerous thing).
I’m worried I might try something that breaks a chain of logic that I won’t have the skills to unpick and debug successfully right now.
Any insight you can offer into what might happen between the two offset DNR’s, would be much appreciated.
Kind regards.
CT