portfolio.bas

Option Explicit

' Description: Outputs quarterly average for a range monthly data.
' "quarter" for quarter reports
' "year" for yearly reports
Sub GenerateReport(term As String)
    
    Dim portfolioData As Range
    Set portfolioData = Range("B2", Range("B2").End(xlToRight).End(xlDown))
    
    Dim columnCount As Long
    columnCount = portfolioData.Columns.Count
    
    Dim rowCount As Long
    rowCount = portfolioData.Rows.Count
    
    Dim reportCell As Range
    Set reportCell = Cells(2, columnCount + 2)
    
    Dim startRow As Long
    Dim steps As Long
    If term = "quarter" Then
        startRow = 3
        steps = 3
    ElseIf term = "year" Then
        startRow = 12
        steps = 12
    End If
    
    Dim currentRow As Long
    Dim currentCol As Long
    Dim currentColumn As Range

    For currentCol = 1 To columnCount
        For currentRow = startRow To rowCount + startRow Step steps
            Set currentColumn = Range(portfolioData.Cells(currentRow - startRow + 1, currentCol), portfolioData.Cells(currentRow, currentCol))
            reportCell.Cells(currentRow + 1, currentCol).Value = WorksheetFunction.Average(currentColumn)
        Next currentRow
    Next currentCol

End Sub

Sub Report()
    ' "quarter" or "year"
    GenerateReport "year"
End Sub