Step-by-Step VBA Implementation Guide for Fraud Detection in Financial Transactions

This Decision Support System (DSS) project will detect fraudulent transactions using Microsoft Excel Visual Basic for Applications (VBA) by applying rule-based anomaly detection.

📌 Step 1: Define the Fraud Detection Problem

We will analyze financial transactions and flag suspicious ones based on:

1. Transaction Amount: Higher-than-usual transactions.
2. Transaction Frequency: Rapid consecutive transactions.
3. Location Mismatch: Transactions occurring in multiple locations within a short period.
4. Account Behavior Change: Sudden high-value transactions from an account.

📌 Step 2: Prepare Sample Dataset

Create a worksheet named "Transactions" with the following headers in Row 1:

➡️ The "Suspicious?" column will be automatically updated by VBA based on predefined fraud rules.

📌 Step 3: Open VBA Editor & Insert Code

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert → Module.
3. Copy and paste the following VBA script:

🔹 VBA Code: Fraud Detection Algorithm

Sub DetectFraud()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim transAmount As Double
    Dim prevAmount As Double
    Dim accountNum As String
    Dim prevAccount As String
    Dim transTime As Date
    Dim prevTime As Date
    Dim transLocation As String
    Dim prevLocation As String
    Dim timeDiff As Double
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Transactions")
    
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through transactions (starting from row 2)
    For i = 2 To lastRow
        ' Read transaction details
        transAmount = ws.Cells(i, 5).Value ' Transaction Amount
        accountNum = ws.Cells(i, 4).Value  ' Account Number
        transTime = ws.Cells(i, 3).Value   ' Time
        transLocation = ws.Cells(i, 6).Value ' Location
        
        ' Compare with previous transaction (if exists)
        If i > 2 Then
            prevAmount = ws.Cells(i - 1, 5).Value
            prevAccount = ws.Cells(i - 1, 4).Value
            prevTime = ws.Cells(i - 1, 3).Value
            prevLocation = ws.Cells(i - 1, 6).Value
            
            ' Time difference in minutes
            timeDiff = DateDiff("n", prevTime, transTime)
            
            ' Fraud detection rules
            If transAmount > 5000 Then
                ws.Cells(i, 7).Value = "⚠ High Amount"
                ws.Cells(i, 7).Interior.Color = RGB(255, 153, 153) ' Red Highlight
            ElseIf prevAccount = accountNum And timeDiff < 5 And prevLocation <> transLocation Then
                ws.Cells(i, 7).Value = "⚠ Rapid Location Change"
                ws.Cells(i, 7).Interior.Color = RGB(255, 204, 102) ' Orange Highlight
            ElseIf prevAccount = accountNum And Abs(transAmount - prevAmount) > 4000 Then
                ws.Cells(i, 7).Value = "⚠ Sudden Large Change"
                ws.Cells(i, 7).Interior.Color = RGB(255, 204, 102) ' Orange Highlight
            Else
                ws.Cells(i, 7).Value = "✔ Normal"
                ws.Cells(i, 7).Interior.Color = RGB(204, 255, 204) ' Green Highlight
            End If
        Else
            ' First transaction is always normal
            ws.Cells(i, 7).Value = "✔ Normal"
            ws.Cells(i, 7).Interior.Color = RGB(204, 255, 204)
        End If
    Next i
    
    ' Message after completion
    MsgBox "Fraud detection completed!", vbInformation, "Fraud Detection"
End Sub

📌 Step 4: Run the VBA Macro

1. Go back to Excel.
2. Press ALT + F8, select DetectFraud, and click Run.
3. The "Suspicious?" column will be updated with:
   • ✔ Normal (Green): No anomalies detected.
   • ⚠ High Amount (Red): Large transactions over $5,000.
   • ⚠ Rapid Location Change (Orange): Transactions in different locations within 5 minutes.
   • ⚠ Sudden Large Change (Orange): Drastic changes in transaction amounts.

📌 Step 5: Automate Fraud Alerts with Email (Optional)

To send an email alert when a fraudulent transaction is detected, modify the script to send an email using Outlook.

🔹 VBA Code: Send Email Alert

Sub SendFraudAlert()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Dim emailBody As String
    Dim fraudFound As Boolean
    
    ' Set worksheet
    Set ws = ThisWorkbook.Sheets("Transactions")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize email body
    emailBody = "Fraud Alert! The following suspicious transactions were detected:" & vbNewLine & vbNewLine
    
    ' Check for fraudulent transactions
    fraudFound = False
    For i = 2 To lastRow
        If ws.Cells(i, 7).Value <> "✔ Normal" Then
            emailBody = emailBody & "Transaction ID: " & ws.Cells(i, 1).Value & _
                        ", Amount: $" & ws.Cells(i, 5).Value & _
                        ", Suspicion: " & ws.Cells(i, 7).Value & vbNewLine
            fraudFound = True
        End If
    Next i
    
    ' Send email if fraud is found
    If fraudFound Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
            .To = "fraudalerts@bank.com"
            .Subject = "🚨 Fraud Alert: Suspicious Transactions Detected"
            .Body = emailBody
            .Send
        End With
        
        Set OutMail = Nothing
        Set OutApp = Nothing
        
        MsgBox "Fraud alert email sent!", vbInformation, "Email Sent"
    Else
        MsgBox "No fraud detected.", vbInformation, "Fraud Detection"
    End If
End Sub

📌 Step 6: Automate with a Button

1. Go to Excel → Developer Tab → Insert → Button.
2. Assign the DetectFraud macro to the button.
3. (Optional) Add another button for SendFraudAlert.

📌 Step 7: Final Testing

1. Add new transactions with anomalies and run the fraud detection macro.
2. If fraud is found, run the email alert macro to notify administrators.

🔹 Key Takeaways

✅ Uses VBA to analyze financial data and detect fraud.
✅ Highlights suspicious transactions using color coding.
✅ Automates fraud alerts via Outlook emails.
✅ Decision Support for Business: Helps banks and businesses reduce fraud losses.

Would you like to extend this with AI-based anomaly detection in Excel? 🚀