Created
March 21, 2025 21:47
-
-
Save ncalm/a1c06301bd0edf430595d28a7b8c6187 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def portfolio_analysis(data: UDF.Range, has_dates: bool = True, price_type: str = "Close", | |
| annual_factor: int = 252, custom_weights: list = None, risk_free_rate: float = 0.0) -> UDF.Range: | |
| """ | |
| Calculates key portfolio metrics from stock price data. | |
| Args: | |
| data: Daily stock prices. If has_dates=True, the first column should be dates. | |
| has_dates: Whether the first column contains dates (True) or is price data (False). | |
| price_type: String indicating price type used ("Close" or "Adj Close"). | |
| annual_factor: Number of trading days per year, defaulting to 252 for daily data. | |
| custom_weights: Optional list of weights for each asset. Defaults to equal weighting. | |
| risk_free_rate: Annualized risk-free rate used for calculating Sharpe ratio. | |
| Returns: | |
| Array of portfolio metrics with raw numeric values (not formatted). | |
| """ | |
| def extract_value(cell): | |
| """Helper function to extract basic value from Excel cell.""" | |
| if isinstance(cell, dict): | |
| return cell.get('basicValue', cell) | |
| elif isinstance(cell, list) and len(cell) > 0 and isinstance(cell[0], dict): | |
| return cell[0].get('basicValue', cell[0]) | |
| return cell | |
| # Convert data to numeric array, handle NaN values | |
| numeric_data = [[extract_value(cell) for cell in row] for row in data] | |
| if has_dates: | |
| # Skip the first column (dates) for calculations | |
| prices_array = np.array(numeric_data)[:, 1:].astype(float) | |
| dates = [row[0] for row in data] | |
| start_date = dates[0] | |
| end_date = dates[-1] | |
| else: | |
| prices_array = np.array(numeric_data, dtype=float) | |
| start_date = "N/A" | |
| end_date = "N/A" | |
| # Handle missing values by forward-filling (if necessary) | |
| prices_array = np.where(np.isnan(prices_array), np.nan_to_num(prices_array, nan=0.0), prices_array) | |
| # Calculate log returns | |
| returns = np.diff(np.log(prices_array), axis=0) | |
| # Determine weights (custom or equal) | |
| n_assets = returns.shape[1] | |
| weights = np.array(custom_weights) if custom_weights else np.ones(n_assets) / n_assets | |
| # Calculate portfolio returns | |
| port_returns = np.dot(returns, weights) | |
| # Basic portfolio metrics | |
| annual_return = np.mean(port_returns) * annual_factor | |
| annual_vol = np.std(port_returns) * np.sqrt(annual_factor) | |
| sharpe_ratio = (annual_return - risk_free_rate) / annual_vol | |
| # Drawdown calculation | |
| cum_returns = np.cumprod(1 + port_returns) - 1 | |
| running_max = np.maximum.accumulate(cum_returns) | |
| drawdowns = cum_returns / running_max - 1 | |
| max_drawdown = np.min(drawdowns) | |
| # Value at Risk (95%) | |
| var_95 = np.percentile(port_returns, 5) * np.sqrt(annual_factor) | |
| # Sortino Ratio (downside risk) | |
| downside_returns = port_returns[port_returns < 0] | |
| sortino_ratio = (annual_return - risk_free_rate) / (np.std(downside_returns) * np.sqrt(annual_factor)) if len(downside_returns) > 0 else np.nan | |
| metrics = [ | |
| ['Analysis Details', ''], | |
| ['Start Date', start_date], | |
| ['End Date', end_date], | |
| ['Price Type Used', price_type], | |
| ['Number of Assets', n_assets], | |
| ['Number of Trading Days', len(returns)], | |
| ['', ''], | |
| ['Portfolio Metrics', 'Value'], | |
| ['Annualized Return', annual_return], | |
| ['Annualized Volatility', annual_vol], | |
| ['Sharpe Ratio', sharpe_ratio], | |
| ['Sortino Ratio', sortino_ratio], | |
| ['Maximum Drawdown', max_drawdown], | |
| ['Daily Value at Risk (95%)', var_95] | |
| ] | |
| return metrics |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment