Analytics

ACL Analytics & Excel Data Manipulation Reference

Articles · 2026 · Ioannis (John) Konstas

Production-grade scripts, formulas, and audit patterns — from data cleaning through fraud detection. Built for professional auditors and data analysts.

ACL Analytics (Galvanize HighBond) · Excel 365 · Ioannis (John) Alexander Konstas

Data Cleaning Duplicate Detection Fraud Testing Benford's Law Aging Analysis Join & Merge Sampling Gap Analysis ACL Script Excel 365

🧹 ACL — Data Cleaning & Standardization

Section 1

Always standardize text before any matching, joining, or deduplication. Inconsistent casing and whitespace are the most common cause of missed duplicates and failed joins in audit data.

Core Text Functions

Uppercase — Normalize casing

UPPER(Name)

Trim — Remove leading/trailing spaces

ALLTRIM(Name)

Include — Keep only letters A–Z

INCLUDE(UPPER(Name), "ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Exclude — Strip numeric digits

EXCLUDE(Field, "0123456789")

Include — Keep alphanumeric only

INCLUDE(UPPER(Field),
  "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")

Handle blanks — Replace with placeholder

IF ISBLANK(Name) THEN "UNKNOWN" ELSE Name

Substitute — Replace a specific character

REPLACE(Name, "-", "")

Pad — Right-pad to fixed length

PADR(Name, 20)    " right-pad to 20 chars"
PADL(ID, 10)      " left-pad to 10 chars"

Convert number to string

STRING(Amount, "2")   " 2 decimal places"

Convert string to number

VALUE(Amount_String, "2")

🔧 ACL — Calculated & Derived Fields

Section 2

Building Standardized Match Keys

Clean name field — 33-char key

DEFINE FIELD Clean_Name COMPUTED
  SUBSTR(INCLUDE(UPPER(ALLTRIM(Name)),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 33)

Full name key — First + Last combined

DEFINE FIELD Full_Key COMPUTED
  SUBSTR(INCLUDE(UPPER(
    ALLTRIM(First_Name) + ALLTRIM(Last_Name)),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 20)

Last + First key — For surname-first matching

DEFINE FIELD LF_Key COMPUTED
  SUBSTR(INCLUDE(UPPER(
    ALLTRIM(Last_Name) + ALLTRIM(First_Name)),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), 1, 20)

Composite transaction key — ID + Date + Amount

DEFINE FIELD Txn_Key COMPUTED
  ALLTRIM(Vendor_ID) + "_"
  + STRING(Invoice_Date, "YYYYMMDD")
  + "_"
  + STRING(Amount, "2")

Flag high-value transactions

DEFINE FIELD Risk_Flag COMPUTED
  IF Amount >= 10000 THEN "HIGH"
  ELSE IF Amount >= 5000 THEN "MEDIUM"
  ELSE "NORMAL"

Days outstanding (aging)

DEFINE FIELD Days_Open COMPUTED
  AGE(Invoice_Date, TODAY())

Fiscal year derived from date

DEFINE FIELD Fiscal_Year COMPUTED
  IF MONTH(Txn_Date) >= 10
  THEN YEAR(Txn_Date) + 1
  ELSE YEAR(Txn_Date)

Extract leading digit (Benford prep)

DEFINE FIELD Lead_Digit COMPUTED
  VALUE(SUBSTR(LTRIM(
    STRING(ABS(Amount), "2"),
    "0"), 1, 1), "0")

🔍 ACL — Filtering & Extraction

Section 3

Filter Expressions & EXTRACT Commands

Basic amount filter

EXTRACT RECORD IF Amount > 1000
  TO "High_Value"

Date range filter

EXTRACT RECORD IF
  BETWEEN(Invoice_Date,
    `20240101`, `20241231`)
  TO "FY2024"

Multi-condition filter

EXTRACT RECORD IF
  Amount > 5000
  AND Status = "APPROVED"
  AND Dept_Code <> "IT"
  TO "Flagged_Transactions"

Exclude specific descriptions

EXTRACT RECORD IF
  Description <> "TEST"
  AND Description <> "SAMPLE"
  AND Description <> "VOID"
  TO "Live_Transactions"

Pattern match — contains substring

EXTRACT RECORD IF
  FIND("PETTY CASH", UPPER(Description)) > 0
  TO "Petty_Cash_Items"

Wildcard match with MATCH()

EXTRACT RECORD IF
  MATCH(Vendor_Name,
    "ACME*", "*HOLDINGS*", "*LTD")
  TO "Matched_Vendors"

Round-dollar amount test (fraud indicator)

EXTRACT RECORD IF
  Amount = INT(Amount)
  AND Amount >= 1000
  TO "Round_Amounts"

Extract specific fields only (not all columns)

EXTRACT FIELDS
  Invoice_ID, Vendor_ID, Amount,
  Invoice_Date, Approved_By
  IF Amount > 5000
  TO "High_Value_Summary"

Weekend transaction detection

EXTRACT RECORD IF
  DOW(Txn_Date) = 1   " 1=Sunday"
  OR DOW(Txn_Date) = 7 " 7=Saturday"
  TO "Weekend_Transactions"

Transactions below approval threshold (split payments)

EXTRACT RECORD IF
  Amount >= 4500 AND Amount < 5000
  TO "Just_Below_Threshold"

📂 ACL — Sorting, Indexing & Sequencing

Section 4

Sort ascending by single field

SORT ON Name TO "Sorted_By_Name"

Sort descending by amount

SORT ON Amount D TO "Sorted_High_Low"

Sort on multiple fields

SORT ON Vendor_ID, Invoice_Date, Amount D
  TO "Vendor_Date_Sorted"

Create index (faster than sort for large tables)

INDEX ON Name TO "Name_Index"

Sequence check — detect gaps in ID series

SEQUENCE ON Invoice_Num
  TO "Missing_Invoices"
  PRESORT

Find gaps in numeric sequence

GAPS ON Check_Number
  TO "Missing_Checks"
  PRESORT

🔁 ACL — Duplicate & Gap Detection

Section 5

Find duplicate names

DUPLICATES ON Clean_Name
  TO "Duplicate_Names"
  PRESORT

Duplicate invoices — same vendor, amount, date

DUPLICATES ON Vendor_ID, Amount, Invoice_Date
  TO "Duplicate_Invoices"
  PRESORT

Extract unique records only

SUMMARIZE ON Clean_Name
  TO "Unique_Names"
  PRESORT

Duplicate employees — same SSN/TIN

DUPLICATES ON
  SUBSTR(INCLUDE(SSN, "0123456789"), 1, 9)
  TO "Duplicate_SSN"
  PRESORT

Duplicate payments — same payee + amount

DUPLICATES ON Payee_Name, Payment_Amount
  TO "Possible_Double_Payments"
  PRESORT

Gaps in check sequence

GAPS ON Check_Number
  TO "Missing_Check_Numbers"
  PRESORT

🔗 ACL — Joins, Lookups & Merges

Section 6

Inner join — matched records only

JOIN
  PRIMARY Transactions KEY Vendor_ID
  SECONDARY Vendor_Master KEY Vendor_ID
  MATCH
  TO "Matched_Vendors"

Left join — unmatched primary (orphaned records)

JOIN
  PRIMARY Payments KEY Employee_ID
  SECONDARY Employee_Master KEY Employee_ID
  UNMATCH
  TO "Payments_No_Employee"

Many-to-many join (all matches)

JOIN
  PRIMARY Orders KEY Customer_ID
  SECONDARY Customers KEY Customer_ID
  MANY
  TO "Orders_With_Customers"

Lookup — pull single value from secondary table

DEFINE FIELD Vendor_Name COMPUTED
  LOOKUP(Vendor_Master,
    Vendor_ID, Vendor_ID,
    Vendor_Name)

Merge two tables with same structure

MERGE
  Table_Q1 Table_Q2 Table_Q3 Table_Q4
  TO "Full_Year_Transactions"

Exception join — find records in A not in B

JOIN
  PRIMARY Active_Users KEY User_ID
  SECONDARY HR_Roster KEY Employee_ID
  UNMATCH
  TO "Ghost_Users"

📊 ACL — Summarization & Stratification

Section 7

Summarize with subtotals

SUMMARIZE ON Vendor_ID
  SUBTOTAL Amount
  TO "Vendor_Totals"
  PRESORT

Summarize — count + sum + max

SUMMARIZE ON Dept_Code
  SUBTOTAL Amount
  COUNT
  TO "Dept_Summary"
  PRESORT

Stratify amounts into bands

STRATIFY ON Amount
  INTERVALS 0 1000 5000 10000 50000 999999999
  TO "Amount_Bands"

Histogram by frequency

HISTOGRAM ON Amount
  MINIMUM 0 MAXIMUM 100000
  INTERVALS 10
  TO "Amount_Histogram"

Crosstab — pivot by two dimensions

CROSSTAB
  ON Dept_Code
  SUBTOTAL Amount
  COLUMNS Quarter
  TO "Dept_Quarter_Crosstab"

Top N vendors by spend

SUMMARIZE ON Vendor_ID
  SUBTOTAL Amount
  TO "Vendor_Totals" PRESORT
SORT ON Amount D TO "Top_Vendors"
EXTRACT RECORD IF RECNO() <= 10
  TO "Top_10_Vendors"

🎲 ACL — Sampling Methods

Section 8

Random sample — 100 records

SAMPLE 100 RANDOM
  TO "Random_Sample_100"

Systematic sample — every Nth record

SAMPLE 50 SYSTEMATIC
  TO "Systematic_Sample"

Monetary Unit Sampling (MUS) — value-weighted

SAMPLE 50 MU ON Amount
  TO "MUS_Sample"

Cell sampling — fixed intervals across range

SAMPLE 30 CELL
  TO "Cell_Sample"

🚨 ACL — Fraud & Anomaly Detection

Section 9

These tests are indicators only — each result requires professional judgment and investigation before conclusions are drawn.

Common Fraud Indicators

Just-below-threshold (split payment) test

EXTRACT RECORD IF
  Amount >= 4500 AND Amount < 5000
  TO "Just_Below_5K_Threshold"

Round-number concentration (fraud indicator)

EXTRACT RECORD IF
  MOD(Amount, 1000) = 0
  AND Amount >= 1000
  TO "Round_Dollar_Amounts"

Weekend and after-hours transactions

EXTRACT RECORD IF
  DOW(Txn_Date) = 1
  OR DOW(Txn_Date) = 7
  TO "Weekend_Txns"

" After-hours: hour outside 08:00-18:00"
EXTRACT RECORD IF
  HOUR(Txn_Time) < 8
  OR HOUR(Txn_Time) > 18
  TO "After_Hours_Txns"

Backdated entries — posted after period close

EXTRACT RECORD IF
  Post_Date > `20241231`
  AND Invoice_Date < `20241231`
  TO "Backdated_Entries"

Same vendor, same day, multiple payments

SUMMARIZE ON Vendor_ID, Payment_Date
  SUBTOTAL Amount COUNT
  TO "Vendor_Day_Summary" PRESORT
EXTRACT RECORD IF Count > 1
  TO "Multi_Payments_Same_Day"

Terminated employee still receiving payments

JOIN
  PRIMARY Payroll KEY Employee_ID
  SECONDARY Terminations KEY Employee_ID
  MATCH
  TO "Terminated_Still_Paid"
EXTRACT RECORD IF
  Pay_Date > Term_Date
  TO "Ghost_Employees"

Vendor address matches employee address

JOIN
  PRIMARY Vendors KEY
    SUBSTR(INCLUDE(UPPER(Address),
      "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"),
      1, 30)
  SECONDARY Employees KEY
    SUBSTR(INCLUDE(UPPER(Home_Address),
      "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"),
      1, 30)
  MATCH
  TO "Vendor_Employee_Address_Match"

PO Box vendor addresses (elevated risk)

EXTRACT RECORD IF
  FIND("PO BOX", UPPER(Vendor_Address)) > 0
  OR FIND("P.O.", UPPER(Vendor_Address)) > 0
  TO "PO_Box_Vendors"

📐 ACL — Benford's Law & Statistical Analysis

Section 10

Benford's Law states that in naturally occurring datasets, the leading digit is 1 approximately 30.1% of the time, 2 about 17.6%, and so on. Significant deviations suggest manipulation or data entry anomalies.

Benford's Law test — leading digit

BENFORD ON Amount
  LEADING 1
  TO "Benford_Leading1"

Benford — first two digits

BENFORD ON Amount
  LEADING 2
  TO "Benford_Leading2"

Statistics — descriptive summary of a field

STATISTICS ON Amount
  TO "Amount_Statistics"

Count records meeting condition

COUNT IF Amount > 10000

Total sum of field

TOTAL FIELDS Amount, Tax_Amount

Age profile — transactions by time bucket

AGE ON Invoice_Date
  CUTOFF `20241231`
  INTERVALS 30 60 90 120 999
  TO "AR_Aging"

📅 ACL — Aging & Threshold Analysis

Section 11

Accounts receivable aging — 30/60/90/120 days

DEFINE FIELD Age_Bucket COMPUTED
  IF AGE(Invoice_Date, `20241231`) <= 30
  THEN "0-30"
  ELSE IF AGE(Invoice_Date, `20241231`) <= 60
  THEN "31-60"
  ELSE IF AGE(Invoice_Date, `20241231`) <= 90
  THEN "61-90"
  ELSE IF AGE(Invoice_Date, `20241231`) <= 120
  THEN "91-120"
  ELSE "120+"

Days between invoice and payment

DEFINE FIELD Days_To_Pay COMPUTED
  AGE(Invoice_Date, Payment_Date)

Flag overdue invoices (past 90 days)

EXTRACT RECORD IF
  AGE(Due_Date, TODAY()) > 90
  AND Status <> "PAID"
  TO "Overdue_90_Plus"

Slow-moving inventory (no movement in 180 days)

EXTRACT RECORD IF
  AGE(Last_Movement_Date, TODAY()) > 180
  AND On_Hand_Qty > 0
  TO "Slow_Moving_Inventory"

⚙️ ACL — Script Structure & Automation

Section 12

Complete Script Template

Full audit script — structured template

" ═══════════════════════════════════════════
" AUDIT SCRIPT: Vendor Payment Review
" Purpose: Identify anomalies in vendor payments
" Run on: Payments table (full year extract)
" Author: [Auditor Name]  Date: 2024-01-01
" ═══════════════════════════════════════════"

COMMENT Step 1: Open and verify source table
OPEN Payments

COMMENT Step 2: Set variables
SET v_start_date = `20240101`
SET v_end_date   = `20241231`
SET v_threshold  = 5000

COMMENT Step 3: Filter to period
EXTRACT RECORD IF
  BETWEEN(Payment_Date, v_start_date, v_end_date)
  TO "Period_Payments"

COMMENT Step 4: Run tests
OPEN Period_Payments
DUPLICATES ON Vendor_ID, Amount, Payment_Date
  TO "Duplicate_Payments" PRESORT
EXTRACT RECORD IF
  Amount >= v_threshold * 0.9
  AND Amount < v_threshold
  TO "Near_Threshold"

COMMENT Step 5: Summarize results
OPEN Duplicate_Payments
TOTAL FIELDS Amount
COUNT

Variables, Loops & Control Flow

Variable declaration and assignment

SET v_amount = 10000
SET v_name = "ACME CORP"
SET v_date = TODAY()

Conditional execution

IF v_amount > 5000
  EXTRACT RECORD IF Amount > 5000
    TO "High_Value"
END

Loop — run a script against multiple tables

GROUP
  DO SCRIPT "Analyze_Table"
    USING "Q1_Payments"
  DO SCRIPT "Analyze_Table"
    USING "Q2_Payments"
  DO SCRIPT "Analyze_Table"
    USING "Q3_Payments"
END

Log output to session log

LOG ON TO "Audit_Session_Log.log"
" ... run commands ..."
LOG OFF

Import Excel data into ACL table

IMPORT EXCEL
  FROM "C:\Audit\Payments.xlsx"
  WORKSHEET "Sheet1"
  TO "Payments_Imported"

Export results to CSV

EXPORT
  FORMAT DELIMITED
  TO "C:\Audit\Results.csv"

📗 Excel — Formulas & Functions

Section 13

Text Cleaning

Uppercase + trim combined

=UPPER(TRIM(A1))

Remove non-printable characters

=CLEAN(TRIM(A1))

Keep letters only — array formula (Ctrl+Shift+Enter)

=TEXTJOIN("",TRUE,
  IF(ISNUMBER(FIND(
    MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),
    "ABCDEFGHIJKLMNOPQRSTUVWXYZ")),
  MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),
  ""))

Replace character in string

=SUBSTITUTE(A1, "-", "")

Pad ID to fixed length with leading zeros

=TEXT(A1, "000000")   ' 6-digit zero-padded ID

Lookup & Reference

XLOOKUP — modern lookup with fallback

=XLOOKUP(A2,
  Table1[ID],
  Table1[Name],
  "NOT FOUND")

VLOOKUP — exact match

=VLOOKUP(A2, Sheet2!A:D, 2, FALSE)

INDEX + MATCH — flexible two-way lookup

=INDEX(C:C,MATCH(A2,B:B,0))

IFERROR — suppress lookup errors

=IFERROR(
  VLOOKUP(A2,Sheet2!A:B,2,FALSE),
  "UNMATCHED")

Duplicate Detection & Counting

Flag duplicates with COUNTIF

=COUNTIF(A:A, A2) > 1

Count duplicates — how many times does this appear

=COUNTIF($A$2:$A$1000, A2)

Multi-column duplicate check

=COUNTIFS(A:A,A2,B:B,B2,C:C,C2)>1

Unique count of values in range

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Date & Logic Functions

Days outstanding

=TODAY()-A2

DATEDIF — months between two dates

=DATEDIF(A2, TODAY(), "M")

Flag weekends

=WEEKDAY(A2,2)>=6   ' Mon=1, Sat=6, Sun=7

Aging bucket — 30/60/90/120+

=IFS(
  TODAY()-A2<=30,  "0-30",
  TODAY()-A2<=60,  "31-60",
  TODAY()-A2<=90,  "61-90",
  TODAY()-A2<=120, "91-120",
  TRUE, "120+")

Multi-condition flag (AND logic)

=IF(AND(B2>5000,C2="APPROVED",D2<>"IT"),
  "FLAG","OK")

SUMIFS — conditional sum

=SUMIFS(C:C,A:A,"ACME",D:D,"PAID")

⚖️ Tool Comparison & Best Practices

Section 14

Task	ACL	Excel	Notes
Datasets > 1M rows	✅ Yes	❌ No	Excel hard limit is ~1M rows
Audit trail / logging	✅ Built-in	❌ Manual	ACL logs every command executed
Benford's Law test	✅ Native	⚠️ Manual	ACL runs Benford in one command
Gap / sequence check	✅ Native	⚠️ Formula	ACL GAPS/SEQUENCE commands are direct
Join large tables	✅ Yes	❌ Slow	ACL JOIN handles millions of records
Quick pivot / visual	❌ Limited	✅ Pivot Table	Excel pivot tables are faster to build
Formatted reporting	⚠️ Export	✅ Yes	Export ACL results to Excel for presentation
Monetary Unit Sampling	✅ Native	❌ No	ACL MU sampling is GAAS-compliant
Repeatable/automated	✅ Scripts	⚠️ Macros	ACL scripts are version-controlled; VBA is fragile
Ad-hoc formula testing	❌ Verbose	✅ Fast	Excel wins for quick one-off calculations

Universal Best Practices

1. Standardize before matching. Always apply UPPER() + ALLTRIM() + INCLUDE() before any join, duplicate, or comparison. Case and whitespace mismatches silently kill accuracy.

2. Build consistent composite keys. For cross-table matching, derive a clean key field in both tables using the same normalization logic, then join on the key — not on raw source fields.

3. Never modify source data. Always EXTRACT to a new table before cleaning or transforming. Preserve the original import in its raw state for defensibility.

4. Document threshold decisions. Every filter threshold (e.g., >5000, 90 days) must be documented with the audit rationale. Reviewers and regulators will ask.

5. Validate results by sampling. After any EXTRACT or JOIN, run COUNT and TOTAL against the output and reconcile totals back to the source population. Never assume the output is correct.

← All articles

Research and direction by Ioannis (John) Konstas; compiled and finalized with AI assistance.