Portfolio optimization is a classic finance problem: given a set of assets, what mix creates the lowest overall risk?
In this post, we’ll use Python in Excel to answer that question. We’ll start with monthly return data in an Excel table, bring it into Python, calculate average returns and the covariance matrix, and then use SciPy to find the portfolio weights that minimize risk.
This is a great application of Python in Excel because the data can stay in a familiar workbook, while Python handles the more advanced analytics behind the scenes.
Download the exercise file below to follow along:
Set up the monthly returns data
To get started, we’ll bring in the optimization tool Python needs and pull the monthly returns table from Excel into Python.
Since this is time-based return data, we set Date as the index so Python understands each row as an observation over time rather than just another field to optimize. From there, each asset column becomes a return series we can compare against the others.
from scipy.optimize import minimize
returns = xl("MonthlyReturns[#All]", headers=True)
returns = returns.set_index("Date")
returns.head()
This setup gives us the structure we need to calculate portfolio risk and eventually find the asset weights that minimize variance.
Calculate average returns and how assets move together
Next, we’ll calculate two important pieces from the return data:
mean_returns = returns.mean()
cov_matrix = returns.cov()mean_returns gives us the average monthly return for each investment.
cov_matrix tells us how the investments tend to move together. For example, do they usually rise and fall at the same time, or do they behave differently from one another?
That matters because the lowest-risk portfolio is not always made up of the lowest-risk assets by themselves. Sometimes the better answer is a mix of assets that balance each other out.
Create a starting portfolio
Before Python can search for the best portfolio, it needs a starting point.
n_assets = len(mean_returns)
starting_weights = np.repeat(1 / n_assets, n_assets)
starting_weightsn_assets tells Python how many investments we are working with. Then starting_weights gives each investment an equal share to start. So if there are 5 assets, each one starts at 20%.
This is not the final portfolio. It is just a reasonable first guess so the optimizer has somewhere to begin before it starts searching for the lowest-risk mix.
Define portfolio risk
Now we create a small function that calculates the risk of any portfolio mix.
def portfolio_variance(weights, cov_matrix):
return weights @ cov_matrix @ weightsThe weights are how much of the portfolio goes into each investment. The cov_matrix tells Python how those investments move together.
This function combines those two pieces and returns one number: the portfolio’s overall variance. In plain English, it is the risk score Python is trying to make as small as possible.
The optimizer will use this function over and over, testing different weights until it finds the lowest-risk mix.
Set the portfolio rules
Next, we need to tell the optimizer what it is allowed to do.
constraints = [
{
"type": "eq",
"fun": lambda weights: np.sum(weights) - 1
}
]
bounds = [(0, 1) for asset in range(n_assets)]The constraint says that all portfolio weights must add up to 1, or 100%. In other words, the optimizer has to invest the full portfolio.
The bounds say each asset can have a weight between 0 and 1. An asset can be left out entirely at 0%, or it can receive up to 100% of the portfolio, but it cannot go below zero.
That means this is a long-only portfolio. We are not allowing short selling here.
Run the optimization
Now we can ask Python to solve the portfolio problem.
result = minimize(
portfolio_variance,
starting_weights,
args=(cov_matrix,),
method="SLSQP",
bounds=bounds,
constraints=constraints
)
result.successThe minimize() function tries different portfolio weights and looks for the mix that produces the lowest variance. It starts with the equal-weight portfolio, uses the covariance matrix to measure risk, and follows the rules we set earlier: weights must add up to 100%, and no asset can have a negative weight.
The SLSQP method is the optimization approach being used here because it can handle both the bounds and the constraint.
Finally, result.success checks whether the optimizer was able to find a valid answer. Seeing TRUE returned in the Excel cell tells us the optimization worked and we are good to move on to the actual portfolio weights.
View the optimized weights
Next, we’ll put the optimizer’s answer into a clean table.
optimal_weights = pd.DataFrame({
"Asset": returns.columns,
"Weight": result.x
})
optimal_weightsreturns.columns gives us the asset names from the original dataset. result.x contains the portfolio weights the optimizer found. By putting those together in a DataFrame, we get a simple output showing how much of the portfolio should go into each asset.
This is the actual minimum variance portfolio: the asset mix Python found that produces the lowest overall risk based on the historical monthly returns.
Summarize the portfolio’s return and risk
Now we’ll calculate two summary numbers for the optimized portfolio
portfolio_return = result.x @ mean_returns
portfolio_risk = np.sqrt(result.x @ cov_matrix @ result.x)
summary = pd.DataFrame({
"Metric": ["Expected Monthly Return", "Monthly Risk"],
"Value": [portfolio_return, portfolio_risk]
})
summaryportfolio_return calculates the expected monthly return of the optimized portfolio by combining each asset’s average return with its final weight.
portfolio_risk calculates the monthly risk of that same portfolio. We take the square root because the earlier formula gives us variance, and the square root turns it into a more readable risk measure.
The output gives us a simple summary table: the expected monthly return and the monthly risk for the minimum variance portfolio we just built.
Visualize the portfolio mix
Finally, we’ll plot the optimized weights so the result is easier to interpret.
optimal_weights.plot(
x="Asset",
y="Weight",
kind="bar",
legend=False,
title="Minimum-Risk Portfolio Weights"
)
plt.ylabel("Portfolio Weight")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()Each bar shows how much of the minimum-risk portfolio goes into each asset. A taller bar means the optimizer assigned more of the portfolio to that investment. A bar near zero means the optimizer decided that asset did not help much in reducing overall risk.
In this result, the portfolio is heavily weighted toward the Bond ETF, with a smaller allocation to the Value ETF and a very small allocation to the Broad Market ETF. The Growth ETF, Real Estate ETF, and Commodity ETF receive little to no weight.
That does not mean those assets are “bad.” It just means that, based on this historical return data, they did not improve the lowest-risk mix as much as the assets that were selected. The optimizer found that the lowest-variance portfolio is mostly bonds, with a little exposure to value stocks and the broad market.
Conclusion and next steps
This post shows how Python in Excel can take a familiar workbook and add a more advanced layer of analysis on top. The return data stays in Excel, while Python handles the optimization work behind the scenes.
The key takeaway is that portfolio risk depends not only on each asset individually, but also on how the assets move together. In this case, the minimum-risk portfolio is mostly weighted toward bonds, with smaller allocations to value stocks and the broad market.
Of course, this result is based on historical monthly returns, so it should not be treated as a prediction. The answer could change with a different time period, a different set of assets, or different rules for the optimizer.
As a next step, you could compare this portfolio to an equal-weight portfolio, add a target return, or build an efficient frontier to explore the tradeoff between risk and return.