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 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
                          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