Inferring a CSV’s UTC offset by matching fill prices against 5-minute candles

I build a browser-based trade replay tool as a portfolio project. You give it a position-history CSV exported from an exchange, and it rebuilds the chart around each trade so you can see what the market actually looked like when the position opened and closed.

That only works if the timestamps are correct. And timestamps in exported CSVs are a mess.

The problem: a timestamp with no timezone is not a time

The export gives you rows like this:

2025-03-14 09:35:00, BTC-USDT-SWAP, long, 83421.5, ...

There is no timezone on that string. 09:35 in whose clock? The exchange’s servers run on UTC. The export is rendered in the account’s display timezone, which the user set at signup and probably forgot about. If I assume wrong, every chart is shifted by a whole number of hours, and the marker showing where the position opened lands on a candle that has nothing to do with the actual fill.

This is worse than a cosmetic bug. The entire point of the tool is to look at the bar where the trade happened. A silently wrong timezone makes it confidently show you the wrong bar.

First, look for metadata — don’t infer what you can read

Before doing anything clever: the exporter writes a metadata block above the header row, and it sometimes contains the account’s display offset.

const meta = lines.slice(0, headerIndex).join(" ");
const mtz = meta.match(/userTimeZone"?\s*:\s*"?(-?\d+)/)
         || meta.match(/userDownloadTimeZone"?\s*:\s*"?(-?\d+)/);

If that matches, the work is done. Inference is only for files where it doesn’t — older exports, files that have been opened and re-saved in a spreadsheet, files a user hand-assembled.

The general rule I’d take to any parser: an authoritative source beats a clever guess, so check for one first. Inference is a fallback, not a default.

The insight: the price is the ground truth

Here’s what makes this solvable. Every row has two things that must agree:

a timestamp, whose offset is unknown
a fill price, which is a hard fact

And there’s a constraint linking them: a fill price must fall inside the high-low range of the candle it happened in. If a position opened at 83421.5, then whatever 5-minute bar it opened in must have had a low at or below 83421.5 and a high at or above it. That is not a heuristic, it’s arithmetic — the bar’s high and low are literally the extremes of everything that traded in that window.

So I don’t have to guess the timezone. I can test it. Shift the timestamp by a candidate offset, fetch the bar it lands on, and ask whether the price fits inside it. Wrong offsets land on bars where the price is nowhere near the range.

The method

Parse timestamps as UTC first, without applying any offset, and keep those raw seconds around so they can be re-derived later:

return Math.floor(Date.UTC(+m[1], +m[2] - 1, +m[3], +m[4], +m[5], +(m[6] || 0)) / 1000);

Then score every plausible offset.

Sample a handful of trades, one per symbol.

const seen = new Set(), sample = [];
for (const t of trades) {
  if (seen.has(t.symbol)) continue;   // one trade per symbol, for diversity
  seen.add(t.symbol);
  sample.push(t);
  if (sample.length >= 8) break;
}
if (sample.length < 2) return null;

One per symbol matters. Eight trades on the same instrument during one quiet afternoon give you eight nearly identical tests — the price barely moved, so several offsets will all “fit”. Eight different instruments across different days are eight genuinely independent tests.

Score each candidate offset from −12 to +14.

for (const t of sample) {
  const candles = await fetchCandles(t.symbol, "5m",
                                     t.rawOpenSec - 15 * 3600,
                                     t.rawCloseSec + 15 * 3600);
  if (candles.length < 5) continue;

  const byBar = new Map();
  for (const c of candles) byBar.set(c.time, c);
  const at = (sec) => byBar.get(Math.floor(sec / 300) * 300);  // snap to 5m bar

  for (let k = -12; k <= 14; k++) {
    const be = at(t.rawOpenSec  - k * 3600);
    const bx = at(t.rawCloseSec - k * 3600);
    if (be && be.low <= t.openPrice  && t.openPrice  <= be.high) hit[k]++;
    if (bx && bx.low <= t.closePrice && t.closePrice <= bx.high) hit[k]++;
  }
  checks += 2;
}

Three details worth pointing at:

−12 to +14 covers every real-world offset. Not a magic number — that’s the actual range of UTC offsets in use.
The 15-hour padding on the fetch window exists because the candidate shift can move a timestamp by up to 14 hours in either direction. Without the padding, extreme candidates would fall outside the fetched data and score zero for the wrong reason — they’d look wrong because I didn’t fetch their bars, not because they’re wrong.
**Math.floor(sec / 300) * 300** snaps a timestamp to the 5-minute bar containing it. Candles are keyed by their opening time, so this is how you go from “a moment” to “the bar that moment is inside”.

Each trade contributes two independent tests, open and close.

The confidence gate — the part that actually matters

Picking the highest-scoring offset is not enough. In a quiet market, several adjacent offsets can all land inside the range, because the price simply didn’t move much across those hours. A naive argmax would return an answer with false confidence.

if (best / checks < 0.6 || best - second < 2) return null;
return bestK;

Two conditions, both required:

1. The winner must fit at least 60% of all checks. A winner that only explains a third of the data isn’t a winner, it’s the tallest weed. 2. The winner must beat the runner-up by at least 2 hits. A one-hit lead is noise.

If either fails, the function returns null and the caller falls back to a default — and tells the user what happened:

CSV had no timezone and auto-calibration failed (insufficient/mismatched sample); using UTC+8, times may be off.

And when it succeeds, it also says so, with the offset it settled on. The user is never left guessing whether the chart they’re reading was aligned by evidence or by assumption.

I think this is the most important part of the whole thing. Inference that can’t say “I don’t know” isn’t inference, it’s a random number generator with good manners. The gate is what makes the feature safe to ship — not the scoring.

What this doesn’t handle

Being honest about the edges:

Sub-hour offsets fail. The loop steps in whole hours (k * 3600), so India (UTC+5:30) and Nepal (UTC+5:45) can’t be recovered. A half-hour step would fix it at the cost of doubling the candidate space, and I haven’t needed it.
Very illiquid instruments weaken the signal. If a bar’s high-low range is wide, more candidate offsets fit inside it, and the runner-up gap shrinks. The gate correctly refuses to answer here rather than answering badly.
It requires the exchange to still serve history for that instrument. Delisted symbols return nothing and get skipped.
DST is not modelled. A fixed offset is assumed across the whole file. An account displaying local time in a DST-observing region would drift across the boundary.

Takeaway

The reusable idea isn’t the timezone logic. It’s this: when a field is missing, look for another field that constrains it. The timestamp was ambiguous, but the price wasn’t, and the market data tied them together. That turned an unanswerable question into a scoring problem.

And whatever you infer, ship it with a threshold that lets it decline. A wrong answer delivered confidently is worse than no answer — especially in a tool whose entire job is to show you what really happened.

This is a portfolio project, not commercial software. It’s built with AI-assisted development: I define the requirements and acceptance criteria, iterate on the implementation, then review, test and debug it.