November 2, 2010 at 9:43 am
Sorry, here you go 😀
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(12,4,2,'S',1.359400,1.00,3,'IE',1,'SM','Feb 15 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(14,4,1,'B',1.364100,1.00,3,'MX',1,'BM','Feb 16 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(13,4,1,'B',1.359700,1.00,3,'XXX',1,'BM','Feb 16 2010 7:00:00:000PM')
November 2, 2010 at 9:44 am
WayneS (11/2/2010)
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).
Okay, so change: profit = sum(p.Price - s.Price),
to profit = sum(s.Price - p.Price),
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 9:50 am
If the column 'side' indicates Buy or Sell, then the WHERE clauses for the two CTE's should be swapped around, from
;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 #Orders o, #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 #Orders o, #TALLY t
WHERE o.signalTypeId IN ('MX','XXX')
AND t.N <= o.units
)
to
;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 #Orders o, #TALLY t
WHERE o.signalTypeId IN ('MX','XXX')
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 #Orders o, #TALLY t
WHERE o.signalTypeId IN ('IE','SC')
AND t.N <= o.units
)
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 10:04 am
keymoo (11/2/2010)
Sorry, here you go 😀
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(12,4,2,'S',1.359400,1.00,3,'IE',1,'SM','Feb 15 2010 7:00:00:000AM')INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(14,4,1,'B',1.364100,1.00,3,'MX',1,'BM','Feb 16 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(13,4,1,'B',1.359700,1.00,3,'XXX',1,'BM','Feb 16 2010 7:00:00:000PM')
I think you might have bad data here...
orderID 12 / strategyID "IE" purchase of 2 unit2.
orderID 13 / strategyID "XXX" sale of 1 unit.
orderID 14 / strategyID "MX" sale of 1 unit.
Shouldn't the XXX always be the last orderID?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 10:10 am
keymoo (11/2/2010)
Sorry, here you go 😀
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(12,4,2,'S',1.359400,1.00,3,'IE',1,'SM','Feb 15 2010 7:00:00:000AM')INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(14,4,1,'B',1.364100,1.00,3,'MX',1,'BM','Feb 16 2010 7:00:00:000AM')
INSERT INTO [orders] ([orderId],[tradeId],[units],[side],[price],[spread],[strategyId],[signalTypeId],[brokerId],[orderTypeId],[orderDateTime])VALUES(13,4,1,'B',1.359700,1.00,3,'XXX',1,'BM','Feb 16 2010 7:00:00:000PM')
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
What you supplied has a purchase (IE) with price of 1.359400.
First sale (MX) of 1.364100.
Second sale (XXX) of 1.359700
so:
(1.359700 - 1.359400) + (1.364100 - 1.359400) = +0.005000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 3, 2010 at 1:37 am
WayneS (11/2/2010)
What you supplied has a purchase (IE) with price of 1.359400.
First sale (MX) of 1.364100.
Second sale (XXX) of 1.359700
so:
(1.359700 - 1.359400) + (1.364100 - 1.359400) = +0.005000
Perhaps I have not been entirely clear. The column [side] denotes whether the order is a buy or a sell with a B for a buy (purchase) or an S for a Sale. An IE can be a buy OR a sell, for instance when going short the initial entry could be a sell (side = 'S'). If going long the initial entry would be a buy (side = 'B'). I apologise for omitting this bit of information and for any confusion caused.
Don't worry I worked it out
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 Orders o, TALLY t
WHERE o.side = 'S'
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 Orders o, TALLY t
WHERE o.side = 'B'
AND t.N <= o.units
)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply