Resolved: Find portfolio balance efficiently (remove nested query)

Question:

I could not find an issue like this one on SO. I have seen the following:

None of them help me with the issue I have.
Lets say that I have a system that keeps track of my portfolio of shares. I have two main tables. one with the buy and sell orders that I have made, and the other with share prices for many shares over multiple years.
I am looking for an efficient script that gives me the overall portfolio dollar value for each order I have made over time. The current implementation I am using is far too slow: it executes a sub-query for every order I have. I can sometimes have tens of thousands of orders in the table.

The SQL

I am running this on an Oracle database:

-- Will not have prices on all days (eg weekends and public holidays)<br />
-- One price per day at most for a given ticker<br />
-- This table has hundreds of tickers and hundreds of entries for each ticker<br />
with prices as (<br />
select 'AAPL' ticker, date('2022-01-01') dt, 1.0 price union<br />
select 'AAPL' ticker, date('2022-01-02') dt, 1.1 price union<br />
select 'AAPL' ticker, date('2022-01-03') dt, 1.2 price union<br />
select 'AAPL' ticker, date('2022-01-04') dt, 1.3 price union<br />
select 'AAPL' ticker, date('2022-01-05') dt, 1.1 price union<br />
select 'AAPL' ticker, date('2022-01-06') dt, 1.0 price union<br />
select 'AAPL' ticker, date('2022-01-07') dt, 1.1 price union<br />
select 'GOOG' ticker, date('2022-01-01') dt, 10.3 price union<br />
select 'GOOG' ticker, date('2022-01-02') dt, 10.5 price union<br />
select 'GOOG' ticker, date('2022-01-03') dt, 9.2 price union<br />
select 'GOOG' ticker, date('2022-01-04') dt, 10.1 price union<br />
select 'GOOG' ticker, date('2022-01-05') dt, 11.1 price union<br />
select 'GOOG' ticker, date('2022-01-06') dt, 10.0 price union<br />
select 'GOOG' ticker, date('2022-01-07') dt, 10.1 price union<br />
select 'MSFT' ticker, date('2022-01-01') dt, 5.0 price union<br />
select 'MSFT' ticker, date('2022-01-02') dt, 5.1 price union<br />
select 'MSFT' ticker, date('2022-01-03') dt, 4.2 price union<br />
select 'MSFT' ticker, date('2022-01-04') dt, 6.3 price union<br />
select 'MSFT' ticker, date('2022-01-05') dt, 5.1 price union<br />
select 'MSFT' ticker, date('2022-01-06') dt, 4.9 price union<br />
select 'MSFT' ticker, date('2022-01-07') dt, 5.3 price<br />
)</p>
<p>-- Can have more than 1 order on the same day and same ticker<br />
-- Can have multiple orders for multiple tickers on the same day<br />
-- This table can have tens of thousands of orders but usually less than 20 tickers<br />
, orders as (<br />
select 'AAPL' ticker, date('2022-01-02') dt, 'Buy' type, 1000 shares union<br />
select 'GOOG' ticker, date('2022-01-02') dt, 'Buy' type, 100 shares union<br />
select 'AAPL' ticker, date('2022-01-04') dt, 'Sell' type, -100 shares union<br />
select 'AAPL' ticker, date('2022-01-04') dt, 'Sell' type, -50 shares union<br />
select 'AAPL' ticker, date('2022-01-05') dt, 'Sell' type, -100 shares union<br />
select 'GOOG' ticker, date('2022-01-05') dt, 'Buy' type, 1 shares<br />
)</p>
<p>, summary as (<br />
select<br />
o.ticker,<br />
o.dt,<br />
p.price share_price,<br />
sum(o.shares) order_shares,<br />
sum(o.shares * p.price) order_dollars,<br />
sum(sum(o.shares)) over(partition by o.ticker order by o.dt) balance_shares,<br />
sum(sum(o.shares)) over(partition by o.ticker order by o.dt) * p.price balance_dollars,<br />
(<br />
select sum(o1.shares * p1.price)<br />
from orders o1<br />
inner join prices p1<br />
on p1.ticker = o1.ticker<br />
and p1.dt = o.dt<br />
where o1.dt <= o.dt
) portfolio_balance_dollars
from orders o
inner join prices p on p.ticker = o.ticker and p.dt = o.dt
group by o.ticker, o.dt, p.price
order by o.dt, o.ticker, p.price
)

select s1.*
from summary s1
[/code]

</p>



Giving an output of<br> 


<p>[code]ticker  dt          share_price  order_shares  order_dollars  balance_shares  balance_dollars  portfolio_balance_dollars<br />
------  ----------  -----------  ------------  -------------  --------------  ---------------  -------------------------<br />
AAPL    2022-01-02  1.1          1000          1100.0         1000            1100.0           2150.0<br />
GOOG    2022-01-02  10.5         100           1050.0         100             1050.0           2150.0<br />
AAPL    2022-01-04  1.3          -150          -195.0         850             1105.0           2115.0<br />
AAPL    2022-01-05  1.1          -100          -110.0         750             825.0            1946.1<br />
GOOG    2022-01-05  11.1         1             11.1           101             1121.1           1946.1

I am looking for a faster way to get portfolio_balance_dollars. One with less runtime complexity.

Alternative

An efficient answer for the above would be first prize. But instead of looking up the share prices of all the relevant shares on each order, it also suffices to use the latest share price for the relevant share found within summary itself. This is fine because there are orders taking place quite often. So the following output will also be good enough:

ticker  dt          share_price  order_shares  order_dollars  balance_shares  balance_dollars  portfolio_balance_dollars
------  ----------  -----------  ------------  -------------  --------------  ---------------  -------------------------
AAPL    2022-01-02  1.1          1000          1100.0         1000            1100.0           2150.0                   
GOOG    2022-01-02  10.5         100           1050.0         100             1050.0           2150.0                   
AAPL    2022-01-04  1.3          -150          -195.0         850             1105.0           2155.0                   
AAPL    2022-01-05  1.1          -100          -110.0         750             825.0            1946.1                   
GOOG    2022-01-05  11.1         1             11.1           101             1121.1           1946.1

That is instead of multiplying GOOG share balance with the GOOG share price at 2022-01-04, we instead multiply the share amount by the share price at 2022-01-02 to get a balance of 2155 for 2022-01-02. So it takes the latest available share price shown in summary itself.
db<>fiddle

Answer:

You can use:

SELECT *
FROM (
SELECT t.*,
SUM(balance_dollars) OVER (PARTITION BY dt)
AS portfolio_balance_dollars
FROM (
SELECT t.ticker,
o.dt,
p.price AS share_price,
o.shares AS order_shares,
o.shares * p.price AS order_dollars,
SUM(o.shares) OVER (PARTITION BY t.ticker ORDER BY o.dt)
AS balance_shares,
SUM(o.shares) OVER (PARTITION BY t.ticker ORDER BY o.dt)
* p.price AS balance_dollars
FROM ( SELECT DISTINCT ticker FROM orders ) t
LEFT OUTER JOIN (
SELECT ticker,
dt,
SUM(shares) AS shares
FROM orders
GROUP BY ticker, dt
) o
PARTITION BY (o.dt)
ON (t.ticker = o.ticker)
CROSS JOIN LATERAL (
SELECT p.*
FROM prices p
WHERE p.ticker = t.ticker
AND p.dt <= o.dt ORDER BY p.dt DESC FETCH FIRST ROW ONLY ) p ORDER BY o.dt, t.ticker, share_price ) t ) WHERE order_shares IS NOT NULL; [/code]

Which, for the sample data, outputs:

TICKER DT SHARE_PRICE ORDER_SHARES ORDER_DOLLARS BALANCE_SHARES BALANCE_DOLLARS PORTFOLIO_BALANCE_DOLLARS AAPL 02-JAN-22 1.1 1000 1100 1000 1100 2150 GOOG 02-JAN-22 10.5 100 1050 100 1050 2150 AAPL 04-JAN-22 1.3 -150 -195 850 1105 2115 AAPL 05-JAN-22 1.1 -100 -110 750 825 1946.1 GOOG 05-JAN-22 11.1 1 11.1 101 1121.1 1946.1

db<>fiddle here

If you have better answer, please add a comment about this, thank you!

Resolved: Find portfolio balance efficiently (remove nested query)

Resolved: significance letter above bar graphic in wrong order

Resolved: VBA – Applying border around the areas with value/text

Resolved: How can I implement a function depending on picked up items?

Leave A Reply