Process CSV for portfolio returns and metrics
Company: DRW
Role: Machine Learning Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
Given one or more CSV files containing daily asset prices or returns and optional portfolio weights, write Python (pandas) code to: a) load, clean, and align by date; b) handle missing values and outliers with documented choices; c) compute individual asset returns (simple and log) from prices if needed; d) compute a time series of portfolio returns given weights, supporting fixed-weight and periodic rebalancing; e) calculate cumulative return, annualized return, annualized volatility, Sharpe ratio (risk-free rate provided), maximum drawdown, and rolling-window metrics; f) support multiple portfolios and output a tidy summary table plus a CSV of metrics and a plot-ready time series; g) ensure numerical stability, clear function boundaries, and unit tests for edge cases (non-overlapping dates, zero weights, NaNs); h) report algorithmic complexity and justify design choices.
Overview: This question evaluates a candidate's competency in time-series data manipulation, financial return computation, portfolio analytics, numerical stability, modular software design (clear function boundaries and unit tests), and algorithmic complexity analysis within the Data Manipulation (SQL/Python) domain.
Compute daily asset returns from price history
You are given a table of daily closing prices for multiple assets. Write a SQL query to compute the daily simple and log returns for each asset, aligned by date. For each asset, skip the first available date (because there is no previous price). Return one row per asset and date with the columns: asset_id, price_date, simple_return, log_return.
Tables
asset_prices(asset_id INT, asset_name VARCHAR(50), price_date DATE, close_price DECIMAL(10,2))
Hints
- Use a window function to access the previous day's price for each asset.
- Simple return is (today_price / yesterday_price) - 1, and log return is LN(today_price / yesterday_price).
Compute daily portfolio returns from asset returns and weights
Write a PostgreSQL query. You are given daily simple returns for each asset and daily portfolio weights over those assets for multiple portfolios. For each portfolio and date, compute the portfolio's daily simple return as the weighted sum of weight * simple_return across its constituent assets. Assume weights are already aligned by date, and any asset not listed for a portfolio on a given date has zero weight. Return one row per portfolio per date with return_date, portfolio_id, portfolio_name, and portfolio_simple_return rounded to 4 decimal places.
Tables
asset_returns(asset_id INT, price_date DATE, simple_return DECIMAL(10,4), log_return DECIMAL(10,4))
portfolios(portfolio_id INT, portfolio_name VARCHAR(50))
portfolio_weights(portfolio_id INT, asset_id INT, weight_date DATE, weight DECIMAL(5,4))
Hints
- Join asset_returns to portfolio_weights on both asset_id and date.
- Group by portfolio and date, then sum weight * simple_return to get the portfolio return.
Compute portfolio performance metrics including Sharpe ratio and max drawdown
You are given daily simple returns for multiple portfolios and a daily risk-free rate. Using the full sample period in the data, compute the following performance metrics per portfolio: (1) cumulative return over the sample, (2) annualized return, (3) annualized volatility of returns, (4) annualized Sharpe ratio using daily excess returns over the risk-free rate, and (5) maximum drawdown based on the portfolio wealth index. Assume this sample covers exactly 4 trading days and that there are 4 trading days in a year (so the sample represents one full year). Use 4 as the annualization factor in your calculations. Return one row per portfolio with: portfolio_id, portfolio_name, start_date, end_date, cumulative_return, annualized_return, annualized_volatility, annualized_sharpe_ratio, max_drawdown.
Tables
portfolios(portfolio_id INT, portfolio_name VARCHAR(50))
portfolio_returns(portfolio_id INT, return_date DATE, simple_return DECIMAL(10,4))
risk_free_rates(rate_date DATE, daily_rf_rate DECIMAL(10,4))
Hints
- Join portfolio_returns with risk_free_rates to get daily excess returns.
- Use window functions with LN and EXP to build a wealth index and compute maximum drawdown, and aggregate over the full period for summary metrics.