Portfolio Optimization - PackDB Examples

THE PROBLEM

An investment manager builds a portfolio from a universe of stocks. The budget comes from a configuration table. Each asset has an expected return, a risk score, a minimum investment amount, and belongs to a sector. The portfolio must include at least 5 assets, stay within a total risk budget of 300 points, and have no more than 2 assets per sector (diversification). The objective maximizes expected dollar return, computed as return percentage × investment amount. This combines scalar subqueries, CASE expressions for per-sector limits, and complex objective coefficients.

SAMPLE DATA

CREATE TABLE Config (name VARCHAR, value INTEGER);
INSERT INTO Config VALUES ('budget', 25000);

CREATE TABLE Assets (
    ticker VARCHAR, name VARCHAR, sector VARCHAR,
    expected_return DOUBLE, risk_score INTEGER, min_investment INTEGER
);
INSERT INTO Assets VALUES
    ('MSFT', 'Microsoft', 'Technology', 11.2, 55, 5000),
    ('AAPL', 'Apple', 'Technology', 10.5, 60, 6000),
    ('PFE', 'Pfizer', 'Healthcare', 8.5, 35, 2000),
    ('JNJ', 'Johnson & Johnson', 'Healthcare', 7.2, 25, 3000),
    ('JPM', 'JP Morgan', 'Finance', 9.2, 50, 4000),
    ('BAC', 'Bank of America', 'Finance', 10.1, 55, 3000),
    ('XOM', 'Exxon', 'Energy', 8.0, 45, 3000),
    ('PG', 'Procter & Gamble', 'Consumer', 6.5, 20, 2000),
    ('KO', 'Coca-Cola', 'Consumer', 5.8, 15, 2000);

THE QUERY

SELECT ticker, name, sector, expected_return, risk_score, min_investment, x as selected
FROM Assets
DECIDE x IS BOOLEAN
SUCH THAT
    SUM(x * min_investment) <= (SELECT value FROM Config WHERE name = 'budget') AND
    SUM(x * risk_score) <= 300 AND
    SUM(x) >= 5 AND
    SUM(x * CASE WHEN sector = 'Technology' THEN 1 ELSE 0 END) <= 2 AND
    SUM(x * CASE WHEN sector = 'Healthcare' THEN 1 ELSE 0 END) <= 2 AND
    SUM(x * CASE WHEN sector = 'Finance' THEN 1 ELSE 0 END) <= 2 AND
    SUM(x * CASE WHEN sector = 'Energy' THEN 1 ELSE 0 END) <= 2 AND
    SUM(x * CASE WHEN sector = 'Consumer' THEN 1 ELSE 0 END) <= 2
MAXIMIZE SUM(x * expected_return * min_investment / 100);

QUERY BREAKDOWN

1

SUM(x * min_investment) <= (SELECT value FROM Config WHERE name = 'budget') — Total investment within external budget ($25,000).

2

SUM(x * risk_score) <= 300 — Portfolio risk within 300-point budget.

3

SUM(x) >= 5 — At least 5 assets for diversification.

4

SUM(x * CASE WHEN sector = '...' THEN 1 ELSE 0 END) <= 2 — At most 2 assets per sector.

5

MAXIMIZE SUM(x * expected_return * min_investment / 100) — Maximize expected dollar return (return% × investment / 100).

RESULT

ticker	name	sector	return	risk	investment	selected
MSFT	Microsoft	Technology	11.2%	55	$5,000	1
AAPL	Apple	Technology	10.5%	60	$6,000	0
PFE	Pfizer	Healthcare	8.5%	35	$2,000	1
JNJ	J&J	Healthcare	7.2%	25	$3,000	0
JPM	JP Morgan	Finance	9.2%	50	$4,000	1
BAC	Bank of America	Finance	10.1%	55	$3,000	1
XOM	Exxon	Energy	8.0%	45	$3,000	0
PG	P&G	Consumer	6.5%	20	$2,000	1
KO	Coca-Cola	Consumer	5.8%	15	$2,000	1

INTERPRETATION

The portfolio includes 6 assets across 4 sectors: Microsoft ($5,000), Pfizer ($2,000), JP Morgan ($4,000), Bank of America ($3,000), Procter & Gamble ($2,000), and Coca-Cola ($2,000) — totaling $18,000 under the $25,000 budget. Total risk is 230 (≤300). The solver picks high-return assets (MSFT at 11.2%, BAC at 10.1%, JPM at 9.2%) balanced with low-risk consumer stocks (PG, KO) to stay within the risk budget. Apple was excluded despite its strong return because its high investment ($6,000) and risk score (60) made room for more diversified picks.