0

I am working on a query to calculate the daily value of a portfolio of stocks.

I have a transactions table:

id | portfolio_id  | stock | transaction_type | shares | price  | transaction_date
1  | PA1           | APPL  | buy_sell         | 18     | 324.98 | 2020-01-02
2  | PA1           | MSF   | buy_sell         | 36     | 110.69 | 2020-01-03
3  | PA1           | MSF   | buy_sell         | -6     | 111.35 | 2020-01-06
4  | PA1           | APPL  | buy_sell         | -18    | 324.96 | 2020-01-10

I have a stock_histories table:

id   | value  | value_date
APPL | 324.98 | 2020-01-01
APPL | 321.55 | 2020-01-02
APPL | 320.38 | 2020-01-03
APPL | 327.13 | 2020-01-04
APPL | 334.56 | 2020-01-05
APPL | 364.78 | 2020-01-06
APPL | 360.95 | 2020-01-07
APPL | 344.58 | 2020-01-08
APPL | 340.38 | 2020-01-09
APPL | 339.55 | 2020-01-10
APPL | 339.55 | 2020-01-11
APPL | 334.55 | 2020-01-12
MSF  | 110.69 | 2020-01-01
MSF  | 110.69 | 2020-01-02
MSF  | 111.35 | 2020-01-03
MSF  | 113.22 | 2020-01-04
MSF  | 111.15 | 2020-01-05
MSF  | 110.25 | 2020-01-06
MSF  | 112.23 | 2020-01-07
MSF  | 123.45 | 2020-01-08
MSF  | 118.66 | 2020-01-09
MSF  | 121.10 | 2020-01-10
MSF  | 121.11 | 2020-01-11
MSF  | 131.90 | 2020-01-12
...other daily values....

I want this result:

date       | ticker | shares | price  | totalshares | marketvalue 
2020-01-02 | APPL   | 18     | 324.98 |          18 | 5849.64
2020-01-03 | APPL   |        | 320.38 |          18 | 5766.84
2020-01-04 | APPL   |        | 327.13 |          18 | 5888.34
2020-01-05 | APPL   |        | 334.56 |          18 | 6022.08
2020-01-06 | APPL   |        | 364.78 |          18 | 6566.04
2020-01-07 | APPL   |        | 360.95 |          18 | 6497.1
2020-01-08 | APPL   |        | 344.58 |          18 | 6202.44
2020-01-09 | APPL   |        | 340.38 |          18 | 6126.84
2020-01-10 | APPL   |        | 339.55 |          0  | 0
2020-01-03 | MSF    |        | 110.69 |          36 | 3984.84
2020-01-04 | MSF    |        | 113.22 |          36 | 4075.92
2020-01-05 | MSF    |        | 111.15 |          36 | 4001.4
2020-01-06 | MSF    |        | 110.25 |          30 | 3307.5
2020-01-07 | MSF    |        | 112.23 |          30 | 3366.9
2020-01-08 | MSF    |        | 123.45 |          30 | 3703.5
2020-01-09 | MSF    |        | 118.66 |          30 | 3559.8
2020-01-10 | MSF    |        | 121.10 |          30 | 3633
2020-01-11 | MSF    |        | 121.11 |          30 | 3633.3
2020-01-12 | MSF    |        | 131.90 |          30 | 3957

Is really similar to the issue describe here: Daily Value of Stock Portfolio Postgres

But I am stuck with the window function (in a performance way) to only get the result start with the first transactions date until to start with the first stock_histories date of the stock, same when the stock goes to 0.

Thanks, guys for your help!

EDIT: I created a db-fiddle, https://www.db-fiddle.com/f/g8Utp5kWuVxPSusj2eZaMb/0

The version of Postgres, I using is 13

2
  • Hi, and welcome to dba.se! You could at least provide a fiddle for your table structures and data (dbfiddle.uk) - also, your version of PostgreSQL would be handy! Help us to help you! À+! Jun 9 at 15:09
  • sure i can do it
    – Olivier
    Jun 9 at 16:10
0

I hope this helps

Data preparation.

CREATE TABLE transactions (
  "id" INT not null, 
  "portfolio_id" varchar(255) not null,
  "stock" varchar(255) not null,
  "transaction_type" varchar(55) not null,
  "shares" decimal(20, 9) not null,
  "price" decimal(20, 9) not null,
  "transaction_date" date not null);


CREATE TABLE stock_histories (
  "id" varchar(255) not null, 
  "value" decimal(20, 9) not null,
  "value_date" date not null);


INSERT INTO transactions (id, portfolio_id, stock, transaction_type, shares, price, transaction_date) VALUES 
(1, 'PA', 'APPL', 'buy_sell', 18, 324.98, '2020-01-02'),
(2, 'PA', 'MSF', 'buy_sell', 36, 110.69, '2020-01-03'),
(3, 'PA', 'MSF', 'buy_sell', -6, 111.35, '2020-01-06'),
(4, 'PA', 'APPL', 'buy_sell', -18, 324.96, '2020-01-10');


INSERT INTO stock_histories (id, value, value_date) VALUES 
('APPL', 324.98, '2020-01-01'),
('APPL', 321.55, '2020-01-02'),
('APPL', 320.38 , '2020-01-03'),
('APPL', 327.13, '2020-01-04'),
('APPL', 334.56, '2020-01-05'),
('APPL', 364.78, '2020-01-06'),
('APPL', 360.95, '2020-01-07'),
('APPL', 344.58, '2020-01-08'),
('APPL', 340.38, '2020-01-09'),
('APPL', 339.55, '2020-01-10'),
('APPL', 339.55, '2020-01-11'),
('APPL', 334.55, '2020-01-12'),
('MSF', 110.69, '2020-01-01'),
('MSF', 110.69, '2020-01-02'),
('MSF', 111.35, '2020-01-03'),
('MSF', 113.22, '2020-01-04'),
('MSF', 111.15, '2020-01-05'),
('MSF', 110.25, '2020-01-06'),
('MSF', 112.23, '2020-01-07'),
('MSF', 123.45, '2020-01-08'),
('MSF', 118.66, '2020-01-09'),
('MSF', 121.10, '2020-01-10'),
('MSF', 121.11, '2020-01-11'),
('MSF', 131.90, '2020-01-12');

Below sql should do the trick.

select *
from
(
  with transactions as
(select 
      t.*
,   coalesce(LAG(shares,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),0) previous_share
    ,case when coalesce(LAG(stock,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),'') =stock then coalesce(LAG(shares,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),0)+shares else shares end as total_current_share
      ,
    coalesce(lead(transaction_date,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),now()) end_date  
      from transactions t
      where 1=1 
             order by t.stock,t.transaction_date
     )
select 
sh.value_date,t.stock,t.total_current_share,sh.value,sh.value*t.total_current_share market_value
,to_Char(t.transaction_Date,'dd.mm.yyyy') transaction_Date
,to_Char(t.end_Date,'dd.mm.yyyy') end_Date
,to_Char(sh.value_date,'dd.mm.yyyy') value_date 
, coalesce(lag(total_current_share,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),1) before_share 
from transactions t
inner join stock_histories sh on sh.id=t.stock 
and t.transaction_Date<=sh.value_Date
      and t.end_date>sh.value_Date
order by t.stock,t.transaction_Date,sh.value_Date 
) final
  where 1=1
 and before_share!=0
3
  • Thanks for your help! But the transaction CTE look have an issue. The previous share takes the value of the column shares coming from the previous row. I tried to deal by changing the LAG value of using rows between unbounded preceding and current row without success... Do you think it's possible to achieve that with out a UNION ALL ?
    – Olivier
    Jun 14 at 14:37
  • This query look make the job: ``` SUM(coalesce(gross_share, 0)) OVER ( PARTITION BY stock_id ORDER BY transaction_date ) AS total_current_share, ``` Any feedback, are welcome
    – Olivier
    Jun 14 at 14:48
  • If I understand you correctly your are having problems when changing stocks. I edited the query. Also check this fiddle dbfiddle.uk/… Jun 15 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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