0

I need to plot the Volume-Weighted Average Prive (VWAP) of trades having a price_per_unit and a quantitiy over a certain time range.

As a result of the aggregation every bucket of the date_histogram should contain the VWAP of the all trades that happened so far.

I'm not really sure if this is possible using Elasticsearch and also not what would be the right way to approach it (like using a script?)?

The basic mapping for a trade document is quite simple:

"trade": {
  "properties": 
    "trade_id": {"type": "string", "index": "not_analyzed"},
    "product_id": {"type": "string", "index": "not_analyzed"},
    "quantity": {'type': 'double'}, // number of units
    "execution_time": {'type': 'date'},
    "price_per_unit": {'type': 'double'},
  }
}

Whereas execution_time should be used for the date_histogram and the total price of the trade is the product ofprice_per_unit and quantity. Therefore the VWAP = sum(price_per_unit * quantity) / sum(quantity).

Bernhard Vallant
  • 49,468
  • 20
  • 120
  • 148
  • 1
    Sounds like a [cumulative sum aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-cumulative-sum-aggregation.html) would help. – Andrei Stefan Nov 29 '16 at 13:36
  • Could you come up with a code sample for the question from above? I tried looking into cumulative sum but couldnt really implement it. – Bernhard Vallant Nov 29 '16 at 13:46
  • There is a sample in the link I provided. And I am not able to come up with a better sample than that one since you haven't provided any test data, desired output and mappings of the index. – Andrei Stefan Nov 29 '16 at 14:11
  • @AndreiStefan I've updated the question with the relevant mapping, `price_per_unit` and `quantity` are just numbers, so no real sample data needed; thanks for you help, it's appreciated a lot as it is difficult to wrap your head around ES' aggregations in the beginning. – Bernhard Vallant Nov 29 '16 at 14:31

1 Answers1

2
DELETE test
PUT test
{
  "mappings": {
    "trade": {
      "properties": {
        "trade_id": {
          "type": "string",
          "index": "not_analyzed"
        },
        "product_id": {
          "type": "string",
          "index": "not_analyzed"
        },
        "quantity": {
          "type": "double"
        },
        "execution_time": {
          "type": "date"
        },
        "price_per_unit": {
          "type": "double"
        }
      }
    }
  }
}

POST test/trade/_bulk
{"index":{}}
{"execution_time":"2016-11-18T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-18T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-19T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-20T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-20T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-20T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-21T22:45:27Z","quantity":10,"price_per_unit":5}
{"index":{}}
{"execution_time":"2016-11-21T22:45:27Z","quantity":10,"price_per_unit":5}

POST test/trade/_search
{
  "size": 0,
  "aggs": {
    "sales_per_day": {
      "date_histogram": {
        "field": "execution_time",
        "interval": "day"
      },
      "aggs": {
        "sales": {
          "sum": {
            "script": {
              "lang": "groovy",
              "inline": "doc['quantity'] * doc['price_per_unit']"
            }
          }
        },
        "cumulative_sales": {
          "cumulative_sum": {
            "buckets_path": "sales"
          }
        }
      }
    }
  }
}

And you need to enable inline scripting for groovy.

Andrei Stefan
  • 51,654
  • 6
  • 98
  • 89