Source: https://databento.com/docs/examples/basics-historical/custom-ohlcv # Custom OHLCV bars from trades ## Overview This example shows how to use the [Historical client](https://databento.com/docs/api-historical/client.md) to resample trade data at a fixed interval to construct 1-minute OHLCV bars. Our OHLCV schemas are directly derived from the [Trades schema](https://databento.com/docs/schemas/trades.md). This example will repeat this derivation to demonstrate that trades data can be resampled to construct the bars in the [OHLCV-1m schema](https://databento.com/docs/schemas/ohlcv.md). This will be done using the [pandas](https://pypi.org/project/pandas/) package, specifically the [DataFrame.resample](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.resample.html) method. ## Example ```python 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 ```plaintext 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 ``` # Constructing custom OHLCV bars from trades ## Overview This example shows how to use the [Historical client](https://databento.com/docs/api-historical/client.md) to resample trade data at a fixed interval to construct 1-minute OHLCV bars. Our OHLCV schemas are directly derived from the [Trades schema](https://databento.com/docs/schemas/trades.md). This example will repeat this derivation to demonstrate that trades data can be resampled to construct the bars in the [OHLCV-1m schema](https://databento.com/docs/schemas/ohlcv.md). ## Example ```rust use std::collections::BTreeMap; use databento::{ HistoricalClient, dbn::{ Dataset, OhlcvMsg, RecordHeader, Schema, SymbolIndex, TradeMsg, decode::DbnMetadata, pretty::Px, rtype, }, historical::timeseries::GetRangeParams, }; use time::{OffsetDateTime, macros::datetime}; #[tokio::main] async fn main() -> anyhow::Result<()> { let mut client = HistoricalClient::builder().key_from_env()?.build()?; // First, we will request trades data let mut params = GetRangeParams::builder() .dataset(Dataset::GlbxMdp3) .schema(Schema::Trades) .symbols(["ESM2", "ESU2"]) .date_time_range(datetime!(2022-06-06 20:50 UTC)..datetime!(2022-06-06 21:10 UTC)) .build(); let mut data = client.timeseries().get_range(¶ms).await?; let symbol_map = data.metadata().symbol_map()?; // Bars by minute timestamp and symbol let mut ohlcv = BTreeMap::new(); while let Some(trade) = data.decode_record::().await? { let symbol = symbol_map.get_for_rec(trade).unwrap(); // Round down to minute let ts_bar = trade .hd .ts_event() .unwrap() .replace_second(0)? .replace_nanosecond(0)?; ohlcv .entry((ts_bar, symbol)) .and_modify(|bar| aggregate(bar, trade)) .or_insert_with(|| bar_from_trade(ts_bar, trade)); } println!( "{:<33}{:<6}{:>9}{:>9}{:>9}{:>9}{:>8}", "ts_event", "symbol", "open", "high", "low", "close", "volume" ); for ((ts, symbol), bar) in ohlcv.iter() { println!( "{ts:<33}{symbol:<6}{:>9.2}{:>9.2}{:>9.2}{:>9.2}{:>8}", Px(bar.open), Px(bar.high), Px(bar.low), Px(bar.close), bar.volume ); } // As an additional exercise, we will validate our resampled data against the // OHLCV-1m schema params.schema = Schema::Ohlcv1M; let mut data = client.timeseries().get_range(¶ms).await?; let mut record_count = 0; while let Some(bar) = data.decode_record::().await? { let symbol = symbol_map.get_for_rec(bar).unwrap(); assert_eq!( bar, ohlcv.get(&(bar.hd.ts_event().unwrap(), symbol)).unwrap() ); record_count += 1; } assert_eq!(record_count, ohlcv.len()); Ok(()) } fn bar_from_trade(ts_bar: OffsetDateTime, trade: &TradeMsg) -> OhlcvMsg { OhlcvMsg { hd: RecordHeader::new::( rtype::OHLCV_1M, trade.hd.publisher_id, trade.hd.instrument_id, ts_bar.unix_timestamp_nanos() as u64, ), open: trade.price, high: trade.price, low: trade.price, close: trade.price, volume: trade.size as u64, } } fn aggregate(bar: &mut OhlcvMsg, trade: &TradeMsg) { bar.high = bar.high.max(trade.price); bar.low = bar.low.min(trade.price); bar.close = trade.price; bar.volume += trade.size as u64; } ``` ## Result ```plaintext ts_event symbol open high low close volume 2022-06-06 20:50:00.0 +00:00:00 ESM2 4120.50 4122.25 4120.50 4122.25 428 2022-06-06 20:50:00.0 +00:00:00 ESU2 4123.25 4124.50 4123.25 4124.50 9 2022-06-06 20:51:00.0 +00:00:00 ESM2 4122.25 4122.25 4121.50 4122.00 254 2022-06-06 20:51:00.0 +00:00:00 ESU2 4124.00 4124.00 4124.00 4124.00 2 2022-06-06 20:52:00.0 +00:00:00 ESM2 4122.00 4122.25 4122.00 4122.25 118 2022-06-06 20:52:00.0 +00:00:00 ESU2 4124.25 4124.25 4124.25 4124.25 1 2022-06-06 20:53:00.0 +00:00:00 ESM2 4122.25 4122.50 4121.75 4121.75 94 2022-06-06 20:54:00.0 +00:00:00 ESM2 4121.75 4122.50 4121.75 4122.25 125 2022-06-06 20:55:00.0 +00:00:00 ESM2 4122.00 4122.50 4122.00 4122.00 172 2022-06-06 20:56:00.0 +00:00:00 ESM2 4122.25 4122.50 4122.00 4122.25 34 2022-06-06 20:56:00.0 +00:00:00 ESU2 4124.75 4124.75 4124.75 4124.75 6 2022-06-06 20:57:00.0 +00:00:00 ESM2 4122.25 4122.50 4122.00 4122.00 121 2022-06-06 20:57:00.0 +00:00:00 ESU2 4124.75 4124.75 4124.75 4124.75 1 2022-06-06 20:58:00.0 +00:00:00 ESM2 4122.00 4122.00 4121.25 4121.50 131 2022-06-06 20:58:00.0 +00:00:00 ESU2 4124.00 4124.25 4124.00 4124.25 2 2022-06-06 20:59:00.0 +00:00:00 ESM2 4121.50 4121.75 4120.50 4120.75 313 2022-06-06 20:59:00.0 +00:00:00 ESU2 4123.75 4123.75 4122.75 4122.75 9 ```