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
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:
Output: