Great question! Group-level constraints like the ones you're describing are straightforward to implement with Scipy's optimize.minimize function by defining custom inequality constraints. Below is a complete, step-by-step solution tailored to your problem.

Step 1: Import Libraries and Load Data #

First, we'll import the necessary tools and create sample DataFrames from your provided data (replace these with actual Excel imports using pd.read_excel() for your real data).

import pandas as pd
import numpy as np
from scipy.optimize import minimize

# Asset data (replace with pd.read_excel("your_file.xlsx") for real data)
asset_data = pd.DataFrame({
    'Asset_Class': ['Govt', 'Govt', 'Govt', 'Corp', 'Corp', 'Corp', 'Corp', 'Corp', 'Equity', 'Equity'],
    'Asset_Class2': ['Sov', 'Muni', 'Supra', 'HY', 'Financials', 'Industrials', 'Utilites', 'EM', 'Growth', 'Value'],
    'Expected Return': [0.006, 0.013, 0.015, 0.10, 0.016, 0.013, 0.018, 0.06, 0.08, 0.06],
    'Risk Factor': [0.005, 0.005, 0.005, 0.20, 0.05, 0.05, 0.05, 0.10, 0.30, 0.20]
})

# Constraint limits (replace with your actual constraint data)
constraint_limits = pd.DataFrame({
    'Asset_Class': ['Govt', 'Corp', 'Equity'],
    'Low_Limit': [0.10, 0.10, 0.00],
    'High_Limit': [1.00, 0.60, 0.10]
})

Step 2: Define the Objective Function #

Your goal is to maximize the ratio of expected return to risk factor, which is equivalent to minimizing the negative of that ratio (since Scipy's minimize function finds the minimum of a function). We'll add a small epsilon to avoid division by zero in edge cases.

def objective(weights, expected_returns, risk_factors):
    weighted_return = np.dot(weights, expected_returns)
    weighted_risk = np.dot(weights, risk_factors)
    # Add epsilon to prevent division by zero
    return - (weighted_return / (weighted_risk + 1e-8))

Step 3: Set Up Constraints #

We need three types of constraints:

1. Equality constraint: Sum of all weights equals 1 (full investment).
2. Group-level inequality constraints: For each Asset_Class, the sum of its asset weights falls between the specified low and high limits.
3. Non-negativity bounds: Each asset weight is >= 0 (assuming no short selling; adjust bounds to (-np.inf, np.inf) if shorting is allowed).

# Extract numeric arrays from the DataFrame
expected_returns = asset_data['Expected Return'].values
risk_factors = asset_data['Risk Factor'].values

# Initialize constraint list
constraints = []

# 1. Sum of weights = 1
constraints.append({'type': 'eq', 'fun': lambda w: np.sum(w) - 1})

# 2. Add group-level constraints
for _, group in constraint_limits.iterrows():
    asset_class = group['Asset_Class']
    low_limit = group['Low_Limit']
    high_limit = group['High_Limit']
    
    # Get indices of assets in this group
    group_indices = asset_data[asset_data['Asset_Class'] == asset_class].index
    
    # Constraint: Sum of group weights >= low_limit
    constraints.append({
        'type': 'ineq',
        'fun': lambda w, idx=group_indices, limit=low_limit: np.sum(w[idx]) - limit
    })
    
    # Constraint: Sum of group weights <= high_limit
    constraints.append({
        'type': 'ineq',
        'fun': lambda w, idx=group_indices, limit=high_limit: limit - np.sum(w[idx])
    })

# 3. Non-negativity bounds for each weight
bounds = [(0, None) for _ in range(len(expected_returns))]

Step 4: Run the Optimization #

We'll use the SLSQP method, which is well-suited for problems with both equality and inequality constraints. We start with equal weights as the initial guess.

# Initial guess: equal weights for all assets
initial_weights = np.ones(len(expected_returns)) / len(expected_returns)

# Run the minimization
result = minimize(
    objective,
    initial_weights,
    args=(expected_returns, risk_factors),
    method='SLSQP',
    bounds=bounds,
    constraints=constraints,
    options={'disp': True}  # Set to False to hide solver output
)

Step 5: Process and Output Results #

Finally, we'll format the optimal weights and portfolio metrics into the DataFrames you requested.

# Create optimal weights DataFrame
weights_df = pd.DataFrame({
    'Asset_Class2': asset_data['Asset_Class2'],
    'Weight': result.x.round(4)  # Round to 4 decimal places for readability
})

# Calculate portfolio metrics
weighted_return = np.dot(result.x, expected_returns).round(4)
weighted_risk = np.dot(result.x, risk_factors).round(4)

metrics_df = pd.DataFrame({
    'Metric': ['Weighted Expected Return', 'Weighted Risk Factor'],
    'Value': [weighted_return, weighted_risk]
})

# Print results
print("Optimal Asset Weights:")
display(weights_df)  # Use print(weights_df) if display() isn't available

print("\nPortfolio Metrics:")
display(metrics_df)

Key Notes #

• Solver Choice: SLSQP is a good default for constrained optimization problems like this. If you run into convergence issues, you can try other methods like trust-constr.
• Short Selling: If you want to allow short selling, modify the bounds to [(-np.inf, np.inf) for _ in range(len(expected_returns))].
• Edge Cases: The epsilon in the objective function prevents division by zero if the weighted risk factor is zero (unlikely in your case, but a safe guard).

内容的提问来源于stack exchange，提问作者SS_1