IEOR 215: Final Presentation Dynamically Accessible Portfolio Management Database Florent Robineau Ching-Yu Hu
Design Process n n n EER modification Relation Schema Four sample queries Implementation in PHP/My. SQL Demonstration
EER Revisited
Relation Schema in 3 NF 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. User (id, first. Name, middle. Name, last. Name, credit) Portfolio (id, creation_date, user_id 1) 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) Equity. Index (name) Asset (asset_id, type) Address (id 1, address) Management (id 6, name, function) Equity. History (equity_id 6, date, price, volume, revenue, debt, profit) Asset. History (asset_id, date, interestrate) Watchlist. Equity (equity_id 5, ticker. Id 6) Equity. Index (id 6, name 7) Portfolio. Equity (port. Id 2, ticker_id 6, type, quantity, date, price) Portfolio. Asset (port. Id 2, asset. Id 8, type, quantity, date, price)
Relation Graph
Sample Queries n 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; n 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";
Queries continued (2) n 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 by total desc, avgprice asc;
Queries continued (3) n 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_id GROUP BY spent_by_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;
Queries continued (4) n 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;
Implementation in PHP/My. SQL n n n Software used Query demonstration Query analyzer (provided by Postgresql through the “explain” command, easily readable with the GUI)
Query Analyzer
Results of the query analyzer n n 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 n Supposes that « select » commands occur more frequently than « insert » or « update » commands
Questions!
Скачать презентацию IEOR 215 Final Presentation Dynamically Accessible Portfolio Management
e9b9343da7ae5a554fe452ddf57d99f5.ppt