Skip to content

Tick analytics: a working session

A quant sits down with a day of trades and quotes across a small basket of symbols and wants, in order: clean market-hours data, minute bars with a volume-weighted price, returns, a volatility estimate, the prevailing quote on every bar, and a ranked list of what moved. This guide walks that session top to bottom, one query at a time, on data the guide generates itself — nothing to download, nothing symbol-specific about the engine underneath. Every snippet below runs as written; strung together they are one script.

The day's ticks

Real tick data is uneven: trades cluster and go quiet, arrive before and after the official session, and never line up neatly on the minute. Plain Python generates a day shaped like that — three symbols, a seeded random walk each, irregular inter-arrival gaps, and a scatter of pre- and post-market prints that the next section will need to filter out.

import csv
import random

random.seed(7)

SYMBOLS = [("ATOM", 100.0, 0.30), ("FLUX", 50.0, 0.15), ("NOVA", 250.0, 0.60)]


def clock(sec):
    h, m, s = sec // 3600, (sec % 3600) // 60, sec % 60
    return f"2024-03-04 {h:02d}:{m:02d}:{s:02d}"


trade_rows, quote_rows = [], []
for sym, px0, tick in SYMBOLS:
    px = px0
    t = 9 * 3600  # trading opens for prints at 09:00, ahead of the 09:30 bell
    while t < 16 * 3600 + 30 * 60:  # runs through 16:30, past the 16:00 close
        t += random.randint(3, 45)
        px = max(1.0, px + random.gauss(0, tick * 0.1))
        qty = random.choice([100, 200, 300, 500, 1000])
        trade_rows.append((sym, clock(t), round(px, 2), qty))
        if random.random() < 0.4:
            bid = round(px - tick * (0.5 + random.random() * 0.5), 2)
            ask = round(px + tick * (0.5 + random.random() * 0.5), 2)
            quote_rows.append((sym, clock(t), bid, ask))

trade_rows.sort(key=lambda r: (r[0], r[1]))
quote_rows.sort(key=lambda r: (r[0], r[1]))

with open("trades.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["sym", "ts", "px", "qty"])
    w.writerows(trade_rows)

with open("quotes.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["sym", "ts", "bid", "ask"])
    w.writerows(quote_rows)

assert len(trade_rows) > 1000 and len(quote_rows) > 500

Ingest once: csv_to_native

A file that gets queried more than once is worth parsing exactly once. csv_to_native reads the CSV and writes it straight to the engine's own block format — the ingest-once pattern, one call instead of a scan built by hand and collected to disk.

import keyten as kt

kt.csv_to_native("trades.csv", "trades.k10dir")
kt.csv_to_native("quotes.csv", "quotes.k10dir")

assert kt.scan_native("trades.k10dir").collect().shape[0] == len(trade_rows)

The blocks it writes are sealed: fixed-width, typed, with per-block statistics recorded at write time. Every query below reopens those files by mapping them into memory — nothing decodes until a column is actually touched, and the timestamp inference that happened once at ingest never happens again.

Market hours

The session's signal lives between the open and the close; the pre- and post-market prints generated above are noise for this analysis. A plain datetime compares directly against a Timestamp column — no manual epoch arithmetic, no separate literal-building call.

from datetime import datetime

hours = kt.scan_native("trades.k10dir").filter(
    (kt.col("ts") >= datetime(2024, 3, 4, 9, 30))
    & (kt.col("ts") < datetime(2024, 3, 4, 16, 0))
).collect()

assert hours.shape[0] < len(trade_rows)

The filter runs at the scan itself. Sealed blocks carry a min/max for ts, so a block entirely before 09:30 or at/after 16:00 never gets its rows decoded — the pre-market and post-market prints are skipped, not filtered after the fact.

One-minute bars, VWAP in the aggregate

A quant wants OHLCV and a volume-weighted price per symbol per minute. Bucketing the timestamp and grouping by (sym, bar) gets all of it in one agg — VWAP included, computed directly as sum(px * qty) / sum(qty) rather than as a follow-up division.

bars = (
    hours.lazy()
    .with_columns(kt.col("ts").truncate("1m").alias("bar"))
    .group_by(["sym", "bar"])
    .agg([
        kt.col("px").first().alias("open"),
        kt.col("px").max().alias("high"),
        kt.col("px").min().alias("low"),
        kt.col("px").last().alias("close"),
        kt.col("qty").sum().alias("volume"),
        ((kt.col("px") * kt.col("qty")).sum() / kt.col("qty").sum()).alias("vwap"),
    ])
    .sort(["sym", "bar"])
    .collect()
)

assert bars.shape[0] < hours.shape[0]
assert set(bars.column("sym").to_list()) == {"ATOM", "FLUX", "NOVA"}

The CSV was written sorted by (sym, ts), and csv_to_native preserves that at-rest order. Grouped on symbol-sorted data, each symbol is one contiguous run of rows — the engine slices runs and evaluates each in parallel with no hash table built at all. A single-symbol version of this same bucket-and-group is group_by_bar("ts", "1m"), the shortcut used throughout the time series tutorial; here, grouping on (sym, bar) together is what keeps the three symbols' bars from being merged into one series.

Log returns

Log returns are the natural unit for compounding and for feeding a volatility estimator — close.log() - close.log().shift(1), per symbol so day-open at one symbol never diffs against the prior symbol's last close.

rets = bars.lazy().with_columns(
    (kt.col("close").log() - kt.col("close").log().shift(1)).over("sym").alias("ret")
).collect()

rets_dict = rets.to_dict()
assert rets_dict["ret"][0] is None  # first bar of the first symbol has no predecessor

Realized and EWM volatility

Two flavors of the same question, side by side: rolling_std answers "how much did the last N bars move" with a hard window edge, ewm_std answers it with a decaying weight so one outlier bar fades out rather than dropping off a cliff when it exits the window.

vol = rets.lazy().with_columns([
    kt.col("ret").rolling_std(15).over("sym").alias("vol_rolling"),
    kt.col("ret").ewm_std(span=15).over("sym").alias("vol_ewm"),
]).collect()

vol_dict = vol.to_dict()
assert vol_dict["vol_rolling"][14] is None       # rolling_std(15) needs a full window
assert vol_dict["vol_ewm"][2] is not None        # ewm_std only needs two valid returns

Both windows run as their own execution stage — they read a column in row order, which a per-block plan can't do — but over("sym") still restarts each at every symbol boundary, so the stage costs one pass over sorted data, not one hash lookup per row.

NBBO asof merge, with staleness tolerance

Every bar wants the prevailing quote as of its own timestamp: the latest quote at or before the bar, per symbol, and no quote at all if the nearest one is too old to trust. join_asof with by= scopes the match to each symbol; tolerance nulls a match rather than reaching further back than a quant is willing to trust a stale quote.

merged = (
    vol.lazy()
    .join_asof(
        kt.scan_native("quotes.k10dir"), ("bar", "ts"), by=[("sym", "sym")], tolerance="30s"
    )
    .sort(["sym", "bar"])
    .collect()
)

merged_dict = merged.to_dict()
assert merged.shape[0] == vol.shape[0]  # asof is left-outer shaped: no bar is dropped
assert any(b is None for b in merged_dict["bid"])  # some bars found no quote within 30s

The tolerance is a duration string here because bar and ts are both Timestamp columns; over Int on-keys the same argument is a plain integer instead — see the asof join section of the tutorial for the Int form and the forward/nearest strategies.

Cross-sectional ranks

Within a symbol, over("sym") scopes a window to that symbol's own rows. Turned around — over("bar") — the same rank ranks every symbol's volatility against each other at each shared timestamp: a cross-sectional view instead of a per-symbol one.

ranked = (
    merged.lazy()
    .with_columns(kt.col("vol_rolling").rank(descending=True).over("bar").alias("vol_rank"))
    .sort(["bar", "sym"])
    .collect()
)

ranked_dict = ranked.to_dict()
assert set(v for v in ranked_dict["vol_rank"] if v is not None) <= {1.0, 2.0, 3.0}

Nothing about rank changes between the two uses — only which column is named in over. The partitioning is the whole difference between "how does today compare to this symbol's own history" and "how does this symbol compare to its peers right now."

Top-k movers

The last step of the session: which bars actually moved. top_k is a fused sort().limit() that never materializes the full ordering — the right shape for "give me the five biggest, nothing else."

movers = (
    ranked.lazy()
    .drop_nulls(["ret"])
    .with_columns(kt.col("ret").abs().alias("abs_ret"))
    .top_k(5, "abs_ret")
    .select(["sym", "bar", "ret", "abs_ret"])
    .collect()
)

assert movers.shape == (5, 4)
movers_list = movers.column("abs_ret").to_list()
assert movers_list == sorted(movers_list, reverse=True)

Writing results, and a look at where this goes next

The ranked frame is itself worth persisting — sealed, memory-mappable, ready for the next query without re-parsing anything:

ranked.write_native("session_result.k10dir")
assert kt.scan_native("session_result.k10dir").collect().shape == ranked.shape

Everything above ran locally, in-process. The same plan, unexecuted, is just bytes: serialize() turns a LazyFrame built over a scan source into its versioned wire form, sendable anywhere the plan should actually run.

wire = (
    kt.scan_native("session_result.k10dir")
    .filter(kt.col("vol_rank") == 1.0)
    .serialize()
)
back = kt.LazyFrame.deserialize(wire).collect()

assert isinstance(wire, bytes)
assert back.shape[0] > 0

That's the shape the distributed story takes: the executor that ran this session locally is the same one a worker process runs against shipped blocks and a shipped plan — distribution repeats the local shape at machine scale rather than being a second system bolted on.

Where next

  • The time series tutorial covers the same primitives — temporal kinds, join_asof, over, bucketing — each in isolation, with the edge cases spelled out.
  • The Arrow interop page covers getting a DataFrame in or out of everything else in the Python data ecosystem.
  • bench.k10.works has the live numbers this guide's shapes were built to earn.