Forensic Analytics — Data Profiling (Part 1)
This article is the first of a series of very short articles on how to effectively use python for forensic analytics. These articles are heavily influenced by the methods explained in Mark Nigrini’s book “Forensic Analytics”. I’ve borrowed many of the terms, definitions and explanations used in the book “Forensic Analytics” in this article.
So Let’s Get Into It!
Forensic analytics is a combination of advanced analytics and forensic accounting and uses investigative techniques to identify potential rare events of consequence. With our lives becoming evermore digital, the need for forensic investigators has grown exponentially. Not only have our lives become entirely digital with a huge digital footprint but the methods used to commit fraud have become digital as well.
In this first article, I explain the very first step in Mark Nigrini’s forensic analytics framework — data profiling — and how to properly execute it using python, which is an open source general purpose programming language that is heavily used in the profession of data analytics. The data I use to do this is the state of Maine’s vendor payment data. You can find this data as well as the jupyter notebook that contains my code in my github page located here https://github.com/dlee1982/audit_accounting.
High-Level Data Overview Tests
To give a forensic investigator a better understanding and feel for the data, three high-level tests can be conducted. These three tests are: data profile, a data histogram and a periodic graph. This article covers how to perform the data profile step using python. The next article will cover how to conduct both the data histogram and periodic graph steps.
The Data Profile
Get David Lee’s stories in your inbox
Join Medium for free to get updates from this writer.
To better understand the data and determine whether it is even usable, a forensic investigator must first profile his data. Serious data issues such as erroneous or incomplete data can be revealed during this step. To profile the data using Maine’s vendor payment data, I first import the data using a function I created called read_files. This function first imports the data using pandas read_excel function and then combines all the workbook’s individual worksheets into one worksheet:
Next, I create the function data_profiling that divides the data into six different strata.
Using the data_profiling function, a dataframe containing the total number of occurrences of each strata is returned.
Just like that, an investigator obtains a better understanding and feel for his or her data. As the dataframe above shows, there are 49,115 payments that are greater than $50, 17,309 payments that are within $10 and $49.99, and so on and so on. Further strata can easily be added to the dataframe above depending on the circumstances and the needs of the investigator. In turn, an internal auditor can use these strata to point to low-value items that are costly to process and high-value items that could be used to determine materiality.
Bibliography:
Nigrini, Mark J. 2010 “Forensic Analytics — Methods and Techniques for Forensic Accounting Investigations”.
