Develop Customized Investment Analysis Tools with Excel VBA

This example will include several key features that are often used in investment analysis, such as calculating returns, evaluating portfolio performance, and generating reports.

We will break down the problem into these steps:

1. Data Import: Importing stock data.
2. Risk/Return Calculations: Calculating average returns, volatility, and Sharpe ratio.
3. Portfolio Analysis: Allocating weights and calculating portfolio return and risk.
4. Visualization: Creating simple charts to show performance.
5. Reporting: Automatically generating a summary report.

Step-by-Step VBA Code with Explanations

First, ensure that you have data in the following format (or something similar) in your Excel sheet:

| Date       | Stock A | Stock B | Stock C |

|————|———|———|———|

| 2020-01-01 | 100     | 150     | 200     |

| 2020-01-02 | 102     | 152     | 198     |

| …        | …     | …     | …     |

You will use Excel VBA to process this data and generate custom investment analysis tools.

Step 1: Set Up Excel Sheet

1. Create a new sheet where the user can input historical stock data (price data for different stocks over time).
2. Add a sheet for output results and analysis (like portfolio returns, risk, and performance metrics).

Now, let’s create the VBA code.

Sub InvestmentAnalysisTool()
    ' Declare variables for stock data and analysis results
    Dim wsData As Worksheet
    Dim wsResults As Worksheet
    Dim lastRow As Long
    Dim stockCount As Integer
    Dim stockReturns() As Double
    Dim stockPrices() As Double
    Dim weights() As Double
    Dim portfolioReturn As Double
    Dim portfolioRisk As Double
    Dim sharpeRatio As Double
    Dim riskFreeRate As Double
    Dim i As Integer
    Dim j As Integer
    Dim covarianceMatrix() As Double
    Dim correlationMatrix() As Double
    Dim portfolioVariance As Double   
    ' Set risk-free rate (e.g., 2% per year)
    riskFreeRate = 0.02   
    ' Set the data and results sheets
    Set wsData = ThisWorkbook.Sheets("Data")
    Set wsResults = ThisWorkbook.Sheets("Results")   
    ' Find the last row of data in the 'Data' sheet
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row   
    ' Determine the number of stocks
    stockCount = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column - 1 ' Exclude date column   
    ' Resize the arrays to hold stock data
    ReDim stockPrices(1 To lastRow - 1, 1 To stockCount)
    ReDim stockReturns(1 To lastRow - 1, 1 To stockCount)
    ReDim covarianceMatrix(1 To stockCount, 1 To stockCount)
    ReDim correlationMatrix(1 To stockCount, 1 To stockCount)
    ReDim weights(1 To stockCount)
    ' Import the stock data into the array
    For i = 2 To lastRow
        For j = 1 To stockCount
            stockPrices(i - 1, j) = wsData.Cells(i, j + 1).Value ' Skip the date column
        Next j
    Next i   
    ' Calculate the daily returns for each stock
    For i = 1 To lastRow - 2
        For j = 1 To stockCount
            stockReturns(i, j) = (stockPrices(i + 1, j) - stockPrices(i, j)) / stockPrices(i, j)
        Next j
    Next i   
    ' Calculate Covariance Matrix and Correlation Matrix
    For i = 1 To stockCount
        For j = 1 To stockCount
            ' Covariance
            covarianceMatrix(i, j) = WorksheetFunction.Covar(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)), _
                                                              wsData.Range(wsData.Cells(2, j + 1), wsData.Cells(lastRow, j + 1)))
            ' Correlation
            correlationMatrix(i, j) = WorksheetFunction.Correl(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)), _
                                                                wsData.Range(wsData.Cells(2, j + 1), wsData.Cells(lastRow, j + 1)))
        Next j
    Next i
    ' Portfolio Weights (Assume equal weights for simplicity)
    For i = 1 To stockCount
        weights(i) = 1 / stockCount ' Equal weighting for each stock
    Next i
    ' Calculate Portfolio Return and Risk (Standard Deviation)
    portfolioReturn = 0
    portfolioVariance = 0
    For i = 1 To stockCount
        portfolioReturn = portfolioReturn + weights(i) * WorksheetFunction.Average(wsData.Range(wsData.Cells(2, i + 1), wsData.Cells(lastRow, i + 1)))
    Next i   
    For i = 1 To stockCount
        For j = 1 To stockCount
            portfolioVariance = portfolioVariance + weights(i) * weights(j) * covarianceMatrix(i, j)
        Next j
    Next i   
    portfolioRisk = Sqr(portfolioVariance)   
    ' Calculate Sharpe Ratio
    sharpeRatio = (portfolioReturn - riskFreeRate) / portfolioRisk   
    ' Display Results in the 'Results' Sheet
    wsResults.Cells(1, 1).Value = "Portfolio Return"
    wsResults.Cells(1, 2).Value = portfolioReturn
    wsResults.Cells(2, 1).Value = "Portfolio Risk (Std Dev)"
    wsResults.Cells(2, 2).Value = portfolioRisk
    wsResults.Cells(3, 1).Value = "Sharpe Ratio"
    wsResults.Cells(3, 2).Value = sharpeRatio   
    ' Display Covariance Matrix
    For i = 1 To stockCount
        For j = 1 To stockCount
            wsResults.Cells(5 + i, 1 + j).Value = covarianceMatrix(i, j)
        Next j
    Next i  
    ' Display Correlation Matrix
    For i = 1 To stockCount
        For j = 1 To stockCount
            wsResults.Cells(5 + stockCount + i, 1 + j).Value = correlationMatrix(i, j)
        Next j
    Next i
    ' Creating a simple chart for portfolio performance visualization
    Dim chartObj As ChartObject
    Set chartObj = wsResults.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    chartObj.Chart.ChartType = xlLine
    chartObj.Chart.SetSourceData Source:=wsResults.Range("A1:B3")
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Portfolio Performance"
End Sub

Detailed Explanation of the Code:

1. Data Import:

   – We first import the stock data (daily prices for different stocks) from the `Data` sheet. The data should start from row 2, where column A contains dates and the subsequent columns contain stock prices.

2. Covariance and Correlation:

   – We compute the covariance matrix, which tells us how the stocks’ returns move together. This helps assess the risk of combining different stocks in a portfolio.

   – The correlation matrix is also calculated to understand the linear relationship between different stocks.

3. Portfolio Analysis:

   – In this code, we assume equal weights for all stocks in the portfolio. You can modify the weights array for custom allocations.

   – The portfolio return is the weighted average of the individual stock returns.

   – Portfolio risk (volatility) is computed by calculating the weighted covariance between the stock returns, then applying the portfolio variance formula. The risk is the square root of the variance.

4. Output:

   – Results (Portfolio Return, Risk, Sharpe Ratio) are displayed in the `Results` sheet.

   – Covariance and correlation matrices are displayed as well.

   – A simple line chart is generated to visualize portfolio performance.

Customization:

– Risk-free Rate: The code uses a fixed risk-free rate (2%). You can modify this value as needed.

– Weights: The weights of the stocks in the portfolio are currently set to equal weights. You can modify this to allocate based on your investment strategy.

– Chart: A basic chart is generated to visualize the portfolio performance, but you can enhance this by adding more charts like a bar chart for individual stock performance.

Final Thoughts:

This Excel VBA-based investment analysis tool can be extended with additional functionality like advanced risk metrics (e.g., Value-at-Risk), Monte Carlo simulations for portfolio forecasting, and more complex optimization techniques for asset allocation. It’s a great starting point for customizing your own investment analysis model.