FREE Time Series Outlier and Anomaly Detection Code

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