Benford's Law

Command Overview

The benford command is used to test compliance with Benford's Law. Data to be tested can come from four sources:

Data file in tab separated value format
WorkSheet
Selected range within a worksheet
Database (e.g. MS-Access, MySQL, etc)

The six tests available for Benford's law are described as follows:

Description of Benford's Law tests
Short Description	Full description
First digit	For each numeric amount, the first non-zero digit is used for the test
First two digits	For each numeric amount, the two digits after the first non-zero digit are used for the test
First three digits	For each numeric amount, the three digits after the first non-zero digit is used for the test
Last digit	For each numeric amount, the last digit is used for the test
Last two digits	For each numeric amount, the last two digits are used for the test
Second digit	For each numeric amount, the second digit is used for the test

Syntax

The command is entered on the command line as the command name ("benford", or just "be") followed by one or more command parameters. Each command parameter consists of an argument name, immediately followed by an equal sign ("=") and then immediately followed by a value. The value may be enclosed with quotation marks, and must be enclosed by quotation marks if the value consists of any embedded spaces.

An example of a command parameter to specify that the file name to be processed is test.txt is entered as "file=test.txt" (without the quotations). Each argument on the command line must consist of only valid parameter names. If a required parameter name is missing, then the command line will be colored yellow, an error message will be displayed on the Excel status bar and an audible tone will be sounded.

The following is a listing of all parameter values for the benford command, separated between the types of data sources (ds). Parameters common to all data sources are described in the section "Common Parameters".

Argument name	Description	Example
Common Parameters (Required for all)
type	type of test to be performed, e.g. F1, F2, etc as described above (if omitted, the default is F1 - first digit)	F!
title	Optional, text to be displayed on the chart	"Warehouse Count Test Results"
col	the name of the column containing the amount to be tested	"tran amount"
recap	the worksheet where the results are to be stored	$Results
File Data Source (ds=file)
file	the name of the file to be processed	\test\invoice.txt
Worksheet Data Source (ds=rng)
sheet	the name of the worksheet to be processed	CMAData
ulc	upper left hand corner of the range to be processed	a2
Worksheet Selected Range (ds=sel)
sheet	the name of the worksheet to be processed	CMAData
Database (ds=db)
sql	the SQL query to obtain the data to be used as a source	"select vendor, amount from dupinv"
conn	the connection string for the database (varies by type of database)	"c:\ms\data\dupinv.mdb"

An example

The command to be perform a test of benford's law on the file named \test\invoice\tran.txt using the column named "Paid Amount" and test the first digit, storing the results on the worksheet named "Benford Results" is as follows:

benford ds=file file=\test\invoice\tran.txt type=F1 col="Paid Amount" recap="Benford Results"

Output from the processing will include a text listing of the statistics for each digit, and the digit that has the largest difference between observed and expected is highlighted. At the bottom is a text link to the chart file which has been created.

The command bar for benford processing is shown below:

Output from processing is to a worksheet, such as that shown below:

When the processing is performed, a graphic is displayed of the results. An example is shown below:

Further details on the use of Benford's law are available. An Excel® workbook with the applicable formulae is also available.

Main Form

Complete Auditor resources
Links \| Privacy Policy \| FAQ \| Downloads \| Trademarks