CA$0+
Use this free time series outlier and anomaly detection code to find seasonal trends and patterns in data with the box & whisker plot. Run this free outlier and anomaly detection analysis macro with Python, R or other programming languages. Or, use the outlier analysis function as a formula in a spreadsheet. The anomaly detection code applies box & whisker plot anomaly detection theory using key descriptive statistics: 75th percentile (HIGHQ), 25th percentile (LOWQ), Interquartile Range (IQR). The top (UPPER) and bottom (LOWER) box plot whiskers are calculated. Any data values greater than UPPER or less than LOWER are outliers. A message box pop-ups to summarize which data values are outliers. The FREE SAMPLE OUTPUT - TIME SERIES Outlier and Anomaly Detection Template will show you an example of the output generated by this code. The TIME SERIES Outlier and Anomaly Detection Template explains the process further.
Option Explicit
Option Base 1
Sub mcrCallTimeSeriesOutlierDetection()
'Free time series outlier and anomaly detection code for finding seasonal trends and patterns in time series data.
Dim wsData As Worksheet
Dim Cell As Range
Dim col As Range
Dim Outlier_range As Range
Dim rngHighOutlierCnt As Range
Dim HIGHQ As Variant
Dim LOWQ As Variant
Dim IQR As Variant
Dim UPPER As Variant
Dim LOWER As Variant
Dim i As Integer
Dim j As Integer
Dim ttlOutlier As Integer
Dim perOutlier As Variant
Dim lastCol As Long
Dim lastRow As Long
Dim rngData As Range
Set wsData = Worksheets("Data")
wsData.Select
lastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
lastCol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column
Range(wsData.Cells(2, 2), wsData.Cells(lastRow, lastCol)).Name = "Data"
For Each col In Range("Data").Columns
HIGHQ = WorksheetFunction.Percentile(col, 0.75)
LOWQ = WorksheetFunction.Percentile(col, 0.25)
IQR = HIGHQ - LOWQ
UPPER = HIGHQ + 1.5 * (IQR)
LOWER = LOWQ - 1.5 * (IQR)
col.Offset(-1, lastCol - 1).Value = col.Offset(-1, 0).Value
For Each Cell In col.Cells
Cell.Font.Bold = False
Cell.Offset(0, lastCol - 1).Value = findOutlier(Cell, UPPER, LOWER, HIGHQ, LOWQ)
If Cell.Offset(0, lastCol - 1).Value = "High Outlier" Then
Cell.Offset(0, lastCol - 1).Interior.Color = 65535
Cell.Offset(0, lastCol - 1).Font.Color = -16776961
Else Cell.Offset(0, lastCol - 1).Font.Color = RGB(0, 0, 0)
End If
Next Cell
Next col
With wsData.Range("Data")
For i = 2 To lastRow
Set rngHighOutlierCnt = Range(wsData.Cells(i, lastCol + 1), wsData.Cells(i, lastCol + 12)) wsData.Cells(i, lastCol + 13).Value = WorksheetFunction.CountIf(rngHighOutlierCnt, "High Outlier")
Next i
wsData.Cells(1, lastCol + 13).Value = "Time Series Trend"
wsData.Cells(1, lastCol + 14).Value = "Letter Grade"
End With
With wsData.Range("Z1")
For i = 1 To lastRow - 1
If .Offset(i, 0).Value = 0 Then
.Offset(i, 1).Value = "A+"
ElseIf .Offset(i, 0).Value = 1 Then
.Offset(i, 1).Value = "A"
ElseIf .Offset(i, 0).Value = 2 Then
.Offset(i, 1).Value = "A-"
ElseIf .Offset(i, 0).Value = 3 Then
.Offset(i, 1).Value = "B+"
ElseIf .Offset(i, 0).Value = 4 Then
.Offset(i, 1).Value = "B"
ElseIf .Offset(i, 0).Value = 5 Then
.Offset(i, 1).Value = "B-"
ElseIf .Offset(i, 0).Value = 6 Then
.Offset(i, 1).Value = "C+"
ElseIf .Offset(i, 0).Value = 7 Then
.Offset(i, 1).Value = "C"
ElseIf .Offset(i, 0).Value = 8 Then
.Offset(i, 1).Value = "C-"
ElseIf .Offset(i, 0).Value = 9 Then
.Offset(i, 1).Value = "D+"
ElseIf .Offset(i, 0).Value = 10Then
.Offset(i, 1).Value = "D"
ElseIf .Offset(i, 0).Value = 11 Then
.Offset(i, 1).Value = "D-"
ElseIf .Offset(i, 0).Value = 12 Then
.Offset(i, 1).Value = "F"
End If Next i
End With
End Sub
Function findOutlier(Cell As Range, UPPER As Variant, LOWER As Variant, HIGHQ As Variant, LOWQ As Variant) As String
If Cell.Value > UPPER Then findOutlier = "High Outlier"
ElseIf Cell.Value < LOWER Then
findOutlier = "Low Outlier"
ElseIf Cell.Value > HIGHQ And Cell.Value < UPPER Then
findOutlier = "High" ElseIf Cell.Value < LOWQ And Cell.Value > LOWER Then
findOutlier = "Low" Else findOutlier = "Normal"
End If
End Function