Scribd Logo
Upload

Welcome to Scribd!

  • 256 views

    Acl Scripts 4 Beginners

    Uploaded by

    Adewale Raji
    This document contains instructions for various ACL scripts for beginners. It includes step-by-step instructions for scripts to identify data errors, calculate totals, compile statistics, stratify and classify data, summarize records, cross-tabulate fields, and create reports. The last section provides details on a two-part script that sorts and extracts the top 3 records by organization name.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Acl Scripts 4 Beginners

    Uploaded by

    Adewale Raji
    256 views10 pages
    This document contains instructions for various ACL scripts for beginners. It includes step-by-step instructions for scripts to identify data errors, calculate totals, compile statistics, stratify and classify data, summarize records, cross-tabulate fields, and create reports. The last section provides details on a two-part script that sorts and extracts the top 3 records by organization name.

    Original Description:

    ACL Scripts

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains instructions for various ACL scripts for beginners. It includes step-by-step instructions for scripts to identify data errors, calculate totals, compile statistics, stratify and classify data, summarize records, cross-tabulate fields, and create reports. The last section provides details on a two-part script that sorts and extracts the top 3 records by organization name.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    256 views10 pages

    Acl Scripts 4 Beginners

    Uploaded by

    Adewale Raji
    This document contains instructions for various ACL scripts for beginners. It includes step-by-step instructions for scripts to identify data errors, calculate totals, compile statistics, stratify and classify data, summarize records, cross-tabulate fields, and create reports. The last section provides details on a two-part script that sorts and extracts the top 3 records by organization name.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 10

    New York ACL Users Group www.nyaug.

    com

    Scripts for Beginners

    ACL

    Milan Shah

    September 29, 2009

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009

    ACL
    Support Options This procedure is used to identify Method URL data errors in the file or data. It should Notesbe performed on Self-Help/ Search the ACL Knowledge Base for a https://www.acl.com/supportcenter/search.aspx Online specific issue. Online Online
    https://www.acl.com/supportcenter/ol/ https://www.acl.com/supportcenter/forums/

    Online

    https://www.acl.com/supportcenter/contact_support/support_reque st.aspx

    Telephone

    https://www.acl.com/supportcenter/contact_support/global_support _contacts.aspx

    Learn ACL Online Ask a question on the ACL User Forum. Send a message to the ACL Support Center. Call for ACL Telephone Support:
    Please be specific with your issue; provide a short data extract if possible; or use WebEx as below.

    604.669.4997
    WebEx/ Online
    https://www.acl.com/supportcenter/kb/article.aspx?ID=5440

    ACL WebEx Support

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    2/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009 Verify
    This procedure is used to identify data errors in the file or data. It should be performed on the character, numeric and date fields.

    Step 1. 2. 3. 4. 5.
    Count

    Action Select Select Select Choose Click

    Menu Item Data Verify Main Field Name OK

    Notes

    This procedure is used to assess the completeness of the data set that you are analyzing using ACL.

    Step 1. 2. 3. 4. 5. 6. 7. 8. 9.

    Action Select Select Select Double Click Click Type Click Click Click

    Menu Item Analyze Count Records If Desired Field in Available Fields Box <> Button "0" OK OK OK

    Notes

    The Expression Box should now contain, Desired_Field <> 0. Ensure that you see Expression is valid in the information box. Closes information dialog box Runs the command in ACL

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    3/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009 Total
    This procedure is used to calculate a total on a field with a numeric value that can be used to compare to the control total in the data set.

    Step 1. 2. 3. 4.

    Action Select Select Select Click

    Menu Item Analyze Total Fields Desired Field in Name Box OK

    Notes

    Statistics
    This procedure is used to compile statistics such as: range, positive, negative, zero, totals, absolute value and 5 highest and lowest values in the data set.

    Step 1. 2. 3. 4. 5. 6. 7. 8. 9.

    Action Select Select Select Select Choose Select Select Enter Click

    Menu Item Notes Analyze Statistical Statistics Main Field Name Desired Field in Name Box More Tab # of High/Low Enter 10 for a Top Ten List OK

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    4/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009 Stratify
    This procedure is used to count the number of records occurring into a specified number of even intervals, and provides totals by stratum for selected numeric fields.

    Step 1. 2. 3. 4. 5. 6. 7.
    Classify

    Action Select Select Choose Choose Choose Choose Click

    Menu Item Analyze Stratify Field Name Minimum Maximum Intervals OK

    Notes

    Select the number of intervals desired.

    This procedure is used to count the number of records for each unique value of a character field and computes a subtotal for the selected numeric fields for each of the unique values.

    Step 1. 2. 3. 4. 5.

    Action Select Select Choose Choose Click

    Menu Item Analyze Classify Field Name Subtotal Fields OK

    Notes

    Optional

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    5/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009 Summarize
    This procedure is used to compile a record count and totals for numeric fields for each distinct value of key character fields in a sorted table.

    Step 1. 2. 3. 4. 5.

    Action Select Select Choose Choose Click

    Menu Item Analyze Summarize Field Name Subtotal Fields OK

    Notes

    Optional

    Cross-Tabulate
    This procedure is used to analyze character fields by reorganizing them in rows and columns. The cross-tabulate function counts the number of records in a table which: counts each row value within each column value, subtotals numeric fields for each row value within each column value, and totals the amounts for each column value.

    Step 1. 2. 3. 4. 5. 6. 7.

    Action Select Select Choose Choose Choose Choose Click

    Menu Item Analyze Cross-Tabulate Rows Columns Subtotal Fields Include Count OK

    Notes

    Optional

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    6/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009 Profile
    This procedure is used to provide summary statistics on one or more numeric fields in a table. The profile function provides the following information for numeric fields: Total value, absolute value, minimum, maximum.

    Step 1. 2. 3. 4. 5.

    Action Select Select Select Choose Click

    Menu Item Analyze Statistical Profile Profile Fields OK

    Notes

    Reporting
    This procedure is used to design and print reports.

    Step 1. 2. 3. 4. 5. 6. 7.

    Action Format Design Select Select Select Enter Click

    Menu Item

    Notes Format the view to suit your requirements. Report Layout

    Data Report Main Header, Footer and Other Information OK

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    7/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009
    What is an ACL Script? A script is a series of ACL commands that are stored as a unit in an ACL project. Scripts are useful to auditors because they can be run on demand or automatically. Why should I use scripts? Using scripts saves you and your company time and time is money. The auditor can focus on taking action using audit results instead of spending time preparing reports. Ok, I'm sold, how to I create a basic script? A. Use the script recorder to capture a record of all actions performed in ACL; B. copy the relevant "code" from the log file and save it as a reusable script. How do I learn to write my own scripts? 1. Take an ACL course that is taught by an ACL instructor; 2. read the script posts that are at ACL User Forums (http://www.acl.com/supportcenter/forums/topicview.aspx?id=5) and teach yourself; 3. buy Fraud Toolkit for ACL or other books and use the pre-written scripts at your own risk. Enough talk, let's start!

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    8/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009

    Top 3 Report (1 of 2)
    This ACL script opens a table, sorts it in descending order based on the frequency of occurrences based on the number of sales records and products a Top 3 Report.

    Step 1. 2.

    Action Import Data Verify Data in all Fields Create 5 New Folders

    Menu Item

    Notes Use the Data Definition Wizard Select and VERIFY all fields to identify any format or data integrity issues. A_Source_Data B_Scripts C_Deliverables D_Logs Z_Temp_Tables Name it: Top_3_Clubs

    Data/Verify

    3.

    File/New/Script

    4.

    Create New Script Script

    File/New/Script

    5.

    Script Editor Window/ OPEN NYAUG_SFB Line 1 Line 2 Line 3 Line 4 Line 5 Line 6 SET FOLDER /Z_Temp_Tables ACCEPT "Enter Rank Size:" TO RankCount1 RankCount = Value(RankCount1,0) RankCount = RankCount+1 SUMMARIZE ON Org_Name TO "OrgNameTemp1.FIL" OPEN IF Type = "Sale" PRESORT ISOLOCALE root

    6. 7. 8. 9. 10.

    Script Script Script Script Script

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    9/10

    1/21/2010,5:14 PM

    NY ACL Users Group (www.nyaug.com)


    Scripts for Beginners 9/29/2009

    Top 3 Report (2 of 2)
    This ACL script opens a table, sorts it in descending order based on the frequency of occurrences based on the number of sales records and products a Top 3 Report.

    Step 11. 12. 13.

    Action Script Script Script

    Menu Item Line 7 Line 8 Line 9

    14. 15. 16.

    Script Script Script

    Line 10 Line 11 Line 12

    17.

    Script

    Line 13

    Notes OPEN "OrgNameTemp1" INDEX ON COUNT D TO "ACLQSTMP" ISOLOCALE root SORT ON COUNT D TO "OrgNameTemp2" OPEN ISOLOCALE root OPEN "OrgNameTemp2" SET FOLDER /C_Deliverables EXTRACT RECORD TO "Top_%RankCount%_by_Org_Name" OPEN FIRST %Rankcount% OPEN "Top_%RankCount%_by_Org_Name" OPEN OrgNameTemp2 OPEN Top_%RankCount%_by_Org_Name OPEN OrgNameTemp2 OPEN Top_%RankCount%_by_Org_Name TOTAL FIELDS COUNT DEFINE Field Summary Computed Total1 DEFINE Column Default_View Summary

    18. 19.

    Script Script

    Line 14 Line 15

    20. 21.

    Script

    Line 16

    22. 23. 24. Script Line 17

    Prepared by: Milan Shah New York ACL Users Group www.nyaug.com

    10/10

    1/21/2010,5:14 PM

    You might also like