Develop Customized Portfolio Optimization Tools with Excel VBA

Creating a Customized Portfolio Optimization Tool using Excel VBA involves writing a code that helps investors optimize their portfolio by selecting the best combination of assets, subject to constraints such as budget, risk, and expected return.

The goal of portfolio optimization is to maximize return for a given level of risk or minimize risk for a given level of return. This is typically achieved using concepts from Modern Portfolio Theory (MPT), which includes the efficient frontier, risk (variance or standard deviation), and expected return.

In this tutorial, I will walk you through the creation of a Portfolio Optimization Tool in Excel using VBA. The tool will take input data for various stocks/assets, including their expected returns, volatilities (risks), and correlation, and it will optimize the portfolio to maximize return for a given risk level.

Step 1: Preparing Data in Excel

Before writing the VBA code, you need some data in your Excel sheet. Suppose you have the following columns:

For simplicity, assume you have three stocks with their expected returns, volatility, and correlation coefficients.

Step 2: Setting up the VBA Code for Portfolio Optimization

1. Open the VBA Editor:
   • Press Alt + F11 to open the VBA editor.
   • In the editor, go to Insert -> Module to create a new module.
2. VBA Code for Portfolio Optimization: The following code will calculate the optimal weights of the portfolio that maximize the return for a given risk level (minimizing the portfolio variance or volatility).

Here’s an example VBA code:

Sub PortfolioOptimization()
    ' Define variables
    Dim ws As Worksheet
    Dim n As Integer ' Number of assets
    Dim returns() As Double
    Dim volatility() As Double
    Dim correlation() As Double
    Dim weights() As Double
    Dim risk As Double, return As Double
    Dim portfolioVariance As Double
    Dim portfolioReturn As Double
    Dim objectiveFunction As Double
    Dim sumWeights As Double
    Dim i As Integer, j As Integer
    ' Set the worksheet and asset count
    Set ws = ThisWorkbook.Sheets("Sheet1")
    n = 3 ' Number of assets    
    ' Load the data from the worksheet
    ReDim returns(1 To n)
    ReDim volatility(1 To n)
    ReDim correlation(1 To n, 1 To n)
    ReDim weights(1 To n)    
    For i = 1 To n
        returns(i) = ws.Cells(i + 1, 2).Value
        volatility(i) = ws.Cells(i + 1, 3).Value
    Next i
    ' Load correlation matrix
    For i = 1 To n
        For j = 1 To n
            correlation(i, j) = ws.Cells(i + 1, j + 3).Value
        Next j
    Next i
    ' Initialize portfolio weights equally
    For i = 1 To n
        weights(i) = 1 / n
    Next i    
    ' Run optimization (here we use a simple brute force method for demonstration)
    Dim minRisk As Double
    minRisk = 1000 ' Arbitrarily large number for minimum risk
    Dim optimalWeights() As Double
    ReDim optimalWeights(1 To n)    
    ' Brute force - check different weight combinations (this can be replaced with more sophisticated algorithms)
    Dim stepSize As Double
    stepSize = 0.1 ' Adjust step size as needed    
    For i = 0 To 10
        For j = 0 To 10
            For k = 0 To 10
                ' Calculate the portfolio weights
                weights(1) = i * stepSize
                weights(2) = j * stepSize
                weights(3) = k * stepSize                
                ' Normalize the weights to sum to 1
                sumWeights = weights(1) + weights(2) + weights(3)
                For l = 1 To n
                    weights(l) = weights(l) / sumWeights
                Next l                
                ' Calculate portfolio return
                portfolioReturn = 0
                For l = 1 To n
                    portfolioReturn = portfolioReturn + (weights(l) * returns(l))
                Next l                
                ' Calculate portfolio variance (risk)
                portfolioVariance = 0
                For l = 1 To n
                    For m = 1 To n
                        portfolioVariance = portfolioVariance + (weights(l) * weights(m) * correlation(l, m) * volatility(l) * volatility(m))
                    Next m
                Next l                
                ' Calculate the objective function: Risk/Return ratio
                objectiveFunction = portfolioVariance / portfolioReturn                
                ' Check if this combination gives a lower risk
                If portfolioVariance < minRisk Then
                    minRisk = portfolioVariance
                    For l = 1 To n
                        optimalWeights(l) = weights(l)
                    Next l
                End If
            Next k
        Next j
    Next i    
    ' Output the results
    ws.Cells(5, 1).Value = "Optimal Weights"
    For i = 1 To n
        ws.Cells(5, i + 1).Value = optimalWeights(i)
    Next i    
    ws.Cells(6, 1).Value = "Minimum Risk"
    ws.Cells(6, 2).Value = minRisk
End Sub

Explanation of the Code

1. Variables:
   • returns(): Array to hold the expected returns of the stocks.
   • volatility(): Array to hold the standard deviations (risks) of the stocks.
   • correlation(): 2D array to hold the correlation matrix between the stocks.
   • weights(): Array to hold the portfolio weights.
   • portfolioReturn: The total return of the portfolio.
   • portfolioVariance: The total variance (risk) of the portfolio.
   • objectiveFunction: A measure to evaluate the optimization (here, using risk-to-return ratio).
   • sumWeights: To ensure the sum of portfolio weights is 1.
2. Brute Force Optimization:
   • The code uses a brute force approach to optimize the portfolio weights by checking different combinations of weights (this is a very basic optimization method).
   • For each combination of weights, the portfolio’s expected return and variance are calculated.
   • The combination that results in the lowest risk (minimum portfolio variance) is considered optimal.
3. Output:
   • The optimal weights for the portfolio are displayed starting at cell A5.
   • The minimum risk (portfolio variance) is displayed in cell B6.

Step 3: Running the Code

1. After inserting the code into the VBA editor, close the editor (Alt + Q).
2. Go back to your Excel worksheet.
3. Run the code by pressing Alt + F8, selecting PortfolioOptimization, and clicking Run.

Step 4: Improving the Code

• The brute force method is slow and inefficient for large datasets. You could replace this with optimization algorithms like Gradient Descent, Genetic Algorithms, or Excel’s Solver for better performance.

Conclusion

This basic example gives you the foundation to develop a Portfolio Optimization Tool in Excel VBA. By using historical data, expected returns, volatilities, and correlations, this tool calculates the optimal asset weights for a portfolio. For more advanced solutions, you can use more sophisticated optimization methods or integrate Excel Solver directly into your VBA code.