Ready, Set, Script!

Automate Repeatable Processes Without Coding

More than ever, auditors are taking on the role of programmers; writing scripts to run automated analysis and tests on a regular basis for the organization. Luckily, tools like IDEA offer a graphical user interface (GUI) and built-in compiler to design scripts while reducing or eliminating the need for manually typing code. IDEAScripts work much like Microsoft® Excel macros, but with greater speed and flexibility.

This article provides some tips for creating IDEAScripts, examples of how fellow IDEA users are creating their own to meet audit and business goals, and information about available resources (including access to free pre-written IDEAScripts).

What is an IDEAScript?

 
IDEAScript is a programming language, similar to Visual Basic for Applications (VBA). Like other VBA languages, IDEAScript can only be used inside the IDEA application or an application developed in a higher programming language like Visual Basic, C++ or .NET. Simple routines that users run on a regular basis can be scripted without any VBA knowledge. There are three easy ways to create an IDEAScript:

Use the Recorder

 
The step-by-step process begins with clicking once on the “Record Macro” button, which activates the recorder.

Once you’ve finished the steps you want to make repeatable, click once again on the “Record Macro” button to stop the recording. All of the code for the recorded steps will appear in the IDEAScript editor as a new script. The editor will be live to make adjustments and refinements as needed.

Copy from History

 
First, in the database library, select a file to repeat or use as a template. In the Properties window of the selected file, select the History view.

In the History view, click on the expand button to view all actions related to the database and any parent databases that were used to create it.

You can then right-click on the desired IDEAScript and on the menu select the second option “Copy the IDEAScript for the selected task(s)”.

This will copy the selected script into the “Editor”. Then click on “Save As” to save the script.

Use Project Overview

 
Go to the Home tab and on the left side, find “Project Overview.” Select the routines to script within the Project Overview workflow.

IDEA will copy the VBA to a new script. After selecting the steps you want to repeat, click “Finish.” When you exit the Project Overview screen, IDEA will automatically write the script. Accept the warning notice, then bind to the Ribbon to run the script.

To bind the macro to the IDEA Ribbon, follow these steps:

1. On the Macros tab, in the Macro group, click Bind to Ribbon. The IDEA Options dialog box appears and the Customize Ribbon tab is displayed.
2. Click New….The New dialog box appears.
3. Optionally, from the Symbol box, select a symbol to associate with the macro.
4. Click the Browse button adjacent to the File name field.
5. Navigate to and select the required file.
6. Click Open. The file name and path is added to the File name field and the file name is added to the Command name field.
7. In the Command name field, enter the name you want to appear on the IDEA Ribbon. The default is the file name with the .iss extension.
8. Click OK. The macro is added to the Choose command list in the Customize Ribbon tab in the IDEA Options dialog box.
9. From the Choose command list, select the macro you added.
10. In the Customize the Ribbon list, select a custom group to which you want to add the macro. If a custom group has not been created, you must add a custom group to the IDEA Ribbon.
11. Click Add >> The macro name appears under the selected group in the Customize the Ribbon list.
12. Click OK.

The Script Editor will automatically launch, simply put the “Play” button, which will launch the IDEAScript.

IDEAScripts in Action

IDEAScripts can be customized to repeat just about any task or process. Here are just a few examples of how IDEA users are using scripts to verify data, automate complex processes and meet regulatory requirements:

Government: Verify Data & Identify Exceptions

 
A federal agency with a staff of approximately 100 people tasked with processing detailed information about seized assets into the division’s investigative database system and keeping the information accurate throughout the entire forfeiture lifecycle. Detailed information must be manually recorded and maintained in two non-integrated systems. It was impossible to analyze 100% of the data to search for errors and exceptions, and the entry process required more than 30 staff to examine up to 25,000 entries each year.

A custom IDEAScript was developed to import raw data from the asset tracking system and compare it to data stored in the separate database. The script compares data from 74 fields to produce exceptions in minutes. The IDEAScript streamlined comparisons between the two disparate systems to verify all data entries and search for exceptions. The new process saved hundreds of hours, and the agency was able to reallocate three quality control staff and 25 data entry personnel. More importantly, it significantly increased the accuracy of both systems and allowed the team to quickly identify and correct errors detected.

Energy: Automate a Verification Process

 
Stream, one of the largest direct-selling companies globally and a leading provider of energy, wanted to check customer billing and commission information for accuracy, and create an automated workflow to perform checks regularly.

IDEA is used to check more than 100,000 monthly commission checks, each containing at least 25 data points that must be verified for accuracy. Files from the commission engine are imported into IDEA for analysis. Using the Join feature in IDEA, the commission payment is compared to the invoice generating commission. Qualification rules are programmed into IDEA utilizing @functions and IDEAScripts to ensure adherence.

IDEA is also used to test for duplicate checks, recalculation of Associate point requirements to ensure they meet eligibility requirements, and accuracy of level payouts. Field statistics assist in completing monthly reporting of verification activities. The automated process helps Stream perform approximately millions of data points for commission checks in minutes. Using exception management techniques, the company is able to proactively identify missed commissions, identify defects in the system that require correction and ensure accuracy when calculating commission payouts.

Each customer bill includes up to 30 data points, and with more than 450,000 customers nationwide, the invoice review process validates hundreds of millions of data points. The company also uses IDEA to analyze hundreds of millions of customer billing records for correctness and completeness. An IDEAScript is used to test the accuracy of up to 30 data points on each customer invoice to be sure inputs are accurate. IDEA is also used to look for instances of high, low or negative usage.

Gaming: Track the Status of Chips

 
IDEAScripts can be used to improve just about any process. In the gaming industry, computerized slot machines use electronic chips, which are regulated and inspected by the state. Some machines have multiple chips, all of which management must be able to track at any given time. A GLI listing is used to reference whether a chip is approved, revoked or not used. The gaming organization can be fined for using chips with a revoked status.

An IDEA user-developed an IDEAScript to run the GLI listing against the Slot Master file to append every chip’s status, approved, revoked, not used, or blank where there may be a chip name discrepancy. In one instance of results, the Slot Machine Management Team found a previously unidentified revoked chip and proactively reported it to the State. The State was unaware of the issue until notified, creating goodwill for the company with the regulators.

Access Free IDEAScripts & Resources

The IDEAScript Vault has over 80 IDEAScripts available to any supported user. They are intended as samples and instructional materials, so you should be sure to test them carefully before using them in a production environment. But they can be extremely useful, particularly if you just need to perform something complex on an IDEA database on a one-time basis.

There are two ways you can get to the IDEAScript Vault, either from inside IDEA or by going straight to the CaseWare Analytics Support Portal (Passport). Here’s how to get there from inside IDEA.

Click the “File” tab, then click the “IDEAScript Vault” tile.

Here to Help

 
When writing or working with IDEAScripts, you don’t have to go it alone. The 20-minute rule still applies where all supported IDEA users are encouraged to call or email the Help Desk for assistance:

[email protected]
1-888-641-2800 x4
Hours of Operation: 7:00 AM – 7:00 PM (CST) M-F

Custom IDEAScript Development

 
Our Solutions Development experts can help you automate virtually any task to repeat analysis and testing on new data, or perform the same analytics across different systems and locations. Using Object Linking and Embedding (OLE) technology facilitated by IDEA, we can also perform calculations within text or spreadsheet applications. They can also assist with integration of non-IDEA applications. Contact [email protected] for more information.