St. Louis ACL User Group

Developing a Robust Continuous Audit Tool

Nick and Rene will show us how they combined SQL and ACL to provide a continuous audit tool with an efficient exception management solution that has the flexibility of being able to access reports without having to be “ACL” proficient.

Registration and Lunch – 11:30 am

Meeting 12-1

Lunch provided courtesy of Laclede Gas

ACL UG 2012 Q1 Flyer

ACL has just announced that there will be an ACL ACDA examination in St. Louis on March 15 2012.

Registration details are available at http://www.cvent.com/Events/Calendar/Calendar.aspx?cal=bf960e5d-2985-49e6-8c44-ad18388ff4bd.

__________________________
ACL certification evaluates and recognizes your ability to integrate ACL technology into your financial analysis and business process. Earning the ACL Certified Data Analyst (ACDA) designation enhances your professional development while validating your technical skills and ensuring you meet performance standards necessary to address key business challenges.

Here more details!

Training_901

Please let us know if you have any questions!

SCRIPTING DATA INTEGRITY VERIFICATION

I like this convenient way to script the data intergity verification step with added value.

1.  Extract the entire record into a file if any field is not verifiable.

OPEN TESTFILE

EXTRACT RECORD IF NOT(VERIFY(field1) AND VERIFY(field2) AND VERIFY(field3) AND VERIFY(field…)) TO “Review_for_Invalid_Data.FIL”

You can send the records to excel if any data validation errors are found.

IF WRITE1> 0 EXTRACT Field1 Field2 Field3 Field4 XLS21 TO “Data_errors”

2.  Then you can also create the normal verify text file to indicate which fields weren’t verifiable.

OPEN TESTFILE

VERIFY ALL ERRORLIMIT 1000 TO “Data_ErrorsDetail.TXT”

Usually,  just looking at the .fil created by step one will be enough.  You can usually see why a record wasn’t verified, but the text file confirms exactly which fields were invalid.  The audience doesn’t have to know ACL to review an excel and txt file of data validation errors either!

Since ACL 9.2 is missing the function reference, we are posting it here.

 
http://docs.acl.com

Select ACL User Guide -> ACL Language Reference -> Functions

Please feel free to contribute any advice or resources for our tips and tricks menu.

Scripting Appends

When scripting appends, you can ensure that duplicate records will not occur even if the script is ran multiple times for the same period or other categorization.

For example, append the following fields to a yearly file in a script intended to be ran monthly:  Month, Location, Average_Lbs.

It would look similar to this:

EXTRACT Month Location Average_Lbs TO “A_Year_Avg_Per_Lb.fil” APPEND OPEN

If this is ran more than once in the same month, the monthly information would appear more than once. Add a summarization with no subtotals to all appends to eliminate this problem.

SUMMARIZE ON Month Location OTHER Average_Lbs TO “Final_A_Year_Avg_Per_Lb.fil” PRESORT

.

Blank Lines in scripts?

Use a blank line under the define line to indicate a conditional computed field.

DEFINE FIELD TEST COMPUTED

(blank line)

“Yes” IF test1 = “A”

“No” IF test1 = “B”

“Bad”

Also use a blank line to end a multiline comment section.

COMMENT

Line 1

Line 2

Line 3

(blank line)

code

Scripting multi-line descriptions

You can easily get multi-lined descriptions by doing it the manual way by right clicking on the column name, going to properties, and changing the alternative column title:

But how do you script this action? Try using the following format:

DEFINE FIELD SaleType2 COMPUTED
AS "Sale;Type"
SUBSTR(ltrim(SaleType),1,1)

Of course, your expression will be different than ours, but the key is the second line where the semi colons are used as line breaks. The result:

Thanks to everyone who joined us for the Panel Discussion.

And a special thanks to our panel!

If you missed it or want a refresher, here is a recap.

James Heavin, Shelter Insurance IS Audit Manager, provided us with a couple of very useful handouts.  Thanks James!

Stay tuned for information on our 4th quarter meeting information.

Learn how organizations have successfully implemented ACL through various methods.

Jim Kaiser, Bunzl Internal Audit Director, Implemented AX2 and several continuous monitoring scripts.
Mary Riggs, BJC Audit Services Manager, Works with regular system extracts to run scripts on demand
James Heavin, Shelter Insurance IS Audit Manager, Uses an extensive group of scripts and ad hoc analysis as standard audit procedure.

Tuesday, August 16
11 am – 1 pm
The Center of Clayton
50 Gay Avenue
Clayton, MO 63105
Multipurpose Rooms

Lunch will be provided courtesy of
Brown Smith Wallace, LLC
RSVP to Jan Beckmann by August 8

What do you want to ask?  Would you ask about their approaches to implementing ACL, audit planning, data access, training, continuous monitoring, etc?

Do you have a question to ask our panel?  Submit it via the comment function.  Your question will be sent to the administrator and appear after review.

Calculate Working Days Between Two Days

There may be times when you need to determine the number of working days between two dates. This result can be achieved within ACL by creating a conditional computed field involving multiple ACL functions.

Suppose that start_date and end_date are two date-type fields that are already defined in ACL. Calculating the number of working days between these two dates involves testing for different conditions. For example:

• If the starting date is, for some reason, after the ending date.
• If the starting and ending date both fall on a Saturday or Sunday.
• If either the starting or ending date fall on a Saturday or Sunday.
• If the weekday of the ending date falls before or after the starting date.
• If you need to take into account long weekends or holidays such as Christmas Day, New Year”s Day, and so on.

Read more »