IDEA Tech Tip: How to Create an IDEAScript (Part 2)
A: Within IDEA you can create a script and reuse it without having to make any changes to it, so long as you follow a few rules.
Let’s take a scenario where you receive two files each month: one is the detailed sales files with all the sales transactions for the month, and the other is customer information such as their name, company, etc. Each month you want to take the detailed sales file and join it to the customer information so that each transaction has the name of the company and name of the customer.
There are different ways to create the IDEAScript, with one of the easiest being through the history. The only problem with this, however, is that in this scenario we have two files and the history only shows the history for one file. If you want to don’t want to manipulate the script, the best option is to plan out what is needed to do. In this case there are three steps that need to be performed:
Import the Detailed Sales File
Import the Customer Information File
Join the Customer Information File to the Detailed Sales Files using the CUSTNO (the key field between the two files).
You also need to decide on a generic naming convention for the files you want to import. We will be changing the name of the files, so we want something standard in IDEA.
In this example I have the 2014 and 2015 Detailed Sales file. Because I will be using these files at different times I want to first rename the 2014 to Detailed Sales (when I do the analysis on the 2015 file I will also rename it to Detailed Sales). The files will look like this:
Now that I have the steps I need to perform and the files renamed to a more generic format I can create the script (this will only have to be done the first time you perform the analysis unless the file structure or the analysis changes in the future).
Go to the Macros ribbon and select the Record Macro.
Perform the two imports and the join. Once that is complete, return and click on the Record Macro icon again. You will then have this ‘Create a Macro’ dialog; make sure you select IDEAScript and click on OK.
You will now have a generic script that imports two files and performs one join.
Save the file as you will be using it in the future.
When you next want to do the analysis (such as getting the next periods from the Detailed Sales File and Customer Information File) create a new project or delete/move the files in the current Project folder. Go into Windows explorer and rename the 2015 Detailed Sales.xlsx file to Detailed Sales.xlsx and make sure they are stored in the same place as the previous periods file (usually within the Source Files.ILB folder of the project).
The key to running the script with no changes is that the files used to create the script always have the same name each time you run the script.
Go to the Macros ribbon and select Run.
Select your script and click on open.
The script will then run and the analysis will be recreated with the new files. You will have to go into the file explorer and click refresh to see the new files.
You have now created a reusable script and you haven’t had to change anything in the script to make it work on files from a different point in time. The trick to remember is that the file names imported always have to be the same or else the script will give an error that it can’t find the file.
– Brian Element, IDEA certified instructor and Financial Advisor at Public Works and Government Services Canada
For more tips or to ask your own IDEA question, visit www.ideascripting.com.
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.