Inspect an OHLCV CSV with DuckDB to check coverage, find timestamp issues, build UTC daily bars and calculate two simple price summaries. These five recipes use the column layout that SiftingIO Market Data Export produces: a UTC timestamp, a millisecond timestamp, open, high, low, close and volume.
Start with a small file#
Open Market Data Export, choose one instrument and interval, select Show chart, then Download CSV. Without an account, the tool offers completed bars from the latest 24 hours; a closed market can return no data. With an account, history and limits depend on your plan.
For a reproducible example, save the following as sample-synthetic.csv. Every price is artificial. This is a teaching fixture, not observed SiftingIO market data. The intentional gap and blank volume help test the queries.
Time (UTC),Timestamp (ms),Open,High,Low,Close,Volume
2026-09-28T22:00:00.000Z,1790632800000,100,102,99,101,10
2026-09-28T23:00:00.000Z,1790636400000,101,104,100,103,20
2026-09-29T00:00:00.000Z,1790640000000,103,105,102,104,30
2026-09-29T02:00:00.000Z,1790647200000,104,106,103,105,
2026-09-29T03:00:00.000Z,1790650800000,105,107,104,106,50
Run all five recipes#
Install the DuckDB CLI. Save the SQL below as recipes.sql beside the CSV, then run duckdb < recipes.sql. Written for the DuckDB 1.x CLI.
For your own CSV, change the filename and interval threshold. If your downloaded header row uses different labels, change the quoted column names in the first query to match. The example uses one-hour bars, so its gap threshold is 3,600,000 milliseconds. Five-minute bars need 300,000. One file must contain one instrument and one interval; mixed instruments need an explicit identifier and partitioned queries.
-- One instrument, one fixed interval per file. Synthetic 1-hour UTC fixture.
SET TimeZone='UTC';
CREATE OR REPLACE TABLE bars AS
SELECT "Time (UTC)"::VARCHAR AS time_utc,
"Timestamp (ms)"::BIGINT AS t,
"Open"::DOUBLE AS o, "High"::DOUBLE AS h,
"Low"::DOUBLE AS l, "Close"::DOUBLE AS c,
"Volume"::DOUBLE AS v
FROM read_csv('sample-synthetic.csv', header=true, all_varchar=true);
-- recipe: 01-coverage
SELECT count(*) AS rows, count(DISTINCT t) AS unique_timestamps,
min(epoch_ms(t)) AS first_bar_utc, max(epoch_ms(t)) AS last_bar_utc,
count(*) FILTER (WHERE v IS NULL) AS unavailable_volume_rows
FROM bars;
-- recipe: 02-timestamp-quality
-- This is a gap candidate report, NOT proof of missing market data.
-- 3600000 is only for fixed 1h bars; check sessions and holidays yourself.
WITH unique_times AS (SELECT DISTINCT t FROM bars),
gaps AS (SELECT t, t-lag(t) OVER (ORDER BY t) AS delta FROM unique_times)
SELECT 'duplicate' AS issue, epoch_ms(t) AS bar_utc,
count(*)-1 AS extra_rows, NULL::BIGINT AS elapsed_ms
FROM bars GROUP BY t HAVING count(*)>1
UNION ALL
SELECT 'gap_candidate', epoch_ms(t), NULL, delta FROM gaps
WHERE delta>3600000
ORDER BY bar_utc, issue;
-- recipe: 03-utc-daily-ohlc
-- Run only after duplicate handling. UTC days are not exchange sessions.
-- Do not sum unknown volume units or report a partial sum as complete.
SELECT CAST(epoch_ms(t) AS DATE) AS utc_day,
first(o ORDER BY t) AS open, max(h) AS high, min(l) AS low,
last(c ORDER BY t) AS close, count(*) AS observed_bars,
CASE WHEN count(v)=count(*) THEN sum(v) ELSE NULL END AS volume_sum
FROM bars GROUP BY utc_day ORDER BY utc_day;
-- recipe: 04-close-returns
-- Observed-bar returns, not fixed-duration returns across a gap.
WITH prior AS (SELECT *, lag(c) OVER (ORDER BY t) AS previous_close,
t-lag(t) OVER (ORDER BY t) AS elapsed_ms FROM bars)
SELECT epoch_ms(t) AS bar_utc, c AS close, elapsed_ms,
100.0*(c/nullif(previous_close,0)-1) AS close_return_pct
FROM prior ORDER BY t;
-- recipe: 05-three-observation-mean
-- Three observations, not three elapsed hours. First two results are NULL.
SELECT epoch_ms(t) AS bar_utc, c AS close,
CASE WHEN count(c) OVER w=3 THEN avg(c) OVER w END AS mean_3_bars
FROM bars WINDOW w AS (ORDER BY t ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY t;
Read the results correctly#
- Coverage: the fixture has five rows, five unique timestamps and one unavailable volume. Coverage describes the file, not necessarily the entire requested market history.
- Timestamp quality: one two-hour gap is reported at 2026-09-29 02:00 UTC. A gap candidate does not prove missing data: sessions, holidays and sparse trading can explain it. Resolve duplicates before ordered calculations. The export tool already sorts and deduplicates timestamps, but the query is useful for edited or combined files.
- UTC daily OHLC: the first observed day's open/high/low/close is 100/104/99/103, with two bars and volume 30. The second is 103/107/102/106, with three bars and NULL volume. A missing constituent volume must not become a complete sum. These are partial observed UTC days, not exchange trading sessions.
- Close returns: the first result is NULL; the second is approximately 1.980198%. The query reports elapsed milliseconds so a return across a gap is not mistaken for a fixed-hour return. Zero previous close also yields NULL.
- Rolling mean: the first two rows are NULL; the third is approximately 102.666667. This is three observations, not three elapsed hours. A gap changes the elapsed span.
Keep the export metadata#
Time (UTC) is an ISO UTC string. Timestamp (ms) is a Unix epoch millisecond value for the same bar. Open, High, Low, Close and Volume are numeric; unavailable volume is blank and becomes NULL.
The CSV does not carry all source metadata. Preserve the export screen or Excel metadata sheet for instrument, currency, interval, requested period, completeness, price basis, adjustments and volume units. Do not infer volume units from a ticker. Forex volume may be a tick count; unknown units remain unknown. Sum volume only when its unit is consistent and additive.
UTC days do not reproduce exchange sessions. Stock prices are as traded unless verified metadata says otherwise, so splits can create large apparent changes. Price changes here are not investment returns: dividends, fees and other adjustments are absent. Partial inputs produce partial summaries. These recipes neither fill gaps nor invent bars.
Every expected value above is what these queries return on the synthetic fixture. The fixture is the only data used, and no live market observations are included.
Sources: DuckDB CSV reader, window functions, timestamp functions, SiftingIO Export.
