IEOR 215: Final Presentation Dynamically Accessible Portfolio Management Database

1. IEOR 215: Final PresentationDynamically Accessible Portfolio Management Database Florent Robineau Ching-Yu Hu

2. Design Process • EER modification • Relation Schema • Four sample queries • Implementation in PHP/MySQL • Demonstration

3. EER Revisited

4. Relation Schema in 3NF • User (id, firstName, middleName, lastName, credit) • Portfolio(id, creation_date, user_id1) • Orders (portfolio_id, equity_id, date) • Transactions (order_date, portfolio_id, equity_id, date) • Watchlist (watch_id, user_id) • Equity (id, sector, name, instant_price, market_capital, avail_qty) • EquityIndex (name) • Asset (asset_id, type) • Address (id1, address) • Management (id6, name, function) • EquityHistory (equity_id6, date, price, volume, revenue, debt, profit) • AssetHistory (asset_id, date, interestrate) • WatchlistEquity (equity_id5, tickerId6) • EquityEquityIndex (id6, name7) • PortfolioEquity (portId2, ticker_id6, type, quantity, date, price) • PortfolioAsset (portId2, assetId8, type, quantity, date, price)

5. Relation Graph

6. Sample Queries • Historical price paths of a certain equity before a given date • select * from histories h where equity_id = ‘GOOG' and hdate <= '2001-10-23‘ order by hdate desc; • Comparison of volatility across equities • select e.id, e.name, stddev(h.price) from equities e inner join histories h on e.id = h.equity_id group by e.id, e.name having stddev(h.price) between 0 and "$input";

7. Queries continued (2) • Summary statistics of order activity select u.id, u.name, sum(o.price*o.qty) as total, avg(o.price) as avgprice from orders o inner join portfolios p on p.id = o.portfolio_id inner join users u on u.id = p.user_id group by u.id, u.name order bytotal desc, avgprice asc;

8. Queries continued (3) • Portfolio weights across equities (in 1 plain query) SELECT u.name AS user_name, spent_by_equity.portfolio_id, e.name AS equity_name, (100 * spent_by_equity.sum_spent_equity / (( SELECT sum(s.sum_spent_equity) AS sum FROM spent_by_equity s WHERE s.portfolio_id = spent_by_equity.portfolio_id)))::numeric(5,2) AS percentage FROM ( SELECT portfolio_id, equity_id, sum( CASE WHEN otype::text = 'BUY' THEN - sum_spent_order ELSE sum_spent_order END) AS sum_spent_equity FROM ( SELECT t.portfolio_id, t.equity_id, t.order_date AS odate, sum(t.price * t.qty) AS sum_spent_order FROM transactions t GROUP BY t.portfolio_id, t.equity_id, t.order_date) spent NATURAL JOIN orders o GROUP BY portfolio_id, equity_id ORDER BY portfolio_id) spent_by_equity JOIN portfolios p ON p.id = spent_by_equity.portfolio_id JOIN users u ON u.id = p.user_id JOIN equities e ON e.id = spent_by_equity.equity_id GROUP BY spent_by_equity.equity_id, spent_by_equity.portfolio_id, spent_by_equity.sum_spent_equity, u.name, e.name order by u.name, spent_by_equity.portfolio_id, e.name;

9. Queries continued (4) • Portfolio weights across equities (using views) create view spent as select t.portfolio_id, t.equity_id, t.order_date as odate, sum(t.price*t.qty) as sum_spent from transactions t group by t.portfolio_id, t.equity_id, t.order_date; create view spent_by_equity as select portfolio_id, equity_id, sum(case when otype = 'BUY' THEN - sum_spent ELSE sum_spent END) as sum_spent_equity from spent natural inner join orders o group by portfolio_id, equity_id order by portfolio_id; select u.name, s.portfolio_id, e.name, (100 * s.sum_spent_equity / (select sum(sum_spent_equity) from spent_by_equity where portfolio_id = s.portfolio_id))::numeric(5,2) from spent_by_equity s inner join portfolios p on p.id = s.portfolio_id inner join users u on u.id = p.user_id inner join equities e on e.id = s.equity_id group by s.equity_id, s.portfolio_id, s.sum_spent_equity, u.name, e.name order by u.name, s.portfolio_id, e.name;

10. Implementation in PHP/MySQL • Software used • Query demonstration • Query analyzer (provided by Postgresql through the “explain” command, easily readable with the GUI)

11. Query Analyzer

12. Results of the query analyzer • The last query requires a lot of elementary operations • Efficiency issues: • Use of views • Denormalization of the database + use of triggers to maintain some calculated fields • Supposes that « select » commands occur more frequently than « insert » or « update » commands

13. Questions!