OHLC Daily Prices - Usage Guide¶

Overview¶

Daily OHLC (Open/High/Low/Close) price data for futures products, sourced from TickDataWorks API.

Key Point: Front month data only - no specific contract tracking (ESZ24, ESM25, etc.)

Data Source¶

Provider: TickDataWorks API
Schedule: Every hour starting 4:30 AM UTC

Database Table: `ohlc_daily_prices`¶

Core Columns¶

Column	Type	Description
`products_tickdata_id`	Integer	FK to products_tickdata mapping table
`product_symbol`	Text	TT base symbol (e.g., "6L")
`tickdata_symbol`	Text	TickData base symbol (e.g., "BRL")
`trade_date`	Date	Trading date (UTC, weekdays only)
`open_price`	Numeric	Opening price (front month)
`high_price`	Numeric	High price (front month)
`low_price`	Numeric	Low price (front month)
`close_price`	Numeric	Closing price (front month)
`volume`	Integer	Trading volume
`created_at`	Timestamp	When record was created
`updated_at`	Timestamp	Last update timestamp

Important Notes¶

Price Precision: - Numeric type without fixed scale - stores only significant digits - No trailing zeros (e.g., 4500.25, not 4500.2500)

Front Month Only: - Each date contains the front month contract for that day - Contract rolls are reflected automatically in the data - Do not attempt to track specific contracts (ESZ24, ESM25) using this table

Symbol Denormalization: - product_symbol and tickdata_symbol are duplicated for query convenience - Avoids joins with products_tickdata table for most queries

How to Use¶

Basic Query - Get prices for a product¶

SELECT trade_date, open_price, high_price, low_price, close_price, volume
FROM ohlc_daily_prices
WHERE product_symbol = 'ES'
  AND trade_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY trade_date;

Get latest price¶

SELECT product_symbol, trade_date, close_price
FROM ohlc_daily_prices
WHERE product_symbol = 'ES'
ORDER BY trade_date DESC
LIMIT 1;

Multi-product comparison¶

SELECT product_symbol, trade_date, close_price
FROM ohlc_daily_prices
WHERE product_symbol IN ('ES', 'NQ', 'YM')
  AND trade_date >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY product_symbol, trade_date;

Data Freshness¶

Typical Schedule: - 4:30 AM UTC: Attemps to download missing data for ~110 products (5-10 minutes). Usually around 40% of data is available at this time. - 5:30 AM - 3:30 AM: Hourly checks, downloads missing data as they are avaiable

Coverage: - Rolling 2-week lookback ensures recent gaps are auto-filled - Weekends excluded (markets closed) - Yesterday is latest available (T-1 data)

Unique Constraints¶

One record per (products_tickdata_id, trade_date)
Prevents duplicate OHLC entries for same product/date

products_tickdata: Product symbol mappings and price multipliers
products: Main product definitions