16 / Data analytics / Portfolio review
Client Portfolio Analytics
An individual two-part assignment: extract one client's holdings from a wealth-management database with SQL, measure returns, volatility and Sharpe ratios by security, then present the result as a Tableau dashboard and a management report that flags concentration risk and recommends a rebalance.
01 / The question
What does one client's portfolio really look like, and what should change?
The assignment works in two stages on a course wealth-management database. Part 1 uses SQL to rebuild a single client's portfolio from raw account, holdings, security and price tables, and to measure how each holding has performed and how risky it is. Part 2 loads that view into Tableau and turns the numbers into a management dashboard and written recommendations. The client is a Dubai-based investor whose portfolio is 75.01% equity, 8.92% commodities, 8.37% fixed income and 7.7% alternatives.
02 / Part 1: extracting with SQL
From five tables to one portfolio view.
Every later calculation runs from a single SQL view, so the logic is written once and reused.
| Step | What it does |
|---|---|
| 1 · Client profile and assets | Joins the account, customer, current-holdings, security-master and daily-price tables to find the client's accounts and every holding, priced and classified down to major and minor asset class. Position value is quantity times price. |
| 2 · Portfolio view | Saves the join as a view with security name, ticker, adjusted price, quantity, position value, asset classes and security type, filtered to one client and to adjusted prices from 1 August 2019. |
| 3.1 · Returns | Uses a window function to look back 250, 500 and 750 trading days, and takes the log of the price ratio to get 12-, 24- and 36-month continuous returns for each security. |
| 3.2 · Sigma | Takes the standard deviation of those returns, and the average of daily returns, to compare risk across securities. |
| 3.3 · Diversifier | Picks one existing ticker to add: the AltShares Merger Arbitrage ETF (ARB), for low volatility and steady positive returns in each of the last three years. |
| 3.4 · Sharpe ratio | Divides average return by standard deviation for each security, and ranks them. |
| Horizon | Best | Worst |
|---|---|---|
| 12 months | UNG +43.24% | DIS −47.38% |
| 24 months | UNG +79.70% | BIDU −16.49% |
| 36 months | CDNS +94.65% | BIDU −65.61% |
The pattern moves with the horizon. Over a year, commodity funds lead (UNG, DBC at +26.34%, DJP at +22.08%) and technology names lag, with GOOG down 25.18%, ROK down 19.49% and OTIS down 18.98%. Over three years the leaders are equities, CDNS, TSCO at +80.53% and GOOGL at +58.52%, and only four holdings fell: GIGB, BNDX, DIS and BIDU. On risk, UNG has the highest standard deviation, followed by GOOGL and DBC.
03 / Part 2: the dashboard
Four views, one story.
The Tableau dashboard is built on the same view and is designed for a management reader. Each chart answers one question, and hovering a security shows its asset class, price history and return.
| View | Question it answers | What it shows |
|---|---|---|
| Position value by security | Where is the capital? | Four equity positions (ROK, TSCO, CDNS, BIDU) dwarf the rest, and fixed income and commodities sit at the small end. |
| Portfolio composition | How is it spread across types? | Large-cap shares of $494.29M and small-cap of $137.26M, against ETF segments such as precious metals at $47.72M and oil at $10.28M. |
| Average price over time | How have prices behaved? | Most assets trend upward, while ETFs and fixed income barely move. The higher-priced, more volatile stocks drive the risk. |
| Continuous return by security | Is the risk paid for? | Return and Sharpe ratio side by side, sorted by Sharpe. |
The last view is the one that turns description into a decision. BIDU, CDNS, CEG and TSCO show the highest returns of the equities, but Sharpe ratios of only −0.0024, 0.0192, 0.0677 and 0.0164. The alternatives, CTA and LBAY, have modest returns of 4.48% and 12.52% and much higher Sharpe ratios, 0.1539 and 0.1022.
04 / Insights and recommendations
Fund the efficient holdings, trim the concentrated ones.
| Insight | Evidence | Recommendation |
|---|---|---|
| Equity over-concentration | Four names hold about 61% of the position value in the chart. The portfolio is betting on four companies, mostly in technology, and the fixed-income and commodity buffer is too small to absorb a fall in one of them. | Reduce Rockwell Automation (ROK) and Baidu (BIDU) by 15%, keeping some exposure to their upside. |
| Risk and reward misaligned | The holdings with most of the capital have the lowest Sharpe ratios, and the best ones, CTA at $12,865 and LBAY at $22,728, are the smallest positions. | Move the trimmed weight toward high-Sharpe alternatives such as CTA and LBAY, or new securities with better ratios. |
| Too little ETF exposure | Common shares total $631.54M against $191.48M in ETFs, so single-company risk dominates. | Revisit ETF choices with the client, including sectors the client knows in the home market, and consider oil and gas exposure for lower correlation with equities. |
The report frames the whole set as a risk-budgeting rebalance: dilute what is over-accumulated and refocus capital on well-chosen, less correlated assets, which should lower risk and lift the portfolio's Sharpe ratio.
05 / Reading the work with care
What to keep in mind before relying on the ratios.
This is a course assignment on a fixed dataset, and several details are worth knowing. I did not re-run the SQL. These notes come from reading the two reports.
| Check | What the reports show |
|---|---|
| Two Sharpe figures | The SQL step gives CTA a Sharpe of 0.0692 and the dashboard shows 0.1539. The two figures differ, though both rank CTA first, so the ranking is more reliable than the levels. |
| No risk-free rate | The report drops the risk-free rate on purpose, to compare securities on a level playing field. The result is a return-to-volatility ratio, not a standard Sharpe ratio. |
| Scale of the volatility figures | The SQL sigma values, such as 6.72 for UNG, are large next to the returns they are set against, such as 0.43. The units or window in that query are worth checking before reading the ratios in absolute terms. |
| Duplicates and gaps | ARB and BIDU appear twice in the return tables, and the 36-month table shows NULL for securities without three years of history. |
| Two scales | The position-value chart tops out at 252,060 and totals about 1.23 million across its 27 bars, while the text quotes portfolio values in the hundreds of millions of dollars. Share-of-total figures, such as the 61%, are safe, but the two are not directly comparable in dollars. |
| A fixed snapshot | Prices run from August 2019 and returns are measured at 9 September 2022, so this reflects the course database and not current markets. |