The Role of Spreadsheets in the Allied Irish Bank / Allfirst Currency Trading Fraud

This brief paper outlines how spreadsheets were used as one of the vehicles for John Rusnak’s fraud and the revenue control lessons this case gives us.

💡 Research Summary

The paper examines the infamous Allied Irish Bank (AIB) and its U.S. subsidiary Allfirst currency‑trading fraud, focusing on how spreadsheets became a central vehicle for John Rusnak’s deception and the broader control lessons that emerge. Between 1998 and 2002 Rusnak built a sophisticated scheme that concealed roughly $700 million in foreign‑exchange losses. He did this by maintaining parallel sets of Excel workbooks: one containing the “real” trades that were reported to the front‑office, and another containing fabricated trades that were used to offset the losses. Both workbooks shared identical column structures and relied heavily on VLOOKUP, OFFSET, and custom VBA macros to calculate profit‑and‑loss figures automatically.

The fraud hinged on the bank’s data‑ingestion process. Allfirst’s accounting system was configured to pull daily CSV files from a shared network folder and load them into the core ledger without substantive validation—only format checks (file name pattern, required columns) were performed. Rusnak saved his Excel files as CSVs with innocuous names such as “Daily_Trades_20011231.csv,” which the system accepted as legitimate. Because the CSVs already contained the manipulated figures, the accounting system recorded false profits, and the internal audit trail showed no discrepancy.

Key technical weaknesses identified in the analysis include:

1. Lack of version control – Rusnak repeatedly copied and edited the same file, overwriting prior versions while keeping the same filename, making it impossible for downstream users to verify which version was current.
2. Macro obfuscation – The VBA macros that performed daily “adjustments” were deliberately obfuscated, preventing a straightforward code review. These macros could dynamically replace loss‑making rows with zero‑balance entries before the file was exported.
3. Insufficient access controls – The spreadsheets lived on personal workstations and a network share with read/write permissions granted to many staff members, violating the principle of least privilege.
4. Automated data flow without integrity checks – The accounting platform trusted the incoming CSVs, lacking checksum verification, digital signatures, or independent reconciliation of the spreadsheet totals against source trade confirmations.

From a governance perspective, the case violates several COSO components: the control environment allowed a trader to both generate and report data; risk assessment failed to recognize spreadsheet‑driven data manipulation as a material risk; control activities such as segregation of duties, independent verification, and secure file handling were absent; information and communication channels did not provide timely alerts about anomalous data; and the audit trail was effectively non‑existent.

The paper draws concrete remediation recommendations:

• Restrict spreadsheet use to analytical or “what‑if” scenarios, mandating that all production‑level financial data be entered directly into an enterprise resource planning (ERP) or dedicated database system.
• Implement robust version‑control and digital‑signature workflows for any spreadsheet that feeds downstream systems, ensuring that any change is logged, reviewed, and cryptographically signed.
• Enforce macro governance by disabling VBA by default, requiring a security‑team sign‑off for any approved macro, and conducting regular static‑code analysis.
• Tighten file‑system permissions using role‑based access control, making critical data files read‑only for most users and writable only by a designated data‑steward.
• Introduce multi‑layer data validation before ingestion—checksum or hash verification, sample‑based manual reconciliation, and automated exception reporting for any deviation from expected trade‑to‑ledger mappings.

In summary, the Allfirst fraud illustrates that spreadsheets are not inherently dangerous; rather, they become a conduit for fraud when an organization lacks disciplined controls, transparent audit trails, and rigorous data‑validation processes. By treating spreadsheets as “controlled applications” and applying the same rigor afforded to mission‑critical systems, financial institutions can preserve the flexibility of Excel while mitigating the risk of large‑scale manipulation.