domain specific functions for finance and aggregation

[nwoolmer]

Is your feature request related to a problem?

Many users of QuestDB are in the finance industry. Within this domain, there are common operations that can be complicated to represent in standard SQL.

We should add functions to make these operations easier for our users, and start with the most common operations.

Such functions should support ARRAY types in future, where useful for performance improvements.

This is a tracking issue, and we welcome suggestions from users!

Describe the solution you'd like.

More domain-specific finance functions around trading and order books.

• [x] l2price: level two order-book price
  • PR: https://github.com/questdb/questdb/pull/4462
  • Issue: https://github.com/questdb/questdb/issues/4454
• [x] vwap: volume-weighted average price
  • PR: https://github.com/questdb/questdb/pull/3885
• [ ] twap: time-weighted average price
  • PR: https://github.com/questdb/questdb/pull/4670
• [x] avg: avg window function, can be used to calculate simple moving average
  • docs: https://questdb.io/docs/reference/function/aggregation/#avg
• [ ] vwma: volume-weighted moving average (window)
  • Issue: https://github.com/questdb/questdb/issues/4727
  • not looking for contributions, some internal work needed
• [x] mid(bid,ask): average(bid, ask) = bid+ask / 2
  • Issue: https://github.com/questdb/questdb/issues/4730
  • PR: https://github.com/questdb/questdb/pull/4734
• [x] spread(bid, ask): ask-bid
  • Issue: https://github.com/questdb/questdb/issues/4731
  • PR: https://github.com/questdb/questdb/pull/4744
• [x] wmid(bid_size, bid, ask, ask_size): weighted mid point:
  • wmid = (imbalance * ask_price) + (1 - imbalance) * bid_price where imbalance = bid_size / (bid_size + ask_size)
  • Issue: https://github.com/questdb/questdb/issues/4800
  • PR: https://github.com/questdb/questdb/pull/4801
• [x] spread_bps(bid, ask): spread(bid,ask)/mid(bid,ask) * 10,000
  • PR: https://github.com/questdb/questdb/pull/4939
• [ ] format_price(decimal_form, tick_size): treasury price format, decimal to fraction, e.g. format_price(100.5, 32)= 100-16
  • more detail: https://www.cmegroup.com/education/courses/introduction-to-treasuries/calculating-us-treasury-pricing.html
  • PR: https://github.com/questdb/questdb/pull/4960
• [ ] get_price(fraction_form, tick_size): treasury price format, fraction to decimal, e.g. format_price(100-16, 32)= 100.5
  • more detail: https://www.cmegroup.com/education/courses/introduction-to-treasuries/calculating-us-treasury-pricing.html

More aggregation functions:

• [ ] normalised_perf(column, base_value): base_value x column[i] / column[0]
• [x] regr_slope(y, x): https://duckdb.org/docs/sql/aggregates.html#regr_slopey-x
  • [x] PR: https://github.com/questdb/questdb/pull/5021
• [ ] regr_intercept(y, x): https://duckdb.org/docs/sql/aggregates.html#regr_intercepty-x
  • [ ] PR: https://github.com/questdb/questdb/pull/5071

Convenience functions:

• [ ] to_bbg(Ø): convert a RIC code to a Bloomberg symbol
  • Issue: https://github.com/questdb/questdb/issues/4802
• [ ] to_ric(Ø): convert a Bloomberg symbol to a RIC code
  • Issue: https://github.com/questdb/questdb/issues/4802

And similar. Names are flexible!

Aggregation functions should be linked in the finance section where relevant, with an associated finance example.

• [ ] GT Join
  • [ ] Issue: https://github.com/questdb/questdb/issues/4995

Describe alternatives you've considered.

No response

Full Name:

Nick Woolmer

Affiliation:

QuestDB

Additional context

No response

[siddharth0815]

@nwoolmer whats the expectation with volume-weighted moving average (window) function, how should it work? can you share an example?

[nwoolmer]

Hi @siddharth0815 ,

VWAP: sum(price * quantity) / sum(quantity)

So you multiply all prices by their quantities, and then divide by the total quantity.

VWMA is similar (and maybe we should call the function vwma instead of vwavg, not sure!).

However, you use a window to define a subset of the data to take into account. So let's say you had a 3 trade window. It'd be a calculation like:

((price1 * quantity1) + (price2 * quantity2) + (price3 * quantity3)) / (quantity1 + quantity2 + quantity3)

Best bet is probably to look at the avg window function: https://github.com/questdb/questdb/blob/master/core/src/main/java/io/questdb/griffin/engine/functions/window/AvgDoubleWindowFunctionFactory.java

These can be significant to implement, and @puzpuzpuz might have some advice for how best to approach a solution!

[raj5555]

Hi, Can i work on adding get_price(fraction_form, tick_size) ?