Better Business Through Data Analysis & Monitoring My Account

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.

Screenshot of Macros Ribbon

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.

Screenshot of Properties Window Showing Database Library

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.

List of Actions Taken in History View

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)”.

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.

File - Save As to create your new IDEAScript

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.

Using Project Overview to select routines to create a script out of.

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.

Finish Dialog box after selecting steps you want repeated

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.

View of selecting 'Play' to launch newly created 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 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:

HelpDesk@audimation.com
1-888-641-2800 x4
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 services@audimation.com for more information.

Print

Number of views (2467)/Comments (0)

Brad Newman

Brad Newman

Other posts by Brad Newman
Contact author

Please login or register to post comments.

Name:
Email:
Subject:
Message:
x
Global Chemical Company Automates Risks Identification with CaseWare IDEA
14 September 2018

Global Chemical Company Automates Risks Identification with CaseWare IDEA

Global corporation used IDEA to standardize & develop an automated process to comply with anti-corruption policy.

Read More

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®
14 September 2018

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®

Auditors Analyzing Hundreds of Millions of Records to Track Buying and Usage Patterns

One of the world’s largest telecom companies' internal audit team leverages tech to analyze trends, tendencies & patterns within customer & financial data.

Read More

Exploring IDEA's Built-In Power of Python
20 August 2018

Exploring IDEA's Built-In Power of Python

IDEA Tech Tip

With the release of version 10.3, IDEA created a buzz with its implementation of the Python scripting language, thereby taking a huge step towards having a more robust coding environment.

Read More

Sharing IDEA Data Using IDEA's ODBC Driver
18 July 2018

Sharing IDEA Data Using IDEA's ODBC Driver

IDEA Tech Tip

Read More

Oncor Achieves Operational Efficiencies with CaseWare IDEA
25 June 2018

Oncor Achieves Operational Efficiencies with CaseWare IDEA

Oncor's internal audit team needed to acquire years of data from different environments and formats to help improve processes and efficiency levels.

Read More

Analyzing the Past to Predict the Future with Trend Analysis
18 June 2018

Analyzing the Past to Predict the Future with Trend Analysis

In business, predicting the future is never an easy task. But IDEA can help as long as you have the right data, and Trend Analysis can help.

Read More

2018 IDEA® User of Excellence Award Winner Selected
15 June 2018

2018 IDEA® User of Excellence Award Winner Selected

Jennifer Girard, CIDA with American Red Cross Recognized for Exceptional Use of Data Analytics

Jennifer Girard, Sr Auditor with the American Red Cross is the recipient of the 2018 US IDEA UoE Award. She utilized IDEA to assist Finance Department during a natural disaster.

Read More

The Rules of Nature and Audit
6 June 2018

The Rules of Nature and Audit

What’s missing is often as important as what’s present. The rules of nature apply very well to audit if you consider, “Everyone knows it, but do you test for it?”

Read More

Using IDEA to Import QuickBooks General Ledger Data
4 June 2018

Using IDEA to Import QuickBooks General Ledger Data

The General Ledger is typically the starting point to an expanse of detailed information, see a step-by-step guide on how to get a readable version in idea.

Read More

New Features in IDEA 10.3 Part II
10 May 2018

New Features in IDEA 10.3 Part II

IDEA Tech Tip

In a recent article, we introduced the improvements to Stratified Random Samples and Python integration added in IDEA 10.3. But that’s just a small part...

Read More

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA
10 May 2018

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA

Auditors Achieve More Success in First Year with IDEA Than 5+ Years with Other Tool

With the entire audit team up and running using data analytics, they are finding new ways to integrate IDEA into other areas of the business and automate repeatable processes.

Read More

Maximizing Your Environment - Global Variables
8 May 2018

Maximizing Your Environment - Global Variables

Consider scope to be a building in which people work. The tools required to accomplish their tasks will of course be housed in that building, but in scripting it’s important to determine which tools need to be shared among the buildings.

Read More

Tips on Acquiring Data
8 May 2018

Tips on Acquiring Data

An IDEA User Shares Frequent Practices

When working with rows and columns of data to spot suspicious behaviors, fraud and errors can look very similar to one another. While Yeriazarian often uses Excel as an interim format for data, he also understands there are several avenues to navigate before getting the right data.

Read More

Identifying Churn in AR
19 April 2018

Identifying Churn in AR

IDEA Tech Tip

Customer churn (customer attrition, turnover, or defection) is when a customer decides to stop engagement with your company. IDEA can help easily identify “churn” in accounts receivable by following these steps...

Read More

RSS