Option Explicit
Option Private Module ' makes function invisible to Excel user

Global Const use_trends = True ' False  'True

''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Simple Claim Analyser
' Copyright Jan Iwanik 2014-2017. All rights reserved.
' Unauthorised copying is prohibited.
' Under no circumstances can the software be considered a deliverable of any
' contract for services between you and Iwanik Ltd. The software is a separate
' proprietary tool developed and used by Iwanik Ltd. You should delete any
' copies of the software if your contract for services with Iwanik Ltd has finished.
' You must not decrypt or remove access protection from this software.
' The software is provided 'as is' and we take not responsibility for the results of
' your work if it is completed using this software.


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''     A U X I L I A R Y

Private Function get_buckets() As Integer
    get_buckets = 20
End Function


Private Function get_bucket_higher(inval As Double) As Double
    Dim temp2 As Double
    temp2 = 0
    If inval > 0 Then temp2 = Int(Application.Log(inval, 10))
    get_bucket_higher = Application.Power(10, temp2) * Application.Ceiling(inval / Application.Power(10, temp2), 1)
End Function




'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''     M A K I N G   S H E E T S
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' One function to call them all..
Sub analyser_claims()
    If licence() = False Then MsgBox ("The licence has expired."): Exit Sub
    
    Dim pref As String
    pref = clean_input
    
    Dim get_b As Integer, blank_small As Integer
    get_b = get_buckets()
    
    blank_small = make_histograms()
    
    Call make_trends
    Call make_large
           
    
    ' solve small claims CDF
    ' solve distribution for small claims
    Range("L6").FormulaArray = "=max(abs(L" & 12 + blank_small & ":L" & get_b + 11 & " -M" & 12 + blank_small & ":M" & 11 + get_b & ") )"
    Range("M6").Formula = "=IF(L6<1.3581/sqrt(SUM(K12:K" & get_b + 11 & ")), ""KS 5% asympt fit"", ""KS 5% asymp not fit"")"
    SolverReset
    SolverOk SetCell:="$L$6", MaxMinVal:=2, ValueOf:="0", ByChange:="L4:L5"
    SolverAdd cellRef:=Range("$L$5"), relation:=3, formulaText:="0"
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    
    ' remove wrapping of text
    rows("12:12").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .WrapText = False
    End With
    
    ' get discretised distributions
    Call make_discretised
    
    ' Setting column width....
    Range("A:A").Select
    Selection.ColumnWidth = 8
    Range("B:AB").Select
    Selection.ColumnWidth = 10
    ''Selection.EntireColumn.AutoFit
    Range("F:F,N:N,U:V,AF:AF").Select
    Selection.ColumnWidth = 2
    Call fontReset("claims_in")
    If use_trends = False Then
        Columns("N:U").Select
        Selection.EntireColumn.Hidden = True
    End If
    Range("A1").Select
    
    
End Sub






'
' Cleanses and summarizes input data
Private Function clean_input() As String

    ' make sheet if neeeded
    Call addSheet("claims_in")
    Sheets("claims_in").Activate
    Call set_style("A:A", "general")

    ' no data entered - make some up
    If Range("A1").Value <> "Simple Claim Analyser (c) Jan Iwanik" And Range("A12").Value = "" Then
        Range("A12").Value = 1
        Range("A13").Value = 2
        Range("A14").Value = 3
        Range("A15").Value = 4
        Range("A16").Value = 5
        Range("A17").Value = 6
        Range("A18").Value = 7
        Range("A19").Value = 8
        Range("A20").Value = 9
        Range("A21").Value = 10
        Range("A22").Value = 11
        Range("A23").Value = 12
        Range("A24").Value = 13
        Range("A25").Value = 14
        Range("A26").Value = 15
        Range("A27").Value = 16
        Range("B12").Value = "2014-02-12"
        Range("B13").Value = "2014-12-12"
        Range("B14").Value = "2015-01-07"
        Range("B15").Value = "2015-11-17"
        Range("B16").Value = "2015-12-04"
        Range("B17").Value = "2015-12-24"
        Range("B18").Value = "2015-12-01"
        Range("B19").Value = "2015-12-29"
        Range("B20").Value = "2014-02-12"
        Range("B21").Value = "2014-12-12"
        Range("B22").Value = "2015-01-07"
        Range("B23").Value = "2015-11-17"
        Range("B24").Value = "2015-12-04"
        Range("B25").Value = "2015-12-24"
        Range("B26").Value = "2015-12-01"
        Range("B27").Value = "2015-12-29"
        Range("C12").Value = 555555
        Range("C13").Value = 260667
        Range("C14").Value = 735522
        Range("C15").Value = 1011010
        Range("C16").Value = 1101099
        Range("C17").Value = 199033
        Range("C18").Value = 1107392
        Range("C19").Value = 3468712
        Range("C20").Value = 388889
        Range("C21").Value = 182467
        Range("C22").Value = 514865
        Range("C23").Value = 707707
        Range("C24").Value = 770769
        Range("C25").Value = 139323
        Range("C26").Value = 775174
        Range("C27").Value = 2428098

    End If
    
    ' make info
    Range("A1").Value = "Simple Claim Analyser (c) Jan Iwanik"
    Call set_style("A1", "bold")
    Range("A2").Value = "input data here in 'Raw claims' table, into the exact columns below, do not add extra columns or move header row"
    Range("A3").Value = "claims must already be unique by 'Claim ID'"
    Call set_style("A2:A3", "italic")

    ' make input areas
    Range("A5").Value = "Ignore below"
    If Range("C5").Value = "" Then Range("C5").Value = 10
    Range("A6").Value = "Large threshold"
    If Range("C6").Value = "" Then Range("C6").Value = 1500000
    Call set_style("C5:C6", "integer")
    Range("A7").Value = "Analyse by"
    If Range("C7").Value = "" Then Range("C7").Value = "quarter"
    Call set_style("A5:B7", "headernowrap")
    Call set_style("C5:C7", "input")
    Range("D5").Value = "As of date"
    If Range("E5").Value = "" Then Range("E5").Value = "2016-01-01"
    Range("D6").Value = "Future date"
    If Range("E6").Value = "" Then Range("E6").Value = "2019-01-01"
    Call set_style("D5:D6", "headernowrap")
    Call set_style("E5:E6", "input")
    Call set_style("E5:E6", "date")
    Range("C8").Select
    Selection.HorizontalAlignment = xlRight
    Call set_validation("C7", "month,quarter,year")
    
    ' make table headers & formats
    Range("A11").Value = "Claim ID"
    Range("B11").Value = "Accident date"
    Range("C11").Value = "Incurred amount"
    Range("D11").Value = "Incurred trended"
    Call set_style("A11:D11", "header")
    Call set_style("B12:B1048576", "date")
    Range("K4").Value = "Mu"
    Range("K5").Value = "Sigma"
    Range("K6").Value = "KS error"
    Range("K8").Value = "P(x<low th'd)"
    Range("K9").Value = "P(x<high th'd)"
    Call set_style("K4:K6", "headernowrap")
    Call set_style("L4:L6", "0000")
    Call set_style("K8:K9", "headernowrap")
    Call set_style("L8:L9", "0000")
        
    Dim threshold1 As Double, threshold2 As Double
    threshold1 = Range("C5").Value
    threshold2 = Range("C6").Value
                
    Dim this_period As Date, period_start As Date, period_stop As Date
    Dim form_y As String, form_q As String, form_m As String, form_min
    Dim claim_count As Long
    claim_count = count_rows_from("A", 12)
    Call set_style("A12:C" & 11 + claim_count, "input")
    Call set_style("A" & 12 + claim_count & ":D1048576", "normal")
    
    form_y = "DATE(YEAR(B12:B" & claim_count + 11 & "),6,30)"
    form_q = "DATE(YEAR(B12:B" & claim_count + 11 & "),2+INT((MONTH(B12:B" & claim_count + 11 & ")-1)/3)*3,15)"
    form_m = "DATE(YEAR(B12:B" & claim_count + 11 & "),MONTH(B12:B" & claim_count + 11 & "),15)"
    Range("B9").FormulaArray = "=min(IF(C7=""year""   , " & form_y & ",IF(C7=""quarter"", " & form_q & ", " & form_m & " )))"
    Range("B10").FormulaArray = "=max(IF(C7=""year""   , " & form_y & ",IF(C7=""quarter"", " & form_q & ", " & form_m & " )))"
    
    ' approx distribution
    If Range("L4").Value = "" Then Range("L4").Formula = "=ln(($C$5+$C$6)/4)"
    If Range("L5").Value = "" Then Range("L5").Formula = "=1"
    
    Range("G12:AG1048576").Clear
    Call set_style("B9:B10", "date")
End Function





'
' Makes histograms & CDFs
Private Function make_histograms()
    
    Application.ScreenUpdating = False
    
    'copy headers
    Range("G11").Value = "Incurred bucket"
    Range("H11").Value = "Claim count"
    Range("I11").Value = "Excess curve"
    Range("J11").Value = "Incurred capped bucket"
    Range("K11").Value = "Claim count"
    Range("L11").Value = "Small CDF conditional"
    Range("M11").Value = "Fitted Small CDF cond"
    Call set_style("G11:M11", "header")
    
    ' get buckets for rounding - and round claims
    Dim bucket1_high As Double, bucket2_high As Double
    Dim bucket1_step As Double, bucket2_step As Double
    Dim claim_count As Long
    claim_count = count_rows_from("A", 12)
    Call set_style("C10:D1048576", "integer")
    
    Range("C10").Formula = "=max(C12:C" & claim_count + 11 & ")"
    Range("C9").FormulaArray = "=sum( --(C12:C" & claim_count + 11 & ">= $C$5) * C12:C" & claim_count + 11 & ")"
    bucket1_high = get_bucket_higher(Range("C10").Value)
    bucket2_high = Range("C6").Value
    bucket1_step = bucket1_high / get_buckets()
    bucket2_step = bucket2_high / get_buckets()
    
    ' Make formulas
    Dim I As Integer, get_b As Integer, blank_small As Integer
    get_b = get_buckets()
    For I = 1 To get_b
        Range("G" & I + 11).Value = I * bucket1_step
        Range("H" & I + 11).Formula = "=sumproduct( --($C$12:$C$" & claim_count + 11 & "<= G" & I + 11 & "), " & _
                                                  " --($C$12:$C$" & claim_count + 11 & ">  iferror(value(G" & I + 10 & "),0)), " & _
                                                  " --($C$12:$C$" & claim_count + 11 & ">= $C$5) )"
        Range("I" & I + 11).FormulaArray = "=sum( --($C$12:$C$" & claim_count + 11 & ">= $C$5) * " & _
                                                " if($C$12:$C$" & claim_count + 11 & "<=G" & I + 11 & " , $C$12:$C$" & claim_count + 11 & ", G" & I + 11 & ")) / C9"
        Range("J" & I + 11).Value = I * bucket2_step
        Range("K" & I + 11).Formula = "=sumproduct( --($C$12:$C$" & claim_count + 11 & "<= J" & I + 11 & "), " & _
                                                  " --($C$12:$C$" & claim_count + 11 & ">  iferror(value(J" & I + 10 & "),0)), " & _
                                                  " --($C$12:$C$" & claim_count + 11 & ">= $C$5) )"
        Range("L" & I + 11).FormulaArray = "=if(J" & I + 11 & "<=$C$5, """", " & _
                                                  "sumproduct( --($D$12:$D$" & claim_count + 11 & "< J" & I + 11 & "), " & _
                                                  " --($D$12:$D$" & claim_count + 11 & ">= $C$5) ) / " & _
                                                  " sumproduct( --($D$12:$D$" & claim_count + 11 & "< $C$6), " & _
                                                  " --($D$12:$D$" & claim_count + 11 & ">= $C$5) )) "
        Range("M" & I + 11).Formula = "=iferror(if(J" & I + 11 & "<=$c$5, """", " & _
                                               "   (norm.dist((ln(J" & I + 11 & ")- $L$4)/$L$5, 0, 1, 1) - " & _
                                               "    norm.dist((ln($C$5)           - $L$4)/$L$5, 0, 1, 1) ) / " & _
                                               "   (norm.dist((ln($C$6)           - $L$4)/$L$5, 0, 1, 1) -" & _
                                               "    norm.dist((ln($C$5)           - $L$4)/$L$5, 0, 1, 1)) ), 999)"
        If Range("J" & I + 11).Value <= Range("C5").Value Then blank_small = blank_small + 1
    Next I
    
    Call set_style("G12:K" & I + 11, "integer")
    Call set_style("I12:I" & I + 11, "percent")
    Call set_style("L12:M" & I + 11, "percent")
        
    Range("L8").Formula = "=NORM.DIST((LN($C$5)-$L$4)/$L$5, 0,1,1)"
    Range("L9").Formula = "=NORM.DIST((LN($C$6)-$L$4)/$L$5, 0,1,1)"
    
    ' make charts
    Call deleteChart("hist1")
    Range("$H$11:$H$" & 11 + get_b).Select
    ActiveSheet.Shapes.AddChart(201, xlColumnClustered).Select
    With ActiveChart
        .Parent.name = "hist1"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Histogram uncapped no-trend"
        .Parent.Top = Range("G14").Top
        .Parent.Left = Range("G14").Left
        .Parent.Width = Range("G14:K29").Width
        .Parent.Height = Range("G14:K29").Height
    End With
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).XValues = "=claims_in!$G$12:$G$" & 11 + get_buckets()
    Call set_style_chart
    
    
    Call deleteChart("hist2")
    Range("$K$11:$K$" & 11 + get_b).Select
    ActiveSheet.Shapes.AddChart(201, xlColumnClustered).Select
    With ActiveChart
        .Parent.name = "hist2"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Histogram capped no-trend"
        .Parent.Top = Range("G33").Top
        .Parent.Left = Range("G33").Left
        .Parent.Width = Range("G33:K48").Width
        .Parent.Height = Range("G33:K48").Height
    End With
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).XValues = "=claims_in!$J$12:$J$" & 11 + get_buckets()
    
    
    Call deleteChart("cummulat")
    Range("$I$11:$I$" & 11 + get_b).Select
    ActiveSheet.Shapes.AddChart(201, xlLine).Select
    With ActiveChart
        .Parent.name = "cummulat"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Excess curve"
        .Parent.Top = Range("G52").Top
        .Parent.Left = Range("G52").Left
        .Parent.Width = Range("G52:K67").Width
        .Parent.Height = Range("G52:K67").Height
    End With
    ActiveChart.ChartType = xlLine
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).XValues = "=claims_in!$G$12:$G$" & 11 + get_buckets()
    Call set_style_chart
    
    
    Call deleteChart("smallcdf")
    Range("$L$" & blank_small + 12 & ":$M$" & 11 + get_b).Select
    ActiveSheet.Shapes.AddChart(201, xlLine).Select
    With ActiveChart
        .Parent.name = "smallcdf"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Small claims CDF"
        .Parent.Top = Range("G71").Top
        .Parent.Left = Range("G71").Left
        .Parent.Width = Range("G71:K86").Width
        .Parent.Height = Range("G71:K86").Height
    End With
    ActiveChart.ChartType = xlLine
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SeriesCollection(1).XValues = "=claims_in!$J$" & blank_small + 12 & ":$J$" & 11 + get_buckets()
    Call set_style_chart
        
        
    make_histograms = blank_small
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Function
    


'
' Makes histograms
Private Sub make_trends()
        
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Columns("N:U").Select
    Selection.EntireColumn.Hidden = False

    'make params
    Dim claim_count As Long
    claim_count = count_rows_from("A", 12)
    Range("O4").Value = "Annual trend"
    
    Range("O5").Value = "Annual trend selected"
    If Range("Q5").Formula = "" Then If Range("Q5").Value = "" Then Range("Q5").Formula = "=Q4"
    Range("O6").Value = "Future trend selected"
    If Range("Q6").Formula = "" Then If Range("Q6").Value = "" Then Range("Q6").Formula = "=Q5"
    Range("O7").Value = "Mid point of data"
    Range("O8").Value = "Large loss load (naive)"
    Range("O9").Value = "Large loss load (trended)"
    Range("Q8").FormulaArray = "=sum(if(C12:C" & claim_count + 11 & ">$C$5,1,0) *  C12:C" & claim_count + 11 & " ) / " & _
                        "sum(if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(C12:C" & claim_count + 11 & ">$C$6, $C$6,C12:C" & claim_count + 11 & " ))"
    Range("Q9").FormulaArray = "=sum(if(C12:C" & claim_count + 11 & ">$C$5,1,0) *  D12:D" & claim_count + 11 & " ) / " & _
                        "sum(if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(D12:D" & claim_count + 11 & ">$C$6, $C$6,D12:D" & claim_count + 11 & " ))"
    
    Call set_style("O4:P9", "headernowrap")
    Call set_style("Q4:Q9", "0000")
    Call set_style("Q5:Q6", "input")
    Call set_style("Q7", "date")

    'copy headers
    Range("O11").Value = "Accident period"
    Range("P11").Value = "Count"
    Range("Q11").Value = "Weight"
    Range("R11").Value = "Average claim"
    Range("S11").Value = "Linear trend"
    Range("T11").Value = "Multipl trend"
    Call set_style("O11:T11", "header")
    
    'iterate periods
    Dim data_in_sheet  As String, period_name As String
    Dim period_length As Long, trend_points As Long
    Dim this_period As Date
    Dim period_start As Date
    Dim period_stop As Date
    period_start = Range("B9")
    period_stop = Range("B10")
    period_name = Range("C7")
    If period_start <= 0 Or period_stop <= 0 Then Exit Sub
    If period_name = "year" Then period_length = 12
    If period_name = "quarter" Then period_length = 3
    If period_name = "month" Then period_length = 1
    
    Dim form_y As String, form_q As String, form_m As String, form_min
    form_y = "DATE(YEAR($B$12:$B$" & claim_count + 11 & "),6,30)"
    form_q = "DATE(YEAR($B$12:$B$" & claim_count + 11 & "),2+INT((MONTH($B$12:$B$" & claim_count + 11 & ")-1)/3)*3,15)"
    form_m = "DATE(YEAR($B$12:$B$" & claim_count + 11 & "),       MONTH($B$12:$B$" & claim_count + 11 & "),15)"
    form_min = "--($C$12:$C$" & claim_count + 11 & " >= $C$5" & ")"
    
    this_period = period_start
    trend_points = 0
    Do Until this_period > period_stop
        Range("O" & 12 + trend_points).Value = this_period
        If (Range("C7").Value = "year") Then
            Range("P" & 12 + trend_points).Formula = "=SUMPRODUCT(--(" & form_y & "=O" & 12 + trend_points & "), " & form_min & ")"
            Range("R" & 12 + trend_points).FormulaArray = "=iferror(SUM(if(" & form_y & "=O" & 12 + trend_points & ", 1, 0) * " & form_min & _
                                                          "* if($C$12:$C$" & claim_count + 11 & "<$C$6, $C$12:$C$" & claim_count + 11 & ", $C$6))" & _
                                                          " / P" & 12 + trend_points & ",0.000001)"
            End If
        If (Range("C7").Value = "quarter") Then
            Range("P" & 12 + trend_points).Formula = "=SUMPRODUCT(--(" & form_q & "=O" & 12 + trend_points & "), " & form_min & ") "
            Range("R" & 12 + trend_points).FormulaArray = "=iferror(SUM(if(" & form_q & "=O" & 12 + trend_points & ",1,0) * " & form_min & _
                                                          "* if($C$12:$C$" & claim_count + 11 & "<$C$6, $C$12:$C$" & claim_count + 11 & ", $C$6))" & _
                                                          " / P" & 12 + trend_points & ",0.000001)"
            End If
        If (Range("C7").Value = "month") Then
            Range("P" & 12 + trend_points).Formula = "=SUMPRODUCT(--(" & form_m & "=O" & 12 + trend_points & "), " & form_min & ")"
            Range("R" & 12 + trend_points).FormulaArray = "=iferror(SUM(if(" & form_m & "=O" & 12 + trend_points & ",1,0) * " & form_min & _
                                                          "* if($C$12:$C$" & claim_count + 11 & "<$C$6, $C$12:$C$" & claim_count + 11 & ", $C$6))" & _
                                                          " / P" & 12 + trend_points & ",0.000001)"
            End If
        
        Range("S" & 12 + trend_points).Formula = "=S8 + S7 * O" & 12 + trend_points
        Range("T" & 12 + trend_points).Formula = "= ($S$7+$S$6*$Q$7) * $Q$4 ^ ((O" & 12 + trend_points & "- $Q$7)/365.25)"
        this_period = DateAdd("m", period_length, this_period)
        trend_points = trend_points + 1
    Loop
    Call set_style("O11:O" & trend_points + 11, "date")
    Call set_style("P11:P" & trend_points + 11, "integer")
    Call set_style("Q11:Q" & trend_points + 11, "percent")
    Call set_style("R11:R" & trend_points + 11, "integer")
    
    ' set weight
    Dim I As Integer
    For I = 1 To trend_points
        Range("Q" & 11 + I).Formula = "=P" & 11 + I & "/sum($P$12:$P$" & 11 + trend_points & ")"
    Next I
    Call set_style("Q12:Q" & 11 + trend_points, "percent")
    
    Range("Q7").Formula = "=sumproduct(O12:O" & 11 + trend_points & ",Q12:Q" & 11 + trend_points & ")"
    
    'add regression maths
    Call addRegressionMaths("O12:O" & 11 + trend_points, _
                            "R12:R" & 11 + trend_points, _
                            "Q12:Q" & 11 + trend_points, "", _
                            "R2:R8", "S2:S8", _
                            "S12:S" & 11 + trend_points, True)
    Call set_style("R2:S9", "fading")
    Range("Q4").Formula = "=(1 + (S" & 11 + trend_points & "-S12) / (S7+S6*Q7))^(365.25/(O" & 11 + trend_points & "-O12))"
        
    'add trended claim amounts
    Range("D12").Formula = "=C12  *  $Q$5 ^ (($E$5-B12)/365.25)  *  $Q$6^(($E$6-$E$5)/365.25)"
    Range("D12").Select
    Selection.AutoFill Destination:=Range("D12:D" & 11 + claim_count)
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    
    
    ' make chart
    Call deleteChart("trend")
    Range("$T$11:$T$" & 11 + trend_points).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'claims_in'!$T$11:$T$" & 11 + trend_points)
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SeriesCollection(1).XValues = "='claims_in'!$O$12:$O$" & 11 + trend_points
    ActiveChart.SeriesCollection(1).name = "='claims_in'!$T$11"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).XValues = "=claims_in!$O$12:$O$" & 11 + trend_points
    ActiveChart.SeriesCollection(2).Values = "=claims_in!$S$12:$S$" & 11 + trend_points
    ActiveChart.SeriesCollection(2).name = "=claims_in!$S$11"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).XValues = "=claims_in!$O$12:$O$" & 11 + trend_points
    ActiveChart.SeriesCollection(3).Values = "=claims_in!$R$12:$R$" & 11 + trend_points
    ActiveChart.SeriesCollection(3).name = "=claims_in!$R$11"
    ActiveChart.SeriesCollection(3).Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.SeriesCollection(1).MarkerStyle = -4142
    ActiveChart.SeriesCollection(2).MarkerStyle = -4142
    ActiveChart.Axes(xlCategory).MinimumScale = Range("B9").Value - 366 / 4
    ActiveChart.Axes(xlCategory).MaximumScale = Range("B10").Value + 366 / 4


    With ActiveChart
        .Parent.name = "trend"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "Capped average claim trend"
        .Parent.Top = Range("O14").Top
        .Parent.Left = Range("O14").Left
        .Parent.Width = Range("O14:T29").Width
        .Parent.Height = Range("O14:T29").Height
    End With
    Call set_style_chart

    If use_trends = False Then
        Range("Q5").Value = 1
        Range("Q6").Value = 1
    End If

End Sub





'
' Makes large claim analysis
Private Sub make_large()
    'For Pareto maths see http://www.mhnederlof.nl/pareto.html
    
    Dim claim_count As Long
    claim_count = count_rows_from("A", 12)
    
    ' Main headers
    Range("AB2").Value = "Regression"
    Range("AC2").Value = "Reg. intrcpt=0"
    Range("AD2").Value = "MLE"
    Range("AE1").Value = "(often unstable)"
    Range("AE2").Value = "MLE GPD"
    Range("AG2").Value = "Selected"
    Range("Z3").Value = "P(large)"
    Range("Z4").Value = "Pareto alpha"
    Range("Z5").Value = "GPD mu = suggested threshold"
    Range("Z6").Value = "GPD sigma"
    Range("Z7").Value = "GPD xi"
    Range("Z8").Value = "Expected loss"
    Range("Z9").Value = "Large loss load (multipl)"
    Call set_style("AB2:AE2", "headernowrap")
    Call set_style("Z3:AA9", "headernowrap")
    Call set_style("AB2:AE2", "center")
    Call set_style("AD2", "center")
    Call set_style("AG2", "center")
    Call set_style("AG2", "bold")
    
    ' Table headers
    Range("W11").Value = "Large claim ID"
    Range("X11").Value = "Trended large claim X"
    Range("Y11").Value = "log Trended large claim"
    Range("Z11").Value = "P(large claim >= X)"
    Range("AA11").Value = "empirical tail"
    Range("AB11").Value = "Regression"
    Range("AC11").Value = "Reg. intrcpt=0"
    Range("AD11").Value = "MLE"
    Range("AE11").Value = "MLE GPD"
    Call set_style("W11:AE11", "header")

    ' obtain, filter, sort large claims
    Range("U12:Z1048576").Clear
    Dim in_row As Long, large_count As Long
    Application.Calculation = xlManual
    in_row = 12
    large_count = 0
    For in_row = 12 To claim_count + 11
        If Range("D" & in_row).Value > Range("C6") Then
            large_count = large_count + 1
            Range("W" & 11 + large_count).Value = Range("A" & in_row).Value
            Range("X" & 11 + large_count).Value = Range("D" & in_row).Value
            Range("Y" & 11 + large_count).Formula = "=ln(X" & 11 + large_count & ")"
        End If
    Next in_row
    Call set_style("X12:X" & 11 + large_count, "integer")
    Call set_style("Y12:Y" & 11 + large_count, "comma")
    Call set_style("Z12:AB" & 11 + large_count, "comma")
    
    ActiveWorkbook.Worksheets("claims_in").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("claims_in").Sort.SortFields.Add Key:=Range("Y12:Y" & 11 + large_count), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("claims_in").Sort
        .SetRange Range("W11:Y" & 11 + large_count)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        
        
    ' Header formulae
    ' https://math.stackexchange.com/questions/1399209/how-to-find-the-maximum-likelihood-estimators-of-parameters-in-the-pareto-distri
    'regression
    Range("AB3").FormulaArray = "=sum(if(D12:D" & claim_count + 11 & ">=$C$6,1,0)) / sum(if(D12:D" & claim_count + 11 & ">$C$5,1,0)) "
    Range("AB4").Formula = "=-$X$6"
    Range("AB5").Formula = "=EXP(-$X$7/$X$6)"
    Range("AB6").Formula = "=$C$6 * AB7"
    Range("AB7").Formula = "=1/AB4"
    Range("AB8").Formula = "=if(AB4>1, AB4 * $C$6 / (AB4-1), """")"
    Range("AB9").FormulaArray = "=1+(AB8-$C$6) * " & large_count & _
                                 "/ sum( if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(D12:D" & claim_count + 11 & "<$C$6, D12:D" & claim_count + 11 & ", $C$6 ))"
    'regression with intercept=0
    Range("AC3").Formula = "=AB3"
    Range("AC4").FormulaArray = "=-SUM((Y12:Y" & 11 + large_count & "-LN($AC$5))*AA12:AA" & 11 + large_count & ") / SUM((Y12:Y" & 11 + large_count & "-LN($AC$5))^2)"
    Range("AC5").Formula = "=$C$6"
    Range("AC6").Formula = "=AC5 * AC7"
    Range("AC7").Formula = "=1/AC4"
    Range("AC8").Formula = "=if(AC4>1, AC4 * AC5 / (AC4-1), """")"
    Range("AC9").FormulaArray = "=1+(AC8-AC5) * " & large_count & _
                                 "/ sum( if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(D12:D" & claim_count + 11 & "<$C$6, D12:D" & claim_count + 11 & ", $C$6 ))"
    'mle
    Range("AD3").Formula = "=AB3"
    Range("AD4").Formula = "=" & large_count & " / ( sum(Y12:Y" & large_count + 11 & ") - " & large_count & " * ln($C$6) )"
    Range("AD5").Formula = "=$C$6"
    Range("AD6").Formula = "=AD5 * AD7"
    Range("AD7").Formula = "=1/AD4"
    Range("AD8").Formula = "=IF(AD4>1, AD4 * AD5 / (AD4-1), """")"
    Range("AD9").FormulaArray = "=1+(AD8-AD5) * " & large_count & _
                                 "/ sum( if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(D12:D" & claim_count + 11 & "<$C$6, D12:D" & claim_count + 11 & ", $C$6 ))"
    'gpd
    Range("AE3").Formula = "=AB3"
    Range("AE5").Formula = "=$C$6"
    Range("AE6").Formula = "=AC6 * AF6"
    Range("AE7").Formula = "=AC7"
    Range("AE8").Formula = "=IF(AE7<1, AE5 + AE6/(1-AE7),"""")"
    Range("AE9").FormulaArray = "=1+(AE8-AE5) * " & large_count & _
                                 "/ sum( if(C12:C" & claim_count + 11 & ">$C$5,1,0) * if(D12:D" & claim_count + 11 & "<$C$6, D12:D" & claim_count + 11 & ", $C$6 ))"
    Range("AF6").Value = 1 ' for optimising solver
    'selected
    Range("AG4").Formula = "=1/AG7"
    Range("AG8").Formula = "=IF(AG7<1, AG5 + AG6/(1-AG7),"""")"
        
    Call set_style("AB3:AG9", "0000")
    Call set_style("AB5:AG6", "integer")
    Call set_style("AB8:AG8", "integer")
    Call set_style("AF6", "fading")
   
    ' Calculate row by row
    Dim I As Long
    For I = 1 To large_count
        Range("Z" & 11 + I).Formula = "=1-(" & large_count & "-" & (large_count - I + 1) & ") / " & large_count
        Range("AA" & 11 + I).Formula = "=ln(Z" & 11 + I & ")"
        Range("AC" & 11 + I).Formula = "=$AC$4 * ln($C$6) - $AC$4 * $Y" & 11 + I
        Range("AD" & 11 + I).Formula = "=$AD$4 * LN($AD$5) - $AD$4 * Y" & 11 + I
        Range("AE" & 11 + I).Formula = "=-LN(1+$AE$7*(X" & 11 + I & "-$AE$5)/$AE$6)/$AE$7"
        ''Range("AE" & 11 + i).Formula = "=(-1/$AA$7-1) * (-LN($AA$6) + LN(1+$AA$7*(X" & 11 + i & "-$AA$5)/$AA$6))"
        Range("AF" & 11 + I).Formula = "=-LN($AE$6) - (1/$AE$7+1) *  LN(1+$AE$7*($X" & I + 11 & "-$AE$5)/$AE$6)"
    Next I
    If large_count > 0 Then Range("AF11").Formula = "=sum(AF12:AF" & large_count + 11 & ")"
    Call set_style("Z12:Z" & 11 + large_count, "percent")
    Call set_style("AB12:AE" & 11 + large_count, "0000")
    Call set_style("AF11:AF" & 11 + large_count, "fading")

    ' Regression maths
    Call addRegressionMaths("Y12:Y" & 11 + large_count, _
                            "AA12:AA" & 11 + large_count, "", "", _
                            "W2:W9", "X2:X9", _
                            "AB12:AB" & 11 + large_count, True)
    Call set_style("W2:X9", "fading")
    
    ' Solving GPD x2
    Application.Calculation = xlAutomatic
    For I = 1 To 2
        SolverReset
        SolverOk SetCell:="$AF$11", MaxMinVal:=1, ValueOf:="0", ByChange:="$AF$6,$AE$7"
        SolverAdd cellRef:=Range("$AE$7"), relation:=3, formulaText:="0.01"
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    Next I
    
    ' Make chart
    Call deleteChart("large")
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SeriesCollection(1).name = "=claims_in!$AA$11"
    ActiveChart.SeriesCollection(1).XValues = "=claims_in!$Y$12:$Y$" & 11 + large_count
    ActiveChart.SeriesCollection(1).Values = "=claims_in!$AA$12:$AA$" & 11 + large_count
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).name = "=claims_in!$AB$11"
    ActiveChart.SeriesCollection(2).XValues = "=claims_in!$Y$12:$Y$" & 11 + large_count
    ActiveChart.SeriesCollection(2).Values = "=claims_in!$AB$12:$AB$" & 11 + large_count
    ActiveChart.SeriesCollection(2).Select
    Selection.MarkerStyle = -4142
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).name = "=claims_in!$AC$11"
    ActiveChart.SeriesCollection(3).XValues = "=claims_in!$Y$12:$Y$" & 11 + large_count
    ActiveChart.SeriesCollection(3).Values = "=claims_in!$AC$12:$AC$" & 11 + large_count
    ActiveChart.SeriesCollection(3).Select
    Selection.MarkerStyle = -4142
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).name = "=claims_in!$AD$11"
    ActiveChart.SeriesCollection(4).XValues = "=claims_in!$Y$12:$Y$" & 11 + large_count
    ActiveChart.SeriesCollection(4).Values = "=claims_in!$AD$12:$AD$" & 11 + large_count
    ActiveChart.SeriesCollection(4).Select
    Selection.MarkerStyle = -4142
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).name = "=claims_in!$AE$11"
    ActiveChart.SeriesCollection(5).XValues = "=claims_in!$Y$12:$Y$" & 11 + large_count
    ActiveChart.SeriesCollection(5).Values = "=claims_in!$AE$12:$AE$" & 11 + large_count
    ActiveChart.SeriesCollection(5).Select
    Selection.MarkerStyle = -4142
    
    ActiveChart.SeriesCollection(1).Select
    Selection.Format.Line.Visible = msoFalse
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    On Error Resume Next
    ActiveChart.Axes(xlCategory).MinimumScale = Int(Range("Y12").Value)
    
    With ActiveChart
        .Parent.name = "large"
        .HasTitle = True
        .ChartTitle.Select
        .ChartTitle.Text = "P( claim >= X ) log-log scale"
        .Parent.Top = Range("W14").Top
        .Parent.Left = Range("w14").Left
        .Parent.Width = Range("W14:AB29").Width
        .Parent.Height = Range("W14:AB29").Height
    End With
    ActiveChart.Legend.Select
    Selection.Delete
    ActiveChart.SetElement (msoElementLegendBottom)
    ActiveSheet.ChartObjects("large").Activate
    ActiveChart.Legend.Select
    Selection.Format.TextFrame2.TextRange.Font.size = 8
    
    Call set_style_chart
    Range("A1").Select
End Sub






'
' Makes discretised calculation
Private Sub make_discretised()
    
    
    
    ' Main headers
    Range("AG10").Value = "tail type"
    Range("AH10").Value = "Regression"
    Range("AH10").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Regression,Reg intercpt=0,MLE,MLE GPD,Selected"
        .ShowInput = True
        .ShowError = True
    End With
    
    
    Range("AG11").Value = "loss"
    Range("AH11").Value = "cdf"
    Call set_style("AG10", "headernowrap")
    Call set_style("AG10", "dark-blue-white")
    Call set_style("AG11:AH11", "headernowrap")
    Call set_style("AG11:AH11", "dark-blue-white")
    
    Range("AH12").Value = 0.01
    Range("AH13").Value = 0.02
    Range("AH14").Value = 0.03
    Range("AH15").Value = 0.04
    Range("AH16").Value = 0.05
    Range("AH17").Value = 0.06
    Range("AH18").Value = 0.07
    Range("AH19").Value = 0.08
    Range("AH20").Value = 0.09
    Range("AH21").Value = 0.1
    Range("AH22").Value = 0.11
    Range("AH23").Value = 0.12
    Range("AH24").Value = 0.13
    Range("AH25").Value = 0.14
    Range("AH26").Value = 0.15
    Range("AH27").Value = 0.16
    Range("AH28").Value = 0.17
    Range("AH29").Value = 0.18
    Range("AH30").Value = 0.19
    Range("AH31").Value = 0.2
    Range("AH32").Value = 0.21
    Range("AH33").Value = 0.22
    Range("AH34").Value = 0.23
    Range("AH35").Value = 0.24
    Range("AH36").Value = 0.25
    Range("AH37").Value = 0.26
    Range("AH38").Value = 0.27
    Range("AH39").Value = 0.28
    Range("AH40").Value = 0.29
    Range("AH41").Value = 0.3
    Range("AH42").Value = 0.31
    Range("AH43").Value = 0.32
    Range("AH44").Value = 0.33
    Range("AH45").Value = 0.34
    Range("AH46").Value = 0.35
    Range("AH47").Value = 0.36
    Range("AH48").Value = 0.37
    Range("AH49").Value = 0.38
    Range("AH50").Value = 0.39
    Range("AH51").Value = 0.4
    Range("AH52").Value = 0.41
    Range("AH53").Value = 0.42
    Range("AH54").Value = 0.43
    Range("AH55").Value = 0.44
    Range("AH56").Value = 0.45
    Range("AH57").Value = 0.46
    Range("AH58").Value = 0.47
    Range("AH59").Value = 0.48
    Range("AH60").Value = 0.49
    Range("AH61").Value = 0.5
    Range("AH62").Value = 0.51
    Range("AH63").Value = 0.52
    Range("AH64").Value = 0.53
    Range("AH65").Value = 0.54
    Range("AH66").Value = 0.55
    Range("AH67").Value = 0.56
    Range("AH68").Value = 0.57
    Range("AH69").Value = 0.58
    Range("AH70").Value = 0.59
    Range("AH71").Value = 0.6
    Range("AH72").Value = 0.61
    Range("AH73").Value = 0.62
    Range("AH74").Value = 0.63
    Range("AH75").Value = 0.64
    Range("AH76").Value = 0.65
    Range("AH77").Value = 0.66
    Range("AH78").Value = 0.67
    Range("AH79").Value = 0.68
    Range("AH80").Value = 0.69
    Range("AH81").Value = 0.7
    Range("AH82").Value = 0.71
    Range("AH83").Value = 0.72
    Range("AH84").Value = 0.73
    Range("AH85").Value = 0.74
    Range("AH86").Value = 0.75
    Range("AH87").Value = 0.76
    Range("AH88").Value = 0.77
    Range("AH89").Value = 0.78
    Range("AH90").Value = 0.790000000000001
    Range("AH91").Value = 0.800000000000001
    Range("AH92").Value = 0.810000000000001
    Range("AH93").Value = 0.820000000000001
    Range("AH94").Value = 0.830000000000001
    Range("AH95").Value = 0.840000000000001
    Range("AH96").Value = 0.850000000000001
    Range("AH97").Value = 0.860000000000001
    Range("AH98").Value = 0.870000000000001
    Range("AH99").Value = 0.880000000000001
    Range("AH100").Value = 0.890000000000001
    Range("AH101").Value = 0.900000000000001
    Range("AH102").Value = 0.910000000000001
    Range("AH103").Value = 0.920000000000001
    Range("AH104").Value = 0.930000000000001
    Range("AH105").Value = 0.940000000000001
    Range("AH106").Value = 0.950000000000001
    Range("AH107").Value = 0.960000000000001
    Range("AH108").Value = 0.970000000000001
    Range("AH109").Value = 0.980000000000001
    Range("AH110").Value = 0.990000000000001
    Range("AH111").Value = 0.995
    Range("AH112").Value = 0.996
    Range("AH113").Value = 0.997
    Range("AH114").Value = 0.998
    Range("AH115").Value = 0.999
    Range("AH116").Value = 0.9995
    Range("AH117").Value = 0.9996
    Range("AH118").Value = 0.9997
    Range("AH119").Value = 0.9998
    Range("AH120").Value = 0.9999
    Range("AH121").Value = 0.99995
    Range("AH122").Value = 0.99996
    Range("AH123").Value = 0.99997
    Range("AH124").Value = 0.99998
    Range("AH125").Value = 0.99999
    Range("AH126").Value = 0.999995
    Range("AH127").Value = 0.999996
    Range("AH128").Value = 0.999997
    Range("AH129").Value = 0.999998
    Range("AH130").Value = 0.999999
    Range("AH131").Value = 0.9999995
    Range("AH132").Value = 0.9999996
    Range("AH133").Value = 0.9999997
    Range("AH134").Value = 0.9999998
    Range("AH135").Value = 0.9999999

    Range("AG12").Formula = "=if($AH$10=""Regression""    , invCondLognormGPD(AH12, $C$5, $L$4, $L$5, $AB$3, $C$6 , $AB$6, $AB$7), " & _
                             "if($AH$10=""Reg intercpt=0"", invCondLognormGPD(AH12, $C$5, $L$4, $L$5, $AC$3, $AC$5, $AC$6, $AC$7), " & _
                             "if($AH$10=""MLE""           , invCondLognormGPD(AH12, $C$5, $L$4, $L$5, $AD$3, $AD$5, $AD$6, $AD$7), " & _
                             "if($AH$10=""MLE GPD""       , invCondLognormGPD(AH12, $C$5, $L$4, $L$5, $AE$3, $AE$5, $AE$6, $AE$7), " & _
                             "if($AH$10=""Selected""      , invCondLognormGPD(AH12, $C$5, $L$4, $L$5, $AG$3, $AG$5, $AG$6, $AG$7), " & _
                             """x"")))))"
    Range("AG12").Select
    Selection.AutoFill Destination:=Range("AG12:AG135")
    Range("A1").Select
End Sub