使用sql生成所有已实现和未实现的交易? [英] Generating all realized and unrealized trades using sql?

问题描述

鉴于某些金融证券中的某些头寸，我想在给定日期之前对它们进行交易.在此过程中，我想跟踪哪些交易平仓并开立新仓.进行交易后，当头寸数量变为0时，头寸为CLOSED.当头寸数量从0变为某值时，该头寸被视为OPENED.

Given some positions in different financial securities, I would like to apply trades to them up to a given date. In doing so I would like to keep track which trades closed the position and opened a new one. The position is CLOSED when the Position Quantity becomes 0 after a trade is applied. A position is considered OPENED when the Position Quantity changes from 0 to something.

假设我有以下表格:

CREATE TABLE tPosition 
(
    SecNum INT,
    Position INT
)

CREATE TABLE tTrade
(
    TradeID INT IDENTITY(1,1),
    TradeDate DATETIME,
    SecNum INT,
    Quantity INT
)

以及一些示例数据:

INSERT INTO tPosition (SecNum, Position) 
SELECT 1, 100
UNION
SELECT 2, 200
UNION
SELECT 3, -300

INSERT INTO tTrade (TradeID, TradeDate, SecNum, Quantity)
SELECT 1, '1/1/2016', 1, -50
UNION
SELECT 2, '1/2/2016', 1, -50
UNION
SELECT 3, '1/3/2016', 1, -50
UNION
SELECT 4, '1/4/2016', 1, 50
UNION
SELECT 6, '1/5/2016', 3, 200
UNION
SELECT 7, '1/5/2016', 3, 200;

---

示例场景/案例: SQL DEMO SOURCE

没有任何交易，我的结果将是(即带有2个额外字段的仓位表，稍后将有用):

---

SAMPLE SCENARIOS/CASES: SQL DEMO SOURCE

Without any trades my result would be (i.e. exactly the position table with 2 extra fields which will be useful later):

SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,     100,       NULL,           NULL
    2,     200,       NULL,           NULL
    3,     -300,      NULL,           NULL

---

因此，假设我将交易应用于1/1/2016(含)以下的头寸. TradeID:1的交易将影响securityID:1的头寸，因为100 +(-50)= 50，所以我的结果应该是:

---

So let's say I apply trades to the positions up to and including 1/1/2016. This trade with TradeID:1, will affect the position for securityID: 1 as 100+(-50)=50 so my result should be:

SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,     50,       NULL,         NULL
    2,     200,      NULL,         NULL
    3,     -300,     NULL,         NULL

OpenedByTradeID和ClosedTradeID仍为NULL，因为位置尚未超过0.

The OpenedByTradeID and ClosedTradeID are still NULL because the position hasn't crossed 0 yet.

如果我进行的交易不超过1/2/2016，我应该得到:

If I apply trades up to and including 1/2/2016 I should get:

 SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,      0,       NULL,             2
    2,     200,      NULL,             NULL
    3,     -300,     NULL,             NULL

请注意，该头寸已变为0，因此我们将ClosedByTradeID与关闭该头寸的tradeID一起记录.

Notice that the position has become 0 so we record the ClosedByTradeID with the tradeID that closed this position.

直到并包括1/3/2016我应该得到:

Up to and including 1/3/2016 I should get:

 SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,      0,       NULL,             2
    1,     -50,       3,               NULL
    2,     200,      NULL,             NULL
    3,     -300,     NULL,             NULL

请注意，最新的tradeID:3在securityID:1中打开了一个新头寸，因此我们在OpenedByTradeID列中标记了TradeID:3

Notice that a new position was opened in securityID:1 by the latest tradeID:3 so we mark the OpenedByTradeID column with TradeID:3

直到并包括1/4/2016我应该得到:

Up to and including 1/4/2016 I should get:

 SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,      0,       NULL,              2
    1,      0,       3,                 4
    2,     200,      NULL,              NULL
    3,     -300,     NULL,              NULL

请注意，该位置已变为0，因此我们记录了ClosedByTradeID和tradeID关闭该位置的TradeID:4

Notice that the position has become 0 so we record the ClosedByTradeID with the tradeID that closed this position -- TradeID:4

直到1/5/2016并包括在内的是边缘情况.这里发生了两件事:交易的应用越过了0点，因此需要建立新的头寸，并且同一天发生2笔交易.

Up to and including 1/5/2016 is an edge case. There are 2 things happening here: the application of the trade crosses the 0 mark so a new position needs to be formed AND 2 trades happen on the same day.

正在申请:

• tradeID:6对于securityID:3会将位置调整为-100(-300 + 200 = -100).
• 这时我们需要应用tradeID:7，但是那会越过0(-100 + 200 = 100从neg-> pos交叉)，因此我们需要在越过0之前应用该零件以关闭头寸，然后开始剩余金额的新头寸.
• tradeID:7 [100]的一部分将位置调整为0(-100 + 100 = 0)并将其关闭(将在ClosedByTradeID列中注明)，然后tradeID:7的一部分[剩下的100]会将位置调整为100(0 + 100 = 100)[也会在OpenedByTradeID列中注明].

• tradeID:6 for securityID:3 would adjust the position to -100 (-300 + 200 = -100).
• Then we need to apply tradeID:7 but that would cross 0 (-100 + 200 = 100 crossing from neg -> pos) so we need to apply the part before crossing 0 to close the position and then start a new position with the remaining amount.
• Part of tradeID:7 [100] would adjust the position to 0 (-100 + 100 = 0) and close it (would be noted in ClosedByTradeID column) and then part of tradeID:7 [the remaining 100] would adjust the position to 100 (0 + 100 = 100) [would also get noted in OpenedByTradeID column].

因此，我应该得到:

 SecNum, Position, OpenedByTradeID, ClosedByTradeID
    1,      0,       NULL,              2
    1,      0,       3,                 4
    2,     200,      NULL,              NULL
    3,      0,       NULL,              7
    3,     100,       7,                NULL

---

这听起来像是一个孤岛问题，但我似乎无法弄清楚该怎么写.

---

This sounds like an islands problem but I just can't seem to figure out how to write this one.

推荐答案

我认为我可以使用WHILE循环方法来使其工作.也许有一种更有效的方法，但是我认为这可行...

I think I got it to work using the WHILE loop approach. Maybe there is a more efficient way but I think this works...

DECLARE @result TABLE
(
    SecNum INT,
    Position INT,
    OpenedByTradeID INT,
    ClosedByTradeID INT
)

INSERT INTO @result(SecNum, Position)
SELECT SecNum, Position
FROM dbo.tPosition

SELECT *
FROM @result
ORDER BY SecNum

DECLARE @CurTradeID INT
SELECT @CurTradeID = MIN(TradeID) FROM dbo.tTrade
WHILE(@CurTradeID IS NOT NULL) 
BEGIN
     DECLARE @TradeQty INT, @TradeSecNum INT
     SELECT @TradeQty = Quantity, @TradeSecNum = SecNum FROM dbo.tTrade WHERE TradeID = @CurTradeID

     DECLARE @OldPos INT = (SELECT Position FROM @result WHERE ClosedByTradeID IS NULL AND SecNum = @TradeSecNum)

     -- IF THERE IS NO POSITION
     IF (@OldPos IS NULL)
        BEGIN
            INSERT INTO @result(SecNum, Position, OpenedByTradeID, ClosedByTradeID)
            SELECT @TradeSecNum, @TradeQty, @CurTradeID, NULL
        END

    -- IF THIS TRADE CLOSES THE POSITION
     ELSE IF (@OldPos + @TradeQty = 0) 
         BEGIN
            UPDATE @result 
            SET ClosedByTradeID = @CurTradeID, Position = Position + @TradeQty
            WHERE SecNum = @TradeSecNum AND ClosedByTradeID IS NULL
         END

     -- IF THIS TRADE MAKES THE POSITION CROSS THROUGH 0 i.e. IF TRADE MAKES POSITION CHANGE SIGN
     ELSE IF (SIGN(@OldPos + @TradeQty) <> SIGN(@OldPos))
        BEGIN
            DECLARE @RemainingAmt INT = @TradeQty + @OldPos

            UPDATE @result
            SET ClosedByTradeID = @CurTradeID, Position = 0
            WHERE SecNum = @TradeSecNum AND ClosedByTradeID IS NULL

            INSERT INTO @result(SecNum, Position, OpenedByTradeID, ClosedByTradeID)
            SELECT @TradeSecNum, @RemainingAmt, @CurTradeID, NULL
        END

    -- JUST UPDATE THE ACTIVE POSITION
     ELSE        
        BEGIN
            UPDATE @result 
            SET Position = Position + @TradeQty
            WHERE SecNum = @TradeSecNum AND ClosedByTradeID IS NULL
        END

    SELECT @CurTradeID = MIN(TradeID) FROM dbo.tTrade WHERE TradeID > @CurTradeID
END

SELECT *
FROM @result
ORDER BY SecNum

PS:我认为我可以用自己需要的交易创建一个临时表，并且可以在上面的查询中使用该表代替tTrade，因此我不必继续选择交易到特定日期.

P.S.: I figured that I can make a temp table with the trades that I need before-hand and I can use that table instead of tTrade in my query above so I don't have to keep dealing with selecting the trades up to a particular date.

这篇关于使用sql生成所有已实现和未实现的交易?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！