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:
The Script Editor will automatically launch, simply put the “Play” button, which will launch the IDEAScript.
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 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.
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:
Hours of Operation: 7:00 AM – 7:00 PM (CST) M-F
Hands-on IDEAScripting Training
If you plan on attending the next IDEA Innovations Conference in November 1-2, 2017 in Houston, be sure to attend our Ready, Set, Script hands-on session. Click here for more information.
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.