Tech Tip: How to Automate IDEA Workflows for Data Analytics
In my years of training, I have had many questions about creating scripts. Most users don’t realize that you can create a reusable script without ever having to learn anything about IDEAScript or coding in general – if you follow a few simple rules.
The first thing you need to do is understand what types of analysis you want to perform on your file in IDEA. This is needed as you will have to record the steps the first time and then automate them to work on subsequent files after that.
The second thing is to be aware that this script will only be able to work on a file with the same fields and fields types. This is usually not a problem because users probably already do this on standard files, such as bank statements, a p-card statement, and others. This file structure doesn’t usually change over time.
Play by the rules and you’re golden
If you are willing and able to follow the above rules, here is an example of how to do it:
In this example, I will be importing a simple bank statement (it is actually the Sample-Bank Transactions file that can be found in your samples project folder). I will do three different types of analysis on this file:
- Duplicate test
- High-value extraction.
The first thing I need to do is start recording the steps in IDEA. This is done by selecting the Macros ribbon and clicking on the Record Macro, as below. Now everything you do in IDEA will be recorded.
Next, we need to do is import the file. In this case we will be importing an Excel file called Sample-Bank Transactions.xlsx. This is a simple file, so we will stick with the defaults and import the file.
The next item on the list involves starting the analysis. The first extraction I will perform here is the duplicate key extraction. Select the Analysis ribbon and under Duplicate Key select Detection. You will see the dialog below and the key we are interested in is the TRANS_ID, so we are looking for any duplicate TRANS_IDs in the file as these should be unique.
Once we run the test, we will find there are two transactions with the same TRANS_ID.
The next test is performing a summary on the file by TYPE. Select the Analysis Ribbon and select the Summarization analytic.
In this case, I have selected the TYPE field to summarize and the AMOUNT field to total, and I will also include the Sum and Average statistics in the output file.
We can see that there are only two types and that there are more deposits then cheques.
The final test I want to perform is a high value extraction on the amount field. Namely, I want all amounts that are larger than $10K or less than -$10K. In order to do this test select the Analysis ribbon and the Direct extraction option.
In the direct extraction, select the calculator to enter the equation which will be, in this case, @Abs(AMOUNT) > 10000. This takes the absolute amount so we will see all amounts greater than 10K or less than -10K.
Now that we have performed all the audit steps, it is time to create our script. Go back to the Macros ribbon and select the Record Macro again to turn off the recording.
I will then have an option to create a Visual Script or an IDEAScript, select IDEAScript.
The equation editor will open up with the script. Now, have no fear – still no scripting necessary. The only thing we will be doing here is saving it so we can reuse it.
Save the file and give it a name to indicate the process. In this example I will just call it Automation Script.iss. By default this is saved in the Macros.ILB folder under the current project, but since I want to reuse it in other projects I am going to save it to the local library. Assuming that the default location has not been changed, you can find this folder in This PC\Documents\My IDEA Document\Local Library\Macros.ILB
Now that I have saved the script, I want to make it easily accessible so I am going to attach it to a ribbon.
Under the Macros ribbon there is a Bind to Ribbon option which allows you to attach scripts to your ribbon for easy access.
Once the Customize Ribbon dialog has opened from the Customize the Ribbon section, select Macros to attach the script to the Macros ribbon. Select the New Group option at the bottom to create a new group within the Macros ribbon.
Now select Rename to give it a better name than New Custom Group. In this example, I will use Automated Script.
Then select OK to rename the category.
Next thing is to add the script to this category. In the Choose Command section choose New.
This opens a dialog to select the script. Go to the PC\Documents\My IDEA Document\Local Library\Macros.ILB folder to find the Automation Script.iss and select the file. There is also the option to select an icon for the script.
Select OK to add the script.
The next step is to add the script to the category. Make sure the script is selected in the Choose command section and the Automated Script is selected in the Customize the Ribbon section and then select Add.
The script will now be accessible from the Macros Ribbon.
Now, when you want to rerun the script to create a new project, ensure:
- The source file has the same name and type you used to create the script.
- The fields and field types are the same.
- The source file is stored in the Source Files.ILB.
Click on the script from the ribbon and the script will perform the same analysis as before. Just remember to do a refresh in the File Exporer to see all the new files.
That’s all for today, but stay tuned to more tips on how to optimize IDEA to get the most out of your data analytics program.
Brian Element is a CaseWare IDEA Industry Strategist and former financial advisor to Public Services and Procurement at the Government of Canada. He has worked closely with IDEA software for nearly three decades.
To better incorporate data analytics into your own audit activities, watch our recent webinar, Data Analytics: Tools, Not Toys, which features case studies presented by two industry experts.