UpdateSummary-UK Version

Option Explicit

Public Sub UpdateSummary()
    ' This macro creates the Summary sheet and pivot tables
    ' Run this SECOND after ProcessProlificData

    Dim wsData As Worksheet
    Dim wsSummary As Worksheet

    ' Prevent screen updating during calculations
    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    ' Check if Processed_Data exists
    On Error Resume Next
    Set wsData = ThisWorkbook.Sheets("Processed_Data")
    If wsData Is Nothing Then
        MsgBox "Processed_Data sheet not found! Run ProcessProlificData first.", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0

    ' Delete existing Summary sheet if it exists
    On Error Resume Next
    ThisWorkbook.Sheets("Summary").Delete
    On Error GoTo 0

    ' Create new Summary sheet
    Set wsSummary = ThisWorkbook.Sheets.Add(After:=wsData)
    wsSummary.Name = "Summary"

    ' Create summary sheet and pivot tables
    CreateSummarySheet

    ' Create hourly rate data and chart
    TestHourlyRateCalculation
    CreateYearlyRateAnalysis
    CreateHourlyRateChartOnSummary

    ' Add these lines at the end, just before End Sub
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    wsSummary.Activate    ' This will make the Summary sheet active
    ThisWorkbook.Sheets("TempData").Visible = False  ' This will hide the TempData sheet
End Sub

Private Sub CreateSummarySheet()
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long

    ' Reference the processed data sheet
    Set wsData = ThisWorkbook.Sheets("Processed_Data")
    Set wsSummary = ThisWorkbook.Sheets("Summary")

    lastRow = wsData.Cells(wsData.Rows.count, "A").End(xlUp).row

    ' Clear existing content
    wsSummary.Cells.Clear

    ' Add headers and labels
    With wsSummary
        .Cells(1, 1).Value = "Prolific Summary"
        .Cells(3, 1).Value = "Study Status Breakdown"
        .Cells(4, 1).Value = "Approved Studies"
        .Cells(5, 1).Value = "Returned Studies"
        .Cells(6, 1).Value = "Awaiting Review Studies"
        .Cells(7, 1).Value = "Rejected Studies"
        .Cells(8, 1).Value = "Approval Rating"

        .Cells(10, 1).Value = "Financial Summary"
        .Cells(11, 1).Value = "Total Reward (£)"
        .Cells(12, 1).Value = "Total Bonus (£)"
        .Cells(13, 1).Value = "Total Reward ($)"
        .Cells(14, 1).Value = "Total Bonus ($)"
        .Cells(15, 1).Value = "Total Returned Bonus (£)"
        .Cells(16, 1).Value = "Total Returned Bonus ($)"

        .Cells(18, 1).Value = "Exchange Rate (USD to GBP) "
        .Cells(19, 1).Value = "Total USD Converted to GBP"
        .Cells(20, 1).Value = "Total Combined GBP"

        .Cells(22, 1).Value = "Time and Rate Analysis"
        .Cells(23, 1).Value = "Total Hours"
        .Cells(24, 1).Value = "Hourly Rate (£)"

        ' Add exchange rate input cell with default value
        .Cells(18, 2).Value = 0.760967  ' Average exchange rate 2015 - 2024
    End With

    ' Calculate status counts
    With wsData.Range("J2:J" & lastRow)  ' Status column
        wsSummary.Cells(4, 2).Value = Application.CountIf(.Cells, "APPROVED")
        wsSummary.Cells(5, 2).Value = Application.CountIf(.Cells, "RETURNED")
        wsSummary.Cells(6, 2).Value = Application.CountIf(.Cells, "AWAITING REVIEW")
        wsSummary.Cells(7, 2).Value = Application.CountIf(.Cells, "REJECTED")
    End With

    ' Calculate approval rating
    Dim approvedCount As Long, rejectedCount As Long
    approvedCount = wsSummary.Cells(4, 2).Value
    rejectedCount = wsSummary.Cells(7, 2).Value
    If (approvedCount + rejectedCount) > 0 Then
        wsSummary.Cells(8, 2).Value = approvedCount / (approvedCount + rejectedCount)
        wsSummary.Cells(8, 2).NumberFormat = "0.00%"
    End If

    ' Calculate financial totals
    With Application.WorksheetFunction
        ' Approved studies totals
        wsSummary.Cells(11, 2).Value = .SumIfs(wsData.Range("B2:B" & lastRow), _
            wsData.Range("J2:J" & lastRow), "APPROVED")  ' Reward GBP
        wsSummary.Cells(12, 2).Value = .SumIfs(wsData.Range("C2:C" & lastRow), _
            wsData.Range("J2:J" & lastRow), "APPROVED")  ' Bonus GBP
        wsSummary.Cells(13, 2).Value = .SumIfs(wsData.Range("D2:D" & lastRow), _
            wsData.Range("J2:J" & lastRow), "APPROVED")  ' Reward USD
        wsSummary.Cells(14, 2).Value = .SumIfs(wsData.Range("E2:E" & lastRow), _
            wsData.Range("J2:J" & lastRow), "APPROVED")  ' Bonus USD

        ' Returned bonus totals
        wsSummary.Cells(15, 2).Value = .SumIfs(wsData.Range("C2:C" & lastRow), _
            wsData.Range("J2:J" & lastRow), "RETURNED")  ' Returned Bonus GBP
        wsSummary.Cells(16, 2).Value = .SumIfs(wsData.Range("E2:E" & lastRow), _
            wsData.Range("J2:J" & lastRow), "RETURNED")  ' Returned Bonus USD
    End With

    ' Calculate USD to GBP conversion
    Dim totalUSD As Double
    totalUSD = wsSummary.Cells(13, 2).Value + wsSummary.Cells(14, 2).Value + wsSummary.Cells(16, 2).Value
    wsSummary.Cells(19, 2).Formula = "=(" & wsSummary.Cells(13, 2).Address & "+" & _
                                      wsSummary.Cells(14, 2).Address & "+" & _
                                      wsSummary.Cells(16, 2).Address & ")*" & _
                                      wsSummary.Cells(18, 2).Address

    ' Calculate total combined GBP
    wsSummary.Cells(20, 2).Value = wsSummary.Cells(11, 2).Value + wsSummary.Cells(12, 2).Value + _
                                   wsSummary.Cells(15, 2).Value + wsSummary.Cells(19, 2).Value

    ' Calculate total duration for approved and valid returned studies
    Dim totalSeconds As Double
    totalSeconds = 0

    Dim i As Long
    For i = 2 To lastRow
        If (wsData.Cells(i, "J").Value = "APPROVED" Or wsData.Cells(i, "J").Value = "RETURNED") And _
           Not IsEmpty(wsData.Cells(i, "F").Value) And Not IsEmpty(wsData.Cells(i, "G").Value) Then
            ' Convert duration to seconds
            totalSeconds = totalSeconds + (wsData.Cells(i, "H").Value * 86400) ' 86400 seconds in a day
        End If
    Next i

    ' Convert total seconds to duration format
    wsSummary.Cells(23, 2).Value = totalSeconds / 86400 ' Convert back to Excel time format

    ' Calculate hourly rate
    If totalSeconds > 0 Then
        wsSummary.Cells(24, 2).Value = wsSummary.Cells(20, 2).Value / (totalSeconds / 3600) ' Convert seconds to hours
    End If

    ' Format numbers
    With wsSummary
        .Range("B11:B12,B15,B19:B20").NumberFormat = "£#,##0.00"
        .Range("B13:B14,B16").NumberFormat = "[$$-en-US]#,##0.00"
        .Range("B23").NumberFormat = "[h]:mm:ss"
        .Range("B24").NumberFormat = "£#,##0.00"
        .Range("B18").NumberFormat = "0.000000"
    End With

    ' Add styling to the summary sheet
    With wsSummary
        ' Set dark background and remove gridlines
        .Cells.Interior.Color = RGB(32, 32, 32)
        ActiveWindow.DisplayGridlines = False
        .Cells.Font.Color = RGB(255, 255, 255)

        ' AutoFit columns
        .Columns("A").ColumnWidth = 30
	.Columns("B").AutoFit

        ' Main title formatting
        With .Range("A1:B1")
            .Merge
            .Font.Size = 14
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .Interior.Color = RGB(64, 64, 64)
        End With

        ' Section headers formatting
        With .Range("A3,A10,A22")
            .Font.Bold = True
            .Font.Size = 12
            .Interior.Color = RGB(64, 64, 64)
        End With

        ' Add borders to the data ranges
        With .Range("A4:B8,A11:B16,A18:B20,A23:B24")
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Color = RGB(128, 128, 128)
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Color = RGB(128, 128, 128)
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Color = RGB(128, 128, 128)
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Color = RGB(128, 128, 128)
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Color = RGB(128, 128, 128)
        End With

        ' Alternate row coloring for better readability
        With .Range("A4:B8")
            .Interior.Color = RGB(45, 45, 45)
        End With
        With .Range("A11:B16")
            .Interior.Color = RGB(45, 45, 45)
        End With
        With .Range("A23:B24")
            .Interior.Color = RGB(45, 45, 45)
        End With

        ' Exchange rate cell special formatting
        With .Range("B18")
            .Interior.Color = RGB(0, 100, 0)  ' Darker green
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(128, 128, 128)
        End With

        ' Indent labels slightly
        .Range("A4:A8,A11:A16,A18:A20,A23:A24").IndentLevel = 1

        ' Bold totals and important figures
        .Range("B8,B20,B24").Font.Bold = True

        ' Add a thin border around the entire summary
        With .Range(.Cells(1, 1), .Cells(24, 2))
            .BorderAround Weight:=xlThin, Color:=RGB(128, 128, 128)
        End With

        ' Select A1 and zoom to 100%
        .Range("A1").Select
        ActiveWindow.Zoom = 100
    End With

    ' Add tax year summary
    CreateTaxYearSummary wsData, wsSummary

    ' Create trend line chart
    Dim chtTrends As Chart
    Dim tempWs As Worksheet

    ' Create temporary worksheet for data
    On Error Resume Next
    ThisWorkbook.Sheets("TempData").Delete
    Set tempWs = ThisWorkbook.Sheets.Add
    tempWs.Name = "TempData"
    On Error GoTo 0

    ' Create data table for Approved studies by year and month
    With tempWs
        ' Headers
        .Cells(1, 1).Value = "Year"
        .Cells(1, 2).Value = "Month"
        .Cells(1, 3).Value = "Number of Approved Studies"
        .Cells(1, 4).Value = "X-Axis Label"
        .Cells(1, 5).Value = "MonthNum"
        .Cells(1, 6).Value = "Number of Returned Studies"
        .Cells(1, 7).Value = "Number of Rejected Studies"

        Dim chartRow As Long
        chartRow = 2

        Dim yearMonth As Object
        Set yearMonth = CreateObject("Scripting.Dictionary")

        Dim rowNum As Long
        For rowNum = 2 To lastRow
            Dim monthNum As Long
            monthNum = wsData.Cells(rowNum, "M").Value

            ' Convert month number to name
            Dim monthName As String
            Select Case monthNum
                Case 1: monthName = "Jan"
                Case 2: monthName = "Feb"
                Case 3: monthName = "Mar"
                Case 4: monthName = "Apr"
                Case 5: monthName = "May"
                Case 6: monthName = "Jun"
                Case 7: monthName = "Jul"
                Case 8: monthName = "Aug"
                Case 9: monthName = "Sep"
                Case 10: monthName = "Oct"
                Case 11: monthName = "Nov"
                Case 12: monthName = "Dec"
            End Select

            Dim key As String
            key = wsData.Cells(rowNum, "L").Value & "-" & Format(monthNum, "00")

            If Not yearMonth.Exists(key) Then
                yearMonth.Add key, 1

                ' Write data immediately
                .Cells(chartRow, 1).Value = wsData.Cells(rowNum, "L").Value  ' Year
                .Cells(chartRow, 2).Value = monthName  ' Month name
                .Cells(chartRow, 3).Value = 0  ' Approved count
                .Cells(chartRow, 6).Value = 0  ' Returned count
                .Cells(chartRow, 7).Value = 0  ' Rejected count

                ' Increment appropriate counter
                Select Case wsData.Cells(rowNum, "J").Value
                    Case "APPROVED": .Cells(chartRow, 3).Value = 1
                    Case "RETURNED": .Cells(chartRow, 6).Value = 1
                    Case "REJECTED": .Cells(chartRow, 7).Value = 1
                End Select

                chartRow = chartRow + 1
            Else
                ' Find the row with matching year and month and update count
                Dim findRow As Long
                For findRow = 2 To chartRow - 1
                    If .Cells(findRow, 1).Value = wsData.Cells(rowNum, "L").Value And _
                       .Cells(findRow, 2).Value = monthName Then
                        ' Increment appropriate counter
                        Select Case wsData.Cells(rowNum, "J").Value
                            Case "APPROVED": .Cells(findRow, 3).Value = .Cells(findRow, 3).Value + 1
                            Case "RETURNED": .Cells(findRow, 6).Value = .Cells(findRow, 6).Value + 1
                            Case "REJECTED": .Cells(findRow, 7).Value = .Cells(findRow, 7).Value + 1
                        End Select
                        Exit For
                    End If
                Next findRow
            End If
        Next rowNum

        ' Add helper column for month numbers
        .Cells(1, 5).Value = "MonthNum"

        ' Fill in month numbers
        For i = 2 To chartRow - 1
            Select Case .Cells(i, 2).Value
                Case "Jan": .Cells(i, 5).Value = 1
                Case "Feb": .Cells(i, 5).Value = 2
                Case "Mar": .Cells(i, 5).Value = 3
                Case "Apr": .Cells(i, 5).Value = 4
                Case "May": .Cells(i, 5).Value = 5
                Case "Jun": .Cells(i, 5).Value = 6
                Case "Jul": .Cells(i, 5).Value = 7
                Case "Aug": .Cells(i, 5).Value = 8
                Case "Sep": .Cells(i, 5).Value = 9
                Case "Oct": .Cells(i, 5).Value = 10
                Case "Nov": .Cells(i, 5).Value = 11
                Case "Dec": .Cells(i, 5).Value = 12
            End Select
        Next i

        ' Sort the data by year and month number
        .Range("A2:G" & chartRow - 1).Sort _
            Key1:=.Range("A2"), Order1:=xlAscending, _
            Key2:=.Range("E2"), Order2:=xlAscending, _
            Header:=xlNo

        ' Create formatted X-axis labels
        Dim prevYear As String
        Dim currYear As String

        For i = 2 To chartRow - 1
            currYear = .Cells(i, 1).Value

            If currYear <> prevYear Then
                ' If it's a new year, include the year
                .Cells(i, 4).Value = currYear & " - " & .Cells(i, 2).Value
            Else
                ' If it's the same year, just show month
                .Cells(i, 4).Value = .Cells(i, 2).Value
            End If

            prevYear = currYear
        Next i
    End With

    ' Create the chart
    Set chtTrends = wsSummary.Shapes.AddChart2(227, xlLine).Chart

    ' First, modify the data in tempWs to replace zeros with empty cells
    For i = 2 To chartRow - 1
        If tempWs.Cells(i, 6).Value = 0 Then tempWs.Cells(i, 6).ClearContents
        If tempWs.Cells(i, 7).Value = 0 Then tempWs.Cells(i, 7).ClearContents
    Next i

    With chtTrends
        ' Clear any existing series
        Do While .SeriesCollection.count > 0
            .SeriesCollection(1).Delete
        Loop

        ' Format chart area
        .ChartArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)
        .PlotArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)

        ' Add Approved Studies series
        With .SeriesCollection.NewSeries
            .XValues = tempWs.Range("D2:D" & chartRow - 1)
            .values = tempWs.Range("C2:C" & chartRow - 1)
            .Format.Line.ForeColor.RGB = RGB(0, 255, 0)  ' Green
            .Format.Line.Weight = 1.5
            .MarkerStyle = xlMarkerStyleCircle
            .MarkerSize = 4
            .Name = "Approved Studies"

            .HasDataLabels = True
            With .DataLabels
                .ShowSeriesName = False
                .ShowValue = True
                .Position = xlLabelPositionAbove
                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            End With
        End With

        ' Add Returned Studies series
        With .SeriesCollection.NewSeries
            .XValues = tempWs.Range("D2:D" & chartRow - 1)
            .values = tempWs.Range("F2:F" & chartRow - 1)
            .Format.Line.ForeColor.RGB = RGB(255, 165, 0)  ' Orange
            .Format.Line.Weight = 1.5
            .MarkerStyle = xlMarkerStyleCircle
            .MarkerSize = 4
            .Name = "Returned Studies"

            .HasDataLabels = True
            With .DataLabels
                .ShowSeriesName = False
                .ShowValue = True
                .Position = xlLabelPositionAbove
                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            End With
        End With

        ' Add Rejected Studies series
        With .SeriesCollection.NewSeries
            .XValues = tempWs.Range("D2:D" & chartRow - 1)
            .values = tempWs.Range("G2:G" & chartRow - 1)
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)  ' Red
            .Format.Line.Weight = 1.5
            .MarkerStyle = xlMarkerStyleCircle
            .MarkerSize = 3
            .Name = "Rejected Studies"

            .HasDataLabels = True
            With .DataLabels
                .ShowSeriesName = False
                .ShowValue = True
                .Position = xlLabelPositionAbove
                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            End With
        End With

        ' Add legend
        .HasLegend = True
        With .Legend
            .Position = xlBottom
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
        End With

        ' Format Y axis
        With .Axes(xlValue)
            .HasTitle = True
            With .AxisTitle
                .Text = "Number of Studies"
                .Orientation = 90
                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            End With
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .MajorGridlines.Format.Line.ForeColor.RGB = RGB(64, 64, 64)
            .MinimumScale = 0
        End With

        ' Format X axis
        With .Axes(xlCategory)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabelSpacing = 1
            .TickLabels.Orientation = 90
        End With

        ' Add title
        .HasTitle = True
        With .ChartTitle
            .Text = "Number of Studies a Month"
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
        End With

        ' Position the chart
        With .Parent
            .Left = wsSummary.Range("D2").Left
            .Top = wsSummary.Range("D2").Top
            .Width = 800
            .Height = 400
        End With
    End With

    ' Create pivot tables on a new sheet
    Application.DisplayAlerts = False

    ' Create new Pivot sheet
    Dim wsPivot As Worksheet
    On Error Resume Next
    ThisWorkbook.Sheets("Pivot_Analysis").Delete
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsSummary)
    wsPivot.Name = "Pivot_Analysis"
    wsPivot.Parent.Windows(1).DisplayGridlines = False
    On Error GoTo 0

    ' Create pivot tables on the new sheet
    CreateApprovedPivotTable wsData, wsPivot
    CreateReturnedPivotTable wsData, wsPivot
    Application.DisplayAlerts = True

    ' Create bar chart after pivot tables exist
    CreateBarChart tempWs, wsSummary, chartRow
End Sub

Private Sub CreateTaxYearSummary(wsData As Worksheet, wsSummary As Worksheet)
    ' Add header at A27
    With wsSummary
        .Cells(27, 1).Value = "Tax Year 6th Apr - 5th Apr Breakdown"
        .Range("A27:B27").Merge
        .Range("A27").Font.Size = 12
        .Range("A27").Font.Bold = True
        .Range("A27").Interior.Color = RGB(64, 64, 64)
        .Range("A27").HorizontalAlignment = xlCenter
    End With

    ' Get last row of data
    Dim lastRow As Long
    lastRow = wsData.Cells(wsData.Rows.count, "A").End(xlUp).row

    ' Create dictionary to store tax year totals
    Dim taxYearDict As Object
    Set taxYearDict = CreateObject("Scripting.Dictionary")

    ' Exchange rate from cell B18 on summary sheet
    Dim exchangeRate As Double
    exchangeRate = wsSummary.Range("B18").Value

    ' Loop through data
    Dim i As Long
    For i = 2 To lastRow
        If (wsData.Cells(i, "J").Value = "APPROVED" Or _
           (wsData.Cells(i, "J").Value = "RETURNED" And _
            Not IsEmpty(wsData.Cells(i, "F").Value) And _
            Not IsEmpty(wsData.Cells(i, "G").Value))) Then

            ' Get date from columns L (Year) and M (Month)
            Dim studyDate As Date
            studyDate = DateSerial(wsData.Cells(i, "L").Value, wsData.Cells(i, "M").Value, 1)

            ' Determine tax year (April 6th to April 5th)
            Dim taxYear As String
            If Month(studyDate) <= 3 Then
                taxYear = CStr(year(studyDate) - 1) & "/" & CStr(year(studyDate))
            ElseIf Month(studyDate) >= 4 Then
                taxYear = CStr(year(studyDate)) & "/" & CStr(year(studyDate) + 1)
            End If

            ' Calculate total for this entry
            Dim entryTotal As Double
            If wsData.Cells(i, "J").Value = "APPROVED" Then
                entryTotal = wsData.Cells(i, "B").Value + _
                             wsData.Cells(i, "C").Value + _
                             (wsData.Cells(i, "D").Value * exchangeRate) + _
                             (wsData.Cells(i, "E").Value * exchangeRate)
            Else ' RETURNED
                entryTotal = wsData.Cells(i, "C").Value + _
                             (wsData.Cells(i, "E").Value * exchangeRate)
            End If

            ' Add to dictionary
            If taxYearDict.Exists(taxYear) Then
                taxYearDict(taxYear) = taxYearDict(taxYear) + entryTotal
            Else
                taxYearDict.Add taxYear, entryTotal
            End If
        End If
    Next i

    ' Output results
    Dim row As Long
    row = 28
    Dim key As Variant

    ' Sort keys
    Dim keys() As String
    ReDim keys(0 To taxYearDict.count - 1)
    Dim k As Long
    k = 0
    For Each key In taxYearDict.keys
        keys(k) = key
        k = k + 1
    Next key

    ' Simple bubble sort for tax years
    Dim j As Long
    For i = LBound(keys) To UBound(keys) - 1
        For j = i + 1 To UBound(keys)
            If keys(i) > keys(j) Then
                Dim temp As String
                temp = keys(i)
                keys(i) = keys(j)
                keys(j) = temp
            End If
        Next j
    Next i

    ' Output sorted results with formatting
    For i = 0 To UBound(keys)
        With wsSummary
            .Cells(row + i, 1).Value = "Tax Year " & keys(i)
            .Cells(row + i, 2).Value = taxYearDict(keys(i))
            .Cells(row + i, 2).NumberFormat = "£#,##0.00"

            ' Apply formatting
            .Range("A" & row + i & ":B" & row + i).Interior.Color = RGB(45, 45, 45)
            .Cells(row + i, 1).IndentLevel = 1

            ' Add borders
            With .Range("A" & row + i & ":B" & row + i)
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Color = RGB(128, 128, 128)
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Color = RGB(128, 128, 128)
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeTop).Color = RGB(128, 128, 128)
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Color = RGB(128, 128, 128)
            End With
        End With
    Next i

    ' Add border around entire section
    With wsSummary.Range("A27:B" & row + UBound(keys))
        .BorderAround Weight:=xlThin, Color:=RGB(128, 128, 128)
    End With
End Sub

Public Sub CreateApprovedPivotTable(wsData As Worksheet, wsPivot As Worksheet)
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim lastRow As Long

    ' Delete existing pivot table if it exists
    On Error Resume Next
    wsPivot.PivotTables("ApprovedPivotTable").TableRange2.Clear
    wsPivot.PivotTables("ApprovedPivotTable").Delete
    On Error GoTo 0

    ' Create pivot cache from data
    lastRow = wsData.Cells(wsData.Rows.count, "A").End(xlUp).row
    Set pvtCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsData.Range("A1:M" & lastRow), _
        Version:=xlPivotTableVersion15)

    ' Create Approved Studies pivot table starting at A4
    Set pvt = pvtCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A4"), _
        TableName:="ApprovedPivotTable", _
        DefaultVersion:=xlPivotTableVersion15)

    ' Change Row Labels caption to Year
    On Error Resume Next
    pvt.RowAxisLayout xlTabularRow
    pvt.PivotFields("Row Labels").Name = "Year"
    On Error GoTo 0

    ' Add title above pivot table after creation
    With wsPivot.Range("A3")
        .Value = "Approved Studies Breakdown Analysis"
        .Font.Bold = True
        .Font.Size = 12
    End With

    ' Configure pivot table
    With pvt
        ' Add Year field to rows
        .PivotFields("Year").Orientation = xlRowField
        .PivotFields("Year").Position = 1

        ' Add Month field to rows and format
        With .PivotFields("Month")
            .Orientation = xlRowField
            .Position = 2

            ' Create month names array
            Dim monthNames(1 To 12) As String
            monthNames(1) = "January"
            monthNames(2) = "February"
            monthNames(3) = "March"
            monthNames(4) = "April"
            monthNames(5) = "May"
            monthNames(6) = "June"
            monthNames(7) = "July"
            monthNames(8) = "August"
            monthNames(9) = "September"
            monthNames(10) = "October"
            monthNames(11) = "November"
            monthNames(12) = "December"

            ' Apply month names to pivot items
            Dim i As Long
            For i = 1 To 12
                On Error Resume Next
                .PivotItems(CStr(i)).Caption = monthNames(i)
                On Error GoTo 0
            Next i

            ' Group by quarters
            On Error Resume Next
            .Group Start:=1, End:=12, By:=3
            On Error GoTo 0
        End With

        ' Add value fields
        With .AddDataField(.PivotFields("Reward (£)"), "Total Reward (£)", xlSum)
            .NumberFormat = "£#,##0.00"
        End With

        With .AddDataField(.PivotFields("Reward ($)"), "Total Reward ($)", xlSum)
            .NumberFormat = "[$$-en-US]#,##0.00"
        End With

        With .AddDataField(.PivotFields("Bonus (£)"), "Total Bonus (£)", xlSum)
            .NumberFormat = "£#,##0.00"
        End With

        With .AddDataField(.PivotFields("Bonus ($)"), "Total Bonus ($)", xlSum)
            .NumberFormat = "[$$-en-US]#,##0.00"
        End With

        With .AddDataField(.PivotFields("Duration"), "Total Duration", xlSum)
            .NumberFormat = "[h]:mm:ss"
        End With

        ' Filter for APPROVED studies only
        On Error Resume Next
        With .PivotFields("Status")
            .Orientation = xlPageField
            .CurrentPage = "APPROVED"
            .EnableMultiplePageItems = False
        End With
        On Error GoTo 0

        ' Format pivot table
        .ShowTableStyleRowStripes = True
        .TableStyle2 = "PivotStyleMedium15"

        ' Collapse all field items
        .PivotFields("Year").ShowDetail = False
    End With
End Sub

Public Sub CreateReturnedPivotTable(wsData As Worksheet, wsPivot As Worksheet)
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim lastRow As Long

    ' Delete existing pivot table if it exists
    On Error Resume Next
    wsPivot.PivotTables("ReturnedPivotTable").TableRange2.Clear
    wsPivot.PivotTables("ReturnedPivotTable").Delete
    On Error GoTo 0

    ' Create pivot cache from data
    lastRow = wsData.Cells(wsData.Rows.count, "A").End(xlUp).row
    Set pvtCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsData.Range("A1:M" & lastRow), _
        Version:=xlPivotTableVersion15)

    ' Create Returned Studies pivot table starting at J4
    Set pvt = pvtCache.CreatePivotTable( _
        TableDestination:=wsPivot.Range("J4"), _
        TableName:="ReturnedPivotTable", _
        DefaultVersion:=xlPivotTableVersion15)

    ' Change Row Labels caption to Year
    On Error Resume Next
    pvt.RowAxisLayout xlTabularRow
    pvt.PivotFields("Row Labels").Name = "Year"
    On Error GoTo 0

    ' Add title above pivot table after creation
    With wsPivot.Range("J3")
        .Value = "Returned Studies Breakdown Analysis"
        .Font.Bold = True
        .Font.Size = 12
    End With

    ' Configure pivot table
    With pvt
        ' Add Year field to rows
        .PivotFields("Year").Orientation = xlRowField
        .PivotFields("Year").Position = 1

        ' Add Month field to rows and format
        With .PivotFields("Month")
            .Orientation = xlRowField
            .Position = 2

            ' Create month names array
            Dim monthNames(1 To 12) As String
            monthNames(1) = "January"
            monthNames(2) = "February"
            monthNames(3) = "March"
            monthNames(4) = "April"
            monthNames(5) = "May"
            monthNames(6) = "June"
            monthNames(7) = "July"
            monthNames(8) = "August"
            monthNames(9) = "September"
            monthNames(10) = "October"
            monthNames(11) = "November"
            monthNames(12) = "December"

            ' Apply month names to pivot items
            Dim i As Long
            For i = 1 To 12
                On Error Resume Next
                .PivotItems(CStr(i)).Caption = monthNames(i)
                On Error GoTo 0
            Next i

            ' Group by quarters
            On Error Resume Next
            .Group Start:=1, End:=12, By:=3
            On Error GoTo 0
        End With

        ' Add only Bonus fields
        With .AddDataField(.PivotFields("Bonus (£)"), "Total Bonus (£)", xlSum)
            .NumberFormat = "£#,##0.00"
        End With

        With .AddDataField(.PivotFields("Bonus ($)"), "Total Bonus ($)", xlSum)
            .NumberFormat = "[$$-en-US]#,##0.00"
        End With

        With .AddDataField(.PivotFields("Duration"), "Total Duration", xlSum)
            .NumberFormat = "[h]:mm:ss"
        End With

        ' Filter for RETURNED studies only
        On Error Resume Next
        With .PivotFields("Status")
            .Orientation = xlPageField
            .CurrentPage = "RETURNED"
            .EnableMultiplePageItems = False
        End With
        On Error GoTo 0

        ' Format pivot table
        .ShowTableStyleRowStripes = True
        .TableStyle2 = "PivotStyleMedium15"

        ' Collapse all field items
        .PivotFields("Year").ShowDetail = False
    End With
End Sub

Private Sub CreateBarChart(tempWs As Worksheet, wsSummary As Worksheet, chartRow As Long)
    Dim wsData As Worksheet
    Set wsData = ThisWorkbook.Sheets("Processed_Data")

    ' Get last row
    Dim lastRow As Long
    lastRow = wsData.Cells(wsData.Rows.count, "A").End(xlUp).row

    ' Create chart first
    Dim chtBars As Chart
    Set chtBars = wsSummary.Shapes.AddChart2(227, xlColumnStacked).Chart

    ' Create dictionary for our data
    Dim monthDict As Object
    Set monthDict = CreateObject("Scripting.Dictionary")

    ' First pass - collect data
    Dim i As Long
    For i = 2 To lastRow
        If wsData.Cells(i, "J").Value = "APPROVED" Or _
           (wsData.Cells(i, "J").Value = "RETURNED" And _
            Not IsEmpty(wsData.Cells(i, "F").Value) And _
            Not IsEmpty(wsData.Cells(i, "G").Value)) Then

            Dim key As String
            key = Format(wsData.Cells(i, "L").Value, "0000") & Format(wsData.Cells(i, "M").Value, "00")

            If Not monthDict.Exists(key) Then
                Dim monthLabel As String
                monthLabel = Format(DateSerial(wsData.Cells(i, "L").Value, wsData.Cells(i, "M").Value, 1), "mmm")
                monthDict.Add key, Array(monthLabel, 0, 0, 0, 0)
            End If

            Dim values As Variant
            values = monthDict(key)

            If wsData.Cells(i, "J").Value = "APPROVED" Then
                values(1) = values(1) + wsData.Cells(i, "B").Value  ' Reward GBP
                values(2) = values(2) + wsData.Cells(i, "C").Value  ' Bonus GBP
                values(3) = values(3) + wsData.Cells(i, "D").Value  ' Reward USD
                values(4) = values(4) + wsData.Cells(i, "E").Value  ' Bonus USD
            Else  ' RETURNED
                values(2) = values(2) + wsData.Cells(i, "C").Value  ' Bonus GBP
                values(4) = values(4) + wsData.Cells(i, "E").Value  ' Bonus USD
            End If

            monthDict(key) = values
        End If
    Next i

    ' Convert dictionary to arrays
    Dim monthCount As Long
    monthCount = monthDict.count

    Dim monthLabels() As String
    Dim rewardGBP() As Double
    Dim bonusGBP() As Double
    Dim rewardUSD() As Double
    Dim bonusUSD() As Double

    ReDim monthLabels(0 To monthCount - 1)
    ReDim rewardGBP(0 To monthCount - 1)
    ReDim bonusGBP(0 To monthCount - 1)
    ReDim rewardUSD(0 To monthCount - 1)
    ReDim bonusUSD(0 To monthCount - 1)

    ' Sort keys
    Dim keys() As String
    ReDim keys(0 To monthCount - 1)
    Dim k As Long
    k = 0
    Dim keyVar As Variant
    For Each keyVar In monthDict.keys
        keys(k) = keyVar
        k = k + 1
    Next keyVar

    ' Sort the keys array
    Call QuickSort(keys, 0, monthCount - 1)

    ' Fill arrays in sorted order
    Dim isFirstEntry As Boolean
    isFirstEntry = True
    Dim yearNum As Long, monthNum As Long

    For i = 0 To monthCount - 1
        values = monthDict(keys(i))

        ' Get year and month from the key
        yearNum = CLng(Left(keys(i), 4))
        monthNum = CLng(Right(keys(i), 2))

        ' Format label
        If isFirstEntry Then
            ' First entry - always show year
            monthLabels(i) = yearNum & " - " & values(0)
            isFirstEntry = False
        ElseIf monthNum = 1 Then
            ' January - show year
            monthLabels(i) = yearNum & " - " & values(0)
        Else
            ' Other months - just show month
            monthLabels(i) = values(0)
        End If

        rewardGBP(i) = values(1)
        bonusGBP(i) = values(2)
        rewardUSD(i) = values(3)
        bonusUSD(i) = values(4)
    Next i

    ' Create the chart
    With chtBars
        .ChartType = xlColumnStacked

        ' Clear any existing series
        Do While .SeriesCollection.count > 0
            .SeriesCollection(1).Delete
        Loop

        ' Add series
        With .SeriesCollection.NewSeries
            .Name = "Total Reward (£)"
            .values = rewardGBP
            .XValues = monthLabels
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)  ' Dark Green
        End With

        With .SeriesCollection.NewSeries
            .Name = "Total Bonus (£)"
            .values = bonusGBP
            .XValues = monthLabels
            .Format.Fill.ForeColor.RGB = RGB(146, 208, 80)  ' Light Green
        End With

        With .SeriesCollection.NewSeries
            .Name = "Total Reward ($)"
            .values = rewardUSD
            .XValues = monthLabels
            .Format.Fill.ForeColor.RGB = RGB(0, 112, 192)  ' Dark Blue
        End With

        With .SeriesCollection.NewSeries
            .Name = "Total Bonus ($)"
            .values = bonusUSD
            .XValues = monthLabels
            .Format.Fill.ForeColor.RGB = RGB(91, 155, 213)  ' Light Blue
        End With

        ' Format chart
        .ChartArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)
        .PlotArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)

        ' Format axes
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Text = "Amount"
            .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.NumberFormat = "#,##0.00"  ' Removed £ symbol
            .MajorGridlines.Format.Line.ForeColor.RGB = RGB(64, 64, 64)
        End With

        With .Axes(xlCategory)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabelSpacing = 1  ' Show all labels
            .TickLabels.Orientation = 90  ' Vertical labels
        End With

        ' Add title and legend
        .HasTitle = True
        .ChartTitle.Text = "Monthly Rewards and Bonuses"
        .ChartTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)

        .HasLegend = True
        .Legend.Position = xlBottom
        .Legend.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)

        ' Position the chart
        With .Parent
            .Left = wsSummary.Range("D29").Left
            .Top = wsSummary.Range("D29").Top
            .Width = 800
            .Height = 400
        End With
    End With
End Sub

' Helper function for sorting
Private Sub QuickSort(arr() As String, low As Long, high As Long)
    Dim pivot As String
    Dim tmp As String
    Dim i As Long
    Dim j As Long

    If low < high Then
        pivot = arr((low + high) \ 2)
        i = low
        j = high

        Do
            Do While arr(i) < pivot
                i = i + 1
            Loop

            Do While arr(j) > pivot
                j = j - 1
            Loop

            If i <= j Then
                tmp = arr(i)
                arr(i) = arr(j)
                arr(j) = tmp
                i = i + 1
                j = j - 1
            End If
        Loop Until i > j

        If low < j Then QuickSort arr, low, j
        If i < high Then QuickSort arr, i, high
    End If
End Sub

Private Sub TestHourlyRateCalculation()
    ' Prevent screen updating
    Application.ScreenUpdating = False

    Dim wsData As Worksheet
    Dim wsSummary As Worksheet

    ' Get the required worksheets
    Set wsData = ThisWorkbook.Sheets("Processed_Data")
    Set wsSummary = ThisWorkbook.Sheets("Summary")

    ' Create temporary storage for our calculations
    Dim tempWs As Worksheet

    ' Create/recreate temp worksheet
    On Error Resume Next
    ThisWorkbook.Sheets("TempHourlyRate").Delete
    Set tempWs = ThisWorkbook.Sheets.Add
    tempWs.Name = "TempHourlyRate"
    On Error GoTo 0

    ' Setup headers with separate currency columns
    With tempWs
        .Cells(1, 1).Value = "Year"
        .Cells(1, 2).Value = "Month"
        .Cells(1, 3).Value = "Total Hours"
        .Cells(1, 4).Value = "Total GBP"
        .Cells(1, 5).Value = "Total USD"
        .Cells(1, 6).Value = "Hourly Rate"
    End With

    ' Create dictionary to store monthly data
    Dim monthData As Object
    Set monthData = CreateObject("Scripting.Dictionary")

    ' Get last row
    Dim lastRow As Long
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Process each row
    Dim i As Long
    For i = 2 To lastRow
        Dim hasValidTimes As Boolean
        hasValidTimes = Not IsEmpty(wsData.Cells(i, "F").Value) And _
                       Not IsEmpty(wsData.Cells(i, "G").Value)

        ' Calculate earnings and hours based on status
        If wsData.Cells(i, "J").Value = "APPROVED" Or _
           (wsData.Cells(i, "J").Value = "RETURNED" And hasValidTimes) Then

            ' Create key for year-month
            Dim key As String
            key = Format(wsData.Cells(i, "L").Value, "0000") & Format(wsData.Cells(i, "M").Value, "00")

            ' Calculate earnings for this entry
            Dim entryGBP As Double, entryUSD As Double
            If wsData.Cells(i, "J").Value = "APPROVED" Then
                ' For Approved: Include all rewards and bonuses
                entryGBP = wsData.Cells(i, "B").Value + wsData.Cells(i, "C").Value
                entryUSD = wsData.Cells(i, "D").Value + wsData.Cells(i, "E").Value
            Else ' RETURNED with valid times
                ' For Returned: Only include bonuses
                entryGBP = wsData.Cells(i, "C").Value
                entryUSD = wsData.Cells(i, "E").Value
            End If

            ' Only add hours if we have valid times
            Dim entryHours As Double
            If hasValidTimes Then
                entryHours = wsData.Cells(i, "H").Value * 24 ' Convert to hours
            End If

            ' Add or update dictionary entry
            If Not monthData.Exists(key) Then
                monthData.Add key, Array(wsData.Cells(i, "L").Value, _
                                      wsData.Cells(i, "M").Value, _
                                      entryHours, _
                                      entryGBP, _
                                      entryUSD)
            Else
                Dim existingData As Variant
                existingData = monthData(key)
                existingData(2) = existingData(2) + entryHours
                existingData(3) = existingData(3) + entryGBP
                existingData(4) = existingData(4) + entryUSD
                monthData(key) = existingData
            End If
        End If
    Next i

	' Write sorted data
    Dim row As Long
    row = 2

    ' Create and populate keys array
    Dim keys() As String
    ReDim keys(0 To monthData.Count - 1)
    Dim k As Long
    k = 0
    Dim keyVar As Variant
    For Each keyVar In monthData.keys
        keys(k) = keyVar
        k = k + 1
    Next keyVar

    ' Sort keys array
    QuickSort keys, LBound(keys), UBound(keys)

    ' Get exchange rate
    Dim exchangeRate As Double
    exchangeRate = wsSummary.Range("B18").Value

    ' Write sorted data
    For k = LBound(keys) To UBound(keys)
        Dim data As Variant
        data = monthData(keys(k))

        With tempWs
            .Cells(row, 1).Value = data(0)  ' Year
            .Cells(row, 2).Value = monthName(data(1), True)  ' Month
            .Cells(row, 3).Value = data(2) / 24  ' Convert hours back to Excel time format
            .Cells(row, 4).Value = data(3)  ' GBP
            .Cells(row, 5).Value = data(4)  ' USD

            ' Calculate hourly rate including converted USD
            If data(2) > 0 Then
                .Cells(row, 6).Value = (data(3) + (data(4) * exchangeRate)) / data(2)
            Else
                .Cells(row, 6).Value = 0
            End If

            ' Format cells
            .Cells(row, 3).NumberFormat = "[h]:mm:ss"
            .Cells(row, 4).NumberFormat = "£#,##0.00"
            .Cells(row, 5).NumberFormat = "[$$-en-US]#,##0.00"
            .Cells(row, 6).NumberFormat = "£#,##0.00"
        End With
        row = row + 1
    Next k

    ' Autofit columns
    tempWs.Columns("A:F").AutoFit

    ' Hide sheet
    tempWs.Visible = xlSheetVeryHidden
End Sub

Private Sub CreateHourlyRateChartOnSummary()
    Dim tempWs As Worksheet
    Dim wsSummary As Worksheet
    Dim cht As Chart
    Dim cboYear As Shape
    Dim years As Collection
    Dim yr As Variant

    ' Get the required worksheets
    Set tempWs = ThisWorkbook.Sheets("TempHourlyRate")
    Set wsSummary = ThisWorkbook.Sheets("Summary")

    ' Get years collection first
    Set years = GetUniqueYears(tempWs)

    ' Add combobox for year selection at W1
    Set cboYear = wsSummary.Shapes.AddFormControl(xlDropDown, _
        wsSummary.Range("W1").Left, _
        wsSummary.Range("W1").Top, _
        80, 20)

    ' Style the combobox and its surroundings
    With cboYear
        .OnAction = "YearSelected"

        ' Add a label for the dropdown with dark background
        With wsSummary.Range("V1")
            .Value = "Select Year:"
            .Font.Color = RGB(255, 255, 255)
            .Font.Bold = True
            .Interior.Color = RGB(32, 32, 32)  ' Match dropdown background
            .HorizontalAlignment = xlRight
        End With

        ' Add background cell styling for dropdown
        With wsSummary.Range("W1")
            .Interior.Color = RGB(64, 64, 64)
        End With

        ' Populate years
        For Each yr In years
            .ControlFormat.AddItem yr
        Next yr
        .ControlFormat.ListIndex = 1

        ' Position carefully
        .Left = wsSummary.Range("W1").Left + 1  ' Slight offset to align with cell
        .Top = wsSummary.Range("W1").Top + 1
        .Width = wsSummary.Range("W1").Width - 2
        .Height = wsSummary.Range("W1").Height - 2
    End With

    ' Create the chart
    Set cht = wsSummary.Shapes.AddChart2(227, xlColumnClustered).Chart

    ' Store chart name for reference in change event
    cht.Parent.Name = "HourlyRateChart"

    ' Format chart
    With cht
        ' Position the chart
        .Parent.Left = wsSummary.Range("U2").Left
        .Parent.Top = wsSummary.Range("U2").Top
        .Parent.Width = 900
        .Parent.Height = 500

        .ChartArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)
        .PlotArea.Format.Fill.ForeColor.RGB = RGB(32, 32, 32)

        ' Clear any existing series first
        Do While .SeriesCollection.count > 0
            .SeriesCollection(1).Delete
        Loop

        ' Add series for hourly rate
        With .SeriesCollection.NewSeries
            .Name = "Hourly Rate"
            .Format.Fill.ForeColor.RGB = RGB(0, 176, 80)  ' Green

            ' Add data labels
            .HasDataLabels = True
            With .DataLabels
                .ShowValue = False
                .Position = xlLabelPositionOutsideEnd
                .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
                .Format.TextFrame2.TextRange.Font.Size = 9
                .Format.Fill.ForeColor.RGB = RGB(64, 64, 64)
                .Format.Fill.Transparency = 0.7
            End With
        End With

        ' Format axes
        With .Axes(xlValue)
            .HasTitle = True
            .AxisTitle.Text = "Hourly Rate (£)"
            .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.NumberFormat = "£#,##0.00"
            .MajorGridlines.Format.Line.ForeColor.RGB = RGB(64, 64, 64)
        End With

        With .Axes(xlCategory)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
        End With

        ' Add title
        .HasTitle = True
        .ChartTitle.Text = "Monthly Hourly Rate Analysis"
        .ChartTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
        .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 14

        ' Remove legend completely
        .HasLegend = False  ' Changed from True to False
    End With

    ' Add initial data for first year in list
    If years.count > 0 Then
        UpdateChartData cht, tempWs, years(1)
    End If
End Sub

Private Function GetUniqueYears(ws As Worksheet) As Collection
    Dim dict As Object
    Dim cell As Range
    Dim lastRow As Long

    Set dict = CreateObject("Scripting.Dictionary")
    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).row

    ' Get unique years from column A
    For Each cell In ws.Range("A2:A" & lastRow)
        If Not dict.Exists(cell.Value) Then
            dict.Add cell.Value, cell.Value
        End If
    Next cell

    ' Convert to collection and sort
    Set GetUniqueYears = New Collection
    Dim yearArray() As Variant
    yearArray = dict.Items

    ' Sort years
    Dim i As Long, j As Long, temp As Variant
    For i = LBound(yearArray) To UBound(yearArray) - 1
        For j = i + 1 To UBound(yearArray)
            If yearArray(i) > yearArray(j) Then
                temp = yearArray(i)
                yearArray(i) = yearArray(j)
                yearArray(j) = temp
            End If
        Next j
    Next i

    ' Add sorted years to collection
    For i = LBound(yearArray) To UBound(yearArray)
        GetUniqueYears.Add yearArray(i)
    Next i
End Function

Private Sub UpdateChartData(cht As Chart, dataWs As Worksheet, selectedYear As Integer)
    ' Filter data for selected year
    Dim lastRow As Long
    Dim monthValues() As String
    Dim hourlyRates() As Double
    Dim customLabels() As String
    Dim count As Long
    Dim i As Long

    lastRow = dataWs.Cells(dataWs.Rows.count, "A").End(xlUp).row

    ' First count matching rows
    count = 0
    For i = 2 To lastRow
        If dataWs.Cells(i, "A").Value = selectedYear Then
            count = count + 1
        End If
    Next i

    ' Size arrays
    ReDim monthValues(1 To count)
    ReDim hourlyRates(1 To count)
    ReDim customLabels(1 To count)

    ' Fill arrays
    count = 0
    For i = 2 To lastRow
        If dataWs.Cells(i, "A").Value = selectedYear Then
            count = count + 1
            monthValues(count) = dataWs.Cells(i, "B").Value
            hourlyRates(count) = dataWs.Cells(i, "F").Value

            ' Create custom label text
            Dim timeValue As Double
            timeValue = dataWs.Cells(i, "C").Value * 24  ' Convert to hours

            customLabels(count) = "Hours: " & Format(timeValue, "0") & ":" & _
                                 Format(timeValue * 60 Mod 60, "00") & ":" & _
                                 Format(timeValue * 3600 Mod 60, "00") & vbNewLine & _
                                 "Earned: " & Format(dataWs.Cells(i, "D").Value, "£#,##0.00") & vbNewLine & _
                                 "Rate: " & Format(dataWs.Cells(i, "F").Value, "£#,##0.00")
        End If
    Next i

    ' Find maximum hourly rate for scaling
    Dim maxRate As Double
    maxRate = 0
    For i = 1 To count
        If hourlyRates(i) > maxRate Then maxRate = hourlyRates(i)
    Next i

    ' Round up to next whole number and add buffer
    maxRate = Application.WorksheetFunction.Ceiling(maxRate + 2, 1)

    ' Update chart data
    With cht.SeriesCollection(1)
        .values = hourlyRates
        .XValues = monthValues

        ' Update data labels
        .HasDataLabels = False
        .HasDataLabels = True

        With .DataLabels
            .Position = xlLabelPositionOutsideEnd
            .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            .Format.TextFrame2.TextRange.Font.Size = 9
            .Format.Fill.ForeColor.RGB = RGB(64, 64, 64)
            .Format.Fill.Transparency = 0.7
        End With

        ' Set individual label text
        Dim j As Long
        For j = 1 To count
            .Points(j).DataLabel.Text = customLabels(j)
        Next j
    End With

    ' Adjust the plot area and chart formatting
    With cht
        ' Increase overall chart height to accommodate labels
        .Parent.Height = 600

        ' Position plot area using relative coordinates
        With .PlotArea
            .Format.Fill.ForeColor.RGB = RGB(32, 32, 32)
            ' Move the plot area down more
            .Top = .Top + 100  ' Increased from 50 to 100 points
        End With

        ' Set the value axis scaling with different increments
        With .Axes(xlValue)
            .MinimumScale = 6  ' Keep minimum at £6

            ' Adjust maximum scale based on the highest rate
            If maxRate <= 20 Then
                .MaximumScale = WorksheetFunction.Ceiling(maxRate + 2, 1)
                .MajorUnit = 1        ' £1 increments up to £20
                .MinorUnit = 0.5      ' £0.50 minor units
            Else
                ' For rates above £20, round up to next £5 increment
                Dim adjustedMax As Double
                adjustedMax = WorksheetFunction.Ceiling(maxRate, 5)  ' Round to nearest £5
                If adjustedMax = maxRate Then adjustedMax = adjustedMax + 5  ' Add another £5 if exactly divisible

                .MaximumScale = adjustedMax
                .MinimumScale = 0     ' Start from 0 for better scale visibility
                .MajorUnit = 5        ' £5 increments (25, 30, 35, etc.)
                .MinorUnit = 1        ' £1 minor units
            End If

            ' Format axis
            .HasTitle = True
            .AxisTitle.Text = "Hourly Rate (£)"
            .AxisTitle.Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.Font.Color = RGB(255, 255, 255)
            .Format.Line.ForeColor.RGB = RGB(255, 255, 255)
            .TickLabels.NumberFormat = "£#,##0.00"
            .MajorGridlines.Format.Line.ForeColor.RGB = RGB(64, 64, 64)
        End With
    End With
End Sub

Private Sub CreateYearlyRateAnalysis()
    Dim wsSummary As Worksheet
    Set wsSummary = ThisWorkbook.Sheets("Summary")

    ' Find the last used row in column A
    Dim lastRow As Long
    lastRow = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row

    ' Add Yearly Rate Analysis section
    With wsSummary
        ' Insert two blank rows for spacing
        .Rows(lastRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Rows(lastRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        ' Start our section
        .Cells(lastRow + 2, 1).Value = "Yearly Hourly Rate Analysis"
        .Range("A" & lastRow + 2 & ":B" & lastRow + 2).Merge
        .Range("A" & lastRow + 2).Font.Size = 12
        .Range("A" & lastRow + 2).Font.Bold = True
        .Range("A" & lastRow + 2).Interior.Color = RGB(64, 64, 64)
        .Range("A" & lastRow + 2).HorizontalAlignment = xlCenter

        ' Get data from TempHourlyRate sheet
        Dim tempWs As Worksheet
        Set tempWs = ThisWorkbook.Sheets("TempHourlyRate")

        ' Get exchange rate
        Dim exchangeRate As Double
        exchangeRate = wsSummary.Range("B18").Value

        ' Create dictionary to store yearly totals
        Dim yearlyData As Object
        Set yearlyData = CreateObject("Scripting.Dictionary")

        ' Calculate yearly totals
        Dim lastTempRow As Long
        lastTempRow = tempWs.Cells(tempWs.Rows.Count, "A").End(xlUp).Row

        Dim i As Long
        For i = 2 To lastTempRow
            If Not IsEmpty(tempWs.Cells(i, 3).Value) Then
                Dim year As Long
                year = tempWs.Cells(i, 1).Value

                If Not yearlyData.Exists(year) Then
                    yearlyData.Add year, Array(0, 0, 0) ' hours, GBP, USD
                End If

                Dim data As Variant
                data = yearlyData(year)
                data(0) = data(0) + tempWs.Cells(i, 3).Value ' Hours
                data(1) = data(1) + tempWs.Cells(i, 4).Value ' GBP
                data(2) = data(2) + tempWs.Cells(i, 5).Value ' USD
                yearlyData(year) = data
            End If
        Next i

        ' Output yearly data
        Dim row As Long
        row = lastRow + 3

        ' Sort and output years
        Dim years() As Long
        ReDim years(0 To yearlyData.Count - 1)
        Dim k As Long
        k = 0
        Dim yearKey As Variant
        For Each yearKey In yearlyData.keys
            years(k) = yearKey
            k = k + 1
        Next yearKey

        ' Sort years
        Dim j As Long, temp As Long
        For i = LBound(years) To UBound(years) - 1
            For j = i + 1 To UBound(years)
                If years(i) > years(j) Then
                    temp = years(i)
                    years(i) = years(j)
                    years(j) = temp
                End If
            Next j
        Next i

        ' Output sorted data
        For i = LBound(years) To UBound(years)
            data = yearlyData(years(i))

            ' Convert hours from days to actual hours for calculation
            Dim totalHours As Double
            totalHours = data(0) * 24 ' Convert days to hours

            ' Calculate total earnings (GBP + converted USD)
            Dim totalEarnings As Double
            totalEarnings = data(1) + (data(2) * exchangeRate)

            .Cells(row, 1).Value = "Year " & years(i)
            .Cells(row + 1, 1).Value = "Hours"
            .Cells(row + 1, 2).Value = data(0)
            .Cells(row + 1, 2).NumberFormat = "[h]:mm:ss"

            .Cells(row + 2, 1).Value = "Earnings"
            .Cells(row + 2, 2).Value = totalEarnings
            .Cells(row + 2, 2).NumberFormat = "£#,##0.00"

            .Cells(row + 3, 1).Value = "Hourly Rate"
            If totalHours > 0 Then
                .Cells(row + 3, 2).Value = totalEarnings / totalHours
            Else
                .Cells(row + 3, 2).Value = 0
            End If
            .Cells(row + 3, 2).NumberFormat = "£#,##0.00"

            ' Format cells
            .Range("A" & row & ":B" & row + 3).Interior.Color = RGB(45, 45, 45)
            .Range("A" & row + 1 & ":A" & row + 3).IndentLevel = 1

            ' Add borders
            With .Range("A" & row & ":B" & row + 3)
                .Borders(xlEdgeLeft).LineStyle = xlContinuous
                .Borders(xlEdgeLeft).Color = RGB(128, 128, 128)
                .Borders(xlEdgeRight).LineStyle = xlContinuous
                .Borders(xlEdgeRight).Color = RGB(128, 128, 128)
                .Borders(xlEdgeTop).LineStyle = xlContinuous
                .Borders(xlEdgeTop).Color = RGB(128, 128, 128)
                .Borders(xlEdgeBottom).LineStyle = xlContinuous
                .Borders(xlEdgeBottom).Color = RGB(128, 128, 128)
                .Borders(xlInsideHorizontal).LineStyle = xlContinuous
                .Borders(xlInsideHorizontal).Color = RGB(128, 128, 128)
            End With

            row = row + 5
        Next i

        ' Add border around entire section
        With .Range("A" & (lastRow + 2) & ":B" & row - 1)
            .BorderAround Weight:=xlThin, Color:=RGB(128, 128, 128)
        End With

        ' Insert a blank row after the section
        .Rows(row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End With
End Sub

Public Sub YearSelected()
    Dim ws As Worksheet
    Dim cht As Chart
    Dim cboYear As Shape

    Set ws = ActiveSheet
    Set cht = ws.Shapes("HourlyRateChart").Chart
    Set cboYear = ws.Shapes.Range(Array(Application.Caller))(1)

    ' Get selected year
    Dim selectedYear As Integer
    selectedYear = cboYear.ControlFormat.List(cboYear.ControlFormat.ListIndex)

    ' Update chart with selected year
    UpdateChartData cht, ThisWorkbook.Sheets("TempHourlyRate"), selectedYear
End Sub