Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 39 ยท Projects and Practice

Project: OHLC Bars from Tick Data

Turn a stream of trade ticks into open, high, low, close and volume bars per minute, add VWAP and returns, and learn the Flux and InfluxDB 3 equivalents.

Financial market data is a textbook time-series workload: millions of ticks (individual trades) per day, queried almost always as bars (also called candles): for each time window, the first price (open), highest (high), lowest (low), last (close) and total volume. Building bars from ticks is a perfect exercise in window aggregation, and it exposes a classic subtlety: "first" and "last" depend on time order, not on which row happens to come first.

This is a data-engineering exercise, not trading advice. All prices here are generated. The server is not available, so the project runs in portable SQL, with the Flux and InfluxDB 3 versions shown read-only.

The data model

One measurement, one series per symbol:

TEXT
trades,symbol=ACME price=101.25,size=300i 1750000000123456789
  • symbol is a tag (a stable, small set; see cardinality).
  • price is a float field, size an integer field.
  • Timestamps use nanosecond precision, because many trades can happen in the same second. If two trades for the same symbol share a timestamp, the second overwrites the first, so use exchange timestamps with enough precision, or add a sequence tag (a bounded one).

Step 1: generate ticks

SQL Generate 20 ticks over 3 minutes

Press Run (or Ctrl + Enter) to see the result here.

Step 2: OHLC per minute

In plain SQL you compute open and close with a window function that finds the first and last row per bar:

SQL Open, high, low, close and volume per minute

Press Run (or Ctrl + Enter) to see the result here.

Check the invariants: low <= open, close <= high in every row. Check them whenever you build bars, because a mistake in ordering shows up immediately.

The Flux version (read-only) uses first()/last() after windowing; the pieces are windowed separately and joined, or computed with reduce():

JavaScript
data = from(bucket: "ticks")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "trades" and r.symbol == "ACME" and r._field == "price")

open  = data |> aggregateWindow(every: 1m, fn: first, createEmpty: false)
high  = data |> aggregateWindow(every: 1m, fn: max,   createEmpty: false)
low   = data |> aggregateWindow(every: 1m, fn: min,   createEmpty: false)
close = data |> aggregateWindow(every: 1m, fn: last,  createEmpty: false)

In InfluxDB 3 SQL, date_bin creates the window and the first_value/last_value aggregate functions with ORDER BY can fetch the open and close; verify the exact syntax against the documentation of your version:

SQL
SELECT date_bin(INTERVAL '1 minute', time) AS bar,
       first_value(price ORDER BY time) AS open,
       max(price) AS high, min(price) AS low,
       last_value(price ORDER BY time) AS close,
       sum(size) AS volume
FROM trades WHERE symbol = 'ACME'
GROUP BY bar ORDER BY bar;

Step 3: VWAP and returns

VWAP (volume-weighted average price) is the sum of price times size divided by total size. It is a weighted mean, the same idea as the weighted-mean lesson in downsampling, and the reason to store volume with every bar. The return from one bar to the next compares closes.

SQL VWAP per bar and the return between closes

Press Run (or Ctrl + Enter) to see the result here.

Step 4: handle the traps

  1. Bars with no trades. A quiet minute has no ticks, so GROUP BY gives no row. Decide: skip the bar, or carry the previous close forward as a flat bar with zero volume.
  2. Time zones and sessions. Bars align to UTC unless you say otherwise. Daily bars for a market must align to the exchange's session, not midnight UTC.
  3. Late and out-of-order ticks. If a tick for 10:00:58 arrives at 10:01:03, the 10:00 bar was already written. Run the rollup with an offset or recompute the last bars (see scheduled tasks). Because rollups overwrite by timestamp, recomputing is safe.
  4. Corrections and cancels. Exchanges send corrections. Keep raw ticks long enough to rebuild bars.
  5. Precision. Prices are decimals. Floating point is fine for charts, but for accounting use integers (ticks of 0.0001) so sums are exact.

Step 5: retention plan

Bucket Content Retention
ticks Raw trades 30 days
bars_1m 1-minute bars 2 years
bars_1d Daily bars Forever

Daily bars come from the minute bars, not the ticks. To combine minute bars into a daily bar: open from the first bar, close from the last, high as the max of highs, low as the min of lows, volume as the sum. You cannot average these, and storing only a mean would make this impossible, which is why the right summary matters.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you get the correct open price for a bar?

    Open is the first trade by time. Storage order may differ from time order.
  2. 2. VWAP is which kind of average?

    Each price is weighted by its volume.
  3. 3. How do you build a daily high from minute bars?

    High is the maximum over the period, so it composes as the max of maxima.
  4. 4. A tick arrives after its minute bar was written. What is a good approach?

    Idempotent rollups with an offset handle late data.

Summary

  • Bars aggregate ticks per window: open, high, low, close, volume.
  • Open and close need time ordering; high, low and volume compose across levels.
  • VWAP is a weighted mean; store volume.
  • Plan for empty bars, sessions, late ticks and corrections.
  • Keep raw ticks short-term and bars long-term.

Next: Time-series SQL Practice Set