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 (1649)/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
5 Elements of a Successful Data Analytics Program
12 March 2018

5 Elements of a Successful Data Analytics Program

Building a successful, sustainable data analytics program takes effort…and a few other elements.

Read More

The Power of Field Statistics
7 March 2018

The Power of Field Statistics

IDEA Tech Tip

IDEA’s Field Statistics can save you a wealth of time. It should be the first thing you go to after importing a file. Why?

Read More

Using Data Analytics to Prepare for Fieldwork
20 February 2018

Using Data Analytics to Prepare for Fieldwork

Automating High-Risk Transactions with IDEA

See how a global corporation used IDEA to develop an automated process to prepare auditors for on-site work.

Read More

People. Processes. Tools.
5 February 2018

People. Processes. Tools.

Successful Integration of Data Analytics

Expert IDEA users share their experiences and advice for building and sustaining a successful data analytics program.

Read More

Planning & Scoping Your Audit with Data Visualization
1 February 2018

Planning & Scoping Your Audit with Data Visualization

Data-Driven Insights Save Hours of Effort

Here are some ways IDEA can help you plan and scope an audit using the data visualization features within IDEA.

Read More

Classroom Collaboration at University of Missouri - St. Louis
30 January 2018

Classroom Collaboration at University of Missouri - St. Louis

Student Learn to Develop Analytic Tests

Masters students attending the University of Missouri - St. Louis share their results using data analytics to assess risk, test 100% of the data population.

Read More

Do One Thing Every Day that Scares You
30 January 2018

Do One Thing Every Day that Scares You

Virginia Tech Students Gain Confidence Using Data Analytics

Learn how Associate Professor of Practice Nadia Rogers, CPA, introduced students to IDEA, including types of audit tests that can be performed within the software and various documentation aspects of an audit engagement.

Read More

New Features in IDEA 10.3
29 January 2018

New Features in IDEA 10.3

IDEA now runs faster and offers more flexibility than ever before

The latest version of IDEA introduced a host of new features and improvements.  IDEA now runs faster and offers more flexibility than ever before.

Read More

5 Ways Data Analytics Saves Time
26 January 2018

5 Ways Data Analytics Saves Time

Turn Hours of Work into Minutes

5 time-saving ways data analytics can help you shrink your audit time, while improving the quality of your work.

Read More

Tired of Wrangling Cumbersome Client Data?
24 January 2018

Tired of Wrangling Cumbersome Client Data?

10 Ways Our Importing Services Make it Easy

10 reasons to try our data importing services, which save you time, effort and undue frustration. 

Read More

A Journey in Using Data Analytics
24 January 2018

A Journey in Using Data Analytics

Testimonial from Brian Cullum

Read how Brian Cullum used IDEA to help identify a multi-million-dollar fraud based on the overstatement of the borrower’s lending base.

Read More

Planning & Scoping Your Audit
15 January 2018

Planning & Scoping Your Audit

With Data Visualization

Data visualization is changing the way auditors are communicating with their audiences. Easy planning, find risk areas and prioritize your scope of work.

Read More

Infusing IDEA into Your Organization
16 November 2017

Infusing IDEA into Your Organization

Building a successful and sustainable data analytics program requires a mix of people, processes and products...it all starts with collaboration.

Read More

Using Nested @Functions()
16 November 2017

Using Nested @Functions()

IDEA Tech Tip

Combining @functions in creative ways can open a wealth of new capabilities that you may not have known your IDEA software is capable of.

Read More

RSS