PostgreSQL – Daily Value of Stock Portfolio Query Using Window Functions

postgresqlquerywindow functions

I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day.

I have a pricesclose table like below:

date       | ticker | close
2020-01-02 | IVV    | 326.32
2020-01-03 | IVV    | 323.81
2020-01-06 | IVV    | 325.09
2020-01-07 | IVV    | 324.20
2020-01-02 | IEF    | 110.73
2020-01-03 | IEF    | 111.47
2020-01-06 | IEF    | 111.35
2020-01-07 | IEF    | 111.19

And a transactions table:

id | ticker | transaction_type | shares | price  | transaction_date
1  | IVV    | buy              | 18     | 324.98 | 2020-01-02
2  | IEF    | buy              | 36     | 110.69 | 2020-01-02
3  | IEF    | sell             | -6     | 111.35 | 2020-01-06

What I want is:

date       | ticker | shares | close  | totalshares | marketvalue 
2020-01-02 | IEF    | 36     | 110.73 |          36 | 3986.28
2020-01-02 | IVV    | 18     | 326.32 |          18 | 5873.76
2020-01-03 | IEF    |        | 111.47 |          36 | 4012.92
2020-01-03 | IVV    |        | 323.81 |          18 | 5828.58
2020-01-06 | IEF    | -6     | 111.35 |          30 | 3340.5
2020-01-06 | IVV    |        | 325.09 |          18 | 5851.62
2020-01-07 | IEF    |        | 111.19 |          30 | 3335.7
2020-01-07 | IVV    |        | 324.2  |          18 | 5835.6
2020-01-08 | IEF    |        | 110.93 |          30 | 3327.9
2020-01-08 | IVV    |        | 325.85 |          18 | 5865.3

But this is what I'm getting:
query result

Here is my query.

SELECT 
  pricesclose.Date
 ,pricesclose.ticker
 ,transactions.shares
 ,pricesclose.close
 ,(pricesclose.close * transactions.shares) AS mktvalue, 
 ,SUM(shares) OVER(partition by pricesclose.ticker order by pricesclose.date)
FROM 
  pricesclose
LEFT JOIN 
  transactions
    ON pricesclose.ticker = transactions.ticker
        AND pricesclose.Date = transactions.transDate
ORDER BY
  pricesclose.Date

For some reason, the SUM OVER isn't totaling each ticker properly. My other issues is getting the mktvalue column to reference the sum column. I am also unsure of the best way to have the total balance in one line instead of six like it's currently doing.

Please let me know what is wrong with my SUM OVER. Or if there is a better way to execute this, I am open to that as well.

Best Answer

Here you go - just needed to multiply the closing price by the running total of shares:

SELECT 
  pricesclose.Date
 ,pricesclose.ticker
 ,transactions.shares
 ,pricesclose.close
 ,SUM(transactions.shares) OVER (PARTITION BY pricesclose.ticker ORDER BY pricesclose.date) AS totalshares
 ,pricesclose.close * SUM(transactions.shares) OVER (PARTITION BY pricesclose.ticker ORDER BY pricesclose.date) AS marketvalue
FROM 
  pricesclose
LEFT JOIN 
  transactions
    ON pricesclose.ticker = transactions.ticker
        AND pricesclose.date = transactions.transaction_date
ORDER BY 
  pricesclose.date
 ,pricesclose.ticker

Output:

+----------------------+--------+--------+--------+-------------+-------------+
|         date         | ticker | shares | close  | totalshares | marketvalue |
+----------------------+--------+--------+--------+-------------+-------------+
| 2020-01-02T00:00:00Z | IEF    | 36     | 110.73 |          36 | 3986.28     |
| 2020-01-02T00:00:00Z | IVV    | 18     | 326.32 |          18 | 5873.76     |
| 2020-01-03T00:00:00Z | IEF    | (null) | 111.47 |          36 | 4012.92     |
| 2020-01-03T00:00:00Z | IVV    | (null) | 323.81 |          18 | 5828.58     |
| 2020-01-06T00:00:00Z | IEF    | -6     | 111.35 |          30 | 3340.5      |
| 2020-01-06T00:00:00Z | IVV    | (null) | 325.09 |          18 | 5851.62     |
| 2020-01-07T00:00:00Z | IEF    | (null) | 111.19 |          30 | 3335.7      |
| 2020-01-07T00:00:00Z | IVV    | (null) | 324.2  |          18 | 5835.6      |
| 2020-01-08T00:00:00Z | IEF    | (null) | 110.93 |          30 | 3327.9      |
| 2020-01-08T00:00:00Z | IVV    | (null) | 325.85 |          18 | 5865.3      |
+----------------------+--------+--------+--------+-------------+-------------+