Creative Blog

Étiquette : vba

  • Calculate the standard deviation in Excel VBA

    Objective:

    To calculate the standard deviation of a set of data stored in an Excel column using VBA.

    1. Preparation Step:

    Make sure your data is present in a column in Excel. For example, assume your data is in column A, from cell A2 to A10.

    1. Opening the VBA Editor:
    • Open Excel.
    • Press Alt + F11 to open the VBA editor.
    • In the editor, click Insert > Module to create a new module.
    1. The VBA Code to Calculate the Standard Deviation:
    Sub CalculateStandardDeviation()
    Dim dataRange As Range
    Dim standardDeviation As Double
    Dim cell As Range
    Dim count As Long
    Dim sum As Double
    Dim sumOfSquares As Double
    Dim variance As Double
    ' Define the range of data (column A from A2 to A10)
    Set dataRange = Range("A2:A10")   
    ' Initialize variables
    sum = 0
    sumOfSquares = 0
    count = 0   
    ' Calculate the sum of the values and the sum of the squares of the values
    For Each cell In dataRange
        If IsNumeric(cell.Value) Then
            sum = sum + cell.Value
            sumOfSquares = sumOfSquares + cell.Value ^ 2
            count = count + 1
        End If
    Next cell   
    ' Check if there are enough values to calculate the standard deviation
    If count > 1 Then
        ' Calculate variance: (sum of squares / number of values) - (mean^2)
        variance = (sumOfSquares / count) - (sum / count) ^ 2       
        ' Calculate standard deviation: square root of the variance
        standardDeviation = Sqr(variance)       
        ' Display the standard deviation in a cell (e.g., cell B1)
        Range("B1").Value = "Standard Deviation: " & standardDeviation
    Else
        MsgBox "Not enough values to calculate the standard deviation.", vbExclamation
    End If
End Sub

    Detailed Explanation of the Code:

    Variable Declarations:

    • dataRange: Represents the range of data (A2:A10 in this example).
    • standardDeviation: Stores the calculated standard deviation.
    • cell: Used to loop through each cell in the data range.
    • count: Keeps track of the number of numeric values in the range.
    • sum: Holds the sum of the values.
    • sumOfSquares: Holds the sum of the squares of the values.
    • variance: Holds the variance, calculated before the standard deviation.

    Defining the Data Range:

    • The range A2:A10 is defined in the code. You can adjust this range according to your needs. Use Range(« A2:A10 ») to specify the data range.

    Calculating the Sum and Sum of Squares:

    • The code loops through each cell in the dataRange and adds the value of each cell to sum and the square of each cell’s value to sumOfSquares.

    Checking the Number of Values:

    • Before calculating the standard deviation, the code ensures that there are more than one value (because the standard deviation is not defined for a single data point).

    Calculating the Variance:

    • The variance is calculated using the formula: variance=∑(xi2)n−(∑xin)2\text{variance} = \frac{\sum (x_i^2)}{n} – \left( \frac{\sum x_i}{n} \right)^2variance=n∑(xi2​)​−(n∑xi​​)2 where xix_ixi​ are the data values and nnn is the number of values.

    Calculating the Standard Deviation:

    • The standard deviation is the square root of the variance: standard deviation=variance\text{standard deviation} = \sqrt{\text{variance}}standard deviation=variance​

    Displaying the Result:

    • The calculated standard deviation is displayed in cell B1. You can choose a different cell for displaying the result.

    Error Message:

    • If there are not enough values to calculate the standard deviation, a warning message will pop up.
    1. Running the Code:
    • To run the code, go back to Excel, press Alt + F8, select CalculateStandardDeviation, and click « Run ».
    • The standard deviation for your data will be calculated and displayed in cell B1.
    1. Possible Improvements:
    • You could make the data range dynamic. For example, use Range(« A2:A » & Cells(Rows.Count, 1).End(xlUp).Row) to include all data up to the last used row in column A.

  • Calculate skewness (asymmetry) in Excel using VBA.

    Steps to Create the VBA Function for Skewness Calculation:

    1. Open the VBA Editor:
      In Excel, press Alt + F11 to open the VBA editor.
    2. Create a New Module:
      Go to Insert > Module to insert a new module.
    3. Write the VBA Code to Calculate Skewness:

    Here is the VBA code to calculate the skewness of a data set:

    Function Skewness(DataRange As Range) As Double
    ' Variable declarations
    Dim n As Long
    Dim Mean As Double
    Dim StdDev As Double
    Dim SumCubedDiff As Double
    Dim i As Long
    Dim diff As Double   
    ' Number of data points in the range
    n = DataRange.Count   
    ' Calculate the mean of the data
    Mean = Application.WorksheetFunction.Average(DataRange)   
    ' Calculate the standard deviation of the data
    StdDev = Application.WorksheetFunction.StDev(DataRange)   
    ' Check if the standard deviation is zero (to avoid division by zero)
    If StdDev = 0 Then
        Skewness = 0
        Exit Function
    End If   
    ' Calculate the sum of cubed differences
    SumCubedDiff = 0
    For i = 1 To n
        diff = DataRange.Cells(i).Value - Mean
        SumCubedDiff = SumCubedDiff + diff ^ 3
    Next i   
    ' Calculate skewness using the formula
    Skewness = (n / ((n - 1) * (n - 2))) * (SumCubedDiff / (StdDev ^ 3))
End Function

    Explanation of the Code:

    1. Function Arguments:
      • DataRange: The range of data over which the skewness is calculated. This range is passed to the function when called in Excel.
    2. Variable Declarations:
      • n: Number of data points in the given range.
      • Mean: The mean (average) of the data.
      • StdDev: The standard deviation of the data.
      • SumCubedDiff: The sum of the cubed differences between each value and the mean.
      • i: A counter for looping through the data.
    3. Calculating the Mean (Mean) and Standard Deviation (StdDev):
      • Application.WorksheetFunction.Average(DataRange) is used to calculate the mean, and Application.WorksheetFunction.StDev(DataRange) is used to calculate the standard deviation of the data.
    4. Checking if Standard Deviation is Zero:
      • If the standard deviation is zero (i.e., all the data points are the same), the function will return a skewness of zero to avoid division by zero.
    5. Calculating the Sum of Cubed Differences:
      • For each value in the range, the difference between the value and the mean is cubed and added to SumCubedDiff.
    6. Calculating the Skewness:
      • The skewness is calculated using the formula: Skewness=n(n−1)(n−2)×(∑i=1n(Xi−mean)3std dev3)\text{Skewness} = \frac{n}{(n – 1)(n – 2)} \times \left( \frac{\sum_{i=1}^{n}(X_i – \text{mean})^3}{\text{std dev}^3} \right)Skewness=(n−1)(n−2)n​×(std dev3∑i=1n​(Xi​−mean)3​) Where nnn is the number of data points, XiX_iXi​ represents each data value, and std dev is the standard deviation of the data.

    How to Use the Function in Excel:

    1. After writing the code in the VBA module, you can use this function in any Excel cell like any other built-in Excel function.
    2. For example, if your data is in the range A1:A10, you can enter the following formula in any cell:
    =Skewness(A1:A10)

    This formula will return the skewness of the data in the range A1:A10.

    Example:

    • If you have the following data in cells A1:A10:
    1
2
3
4
5
6
7
8
9
10
    • Using the function =Skewness(A1:A10), you will get a result close to 0, indicating that the data is relatively symmetric.

    Remarks:

    • If the data set has significant skewness, you will get a higher positive or negative value depending on whether the skew is to the right (positive skew) or to the left (negative skew).

  • Calculate the quartiles in Excel VBA.

    Goal:

    To calculate the quartiles (Q1, Q2 (median), Q3) for a given range of data. The code will take an input range of cells and return the three quartiles.

    Explanation of Quartiles:

    • Q1 (First Quartile): The median of the first half of the data (25% of the values).
    • Q2 (Median): The median of the entire dataset (50% of the values).
    • Q3 (Third Quartile): The median of the second half of the data (75% of the values).

    VBA Code:

    Sub CalculateQuartiles()
    ' Declare variables
    Dim Range As Range
    Dim Data() As Double
    Dim Q1 As Double
    Dim Q2 As Double
    Dim Q3 As Double
    Dim i As Integer   
    ' Prompt the user to select a range of data
    On Error Resume Next
    Set Range = Application.InputBox("Select a data range", Type:=8)
    On Error GoTo 0   
    ' Check if the range is valid
    If Range Is Nothing Then
        MsgBox "No range selected. The process is canceled.", vbCritical
        Exit Sub
    End If   
    ' Check if the selected range contains numeric values
    If WorksheetFunction.Count(Range) = 0 Then
        MsgBox "The selected range does not contain numeric values.", vbCritical
        Exit Sub
    End If   
    ' Copy the data from the range into an array
    ReDim Data(1 To Range.Cells.Count)
    For i = 1 To Range.Cells.Count
        Data(i) = Range.Cells(i).Value
    Next i   
    ' Sort the data
    Call SortArray(Data)   
    ' Calculate the quartiles
    Q1 = CalculateQuartile(Data, 0.25)
    Q2 = CalculateQuartile(Data, 0.50)
    Q3 = CalculateQuartile(Data, 0.75)   
    ' Display the results
    MsgBox "First Quartile (Q1): " & Q1 & vbCrLf & _
           "Median (Q2): " & Q2 & vbCrLf & _
           "Third Quartile (Q3): " & Q3, vbInformation   
End Sub

' Subroutine to sort the array in ascending order
Sub SortArray(ByRef Array() As Double)
    Dim i As Integer, j As Integer
    Dim Temp As Double
    For i = LBound(Array) To UBound(Array) - 1
        For j = i + 1 To UBound(Array)
            If Array(i) > Array(j) Then
                Temp = Array(i)
                Array(i) = Array(j)
                Array(j) = Temp
            End If
        Next j
    Next i
End Sub

' Function to calculate the quartile based on the percentile (p)
Function CalculateQuartile(ByRef Array() As Double, p As Double) As Double
    Dim N As Integer
    Dim Position As Double
    Dim LowerIndex As Integer
    Dim UpperIndex As Integer
    Dim LowerValue As Double
    Dim UpperValue As Double   
    N = UBound(Array) - LBound(Array) + 1
    Position = p * (N + 1)   
    ' If the position is an integer, return the value at that position
    If Position = Int(Position) Then
        CalculateQuartile = Array(Position)
    Else
        ' Otherwise, interpolate between the two adjacent values
        LowerIndex = Int(Position)
        UpperIndex = LowerIndex + 1
        LowerValue = Array(LowerIndex)
        UpperValue = Array(UpperIndex)       
        ' Linear interpolation
        CalculateQuartile = LowerValue + (Position - LowerIndex) * (UpperValue - LowerValue)
    End If
End Function

    Explanation of the Code:

    1. Variables and Data Range:
      • The variable Range allows the user to select a range of data in the Excel sheet.
      • If the selected range does not contain numeric data, the program displays an error message and exits.
    2. Copying Data into an Array:
      • The data from the selected range is copied into an array called Data().
    3. Sorting the Data:
      • The data is sorted in ascending order using the SortArray subroutine.
    4. Calculating the Quartiles:
      • The CalculateQuartile function is used to calculate the quartiles Q1, Q2 (median), and Q3. The function computes the position of the quartile based on the percentile (p), and performs linear interpolation if the position is not an integer.
    5. Displaying Results:
      • A message box shows the calculated quartiles.

    How to Use the Code:

    1. Open Excel and press Alt + F11 to open the VBA editor.
    2. Click Insert and then Module to create a new module.
    3. Copy and paste the VBA code into this module.
    4. Close the VBA editor and return to your Excel sheet.
    5. You can run the macro by pressing Alt + F8, selecting CalculateQuartiles, and clicking « Run ».

    This will prompt you to select the data range, and then a message box will show the three quartiles (Q1, Q2, Q3).

    Customization:

    • You can extend or modify this code to calculate other statistical measures or handle more complex datasets if needed.

  • Calculate R-squared in Excel VBA.

    What is R-squared ?

    The R-squared , or coefficient of determination, measures the proportion of the variance in the dependent variable that can be predicted from the independent variable(s). It ranges from 0 to 1:

    • A value close to 1 indicates that the model explains a large portion of the variance.
    • A value close to 0 means the model explains little of the variance.

    VBA Code to Calculate R-squared

    Let’s assume you have data in two columns of Excel:

    • Column A: Independent variable values X
    • Column B: Dependent variable values Y

    We will use linear regression to calculate R-squared , which can be done using the LinEst function in VBA.

    VBA Code Example to Calculate R-squared

    Sub Calculate_R2()
    ' Declare variables
    Dim RangeX As Range
    Dim RangeY As Range
    Dim Results As Variant
    Dim R2 As Double
    ' Define the data ranges (A2:A10 for X, B2:B10 for Y)
    Set RangeX = Range("A2:A10")
    Set RangeY = Range("B2:B10")
    ' Use the LinEst function to perform linear regression
    ' LinEst returns an array containing several values, including R2
    Results = Application.WorksheetFunction.LinEst(RangeY, RangeX, True, True)
    ' R2 is in the third row, first column of the array returned by LinEst
    R2 = Results(3, 1)
    ' Display the R2 value in a specific cell (e.g., C1)
    Range("C1").Value = "R^2 = " & R2
End Sub

    Explanation of the Code:

    1. Declare variables:
      • RangeX and RangeY represent the data ranges for the independent and dependent variables, respectively.
      • Results is a variable that will hold the regression output.
      • R2 is the variable that will hold the R2R^2R2 value.
    2. Define the data ranges:
      • Range(« A2:A10 ») is the range for X (independent variable), and Range(« B2:B10 ») is the range for Y (dependent variable). You can adjust these ranges based on your data.
    3. Use the LinEst function:
      • Application.WorksheetFunction.LinEst(RangeY, RangeX, True, True) performs the linear regression between X and Y. This function returns an array with multiple outputs:
        • The first row contains the regression coefficients (slope, intercept).
        • The second row contains the standard errors of the coefficients.
        • The third row contains R2 (this is what we are interested in).
        • The fourth row contains the standard error of the predicted Y.
    4. Accessing R2:
      • R2 is located in Results(3, 1), which corresponds to the third row, first column of the array returned by LinEst.
    5. Display the result:
      • The R2 value is displayed in cell C1 along with the label « R^2 « .

    How to Run the Code:

    1. Open Excel and press Alt + F11 to open the VBA editor.
    2. Click Insert and then Module to create a new module.
    3. Paste the code into the module.
    4. Return to Excel and press Alt + F8, select Calculate_R2, and click Run.
    5. The R2R^2R2 value will be displayed in cell C1.

    Example Data:

    X (A) Y (B)
    1 2
    2 4
    3 5
    4 4.5
    5 6
    6 7
    7 8
    8 8.5
    9 9

    If you run the code with these data in columns A and B, the R-squared value will be displayed in cell C1.

     

  • Calculate the percentile in Excel VBA

    Explanation:

    A percentile is a value that divides a set of data into 100 equal parts. For example, the 50th percentile (also known as the median) separates the lowest 50% of the data from the highest 50%. In VBA, we can calculate the percentile using the WorksheetFunction.Percentile function.

    Here is a detailed VBA code to calculate the percentile from a dataset in a column:

    VBA Code to Calculate Percentile

    Sub CalculatePercentile()
    ' Declare variables
    Dim DataRange As Range
    Dim PercentileValue As Double
    Dim Percentile As Double
    Dim PercentileRank As Double   
    ' Ask the user to select the data range
    On Error Resume Next
    Set DataRange = Application.InputBox("Select the data range:", Type:=8)
    On Error GoTo 0   
    ' Check if the data range is empty
    If DataRange Is Nothing Then
        MsgBox "No range selected, operation canceled."
        Exit Sub
    End If   
    ' Ask the user for the percentile they want to calculate (e.g., 90 for the 90th percentile)
    PercentileRank = InputBox("Enter the percentile to calculate (e.g., 90 for the 90th percentile):", "Percentile Calculation")   
    ' Check if the user entered a valid value
    If PercentileRank < 0 Or PercentileRank > 100 Then
        MsgBox "Please enter a percentile between 0 and 100."
        Exit Sub
    End If   
    ' Calculate the percentile using Excel's Percentile function
    Percentile = Application.WorksheetFunction.Percentile(DataRange, PercentileRank / 100)   
    ' Display the result in a message box
    MsgBox "The " & PercentileRank & "th percentile is: " & Percentile
End Sub

    Detailed Explanation of the Code:

    1. Variable Declaration:
      • DataRange: A variable of type Range that will hold the range of data to analyze.
      • PercentileValue: Variable to store the calculated percentile value (though this is not used directly in this version).
      • Percentile: Variable to store the final percentile value.
      • PercentileRank: The rank of the percentile to calculate (a value between 0 and 100).
    2. Selecting the Data Range:
      • The code prompts the user to select a data range using Application.InputBox. This allows the user to select multiple cells in a column or row.
      • On Error Resume Next and On Error GoTo 0 handle any errors if the user cancels the selection.
    3. Requesting the Percentile to Calculate:
      • The code then asks the user to enter the percentile they want to calculate (e.g., 90 for the 90th percentile) through an InputBox.
    4. Validating the Percentile:
      • The code checks if the entered percentile value is between 0 and 100. If it is invalid, it displays an error message and exits the process.
    5. Calculating the Percentile:
      • The code uses the Application.WorksheetFunction.Percentile function to calculate the percentile. The input percentile is divided by 100 to convert it into a valid range for this function.
    6. Displaying the Result:
      • Finally, the calculated percentile is displayed in a message box using MsgBox.

    Example of Usage:

    1. You have a set of data in a column in Excel (for example, in cells A1 to A10).
    2. You run the VBA code by pressing Alt + F11 to open the VBA editor, and then paste the code into a module.
    3. Once the module is run, a dialog will appear asking you to select the data range.
    4. Another dialog will ask you to enter the percentile (e.g., 90 for the 90th percentile).
    5. The result will be displayed in a message box showing the value of the requested percentile.

    Important Notes:

    • Ensure that the data is sorted or appropriate for percentile calculation.
    • This method uses WorksheetFunction.Percentile, which is equivalent to the PERCENTILE function in Excel.

     

  • Calculating Net Present Value (NPV).

    The Net Present Value (NPV) is a financial tool used to evaluate the profitability of a project or investment. It is calculated by subtracting the sum of the initial investment from the sum of the discounted cash flows at a given discount rate.

    The general NPV formula is: VAN=(∑Ct/((1+r)^t)) -I0

    Where:

    • Ct ​: Cash flow at time t
    • r: Discount rate
    • t: Time period (in years, months, etc.)
    • I0​: Initial investment
    • n: Total number of periods

    Example: NPV Calculation in VBA

    Here is an example of VBA code in Excel to calculate NPV based on the cash flows and discount rate.

    Steps to create the VBA code:

    1. Open Excel and press Alt + F11 to open the VBA editor.
    2. In the VBA editor, create a new module by going to Insert > Module.
    3. Paste the following code into the module.
    4. Close the VBA editor and return to Excel.

    VBA Code for NPV Calculation:

    Sub CalculateNPV()
    ' Define the variables
    Dim DiscountRate As Double
    Dim InitialInvestment As Double
    Dim CashFlows(1 To 5) As Double ' For example, an array for 5 cash flows
    Dim NPV As Double
    Dim i As Integer
    Dim n As Integer
    ' Initialize the data
    InitialInvestment = Range("B1").Value ' Initial investment in cell B1
    DiscountRate = Range("B2").Value ' Discount rate in cell B2   
    ' Fill the cash flows (e.g., for 5 years)
    For i = 1 To 5
        CashFlows(i) = Range("B" & i + 2).Value ' Cash flows in cells B3 to B7
    Next 
    ' Calculate the NPV
    NPV = -InitialInvestment ' Start by subtracting the initial investment
    n = 5 ' Number of periods, here we have 5 years
    For i = 1 To n
        NPV = NPV + CashFlows(i) / (1 + DiscountRate) ^ i ' Add each discounted cash flow
    Next i
    ' Display the NPV in cell B8
    Range("B8").Value = NPV
    ' Display a message if the NPV is positive or negative
    If NPV > 0 Then
        MsgBox "The NPV is positive: " & NPV, vbInformation, "Result"
    ElseIf NPV < 0 Then
        MsgBox "The NPV is negative: " & NPV, vbExclamation, "Result"
    Else
        MsgBox "The NPV is zero.", vbInformation, "Result"
    End If
End Sub

    Detailed Explanation of the Code:

    1. Variable Declaration:
      • DiscountRate: the discount rate (expressed as a percentage, e.g., 0.05 for 5%).
      • InitialInvestment: the initial cost of the investment (usually a negative value).
      • CashFlows(1 To 5): an array to store the cash flows for 5 periods (this can be changed based on the number of periods).
      • NPV: the calculated Net Present Value.
      • i and n: used for looping.
    2. Initializing Data:
      • The initial investment is retrieved from cell B1.
      • The discount rate is retrieved from cell B2.
      • Cash flows for each year are stored in cells B3 to B7, and the code reads them into the CashFlows array.
    3. Calculating the NPV:
      • The NPV calculation starts by subtracting the initial investment.
      • Then, for each period (from 1 to n), the corresponding cash flow is discounted and added to the NPV.
    4. Displaying the Result:
      • The NPV result is displayed in cell B8.
      • A message box will pop up to show whether the NPV is positive, negative, or zero.

    How to Use This Code in Excel:

    1. Input Data:
      • B1: Initial Investment (e.g., -1000).
      • B2: Discount Rate (e.g., 0.05 for 5%).
      • B3 to B7: Cash flows for each period (e.g., 200, 300, 400, 500, 600).
    2. Running the Code:
      • After entering the data in the cells, you can run the code by pressing F5 in the VBA editor or assigning the macro to a button in Excel.

    This method allows you to easily calculate the NPV for a project based on its cash flows and discount rate. You can adjust the number of periods and cash flows according to your specific project.

  • Calculate the range of displacement in Excel VBA

    Scenario:

    • Average speed (in km/h)
    • Duration of travel (in hours)
    • The range of displacement will be calculated as:

    Range=Speed×Duration×Adjustment Factor

    Example of VBA Code to Calculate Displacement Range:

    1. Open the VBA Editor
    • Open Excel.
    • Press Alt + F11 to open the VBA editor.
    • In the editor, go to Insert > Module to create a new module.
    1. VBA Code

    Here’s an example of VBA code to calculate the displacement range:

    Sub CalculateDisplacementRange()
    ' Declare variables
    Dim speed As Double ' Speed in km/h
    Dim duration As Double ' Duration of travel in hours
    Dim range As Double ' Displacement range in km
    ' Ask the user for the speed (in km/h)
    speed = InputBox("Enter the average speed (in km/h):")   
    ' Check if the speed is valid (positive)
    If speed <= 0 Then
        MsgBox "The speed must be greater than zero.", vbExclamation
        Exit Sub
    End If   
    ' Ask the user for the duration of travel (in hours)
    duration = InputBox("Enter the duration of travel (in hours):")  
    ' Check if the duration is valid (positive)
    If duration <= 0 Then
        MsgBox "The duration must be greater than zero.", vbExclamation
        Exit Sub
    End If   
    ' Calculate the displacement range
    range = speed * duration  
    ' Display the result in a message box
    MsgBox "The displacement range is: " & range & " km", vbInformation
End Sub

    Code Explanation:

    1. Variable Declarations:
      • speed: The average speed of the vehicle (in km/h).
      • duration: The duration of travel (in hours).
      • range: The calculated displacement range (in kilometers).
    2. User Input:
      • The InputBox function asks the user to enter the speed and duration.
      • The program checks if the input values are positive. If the values are zero or negative, a message box will display an error, and the program will stop execution (Exit Sub).
    3. Displacement Calculation:
      • The displacement range is calculated by multiplying the speed by the duration (range = speed * duration).
    4. Displaying the Result:
      • The result is displayed to the user using the MsgBox function, showing the displacement range in kilometers.

    Steps to Run the Code:

    1. After entering this code in a new module in the VBA editor (following the steps above), you can run the program in two ways:
      • Press F5 in the VBA editor to run the code.
      • Or assign the code to a button on your Excel sheet by selecting « Insert > Shapes > Button. »

    Example of Usage:

    • If the user enters a speed of 60 km/h and a duration of 2 hours, the displacement range will be: 60 km/h×2 h=120 km60 \, \text{km/h} \times 2 \, \text{h} = 120 \, \text{km}60km/h×2h=120km The message displayed will be: « The displacement range is: 120 km. »

    Extension:

    This code can be easily modified to include other factors, such as fuel consumption or environmental factors. For instance, if you need to add a reduction in range based on fuel efficiency or other conditions, you can adjust the calculation accordingly.

  • Calculating a moving median in Excel using VBA

    Calculating a moving median (also called a « sliding median ») in Excel using VBA is a common task for time series analysis or numerical data smoothing. The moving median is used to smooth the data by calculating the median of a sliding window of values within a dataset.

    Objective

    The goal here is to write a VBA code to calculate the moving median over a defined window size (e.g., 3 periods, 5 periods, etc.) within a range of data in Excel.

    Requirements

    1. Data: A column of numerical data.
    2. Window size for the moving median: A defined number of periods (e.g., 3 or 5).
    3. Output: A separate column where the moving median results will be displayed.

    Example Data

    Assume your data is in column A, from cell A2 to A100. You want to calculate the moving median with a 3-period window and display the results starting from cell B3.

    Detailed VBA Code

    Here’s the VBA code that performs this calculation:

    Sub CalculateMovingMedian()
    Dim dataRange As Range
    Dim resultRange As Range
    Dim windowSize As Integer
    Dim i As Long
    Dim j As Long
    Dim window() As Double
    Dim median As Double   
    ' Define the data range (column A from A2 to A100)
    Set dataRange = Range("A2:A100")   
    ' Define the window size (e.g., 3 periods)
    windowSize = 3   
    ' Define the result range (column B starting from B3)
    Set resultRange = Range("B3:B100")   
    ' Check if the result range is large enough
    If resultRange.Rows.Count < dataRange.Rows.Count - windowSize + 1 Then
        MsgBox "The result range is too small!"
        Exit Sub
    End If   
    ' Calculate the moving median
    For i = windowSize To dataRange.Rows.Count
        ' Create an array to store the window values
        ReDim window(windowSize - 1)       
        ' Fill the array with the window data
        For j = 0 To windowSize - 1
            window(j) = dataRange.Cells(i - j, 1).Value
        Next j        
        ' Sort the array to find the median
        Call SortArray(window)       
        ' Calculate the median (middle value of the sorted array)
        median = window(Int(windowSize / 2))       
        ' Display the median in the result column
        resultRange.Cells(i - windowSize + 1, 1).Value = median
    Next i
End Sub

Sub SortArray(ByRef arr() As Double)
    Dim i As Long, j As Long
    Dim temp As Double   
    ' Bubble sort to sort the array in ascending order
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                ' Swap the values
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

    Explanation of the Code

    1. Defining Data and Result Ranges:
      • dataRange specifies the range of cells that contain the raw data. Here, it refers to column A from A2 to A100.
      • resultRange is where the calculated moving medians will be stored. It starts at cell B3 to avoid overwriting the initial rows that don’t have enough data for the median.
    2. Moving Median Window (n periods):
      • The variable windowSize defines the size of the moving window. In this example, a window of 3 periods is used, but this can be adjusted based on your needs.
    3. Calculating the Moving Median:
      • For each position i in the data range, a window of n values is extracted (the last n values).
      • These values are sorted, and the median is calculated as the middle value of the sorted array.
      • The result of the median is stored in the corresponding cell in the result range.
    4. Sorting the Window Values:
      • The code uses an auxiliary procedure SortArray that sorts the window of values in ascending order using the Bubble Sort algorithm.
      • After sorting, the median is simply the middle value in the sorted array (for an odd-sized window).

    How to Run the Code:

    1. Open Excel.
    2. Press Alt + F11 to open the VBA editor.
    3. Go to Insert > Module and paste the code.
    4. Press F5 to run the macro and calculate the moving median.

    Example Use Case:

    • Data in column A (e.g., A2:A100).
    • Moving Median Window Size: 3 periods.
    • Results in column B (starting from B3).

    Additional Improvements:

    • The sorting function uses Bubble Sort, which can be slow for large datasets. For performance, you might want to use faster sorting algorithms like QuickSort or MergeSort if you’re dealing with large amounts of data.
    • You can add an input box or a dialog to let the user choose the window size dynamically instead of hardcoding it in the code.

  • Calculating a moving average in Excel VBA

    Moving Average Calculation Overview:

    A moving average is commonly used in time series analysis to smooth out short-term fluctuations and highlight longer-term trends or cycles. The moving average is typically calculated by taking the average of a subset of data within a specified window size, which then « moves » along the series.

    This code will allow you to calculate a moving average for a given range of data in Excel, where the average is calculated over a specified number of data points (e.g., a 5-point window). The results will be written to another column.

    VBA Code to Calculate Moving Average:

    Sub CalculateMovingAverage()
    ' Declare variables
    Dim ws As Worksheet
    Dim rangeData As Range
    Dim rangeResult As Range
    Dim windowSize As Integer
    Dim i As Integer, j As Integer
    Dim sum As Double
    Dim currentCell As Range
    ' Initialize variables
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if needed
    Set rangeData = ws.Range("A2:A100") ' Range containing the data (adjust as needed)
    Set rangeResult = ws.Range("B2:B100") ' Range where the results will be displayed
    windowSize = 5 ' Size of the moving window (this can be adjusted)
    ' Loop through each cell in the data range
    For i = windowSize To rangeData.Rows.Count
        sum = 0       
        ' Calculate the sum of values in the window of size windowSize
        For j = i - windowSize + 1 To i
            sum = sum + rangeData.Cells(j, 1).Value
        Next j    
        ' Calculate the moving average and display it in the result range
        rangeResult.Cells(i, 1).Value = sum / windowSize
    Next i
    MsgBox "Moving average calculation completed.", vbInformation
End Sub

    Code Explanation:

    1. Declare Variables:
      • ws is a variable that represents the worksheet where the data is stored.
      • rangeData is the range of cells that contains the data you want to calculate the moving average for (in this case, A2:A100).
      • rangeResult is the range where the moving averages will be written (in this case, B2:B100).
      • windowSize is the size of the window used to calculate the moving average (in this example, it’s set to 5).
    2. Initialize Variables:
      • The code references Sheet1 for the data sheet, but you can change this to the appropriate sheet name in your workbook.
      • The range A2:A100 is used for the data, and B2:B100 is where the results are displayed. Adjust these ranges to match your actual data.
      • The windowSize is set to 5, meaning the average will be calculated using the last 5 data points.
    3. Calculate the Moving Average:
      • The outer loop (starting from i = windowSize) iterates through each data point in the range starting from the 5th value (since we need at least 5 data points to calculate the first average).
      • The inner loop calculates the sum of the windowSize values leading up to the current data point.
      • The moving average is calculated by dividing the sum by the windowSize.
      • The result is written into the corresponding cell in the rangeResult column.
    4. Display a Message:
      • Once the moving averages have been calculated, a message box will pop up saying « Moving average calculation completed. »

    How to Use the Code:

    1. Open the VBA Editor: Press Alt + F11 in Excel to open the Visual Basic for Applications editor.
    2. Insert a New Module: Click Insert > Module to add a new module.
    3. Copy and Paste the Code: Copy the code above and paste it into the new module.
    4. Run the Macro: Press F5 or go to Run > Run Sub/UserForm to execute the macro.

    Customization:

    • Window Size: You can adjust the windowSize variable to change how many data points the moving average is calculated over (e.g., changing it from 5 to 10 for a larger window).
    • Data Range: Modify the rangeData and rangeResult ranges to fit the location of your data and where you want to place the results.
    • Result Placement: You can change the result range to any other column (e.g., C2:C100) if you prefer to place the moving averages elsewhere.

    Example:

    • If you have the following data in column A (from A2 to A100):
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...
    • With a window size of 5, the first moving average (in cell B6) would be the average of A2:A6, the second moving average (in cell B7) would be the average of A3:A7, and so on.

    This VBA code makes calculating moving averages in Excel automated and much easier, especially for larger datasets or when doing repeated calculations.

     

  • Kurtosis Calculation

    VBA Code: Kurtosis Calculation

    Function Kurtosis(rng As Range) As Double
    Dim cell As Range
    Dim n As Long
    Dim sumX As Double, sumX2 As Double, sumX4 As Double
    Dim meanX As Double, stdDev As Double
    Dim result As Double
    ' Initialize variables
    n = rng.Cells.Count
    If n < 4 Then
        Kurtosis = CVErr(xlErrDiv0) ' Error if fewer than 4 values (kurtosis requires a sample size of at least 4)
        Exit Function
    End If   
    ' Calculate the mean
    For Each cell In rng
        sumX = sumX + cell.Value
    Next cell
    meanX = sumX / n
    ' Calculate variance and fourth-order moments
    For Each cell In rng
        sumX2 = sumX2 + (cell.Value - meanX) ^ 2
        sumX4 = sumX4 + (cell.Value - meanX) ^ 4
    Next cell
    ' Standard deviation
    stdDev = Sqr(sumX2 / n)
    ' Compute kurtosis using Fisher’s formula (adjusted for a sample)
    If stdDev <> 0 Then
        result = (sumX4 / n) / (stdDev ^ 4)
        Kurtosis = ((n * (n + 1)) / ((n - 1) * (n - 2) * (n - 3))) * result - (3 * (n - 1) ^ 2) / ((n - 2) * (n - 3))
    Else
        Kurtosis = CVErr(xlErrDiv0) ' Error if standard deviation is zero
    End If
End Function

    Code Explanation

    Sample size verification

    • If the range contains fewer than 4 values, kurtosis cannot be properly calculated (risk of division by zero).
    • Returns a #DIV/0! error.

    Mean calculation

    • Sums all values in the range and divides by n.

    Variance and fourth-order moment calculation

    • sumX2: Sum of squared deviations from the mean (raw variance).
    • sumX4: Sum of deviations raised to the fourth power.

    Standard deviation calculation

    • The standard deviation is the square root of variance.

    Application of adjusted kurtosis formula (excess kurtosis)

    • Fisher’s formula for a finite sample is used:
    • This formula adjusts sample kurtosis to avoid bias.

    Error handling

    • If the standard deviation is zero (all values are identical), kurtosis is undefined → returns #DIV/0! error.

    How to use this function in Excel?
    Enter values in a column (e.g., A1:A10).
    In a cell, enter the formula:

    =Kurtosis(A1:A10)

    The cell will display the sample kurtosis.

    This function is more accurate than Excel’s built-in KURT() as it uses an unbiased formula for a sample.