1

I'm trying to create OHLC bars in postgresql starting from tick data. I want to create bars every 1000 ticks or every 500 ticks. Or every X amount of ticks.

The database I'm saving has the bid/ask and a timestamp. I know that I could do a groupby and group them by timestamp, but the desired output is using the amount of ticks.

One tick is composed by a timestamp, a bid price and an ask price.

The tick database looks something like this:

-------------------------------------------------
|            date           |   bid   |   ask   |
|2020-03-20 19:33:56.044533 | 1.06372 | 1.06384 |
|2020-03-20 19:33:37.205241 | 1.06372 | 1.06384 |
|2020-03-20 19:33:54.943593 | 1.06372 | 1.06383 |
|2020-03-20 19:33:55.183255 | 1.06372 | 1.06384 |

I would like to group every X amounts of ticks to create this output:

---------------------------------------------------------------------------
|            date           |   open   |    high    |    low   |   close   |
|2020-03-20 19:33:56.044533 | 1.06372  |   1.07104  |  1.06001 |  1.06579  |

That is 1 candle. The numbers came from the bid column. The open price is the first price registered, the close price is the last price registered and the high and low are the max and min prices registered in those X ticks.

So, if X is 1000 and assuming that the index starts from 0, the OHLC prices would be as follows: - open: price at index 0 - high: max price between index 0 and 999 - low : min price between index 0 and 999 - close: price at index 999

That is for the first 1000 ticks. Then the next candles is created by the next following 1000 ticks. - open: price at index 1000 - high: max price between index 1000 and 1999 - low : min price between index 1000 and 1999 - close: price at index 1999

How can I achieve this?

Thank you in advance!

Pedro Pablo Severin Honorato
  • 377
  • 3
  • 16
  • 1
    Please see [ask]. Following that greatly enhances your chance of getting a satisfactory answer. In particular you need sample data as formatted text - **no images**, the desired results from that data, table definitions (DDL). Include what you have already tried. Describe any specialized terms; what is a tick. – Belayer May 05 '20 at 05:53
  • Thank you for your comment, Belayer. I added more information, as you requested. Hope that this clarifies my question. – Pedro Pablo Severin Honorato May 05 '20 at 06:29
  • 2
    Well this has something. However you still haven't defined a tick. I think this is domain knowledge term, but meaningless for someone outside the domain. Is each timestamp a tick, the data posted covers ~20 seconds, is a tick 20 sec, it is something else? Additionally there is no way to get the output from the input, the values for high, low, close do not exist in the input. – Belayer May 05 '20 at 06:43
  • What is a "candle"? – Gordon Linoff May 05 '20 at 11:42
  • A tick is a variation in the price of an asset @Belayer. When the bid price or ask price changes (or the volume of transaction changes) that is considered a tick. – Pedro Pablo Severin Honorato May 05 '20 at 17:08
  • @GordonLinoff a candle is a representation of the movement of the price. The most common candle (or bar) are the time based one. You create one bar every 1 minute, 5 minutes, 1 hour, 1 day or whatever timeframe you want. Main characteristics are the Open, High, Low, Close prices. Open is the first price registered. High and Low are max and min, and Close is the last price registered. – Pedro Pablo Severin Honorato May 05 '20 at 17:12

1 Answers1

2

You can aggregate fixed numbers of rows using row_number() and arithmetic:

select min(date),
       (array_agg(bid order by seqnum asc))[1] as open,
       (array_agg(bid order by seqnum desc))[1] as close,
       min(bid) as min_bid, max(bid) as max_bid
from (select t.*, row_number() over (order by date) as seqnum
      from ticks t
     ) t
group by floor((seqnum - 1) / 500);

This uses "hack" to get the open and close -- by using arrays.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you @GordonLinoff. I'm getting this error: "ERROR: subquery in FROM must have an alias LINE 7: from ( ^ HINT: For example, FROM (SELECT ...) [AS] foo. SQL state: 42601 Character: 277" I tried to modify the query myself, but could not fix it. Any idea why? – Pedro Pablo Severin Honorato May 05 '20 at 17:16
  • @PedroPabloSeverinHonorato . . . The subquery does have an alias. – Gordon Linoff May 06 '20 at 01:31
  • Thank you! @GordonLinoff , This worked :). One more thing, how can I do to create only candles if I have the required number of ticks? For example, if I have 10500 ticks, then I would like to have only 10 rows and don't consider the other 500 remaining ticks until the amount is 11000 – Pedro Pablo Severin Honorato May 06 '20 at 03:10
  • @PedroPabloSeverinHonorato . . . I think you should ask another question, with a clear explanation of what you want to do. That problem is quite different. – Gordon Linoff May 06 '20 at 10:41