Resample OHLCV from 1-minute to 5-minute

Databento follows a convention in the OHLCV schema to only publish a record when a trade occurs in the interval.

This approach is adopted by most data vendors for two key reasons:

Multiple interpolation strategies exist and the optimal choice depends on the specific use case. Client-side interpolation keeps the strategy transparent.
This reduces storage and bandwidth requirements, especially for markets with many illiquid instruments like options.

This example demonstrates one way to interpolate OHLCV-1m data to ensure exactly one row per minute. The interpolation strategy used in this example does the following:

Forward-fills the close price from the last known value.
Sets open, high, and low equal to the forward-filled close price.
Sets volume to 0 for interpolated periods.

Overview

In this example, we'll use the historical client to request data from the OHLCV-1m schema. First, we'll highlight how to interpolate missing rows in the 1-minute data. Next, we'll show how to resample the data to 5-minute bars.

Note that while this example is showing these methods being used individually, you can also chain these methods together.

Example

Python

      
    
import databento as db
import pandas as pd

def interpolate_ohlcv(
    df: pd.DataFrame,
    start: pd.Timestamp,
    end: pd.Timestamp,
    interp_interval: str,
) -> pd.DataFrame:
    """
    Interpolate OHLCV records between `start` and `end`, since Databento only sends
    an OHLCV record if a trade happens in that interval.
    """
    def _interpolate_group(group):
        """Interpolate OHLCV records for each group"""
        # Reindex with a complete index using specified start/end times
        group = group.reindex(
            pd.date_range(
                start=start,
                end=end,
                freq=interp_interval,
                inclusive="left",
            ).rename(group.index.name),
        )
        # Forward fill close prices (may remain NaN if no prior data exists)
        group["close"] = group["close"].ffill()

        # For intervals with no trades, set open/high/low equal to the close and volume to 0
        group = group.fillna({
            **{col: group["close"] for col in ["open", "high", "low"]},
            "volume": 0,
        })
        group["volume"] = group["volume"].astype(int)
        group = group.drop(columns=["rtype", "instrument_id"], errors="ignore")

        return group

    df = (
        df.groupby(["publisher_id", "symbol"])
        .apply(_interpolate_group, include_groups=False)
        .reset_index(["publisher_id", "symbol"])
        .sort_values(["ts_event", "publisher_id", "symbol"])
    )
    return df


def resample_ohlcv(
    df: pd.DataFrame,
    resample_interval: str,
) -> pd.DataFrame:
    """Resample OHLCV bars to the specified interval"""
    resampled_df = (
        df.groupby(["publisher_id", "symbol"])
        .resample(resample_interval)
        .agg({
            "open": "first",
            "high": "max",
            "low": "min",
            "close": "last",
            "volume": "sum",
        })
        .reset_index(["publisher_id", "symbol"])
        .sort_values(["ts_event", "publisher_id", "symbol"])
    )

    return resampled_df


# Set parameters
start = pd.Timestamp("2024-12-17T09:30:00", tz="US/Eastern")
end = pd.Timestamp("2024-12-17T16:00:00", tz="US/Eastern")

# Create a historical client
client = db.Historical("$YOUR_API_KEY")

# Request OHLCV-1m data for all AAPL options and convert to DataFrame
df = client.timeseries.get_range(
    dataset="OPRA.PILLAR",
    schema="ohlcv-1m",
    symbols="AAPL.OPT",
    stype_in="parent",
    start=start,
    end=end,
).to_df(tz=start.tzinfo)

# Interpolate missing rows.
df1 = interpolate_ohlcv(df, start, end, "1min")
print(df1)

# Resample to 5-minute bars
df2 = resample_ohlcv(df, "5min")
print(df2)

Result

Python

      
    
                           publisher_id                 symbol   open   high    low  close  volume
ts_event
2024-12-17 09:30:00-05:00            20  AAPL  241220C00115000    NaN    NaN    NaN    NaN       0
2024-12-17 09:30:00-05:00            20  AAPL  241220C00155000    NaN    NaN    NaN    NaN       0
2024-12-17 09:30:00-05:00            20  AAPL  241220C00170000    NaN    NaN    NaN    NaN       0
2024-12-17 09:30:00-05:00            20  AAPL  241220C00195000    NaN    NaN    NaN    NaN       0
2024-12-17 09:30:00-05:00            20  AAPL  241220C00200000    NaN    NaN    NaN    NaN       0
...                                 ...                    ...    ...    ...    ...    ...     ...
2024-12-17 15:59:00-05:00            61  AAPL  261218C00240000  50.70  50.70  50.70  50.70       0
2024-12-17 15:59:00-05:00            61  AAPL  261218P00050000   0.29   0.29   0.29   0.29       0
2024-12-17 15:59:00-05:00            61  AAPL  270115C00240000  50.14  50.14  50.14  50.14       0
2024-12-17 15:59:00-05:00            61  AAPL  270115C00250000  44.34  44.34  44.34  44.34       0
2024-12-17 15:59:00-05:00            61  AAPL  270115C00440000   3.50   3.50   3.50   3.50       0

[3129360 rows x 7 columns]

                           publisher_id                 symbol   open   high    low  close  volume
ts_event
2024-12-17 09:30:00-05:00            20  AAPL  241220C00220000  30.70  30.70  30.70  30.70       1
2024-12-17 09:30:00-05:00            20  AAPL  241220C00225000  25.95  25.95  25.95  25.95       2
2024-12-17 09:30:00-05:00            20  AAPL  241220C00242500   8.20   8.20   8.20   8.20       1
2024-12-17 09:30:00-05:00            20  AAPL  241220C00245000   6.30   6.30   6.17   6.17       4
2024-12-17 09:30:00-05:00            20  AAPL  241220C00247500   3.70   3.85   3.70   3.85       2
...                                 ...                    ...    ...    ...    ...    ...     ...
2024-12-17 15:55:00-05:00            61  AAPL  250124C00255000   5.95   5.95   5.95   5.95       1
2024-12-17 15:55:00-05:00            61  AAPL  250124C00285000   0.30   0.30   0.30   0.30      47
2024-12-17 15:55:00-05:00            61  AAPL  250124P00235000   1.01   1.01   1.01   1.01       1
2024-12-17 15:55:00-05:00            61  AAPL  250321C00225000  33.90  33.90  33.90  33.90       1
2024-12-17 15:55:00-05:00            61  AAPL  250919P00105000   0.33   0.33   0.33   0.33       2

[231713 rows x 7 columns]