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

New contributor
Olivier is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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! À+! – Vérace 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
    ,coalesce(LAG(shares,1) OVER (
        PARTITION BY stock
        ORDER BY transaction_date
    ),0)+shares 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

Your Answer

Olivier is a new contributor. Be nice, and check out our Code of Conduct.

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.