Monthly Archives: March 2014

FYTPE to Extend Define Scripts

If you have a set of scripts that you reuse, you probably also have to prepare client data to fit into those scripts. Round peg, square hole. As we know, certain ACL functions are dependent on the field type. We can create a conditional define that will determine the field type, check it out:


VALUE(Debit,2) IF FTYPE(Debit) = "C"
DEC(Debit,2)   IF FTYPE(Debit) = "N"

If Debit is brought in as a character field, then ACL will know to convert it to a numeric value. If it is numeric, it doesn’t change. If it is anything else, we get a lot of nines.

After that, your field is all ready for your script!

Regulation Demanding Increased Internal Audit Focus

There is a lot of survey talk at the beginning of this article, but the gems are at the bottom. Here they are…

Leveraging control testing across multiple compliance areas. Complying in multiple ways with various mandates through one test can save time but requires careful planning and coordination across functions. The percentage of respondents using this “one-to-many” approach grew to 54% from 49% the previous year. But there is room for more use of this tactic—92% of respondents said it’s possible to apply one-to-many principles to up to 50% of their control testing.

Use technology more effectively. Just 29% of respondents said their companies are using governance, risk, and compliance-specific (GRC) technology. That’s up from 23% in last year’s survey, but 36% of respondents said their organizations do not effectively leverage GRC technology. Respondents are using this technology most to manage their departments and report audit plans and results.

Use data analytics. Sixty percent of survey respondents are using data analytics to enhance the internal audit function. The top four benefits of using analytics were listed as increased efficiency; quick identification of patterns, trends, and relationships; increasing internal audit coverage; and (importantly) improving the strategic value of the internal audit function.

Upgrade staff skills. Forty percent of respondents said talent quality or capacity is a barrier to delivering maximum value for internal audit teams. Jordan said internal audit can identify areas for improvement by conducting a skills gap assessment by mapping the skills of the audit team against the audit plan. “It’s in your plan, so you’ve got to acquire that skill, whether you do it in-house or out,” Jordan said.

via Regulation demanding increased internal audit focus.

Run Dates

Ever get to a point when you’re working and you end of with a file named “Some Name v10 Final v4”? I’m definitely guilty of this. While I can’t solve the file hoarding problem, I can recommend a way to differentiate multiple exports of the same file.

We will create a variable that is a time stamp for your script run.


v_runTime   = DATETIME()
v_timeStamp = REPLACE(ALLTRIM(%v_runTime%),":","")

OPEN Table_1
EXPORT FIELDS ALL XLSX TO "Your Fav Filename %v_timeStamp%" WORKSHEET 'Sheet1'

Every time you run a script, the new file’s name will have the current date and time appended to it. Fair warning, if you run the script ten times in a row, you’ll have ten unique files.

EXECUTE Command: Return Code

A new feature of ACL v10+ is the EXECUTE command. For those unfamiliar with it, this command allows a script to execute a script, command line argument, website request, etc. that exists outside of ACL. I was hoping that ACL would patiently wait for the script to execute, take the script’s return value, place it in an ACL variable, and keep running.

Nope. Not the case. That’s just too easy.

Instead, ACL picks up the Windows System Error Code and places that within the RETURN_CODE variable. So what are these?

The important codes you need to know are 0 and 1. If Windows returns a 0, the operation/script executed successfully. If it returns a 1, it did not. Be aware you can receive a many many many different error codes. Here is a link to the Windows System Error Codes. If you are having difficultly falling asleep tonight, crack that open and take a look.

Bottom line, the RETURN_CODE variable is not the return value from your external script. One workaround is to have the external script output to a CSV and have ACL pick it back up via an import.

Hopefully, by the next Users Group Meeting, we will have a good working example to present!

Using FILESIZE to Escape Scripts

If you’re writing a script that has commands or functions dependent on a table having data, you’ve probably run into a situation where when the table is empty, the script breaks. One way to prevent this from happening and returning ACL back to the main script is to use the function FILESIZE to test the table for content and escape if empty or nonexistent.

IF FILESIZE("tableName") <= 0 ESC

Note that the tableName must be contained within double quotes. Also, we only care if the table size is 0 or -1. If it is 0, the table exists but has no records. If it is -1, the table does not exist.

Another caveat is that the script that contains this command should have been called from a main script. Your main script will have some admin commands (SET FOLDER, SET DATE, etc) and then be filled with DO SCRIPT commands.

Fraud Drains Trillions of Dollars Every Year from Business

Interesting article in Accounting Today about fraud here. One of our favorite tid bits is:

By 2016, more than 25 percent of large global companies are expected to use technology involving so-called “big data” and analytics to combat fraud, an increase from only 8 percent today, with those organizations expected to see a full return on investment within six months, according to IBM.

Removing the Last Character From a String

I ran into an interesting problem the other day. The account numbers from an import had an extra period at the end of them (e.g. “1000.0.”). The twist was that not all of the account numbers ended like that. This handy conditional computed field did the trick.


SUBSTR(T_Acct_No, 1, LENGTH(T_Acct_No)-1) IF LAST(T_Acct_No, 1) = "."

T_Acct_No has already been defined and for brevity, I removed the ALLTRIM() that was enclosing each instance of T_Acct_No.

Here’s the English version of what I did:

“Substring the account number, starting at the beginning and ending 1 character less than the full length of the field. Do this if the last character of the field ends with a period. If not, the field will remain unchanged.”


User Group Presentation

Thanks for everyone who came to the User Group meeting yesterday! We had a great turnout and it appeared everyone had fun!

Jan briefly mentioned that the next meeting has been rescheduled to June 18th. There will be an invitation sent out around that time as well.

As promised, here is Kim’s presentation about ACL v10 changes as well as the Excel export script she was demonstrating. This can be used with the ACL sample files that come with ACL. The project is “ACL Demo”.

ACL v10 Changes

Writing to multiple tabs script:


DELETE Workbook_Example2.xlsx OK
OPEN Metaphor_Trans_All

OPEN Metaphor_Dept
 EXPORT FIELDS Dept  DeptDesc  XLSX TO "Workbook_Example2.xlsx" WORKSHEET Metaphor_Dept


Always a Character!

Working in public accounting I am very aware that busy season is in full swing. With that comes journal entry testing! What is journal entry testing? Basically, our team provides the audit department with support in identifying journal entries that could be tested as part of their audit program. Don’t worry, I’m not diving into that in this post. The key point is that a lot of the general ledgers we receive are — how should I put this — less than ideal for imports.

How do we get around iffy fields? Simple, import everything as a character field (ASCII to be exact) and define later. Debits and credits? Character. Dates? Character. You get the drift. Not only does this help prevent issues, it will also help you find errors.

I recently ran into a problem where the description field was pouring into the debit field. The company had ended a few descriptions with “2013”. When I looked at the imported character field, it showed “20131,000”. Had I imported this as a number, I only would have seen a $20,130,xxx debit, not the weird punctuation that alerted me to the issue.

Bottom line, import as character, define your own fields, and enjoy easier analysis!