Page Content

Related Pages

Audit tools for spreadsheets

These analytical, investigative and manipulative tools are suitable for auditors and anyone interested in building and validating Excel spreadsheets. They have been designed so that they automatically generate a record of the investigation or analysis undertaken. In the case of the 'Spreadsheet Data List' functions this record is also dated and timed.

All the functions (except the worksheet formulae/functions) are presented to the user through a new menu item on the Excel 2003 standard menu bar called 'ACBA Functions'. The most regularly used items may also be called through a short cut tool bar. An MS Word document describing the ACBA Tools functions may be downloaded from the link.

The tools are grouped into eight categories

The package of ACBA Tools may be downloaded (free and open-sourced) direct from the ACBA Functions discussion forum.

Technical Requirements

You will need one copy per machine or workstation.

Help System

The package is supported by a fully integrated Help System which is provided for your local computer. The Help System is also maintained up to date on-line.

Spreadsheet Data Lists

Numeric Cells

This functions lists the sources, associated formulae and results of all the cells containing a number on the active worksheet. It is designed to allow reviewers identify those results that have been calculated by, or imported from elsewhere within, the spreadsheet and those figures that have been posted by some other means.

Formulae

It is often very difficult to identify the one formula which is causing integrity problems in a spreadsheet, especially if these involve links to an external file. List formulae identifies every formula within a worksheet or workbook and lists the formula together with its source address and the result on a separate worksheet. This enables a much quicker means of reviewing formulae for potential errors than inspecting cells individually.

Shapes

Shapes cover the whole range of drawing objects, buttons and other control objects that can be placed on a spreadsheet. Each shape can have its own macro which can be linked either internally or to a separate workbook. As with formulae, links to separate workbooks can cause integrity issues especially if the linked file is moved.

'List Shapes' lists all the shapes on each worksheet within the active workbook together with their macros, if they have one. The 'placement' column shows whether one shape is hiding another underneath it.

Errors

'List Errors' lists all the cells in a workbook that contain an error value, the formula that generated the error and a description of the error type.

List Comparison

This function allows user to compare lists (or databases) for missing records, changed critical values or to print the matching records in both lists. A full description of findings is created on a separate worksheet. The primary requirement is that the lists have comparable key values. Where this is not the case, the package offers the facility for creating a key field by combining two or more existing fields.

List Analysis - the Find and Mark Functions

ACBA Tools provides six functions for investigating lists held in Excel. These cover finding

They look for and mark records in a database or list. Where the programming requires that the list be reordered to undertake the comparisons, you are given the opportunity to retain a record of the original order.

"Entries which match a pattern" cause special problems of interpretation when employing negative criteria. The help system offers a discussion on the potential pitfalls of the interpretation of the results.

NB the 'significant entries function' is designed for use by auditors and accountants for whom debits (usually positive numbers) and credits (usually negative numbers) are equally important. It disregards the normal arithmetic rules associated with greater than and less than. This function looks at the size of the number irrespective of whether it is positive or negative. The function uses the comparison descriptions 'bigger than' and 'smaller than' to distinguish them from the standard arithmetic operators.

Random Sampling

Designed to help auditors deliver true random samples from paper based audit trail listings, but is also suitable for randomly selecting from records held in Excel. See Sampling web page for more detail.

Auto-Delete Rows

Used mainly for cleaning up databases imported from other sources - see Oddments - but can be very effective when used in conjunction with Find and Mark Duplicates for preparing unique records for random sampling.

Numbers within Strings

Like the 'Find and Mark' functions, these functions investigate lists in columns. In this case the lists are expected to be strings (a group of characters that is not recognisable as a number) that contain one or more numeric values. The object of each function is to extract and report the value you need. Each of these processes can also be used as a formula and are described in more detail at Numbers from Strings.

The advantage of using these form based processes over the Worksheet Functions described below, is that when sharing a file the new user does not need the ACBA Tools software to read the results. They are reported as standard numbers. Also, like the 'Find and Mark' functions, the process generates a column heading of the precise process and parameters used. This acts as an audit trail to your analysis.

Number Arrays within Strings

Very similar to Numbers within Strings only under this process all the distinct numbers or number groups within a string are analysed and posted to an array (a temporary database of the data found held in memory). The user can extract various characteristics of the number such as its value, its start position within string, its string length by reference to its location in the array.

A further advantage of this approach is that you can select which of several numeric values you want to extract, by requiring them to meet specific criteria that you can dictate in advance (including standard criteria like minimum and maximum values).

The Worksheet Functions

These are functions like SUM, SUMIF, COUNT and COUNTIF, that are available for use in formulae in the worksheet cells. You access the ACBA defined functions in the same way as you would SUM or SUMIF.

ACBA Tools provides a series of functions for reporting or extracting the numeric values contained within strings. These functions broadly divide into two categories

Extracting Numbers from Strings

These functions return those numbers that are either adjacent to each other or are separated by a full point only. No other non numeric characters are accepted. Essentially, the extracted values must resolve to a simple number or decimal fraction.

Two of the functions extract decimal values

All the remaining functions extract integer values

The last three of these functions return errors where the parameters supplied cannot resolve to an answer.

Generating a Number Array from a String

Each of these functions depend on the generation of an array of the numbers or number groupings contained in a string.

The single most important function tells the user how many number groups there are in the array. This defines how many positions (NUM) there are in the array associated with a specified string. The position values (NUM) range from 1 to the total number of groups found. This is the role of the function 'NUM_NUMARRAY' whose only parameter is the string value you want to analyse for the numeric values it contains.

The extraction of all data associated with a numeric group depends on its position in the array. The following functions each have two paramenters MyString (the string being analysed) and Pos (the position in the array). They provide access to

All values are positive. The user may investigate the PRECHAR and POSTCHAR values to determine the sign of a value.

A second range of functions, dependent on the number array approach,  allows the user to determine whether a string contains a number based on the logical comparison parameters of =, < and >. These can be applied to the criteria of value, start position or number string length.

Comparing the Numbers within Strings

Each of the functions above reports those digits that lie next to each other and therefore resolve to simple numbers. The next two functions review every numeric digit contained within a string (irrespective of whether they are separated by non-numeric characters) and compares their presence or absence in a second string. The result of the function is TRUE or FALSE.

SUMDIGITS and NUMDIGITS

These two functions deliver the sum and the number of the numeric values contained in a string, respectively. If there are no numeric values both functions return zero.

Approximately Equal

The approximately equal function is designed to allow limited inequalities to be permissible within the automated environment without creating a fatal crash. There are two versions - one allows for a statement to be true if the variance is within fixed limits; the second allows for a statement to be true if the variance is within a fixed percentage.

Technical Advice and User Support

The ACBA Functions user group provides a forum through which both technical advice and exchanges of user views and feedback can take place. Membership of the user group is free of charge. This forum is restricted to ACBA Function users and postings to it are moderated by ACBA staff. It provides an excellent resource for both technical and operational advice and information. We recommend strongly that users join the group.

Client List

Tools for Pragmatists

While ACBA Tools has an intrinsic self-contained value, its use in combination with ACBA Electronic Working Papers is explored at Tools of Pragmatists.