Generating ohlcv/candlestick charts data with time series

Working with Data
, ,
1

Hi,

Im trying to aggregate ohlcv data with time series and I’m following along this blogpost. So far it works but I ran in two problems:

First Issue: I tried to create a new time series collection with mongoose. But somehow MongoDB Compass does not recognise the created collection as time series. It does work though when I create it manually in Compass. Any idea why it does not work with mongoose?

const transactionSchema = new Schema(
    time: {
      type: Date,
      required: true,
    },

   *some more unrelated fields...*

    symbol: {
      type: String,
      required: true,
    },
  },
  {
    timeseries: {
      timeField: 'time',
      metaField: 'symbol',
      granularity: 'seconds',
    },
  }
);

Second Issue: After that I tried to aggregate the data, see code below. It’s basically the code from the blogpost but I expanded it with densify/fill because I had gaps coming from the low volume of the trading pair.

aggregate([
      {
        $match: {
          symbol: 'MIN/ADA',
        },
      },
      {
        $group: {
          _id: {
            symbol: '$symbol',
            time: {
              $dateTrunc: {
                date: '$time',
                unit: 'minute',
                binSize: 5,
              },
            },
          },
          high: { $max: '$price' },
          low: { $min: '$price' },
          open: { $first: '$price' },
          close: { $last: '$price' },
          volume: { $sum: '$quote_amount' },
        },
      },
      {
        $addFields: {
          timestamp: {
            $toLong: '$_id.time',
          },
        },
      },
      {
        $densify: {
          field: '_id.time',
          range: { step: 5, unit: 'minute', bounds: 'full' },
        },
      },
      {
        $fill: {
          output: {
            high: { value: '$close' },
            low: { value: '$close' },
            open: { value: '$close' },
            close: { method: 'locf' },
            volume: { value: 0 },
            timestamp: { value: { $toLong: '$_id.time' } },
          },
        },
      },
      {
        $sort: {
          '_id.time': 1,
        },
      },
    ]);

This results in this:

.....
 {
        "_id": {
            "symbol": "MIN/ADA",
            "time": "2022-09-15T01:20:00.000Z"
        },
        "high": 0.04064681166924041,
        "low": 0.04064638374655647,
        "open": 0.04064681166924041,
        "close": 0.04064638374655647,
        "volume": 161.889997,
        "timestamp": 1663204800000
    },
    {
        "_id": {
            "symbol": "MIN/ADA",
            "time": "2022-09-15T01:25:00.000Z"
        },
        "high": 0.040638063124977655,
        "low": 0.040638063124977655,
        "open": 0.040638063124977655,
        "close": 0.040638063124977655,
        "volume": 2984.428427,
        "timestamp": 1663205100000
    },

As you can see it works but the close price of the previous is different from the open price of the current. This leads to weird looking charts. Normally they should be the same. I think it happens because of the low volume of the pair. Is there any way to get for the current open price the price from the previous close price (if there is one)?