1

I tried windows functions, CTE, RECURSIVE but I still can't get my head around it

If I have rows of stock orders buy/sell and I want to know what was position after each trade.

If each next order is increasing existing position - I want to have average price,

If the order is decreasing - I want to calculate ProfitLoss and leave price of position as it was

If the order is changing direction of the position - I want to calculate ProfitLoss and new price of position

order_profit_loss = LEAST(ABS(qua), lag(ABS(position_qua))) * price
position_price = (LAG(position_price) * LAG(position_qua) - order_profit_loss) / position_qua

DDL

CREATE TABLE trades
(
    id VARCHAR(255) PRIMARY KEY NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    price NUMERIC NOT NULL,
    qua INTEGER NOT NULL,
    dt TIMESTAMP WITH TIME ZONE
);

INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('1', 'FB', 124.89, 116, '2016-07-29 23:01:46.334000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('2', 'FB', 123.56, -104, '2016-07-30 23:41:13.297000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('3', 'FB', 125.23, 20, '2016-07-31 00:20:41.092000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('4', 'FB', 123.56, -21, '2016-08-01 12:25:34.482000');

How to get such outcome

id | symbol | price  | qua  |             dt             | order_profit_loss | position_qua | position_price
---+--------+--------+------+----------------------------+-------------------+--------------+----------------
1  | FB     | 124.89 |  116 | 2016-07-29 23:01:46.334+03 |                 0 |          116 |         124.89
2  | FB     | 123.56 | -104 | 2016-07-30 23:41:13.297+03 |           -138.32 |           12 |         124.89
3  | FB     | 125.23 |   20 | 2016-07-31 00:20:41.092+03 |                 0 |           32 |       125.1025
4  | FB     | 123.56 |  -21 | 2016-08-01 12:25:34.482+03 |          -32.3925 |           11 |       125.1025

Solution:

CREATE OR REPLACE VIEW v_pos AS
WITH RECURSIVE pos AS
  ( SELECT * FROM (
          SELECT DISTINCT ON (symbol)
               t.*,  0::numeric  AS pnl,
                     qua         AS pos,
                     price       AS p_price
           FROM public.trades AS t
           ORDER BY symbol, dt
         ) AS starting
    UNION ALL
    SELECT
        n.*,
        c.pnl,
        p.pos + n.qua,
        CASE
          WHEN p.pos > 0 AND n.qua > 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos < 0 AND n.qua < 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos / n.qua < 0 AND p.pos > n.qua THEN p.p_price
          WHEN p.pos / n.qua < 0 AND p.pos < n.qua THEN n.price
          ELSE 0
        END
    FROM
        pos AS p,
        LATERAL
        ( SELECT t.* FROM trades AS t
          WHERE t.symbol = p.symbol AND t.dt > p.dt ORDER BY t.dt  LIMIT 1
        ) AS n,
        LATERAL (
          SELECT
            CASE
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos > n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos < n.qua THEN p.pos * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos > -n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos < -n.qua THEN p.pos * (p.p_price - n.price)
              ELSE 0
            END pnl,
            p.pos * p.price cb
        ) AS c
  )
SELECT symbol, qua::BIGINT, price FROM (
  SELECT 
    DISTINCT ON (symbol)
    symbol,
    pos qua,
    p_price price
  FROM pos
    WHERE qua != 0
  ORDER BY symbol, dt DESC  
) as tmp
WHERE tmp.qua != 0
1
  • The position_qua is obviously a running total but you need to explain a bit how the other numbers (eg the -138.32 and the 125.1025) are calculated. Aug 3, 2016 at 20:05

1 Answer 1

2

Not sure if I have got all the logic right but you'll at least have a starting point:

WITH RECURSIVE pos AS
  ( SELECT *
    FROM ( SELECT DISTINCT ON (symbol)
               t.*,  0::numeric  AS order_profit_loss, 
                     qua         AS position_qua, 
                     price       AS position_price 
           FROM public.trades AS t
           ORDER BY symbol, dt 
         ) AS starting
    UNION ALL
    SELECT  
        n.id, n.symbol, n.price, n.qua, n.dt, 
        CASE WHEN n.qua < 0
            THEN n.qua * (p.position_price - n.price)
            ELSE 0
        END                     AS order_profit_loss,
        p.position_qua + n.qua  AS position_qua, 
        CASE WHEN n.qua < 0
            THEN p.position_price
            ELSE (p.position_qua * p.position_price + n.price * n.qua)
                 / (p.position_qua + n.qua)
        END                     AS position_price 
    FROM 
        pos AS p,
      LATERAL
        ( SELECT t.*
          FROM public.trades AS t
          WHERE t.symbol = p.symbol
            AND t.dt > p.dt
          ORDER BY t.dt    LIMIT 1
        ) AS n
  )
SELECT * 
FROM pos
ORDER BY symbol, dt ; 
1
  • Thank you VERY much, this is what I need. I will post complete version here. Aug 3, 2016 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.