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

Gagnon · #1 (**permalink**) 12-30-11, 14:09

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 · #2 (**permalink**) 12-30-11, 14:22

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?

Gagnon · #3 (**permalink**) 12-30-11, 14:29

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

Internet Services

Web Development

Digital Marketing

Consumer Tech