I'm trying to create a trading bar by grouping data every X range the price has moved. The data is from tick and every tick has a timestamp and a price vaue.
The data set looks like this:
date price
"2020-02-03 02:00:00.207" 1.10890
"2020-02-03 02:00:00.258" 1.10887
"2020-02-03 02:00:00.461" 1.10887
"2020-02-03 02:00:00.613" 1.10886
I would like to "create" a bar when the price moves X amount of distance. This distance is measured in pips and, for example, if the price moves from 1.10000 to 1.10010 that is a 1 pip movement. (A trading bar or candlestick is defined as the variation of price of an asset given a condition. For example, 1 hour bars sample every hour and they have an open price, low price, high price and close price. So if it is the 00:00:00 hours, the bar ends sampling at 01:00:00 hours). In this example, the selected range is 10 pips, so I would like to sample (or create) a bar every 10 pips.
I have a python script that can create the bars I need, but I would like to have this as a SQL trigger. The python function I use is as follows:
def generate_rangebars(data,range_pips=10,point_value=0.0001):
_o,_h,_l,_c,_d=[],[],[],[],[]
k = 0
prices = data.to_numpy()
while True:
for j in range(k,len(prices)):
if np.abs(prices[k]-prices[j])/point_value>=range_pips:
_d.append(pd.to_datetime(times[j],format= "%d.%m.%Y %H:%M:%S.%f"))
_o.append(prices[k])
_h.append(np.max(prices[k:j+1]))
_l.append(np.min(prices[k:j+1]))
_c.append(prices[j])
k=j
break
if j == len(prices)-1:
break
So, basically I start at index 0 and iterate through the prices array. If the absolute value of the difference between price[j] and the price at 0 is greater than the desired range, I store the date of the price[j]. The open price of the bar in this case would be the price in k and the close price would be the price in j. To get the high and low prices, I select the max and min prices from k up to j+1. At last, if the condition is not met, then it breaks the loop once we reach the end of the data.
How can I replicate this in SQL, so that the bars are created when the required range is satiesfied and not consider the excess of ticks in the process? For example, if I have 10500 ticks and the first 10000 ticks satisfy the condition to create 10 bars, then I would like to only create those 10 bars and ignore the other 500 ticks until there are enough ticks to create another X pips bar.
How can I achieve this?
Thank you in advance!
EDIT: This is an example of creating two bars. Les suppose that the desired range is 1 pip and we have this data.
date price
"2020-02-03 02:00:00.207" 1.10880
"2020-02-03 02:00:00.258" 1.10887
"2020-02-03 02:00:00.461" 1.10887
"2020-02-03 02:00:00.613" 1.10886
"2020-02-03 02:00:00.753" 1.10888
"2020-02-03 02:00:01.200" 1.10889
"2020-02-03 02:00:01.268" 1.10890 -->1 Pip!
"2020-02-03 02:00:01.439" 1.10886
So, our open price is 1.10886, close price is 1.10890 and the high and low prices are the max and min between "2020-02-03 02:00:00.207" and "2020-02-03 02:00:01.268".
So our bar looks like this:
---------------------------------------------------------------------------
| date | open | high | low | close |
|2020-02-03 02:00:01.268000 | 1.10880 | 1.10890 | 1.10880 | 1.10890 |
Now, we take this value "2020-02-03 02:00:01.268" and search for all ticks that are from "2020-02-03 02:00:01.268" and look forward another range of 1 pip.
date price
"2020-02-03 02:00:01.268" 1.10890 -->Last 1 Pip!
"2020-02-03 02:00:01.439" 1.10886
"2020-02-03 02:00:01.607" 1.10883
"2020-02-03 02:00:01.895" 1.10885
"2020-02-03 02:00:02.125" 1.10890
"2020-02-03 02:00:02.971" 1.10895
"2020-02-03 02:00:03.045" 1.10898
"2020-02-03 02:00:03.200" 1.10105 --> (More than )1 Pip!
So, in this case, the value of the bar are:
---------------------------------------------------------------------------
| date | open | high | low | close |
|2020-02-03 02:00:01.268000 | 1.10880 | 1.10890 | 1.10880 | 1.10890 |
|2020-02-03 02:00:03.200000 | 1.10890 | 1.10105 | 1.10883 | 1.10105 |->new bar
Are you looking to create this via SQL query or PL/pgSQL stored procedure? It looks this can be achieved using SQL query with GROUP BY clause/ WINDOW function. Here is the untested version of code. Hope this will give an idea to achieve this?
SELECT first_value (price) OVER w AS open,
MAX(price) OVER w AS high,
MIN(price) OVER w as low,
last_value(price) OVER w as close,
date_trunc('required interval here', date)
FROM table
WINDOW w AS (PARTITION BY date_trunc('required interval here', date) ORDER BY date)