September 8, 2010 at 7:30 am
Hi there, I have a database to track forex trades. See below for a DDL script.
The orders table contains one row for each order placed in the market. An order can
be an entry order or an exit order. This is defined by the signalTypeId which is an
FK to the signalTypes table. The signalTypes table has a bit column (entrySignal)
which indicates 1 for entry or 0 for exit.
There are two kinds of entry - an IE and an SC. There can only be one IE per trade but
any number of SCs. There are two kinds of exit - an MX and an XXX. There can be
any number of MXs per trade but only one XXX. Trades are placed in units.
An IE and SC are always 2 units, an MX always 1 unit and an XXX any number of units > 0.
So, a typical trade might have the following orders:
tradeId orderId signalTypeId units price side orderDateTime
------- -------- ------------ ----- ------ ---- -------------------
6 18 IE 2 1.3549 S 2010-03-19 15:00:00
6 19 SC 2 1.3473 S 2010-03-22 15:00:00
6 20 MX 1 1.3550 B 2010-03-23 03:00:00
6 21 MX 1 1.3333 B 2010-03-25 00:00:00
6 22 SC 2 1.3273 S 2010-03-26 00:00:00
6 23 XXX 4 1.3412 B 2010-03-26 20:00:00
translated to english would be Sell 2 units at 1.3549, then sell another 2 units
at 1.3473, then buy 1 unit at 1.3550, then buy another unit at 1.3333, then sell
2 units at 1.3273, and finally buy 4 units at 1.3412. Total sell units: 6, total buy units: 6.
I am trying to write a query that will show the profit for each entry. the profit is
entry price - next exit price for each entry. I want to see the following columns:
tradeId, signalTypeId, profit. So for example looking at the first signalTypeId
we have an IE with a sell of 2 units at 1.3549 - this trade can only be completed
when 2 units have been bought to balance the trade. So we would look for either
2 MXs or an XXX or an MX and an XXX. I would like my result set to look like this:
tradeId orderId signalTypeId profit orderDateTime
------- -------- ------------ ------ -------------------
6 18 IE 0.0215 2010-03-19 15:00:00
6 19 SC1 0.0122 2010-03-22 15:00:00
6 22 SC2 -0.0278 2010-03-26 00:00:00
There can be a variable number of MXs and SCs but only one IE and XXX per trade.
This query seems very complicated to write - I would really appreciate some help!
SQL CODE:
CREATE TABLE [dbo].[signalTypes](
[signalTypeId] [varchar](3) NOT NULL,
[description] [varchar](20) NOT NULL,
[entrySignal] [bit] NOT NULL,
CONSTRAINT [PK_signalTypes] PRIMARY KEY CLUSTERED
(
[signalTypeId] ASC
)
)
GO
INSERT INTO [signaltypes] ([signalTypeId],[description],[entrySignal])VALUES('IE','Initial Entry',1)
INSERT INTO [signaltypes] ([signalTypeId],[description],[entrySignal])VALUES('MX','Minor Exit',0)
INSERT INTO [signaltypes] ([signalTypeId],[description],[entrySignal])VALUES('SC','Scale in',1)
INSERT INTO [signaltypes] ([signalTypeId],[description],[entrySignal])VALUES('XXX','Final Exit',0)
go
CREATE TABLE [dbo].[orders](
[orderId] [int] NOT NULL,
[tradeId] [int] NOT NULL,
[units] [tinyint] NOT NULL,
[side] [char](1) NOT NULL,
[price] [decimal](18, 6) NOT NULL,
[spread] [decimal](3, 2) NULL,
[strategyId] [int] NOT NULL,
[signalTypeId] [varchar](3) NOT NULL,
[brokerId] [int] NOT NULL,
[orderTypeId] [char](2) NOT NULL,
[orderDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_orders] PRIMARY KEY NONCLUSTERED
(
[orderId] ASC
)
)
GO
ALTER TABLE [dbo].[orders] WITH CHECK ADD CONSTRAINT [FK_orders_signalTypes] FOREIGN KEY([signalTypeId])
REFERENCES [dbo].[signalTypes] ([signalTypeId])
GO
ALTER TABLE [dbo].[orders] CHECK CONSTRAINT [FK_orders_signalTypes]
GO
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(18,6,2,'S',1.354900,1.00,3,'IE',1,'SM','Mar 19 2010 3:00:00:000PM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(19,6,2,'S',1.347300,1.00,3,'SC',1,'SM','Mar 22 2010 3:00:00:000PM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(20,6,1,'B',1.355000,1.00,3,'MX',1,'BM','Mar 23 2010 3:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(21,6,1,'B',1.333300,1.00,3,'MX',1,'BM','Mar 25 2010 12:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(22,6,2,'S',1.327300,1.00,3,'SC',1,'SM','Mar 26 2010 12:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(23,6,4,'B',1.341200,1.00,3,'XXX',1,'BM','Mar 26 2010 8:00:00:000PM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(24,7,2,'S',1.344300,1.00,3,'IE',1,'SM','Apr 6 2010 4:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(25,7,1,'B',1.339100,1.00,3,'MX',1,'BM','Apr 7 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(26,7,1,'B',1.332700,1.00,3,'XXX',1,'BM','Apr 7 2010 12:00:00:000PM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(27,8,2,'S',1.347100,1.00,3,'IE',1,'SM','Apr 20 2010 3:00:00:000PM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(28,8,1,'B',1.342900,1.00,3,'MX',1,'BM','Apr 21 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(29,8,1,'B',1.325400,1.00,3,'XXX',1,'BM','Apr 23 2010 7:00:00:000AM')
GO
September 8, 2010 at 8:33 am
-- generate a row for every unit bought / sold.
-- I guess this could be adapted to use the least common denominator
-- Another possibility is to use an iTVF (inline table-valued function) and APPLY
IF OBJECT_ID( 'tempdb..#RowGenerator') IS NOT NULL DROP TABLE #RowGenerator
CREATE TABLE #RowGenerator ([RowCount] INT)
INSERT INTO #RowGenerator
SELECT [RowCount] = 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 4
;WITH Sells AS (
SELECT EntryTranNo = ROW_NUMBER() OVER (PARTITION BY o.tradeId ORDER BY o.OrderDateTime),
o.*
FROM #orders o
INNER JOIN #RowGenerator r ON r.[RowCount] = o.units
WHERE o.side = 'S'
),
Buys AS (
SELECT ExitTranNo = ROW_NUMBER() OVER (PARTITION BY o.tradeId ORDER BY o.OrderDateTime),
o.*
FROM #orders o
INNER JOIN #RowGenerator r ON r.[RowCount] = o.units
WHERE o.side = 'B'
)
SELECT s.tradeId, s.orderId, s.signalTypeId, profit = SUM(s.price - b.price), s.orderDateTime
FROM Sells s
LEFT JOIN Buys b
ON b.tradeId = s.tradeId
AND b.ExitTranNo = s.EntryTranNo
AND b.orderDateTime > s.orderDateTime
GROUP BY s.tradeId, s.orderId, s.signalTypeId, s.orderDateTime
ORDER BY s.tradeId, s.orderId
/*
tradeId orderId signalTypeId profit orderDateTime
------- -------- ------------ -------- -------------------
6 18 IE 0.021500 2010-03-19 15:00:00
6 19 SC 0.012200 2010-03-22 15:00:00
6 22 SC -0.027800 2010-03-26 00:00:00
7 24 IE 0.016800 2010-04-06 04:00:00
8 27 IE 0.025900 2010-04-20 15:00:00
*/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 8, 2010 at 9:23 am
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm using a tally table cross joined), and I'm assigning a row number. I also break it down into sales and purchases, and I'm then joining on that row number between the two to get the profit.
Note that this needs a tally table. If you don't have one, please see this article: Tally Table: What it is and how to use it[/url]
WITH Purchases AS
(-- get all of the purchases. Cross Join to a tally table to make one
-- row for each unit. Get a row number.
SELECT o.tradeId, o.orderId, o.signalTypeId, o.orderDateTime, o.price,
RN = ROW_NUMBER() OVER (PARTITION BY tradeID ORDER BY orderID)
FROM dbo.Orders o, dbo.TALLY t
WHERE o.signalTypeId IN ('IE','SC')
AND t.N <= o.units
), Sales AS
(-- get all of the sales. Cross Join to a tally table to make one
-- row for each unit. Get a row number.
SELECT o.tradeId, o.orderId, o.price,
RN = ROW_NUMBER() OVER (PARTITION BY tradeId ORDER BY orderId)
FROM dbo.Orders o, dbo.TALLY t
WHERE o.signalTypeId IN ('MX','XXX')
AND t.N <= o.units
)
-- JOIN the two tables together on tradeId & row number.
SELECT p.tradeId, p.orderId, p.signalTypeId,
profit = sum(p.Price - s.Price),
p.orderDateTime
FROM Purchases p
JOIN Sales s
ON p.tradeId = s.tradeId
AND p.RN = s.RN
GROUP BY p.tradeId, p.orderId, p.signalTypeId, p.orderDateTime
For the sample data provided, I'm returning:
618IE0.0215002010-03-19 15:00:00.000
619SC0.0122002010-03-22 15:00:00.000
622SC-0.0278002010-03-26 00:00:00.000
724IE0.0168002010-04-06 04:00:00.000
827IE0.0259002010-04-20 15:00:00.000
It's likely that an index on the dbo.orders.signalTypeId column would speed things up on a large table. On this small sample data, it's doing a table scan either way.
Edit: provide results.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2010 at 9:26 am
WayneS (9/8/2010)
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm using a tally table cross joined), and I'm assigning a row number. I also break it down into sales and purchases, and I'm then joining on that row number between the two to get the profit.BY p.tradeId, p.orderId, p.signalTypeId, p.orderDateTime[/code]
Much more practical, Wayne 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 8, 2010 at 9:36 am
Chris Morris-439714 (9/8/2010)
WayneS (9/8/2010)
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm using a tally table cross joined), and I'm assigning a row number. I also break it down into sales and purchases, and I'm then joining on that row number between the two to get the profit.BY p.tradeId, p.orderId, p.signalTypeId, p.orderDateTime[/code]Much more practical, Wayne 😉
Thanks Chris. But I have to admit that I spun my wheels for an hour before I saw how you multiplied it out by the units, and realized that a few minor tweaks would make it work.
Also, your solution is limited to just 4 units. Okay for the sample, but that final could be 20, 200, etc. The only viable solution to overcome that was the tally table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2010 at 9:40 am
keymoo - thanks for providing the excellent sample data, table definitions, and desired output. Without it, I think you'd still be waiting for a solution.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2010 at 9:40 am
WayneS (9/8/2010)
Chris Morris-439714 (9/8/2010)
WayneS (9/8/2010)
Here's my solution. I took a similar approach that Chris did to make a row for each unit, but how we did that is different (I'm using a tally table cross joined), and I'm assigning a row number. I also break it down into sales and purchases, and I'm then joining on that row number between the two to get the profit.BY p.tradeId, p.orderId, p.signalTypeId, p.orderDateTime[/code]Much more practical, Wayne 😉
Thanks Chris. But I have to admit that I spun my wheels for an hour before I saw how you multiplied it out by the units, and realized that a few minor tweaks would make it work.
Also, your solution is limited to just 4 units. Okay for the sample, but that final could be 20, 200, etc. The only viable solution to overcome that was the tally table.
Laziness on my part - I only really had time to do a proof of concept! There's another solution to this using two or three recursive cte's but it would be pig-slow.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2010 at 1:06 am
Thanks very much Wayne and Chris. A tally table, eh? What a nice elegant solution, thanks very much.
November 2, 2010 at 7:01 am
Hi Guys,
I've just added some more sample data and realised that the values of the profit column are wrong. here's some extra sample data to show the wrong values:
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(41,12,2,'B',1.254000,1.00,3,'IE',1,'BM','Jul 5 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(42,12,2,'S',1.252700,1.00,3,'XXX',1,'SM','Jul 5 2010 3:00:00:000PM')
When your query is run it shows a profit of 0.0026 but it should be -0.0026 since 1.2527 - 1.2540 = -0.0026. Here's the query:
WITH Purchases AS
(-- get all of the purchases. Cross Join to a tally table to make one
-- row for each unit. Get a row number.
SELECT o.tradeId, o.orderId, o.signalTypeId, o.orderDateTime, o.price,
RN = ROW_NUMBER() OVER (PARTITION BY tradeID ORDER BY orderID)
FROM dbo.Orders o, dbo.TALLY t
WHERE o.signalTypeId IN ('IE','SC')
AND t.N <= o.units
), Sales AS
(-- get all of the sales. Cross Join to a tally table to make one
-- row for each unit. Get a row number.
SELECT o.tradeId, o.orderId, o.price,
RN = ROW_NUMBER() OVER (PARTITION BY tradeId ORDER BY orderId)
FROM dbo.Orders o, dbo.TALLY t
WHERE o.signalTypeId IN ('MX','XXX')
AND t.N <= o.units
)
-- JOIN the two tables together on tradeId & row number.
SELECT p.tradeId, p.orderId, p.signalTypeId,
profit = sum(p.Price - s.Price),
p.orderDateTime
FROM Purchases p
JOIN Sales s
ON p.tradeId = s.tradeId
AND p.RN = s.RN
GROUP BY p.tradeId, p.orderId, p.signalTypeId, p.orderDateTime
Do you know how to fix the query?
Thanks,
keymoo
November 2, 2010 at 7:19 am
I think your profit expression is incorrect:
profit = sum(p.Price - s.Price)
it should be selling price minus purchase price.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2010 at 7:29 am
Chris Morris-439714 (11/2/2010)
I think your profit expression is incorrect:profit = sum(p.Price - s.Price)
it should be selling price minus purchase price.
Chris that is not the case because all the other values are correct. If I make your suggested change (I did think of this myself before posting the Q), then the profit for tradeId 12 is correct but all the others are incorrect. In effect it has been inverted. :crazy:
November 2, 2010 at 7:45 am
The calculation is working as expected. Using your sample data above, you have a purchase price of 1.254000 and a sale price of 1.252700. That's a loss as you point out, but your calculation is the wrong way round, you're using
profit = sum(p.Price - s.Price)
so it's showing a positive value in the result set.
Something else is incorrect.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 2, 2010 at 8:13 am
From your first post:
the profit is
entry price - next exit price for each entry
So, purchase price (p.Price) minus sale price (s.Price).
It definitely seems to me that the "profit" would be the sale price - purchase price; however the code is as you specified earlier.
So, what is your definition of a profit? If it is (sale - purchase), then change the line from (p.Price-s.Price) to (s.Price-p.Price).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 9:10 am
Profit should be the sale price - purchase price. Have a look at tradeId 4.
Buy 1 unit at 1.3641
Buy 1 unit at 1.3597
Sell 2 units at 1.3594
The profit should be (1.3594 - 1.3641) + (1.3594 - 1.3597) = -0.0050
However the query returns +0.0050
November 2, 2010 at 9:26 am
keymoo (11/2/2010)
Profit should be the sale price - purchase price. Have a look at tradeId 4.Buy 1 unit at 1.3641
Buy 1 unit at 1.3597
Sell 2 units at 1.3594
The profit should be (1.3594 - 1.3641) + (1.3594 - 1.3597) = -0.0050
However the query returns +0.0050
I don't have a sample data row with TradeID = 4, please can you supply? Here's what I have:
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(18,6,2,'S',1.354900,1.00,3,'IE',1,'SM','Mar 19 2010 3:00:00:000PM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(19,6,2,'S',1.347300,1.00,3,'SC',1,'SM','Mar 22 2010 3:00:00:000PM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(20,6,1,'B',1.355000,1.00,3,'MX',1,'BM','Mar 23 2010 3:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(21,6,1,'B',1.333300,1.00,3,'MX',1,'BM','Mar 25 2010 12:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(22,6,2,'S',1.327300,1.00,3,'SC',1,'SM','Mar 26 2010 12:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(23,6,4,'B',1.341200,1.00,3,'XXX',1,'BM','Mar 26 2010 8:00:00:000PM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(24,7,2,'S',1.344300,1.00,3,'IE',1,'SM','Apr 6 2010 4:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(25,7,1,'B',1.339100,1.00,3,'MX',1,'BM','Apr 7 2010 7:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(26,7,1,'B',1.332700,1.00,3,'XXX',1,'BM','Apr 7 2010 12:00:00:000PM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(27,8,2,'S',1.347100,1.00,3,'IE',1,'SM','Apr 20 2010 3:00:00:000PM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(28,8,1,'B',1.342900,1.00,3,'MX',1,'BM','Apr 21 2010 7:00:00:000AM')
INSERT INTO #orders ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(29,8,1,'B',1.325400,1.00,3,'XXX',1,'BM','Apr 23 2010 7:00:00:000AM')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply