Applying Forensic Accounting Techniques Using SQL and R
WARNING!
This presentation is now obsolete. The information in this talk has not been updated in a while and could be outdated. Use the information in this presentation at your own risk and be sure to double-check things to ensure that any information you use is accurate.ABSTRACT
Forensic accountants and fraud examiners use a range of techniques to uncover fraudulent journal entries and illegal activities. As data professionals, most of us will never unravel a Bernie Madoff scheme, but we can apply these same techniques in our own environments to uncover dirty data. This session will use a combination of SQL Server and R to apply these fraud detection techniques, which include Benford's Law, outlier analysis, time series analysis, and cohort analysis.
ADDITIONAL MEDIA
On May 3, 2017, I gave a version of this talk at 24 Hours of PASS. You can get the recording and slides notes on the event website.
On November 5, 2017, I recorded a full-length version of this talk, which you can find on my YouTube channel.
SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation.
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
Jupyter Notebooks
If you are not familiar with Jupyter notebooks, I have a guide on how to install Jupyter on Windows. This will allow you to try the notebooks out on your own.
Data Sets
Here are links for the individual data sets used in this talk. Please note that the versions I have archived are for specific points in time, so the data format may change, and the data itself will likely have changed. For the actual data sets I use, check out the demo code above.
- Durham crime set.
- Wake County financial transactions.
- Durham city budget.
- Raleigh police incidents.
- North Carolina town and city populations.
Wake County Transportation Fraud
- WRAL did an excellent job of covering the Wake County school bus fraud case. You can read all of their stories online.
- The North Carolina State Bureau of Investigation (SBI) has a review of several white-collar crime cases, and Wake County transportation fraud starts on page 13.
- Paul Zikmund reviewed the case as well as several others.
- Michael East covered this case as well as several others. The regression analysis slide I show an image of in my presentation comes from this talk.
Benford's Law
Benford's Law is one of the most interesting numeral analysis findings, given its wide-ranging and unexpected appearances in data sets.
- I have a quick post on Benford's Law.
- Antoinette Lynch has an article on using Benford's Law for fraud detection.
- Statistical Consultants, Ltd. provides probability tables for Benford out to two digits.
- Tomaz Kastrun explains Benford's Law using R.
- Mark Nigrini has written the book on Benford's Law as it applies to fraud analysis.
Fraud Detection Techniques
Although I did not use most of the techniques in this section, I want to provide links for people interested in learning more.
- I am indebted to Mike Blakley, whose slide deck on fraud analysis techniques formed a great deal of the basis for my talk.
- The Kolmogorov-Smirnov Test helps explain if two data sets are significantly different.
- Chi Squared Tests have several important uses in statistical analysis.
- Vasileios Megalooikonomou at Temple University has a Power Point slide deck explaining how to determine association rules using the apriori algorithm.
- Christian Borgelt has an open source implementation of the apriori algorithm.
SQL Server Techniques
- David Smith shows off a credit card fraud detection algorithm with SQL Server R Services.
- Dejan Sarka has a great series on fraud detection using SQL Server.
- Itzik Ben-Gan explains how to solve the Gaps and Islands problem in SQL Server 2012.
- Ed Pollack has a detailed explanation of the Gaps and Islands problem.
- Ed also has a slide deck and demo for his advanced-level session on gap analysis.
- If you need a non-disk tally table, here is a good script.
Other Fun Topics
- Jay Zhou explains a check fraud analysis product at Citizens Bank.
- I reference an online Bangladesh bank heist using the SWIFT system in the talk.
- One of the original association rule analyses was the beer and diapers study.
- Mike Blakley has a paper entitled SQL as an Audit Tool. The ungated version is (was?) on his company site. You can get the paper itself through the journal, though that'll probably cost you.
- If you are interested in learning a lot more about regression, I highly recommend Introductory Econometrics: A Modern Approach by Jeffrey M. Wooldridge. I bought a used copy for about the same price as the paperback edition.