Custom OHLCV bars from trades
Overview
This example shows how to use the Historical client to resample trade data at a fixed interval to construct 1-minute OHLCV bars.
Our OHLCV schemas are directly derived from the Trades schema. This example will repeat this derivation to demonstrate that trades data can be resampled to construct the bars in the OHLCV-1m schema.
This will be done using the pandas package, specifically the DataFrame.resample method.
Example
import databento as db import pandas as pd # Create a historical client client = db.Historical("$YOUR_API_KEY") # Set parameters dataset = "GLBX.MDP3" start = "2022-06-06T20:50:00" end = "2022-06-06T21:00:00" symbols = ["ESM2", "ESU2"] # Request trades data for two instruments and convert to DataFrame trades_df = client.timeseries.get_range( dataset=dataset, start=start, end=end, symbols=symbols, schema="trades", ).to_df() # Groupby symbol and resample to 1-minute bars resampled_df = ( trades_df.groupby("symbol") .resample("1min", include_groups=False) .agg( open=("price", "first"), high=("price", "max"), low=("price", "min"), close=("price", "last"), volume=("size", "sum"), ) .dropna() .reset_index() .rename(columns={"ts_recv": "ts_event"}) .sort_values(by=["ts_event", "symbol"]) .set_index("ts_event") ) # Print out resampled data print(resampled_df) # We will validate our resampled data against the OHLCV-1m schema ohlcv_data = client.timeseries.get_range( dataset=dataset, start=start, end=end, symbols=symbols, schema="ohlcv-1m", ) # Sort the DataFrame rows by ts_event and symbol ohlcv_df = ( ohlcv_data.to_df() .reset_index() .sort_values(by=["ts_event", "symbol"]) .set_index("ts_event") ) # Validate these two DataFrames are equal pd.testing.assert_frame_equal( resampled_df, ohlcv_df[["symbol", "open", "high", "low", "close", "volume"]], check_dtype=False, # ignore dtypes ) Result
symbol open high low close volume ts_event 2022-06-06 20:50:00+00:00 ESM2 4120.50 4122.25 4120.50 4122.25 428 2022-06-06 20:50:00+00:00 ESU2 4123.25 4124.50 4123.25 4124.50 9 2022-06-06 20:51:00+00:00 ESM2 4122.25 4122.25 4121.50 4122.00 254 2022-06-06 20:51:00+00:00 ESU2 4124.00 4124.00 4124.00 4124.00 2 2022-06-06 20:52:00+00:00 ESM2 4122.00 4122.25 4122.00 4122.25 118 2022-06-06 20:52:00+00:00 ESU2 4124.25 4124.25 4124.25 4124.25 1 2022-06-06 20:53:00+00:00 ESM2 4122.25 4122.50 4121.75 4121.75 94 2022-06-06 20:54:00+00:00 ESM2 4121.75 4122.50 4121.75 4122.25 125 2022-06-06 20:55:00+00:00 ESM2 4122.00 4122.50 4122.00 4122.00 172 2022-06-06 20:56:00+00:00 ESM2 4122.25 4122.50 4122.00 4122.25 34 2022-06-06 20:56:00+00:00 ESU2 4124.75 4124.75 4124.75 4124.75 6 2022-06-06 20:57:00+00:00 ESM2 4122.25 4122.50 4122.00 4122.00 121 2022-06-06 20:57:00+00:00 ESU2 4124.75 4124.75 4124.75 4124.75 1 2022-06-06 20:58:00+00:00 ESM2 4122.00 4122.00 4121.25 4121.50 131 2022-06-06 20:58:00+00:00 ESU2 4124.00 4124.25 4124.00 4124.25 2 2022-06-06 20:59:00+00:00 ESM2 4121.50 4121.75 4120.50 4120.75 313 2022-06-06 20:59:00+00:00 ESU2 4123.75 4123.75 4122.75 4122.75 9