ddGini(TheBadRange,TheGoodRange)

ddGini is a function within ddGlobal and is use to calculate the Gini-coefficient, also known as the Gini-index or the Gini-Ratio.  The calculation is generally use by statisticians to measure the inequality of income levels.  Scorecard developers however abuse this as a measure to determine how strong their scorecards are at differentiating between a good and a bad account.

The way to use ddGini is as follows.

1. Create a frequency of your individual scores, or score-ranges that you would like to calculate a Gini-coefficient on.  Column A in the following example.

2. Column B, insert the number of BAD accounts that you have in each of the scores / score ranges depicted in Column A

3. Colum C, contains the number of GOOD accounts that you have in each of the scores / score ranges depicted in Column A.

The formula to calculate the Gini-coefficient in the above case is:

=+ddgini(B2:B16,C2:C16)

If you have done everything correctly, you should obtain an answer of 0.815175323.  Typically, Gini-coefficients for scorecards is expressed as a percentage, so just convert to a percentage and you have a scorecard with a 81% Gini.

The below code may be copied and use freely, but please make reference to either ddGlobal or to this page when you use it please.

Function DDGini(TheBadRange As Range, TheGoodRange As Range) As Double
Dim i, j As Integer
Dim TotG, TotB As Double
Dim CumG, CumB As Double
Dim Pct1G, Pct2G, Pct1B, Pct2B As Double
Dim Gini As Double
    With TheGoodRange
        For i = 1 To .Rows.Count
            If IsNumeric(.Cells(i, 1).Value) Then TotG = TotG + .Cells(i, 1).Value
        Next i
    End With
    With TheBadRange
        For i = 1 To .Rows.Count
            If IsNumeric(.Cells(i, 1).Value) Then TotB = TotB + .Cells(i, 1).Value
        Next i
    End With

    CumG = TotG
    CumB = TotB
    For i = 2 To TheGoodRange.Rows.Count + 1
        Pct1G = CumG / TotG
        Pct1B = CumB / TotB
        CumG = CumG - TheGoodRange.Cells(i - 1, 1).Value
        CumB = CumB - TheBadRange.Cells(i - 1, 1).Value
        Pct2G = CumG / TotG
        Pct2B = CumB / TotB
        Gini = Gini + ((Pct1G - Pct2G) * (Pct1G + Pct2G - Pct1B - Pct2B))
        Pct1G = Pct2G
        Pct1B = Pct2B
    Next i
    DDGini = Gini
End Function

This function has been tested to be accurate by various third parties but if you have any suggestionson how to improve it, or you found any errors in it, please contact me.