Using Apps Script, a Google Sheets Macro and Benford’s Law to Detect Potential Fraud
As one of the only technologists in a family of CPAs I’m always seeking ways to participate in the discussion. So when the topic of using Benford’s Law as a risk assessment tool recently surfaced in conversation I decided to develop a custom macro to automate the analysis inside Google Sheets.
Benford’s Law
In a nutshell, Benford’s Law is a mathematical theory based on a logarithmic function which states that for a set of non-manipulated, naturally occurring numbers, the frequency of leading digits “1” to “9” is distributed in a specific pattern as shown in Diagram 1.
Performing analysis on large sets of naturally occurring numbers, Benford’s Law predicts the leading digit “1” to occur approximately 30% of the time while predicting leading digit “9” to occur approximately 5% of the time as shown in Diagram 2.
This is in contrast with an “equal” distribution (approximately 11%) for the same leading digits as shown in Diagram 3 … as well as in contrast to “manipulated” numbers which are not as likely to follow the same predicted distribution.
Armed with Benford’s Law, auditors can perform risk assessments to identify transactions which differ significantly from the expected frequency distribution and more easily spot errors and potential fraud.
For more insight into Benford’s Law and its potential audit uses see this great Journal of Accountancy article Using Excel and Benford’s Law to detect fraud.
Google Sheets Macros
Google Sheets now provides macros to automate repetitive tasks. Macros can be created in two ways:
- Record macros using the built-in recording tool
- Import custom macro functions written in Apps Script
Since I wrote a custom Apps Script function to automate Benford’s Law, I’ll only discuss macro importing in this article.
For more on using Google Sheets macros see Automate Tasks in Google Sheets.
Custom Google Sheets Macro for Benford’s Law
Follow these three steps to install, import and run the Benford’s Law macro in Google Sheets:
1. Install and Import Macro
Make a copy of this Google Sheet with the macro already installed and imported. Click the Copy button to copy the Google Sheet into Google Drive. If you are not logged into your Google Account, you will be automatically prompted to do so.
2. Add Sample Data to the Google Sheet
Import data or use the IMPORTRANGE function to import data from another Google Sheet.
To test the macro with sample data, download and import the example Excel file from the article Using Excel and Benford’s Law to detect fraud.
3. Run Macro
To run the Benford’s Law macro, navigate to the sheet with data to analyze and select the Google Sheets menu Tools > Macros > BenfordsLaw as shown in Diagram 5.
When the macro runs it prompts for two values:
- Column to Analyze — this prompt requires the letter of the column of data to be analyzed. For example, if Column A contains the data to be analyzed enter the letter A as shown in Diagram 6.
- Benford’s Law Start Column — this prompt requires the letter of the first column in which to start the Benford’s Law analysis. This column will be used along with four additional inserted columns and should contain no existing data. For example, if Column C is the first column for Benford’s Law macro calculations then enter the letter C as shown in Diagram 6.
These prompts require column letter values. Any other values will cause the macro to abort.
Important:
- The first time a macro is run in a Google Sheet it will ask for authorization. Follow the prompts to complete the authorization flow.
- You may want to make a copy of the data sheet before running the macro as it will modify the data number formatting as well as add additional columns and a chart to the sheet to accommodate the analysis.
Once the Benford’s Law macro has completed it will have inserted four additional columns to the right of the designated Start Column, performed the analysis on the Sample Data and inserted a line chart showing the comparison of the Benford’s Law and Sample Data First Digit distributions as shown in Diagram 7.
Important:
- The Benford’s Law macro will take longer to run on large data sets and it may initially appear that the macro is no longer working or the chart may initially display with a “No Data” message. Wait for the macro to fully complete.
- If the data set is too large, the Benford’s Law macro may exceed Apps Script quotas and cease to function.
Need help automating processes inside your Google Sheets or G Suite domain? Get in touch
Follow me on Twitter , Medium and Github.
