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.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Is there an easy way to calculate a range bar from (stock) tick prices?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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.
Reply With Quote
  #2 (permalink)  
Old
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?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jun 2005
Posts: 319
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On