If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 > Is there an easy way to calculate a range bar from (stock) tick prices?

 Gagnon Registered User Join Date: Jun 2005 Posts: 319
Is there an easy way to calculate a range bar from (stock) tick prices?

 Assume you have a table called Tick with 2 columns ( tickId bigint IDENTITY(1,1) , price int -- usually money data type, making it int for simplicity ) I am tasked with creating bars that are 10 units long. Now the catch is I'm not looking for the tickId where price is >= t1(price) + 10 where t1(price) is the price for the first row where tickId = 1. (it could also be where price <= t1(price) - 10) Here is sample data: 1, 25 2, 26 3, 23 4, 26 5, 27 6, 30 7, 34 8, 32 9, 30 10, 33 What I am looking for are rows 3 (23) and 7 (34) Currently I have: Code: ```DECLARE @tickDiff int SET @tickDiff = 10 DECLARE @r1TickId bigint DECLARE @r3TickId bigint DECLARE @r4TickId bigint SET @r1TickId = 1 SELECT @r3TickId = MIN(r1.tickId) FROM dbo.Tick r1 JOIN dbo.Tick r2 ON r1.tickId < r2.tickId AND r1.price >= r2.price + @tickDiff WHERE r1.tickId >= @r1TickId SELECT @r4tickId = MIN(r4.tickId) FROM dbo.Tick r3 JOIN dbo.Tick r4 ON r3.tickId < r4.tickId AND r3.price >= r4.price + @tickDiff WHERE r3.tickId = @r3TickId``` This seems to work but it is taking multiple minutes to run for about 50k rows of data (which I created off of the 24 million row table I have just looking at data from today). So it takes ~5 minutes to create the first bar which is not acceptible. If my logic above seems acceptable are there any indexes you could recommend. Database engine tuning advisor didn't find any.
 Gagnon Registered User Join Date: Jun 2005 Posts: 319
 I feel like the set based operation is killing the performance since it has to evaluate every combination of tickId pairs and I would be better off creating a while loop or cursor to perform as such: 1) using the price for tickId 1, find the tickId with a price >= price + 10 and the tickId with a price <= price - 10. 2) take the lesser tickId from (1) -- at this point I will have a subset of data and I need to further evaluate it to see if there is a better paring (i.e. with an end range < the end range found in (1)) 3) evaluate further using query in first post from this subset of data So that I would be decreasing my set from 50k down to 100 records or so, make sense?
 Well, looks like I answered my own question, here is the answer: Code: ```DECLARE @secId int , @r1TickId bigint , @tickDiff money SELECT @r1TickId = 1 SELECT @tickDiff = 10 DECLARE @r2TickId bigint DECLARE @r3TickId bigint DECLARE @r4TickId bigint SELECT @r2tickId = MIN(r2.tickId) FROM dbo.Tick r1 (nolock) JOIN dbo.Tick r2 (nolock) ON r1.tickId < r2.tickId AND ABS(r1.price - r2.price) >= @tickDiff WHERE r1.tickId = @r1TickId SELECT @r3TickId = MIN(r1.tickId) FROM dbo.Tick r1 (nolock) JOIN dbo.Tick r2 (nolock) ON r1.tickId < r2.tickId AND ABS(r1.price - r2.price) >= @tickDiff WHERE r1.tickId between @r1tickId and @r2tickId AND r1.secId = @secId SELECT @r4tickId = MIN(r4.tickId) FROM dbo.Tick r3 (nolock) JOIN dbo.Tick r4 (nolock) ON r3.tickId < r4.tickId AND ABS(r3.price - r4.price) >= @tickDiff WHERE r3.tickId = @r3TickId SELECT * FROM dbo.Tick (nolock) WHERE tickId in (@r1tickId, @r3tickId, @r4tickId) ORDER BY tickId```