Learn to Effectively Script in ACL – The Keys To Getting Started and Fully Automating Your Testing.
Slide 1 Learn to Effectively Script in ACL The Keys To Getting Started and Fully Automating Your Testing Slide 2 AuditNet and AuditSoftware.Net Collaboration Brought to you by AuditSoftware.net andAuditNet , working together to provide Practical audit software training Resource links Independent analysis Tools to improve audit software usage Today focused on providing practical dataanalysis training Page 1 Slide 3 About Jim Kaplan, CIA, CFE President and Founder of AuditNet, the global resource for auditors (now available on Apple and Android devices) Auditor, Web Site Guru, Internet for Auditors Pioneer Recipient of the IIAs 2007 Bradford Cadmus Memorial Award. Author of The Auditors Guide to Internet Resources 2nd Edition Page 2 Slide 4 About AuditNet LLC AuditNet , the global resource for auditors, is available on the Web, iPad, iPhone and Android devices and features: Over 2,000 Reusable Templates, Audit Programs, Questionnaires, and Control Matrices Training without Travel Webinars focusing on fraud, audit software (ACL, IDEA, Excel), IT audit, and internal audit Audit guides, manuals, and books on audit basics and using audit technology LinkedIn Networking Groups Monthly Newsletters with Expert Guest Columnists Book Reviews Surveys on timely topics for internal auditors Introductions Page 3 Slide 5 Webinar Housekeeping Page 4 This webinar and its material are the property of Auditnet LLC Cash Recovery Partners LLC. Unauthorized usage or recording of this webinar or any of its material is strictly forbidden. We are recording the webinar and you will be provided with a link to that recording as detailed below. Downloading or otherwise duplicating the webinar recording is expressly prohibited. Webinar recording link will be sent via email within 5-7 business days. NASBA rules require us to ask polling questions during the Webinar and CPE certificates will be sent via email to those who answer ALL the polling questions The CPE certificates and link to the recording will be sent to the email address you registered with in GTW. We are not responsible for delivery problems due to spam filters, attachment restrictions or other controls in place for your email client. Submit questions via the chat box on your screen and we will answer them either during or at the conclusion. After the Webinar is over you will have an opportunity to provide feedback. Please complete the feedback questionnaire to help us continuously improve our Webinars If GTW stops working you may need to close and restart. You can always dial in and listen and follow along with the handout. Slide 6 Disclaimers 5 The views expressed by the presenters do not necessarily represent the views, positions, or opinions of AuditNet or the presenters respective organizations. These materials, and the oral presentation accompanying them, are for educational purposes only and do not constitute accounting or legal advice or create an accountant-client relationship. While AuditNet makes every effort to ensure information is accurate and complete, AuditNet makes no representations, guarantees, or warranties as to the accuracy or completeness of the information provided via this presentation. AuditNet specifically disclaims all liability for any claims or damages that may result from the information contained in this presentation, including any websites maintained by third parties and linked to the AuditNet website Any mention of commercial products is for information only; it does not imply recommendation or endorsement by AuditNet Slide 7 Richard B. Lanza, CPA, CFE, CGMA Over two decades of ACL and Excel software usage Wrote the first practical ACL publication on how to use the product in 101 ways (101 ACL Applications) Has written and spoken on the use of audit data analytics for over 20 years. Received the Outstanding Achievement in Business Award by the Association of Certified Fraud Examiners for developing the publication Proactively Detecting Fraud Using Computer Audit Reports as a research project for the IIA Recently was a contributing author of: Global Technology Audit Guide (GTAG #13) Fraud in an Automated World Institute of Internal Auditors. Data Analytics A Practical Approach - research whitepaper for the Information System Accountability Control Association. Cost Recovery Turning Your Accounts Payable Department into a Profit Center Wiley and Sons. Please see full bio at www.richlanza.com Slide 8 Learning Objectives Understand the value of automating data work and how ACL scripts can be your link to continuousmonitoring.See how to build an ACL script in two minutes (with no prior experience) and the various approaches towriting scripts.Walkthrough a variety of different built scripts (provided to attendees) to understand the range of ACL inpre-developed applications.Learn how to maximize the new ACL script editor (version 9.3 and forward) to make script writing thatmuch easier.Understand how to create all types of ACL dialogs and menus for rolling out interactive scripts.Learn how to manage and move scripts across various ACL documents, as well as, making desktopicons to run entire ACL programs with one click on your desktop. This includes working with ACLfolders within the ACL document and scripts.Walkthrough intermediate techniques including a Monthly Stratification script, using Loops and Groups,as well as, the difference between Sorting and Pre-Sorting data.See how to complete a key word search using a subroutine script.Learn and document best practices for script development to ensure knowledge transfer to otherswithin the organization. Page 7 Slide 9 Making an ACL Script in Two Minutes Page 8 Slide 10 Value of ACL Scripting Useful for routine tasks Can be for periodic audit steps Also can assist in importing similar data For example, OPEN FILE 1, EXTRACT RECORD,OPEN FILE 2, EXTRACT RECORD, etc. Build the quality in once Set it and forget it Learn how ACL works behind the scenes Value of learning ACL for DOS Page 9 Slide 11 Quick Process to Running Data 1. Know your audit objectives 2. Align reports to the objectives 3. Use past reports to model /refine reports 4. Set data requirements based on reports 5. Obtain, validate, and normalize data 6. Edit scripts for data needs 7. Run reports and document results Page 10 Slide 12 Two Top Methods to Script Add a new session to the ACL log file Save work to a script Use the Script Recorder Turn it on (Tools menu) Turn it on again to save work Page 11 Slide 13 Making a Script In Two Minutes Open payables tableStatistics on all valuesStratify check amount valueExtract invoices above average (AVERAGE1)Safety, Delete All, Pause, and Comments Page 12 Slide 14 Polling Question #1 What comes first identifying data sources,requesting data, building scripts, or listingreport ideas? Getting data sources Listing report ideas Requesting data Building scripts Page 13 Slide 15 Hardcoded and Introducing Dialogs Page 14 Slide 16 101 ACL Applications Over 100 reports including all scripts, auditobjectives, comments to scripts andsuggested audit steps to take with the results Page 15 Slide 17 101 ACL Applications Sample Script Review GL #6 Entries Not Equal to ZeroGL #2 Flattening Journal EntriesVEN_INTR_101 - Weighted DPO Calculation Page 16 Slide 18 101 ACL Applications Approach of Scripts Rename file names to equal script tablesRename field names to equal script fieldsAdjust all variables in the script Page 17 Slide 19 VEN_INTR_PTS Payables Test Version Same functionalityCan be universally appliedUses dialog boxes to: Select a data Select fields Set an interest rate Page 18 Slide 20 Polling Question #2 What is put at the beginning of a script tohave it overwrite existing tables? Set Overwrite On Set Overwrite OK Set Safety Off Set Safety On Page 19 Slide 21 Developing Each Dialog Box Page 20 Slide 22 Project Item Dropdown Most Versatile Project Item Tables Fields Can set type Variables Scripts Page 21 Slide 23 Other Dialogs Dropdown Numbers and Characters Radio IF RADIO1=1 Checkbox IF PAYABLEREP=T DO SCRIPT ABC Text Entry Numbers and Characters Page 22 Slide 24 Script Editor Changes in One Picture Page 23 Slide 25 Setting Folders ACL folders can be established to managescript work.Script command to set folder: SET FOLDER /_CRP_SCRIPTS_AND_FILES/Report_Output_Final_Files Page 24 Slide 26 Polling Question #3 What is one feature not available in the scripteditors enhancements in ACL 9.3 & 10? Word wrap Line numbering Spell check Font style customizations Page 25 Slide 27 Monthly Stratification Script & Working With Variables / Defined Fields Page 26 Slide 28 Monthly Stratification Script Learning Objectives Building Complex Conditional FieldsEnsuring Date Variables are Used ProperlyUsing Variables in Dialog BoxesUsing Variables in File NamesAdjusting Variables in Field NamesExport fields AS Another Field NameMaximizing the Stratify Command Using KEYand the Crosstab function Page 27 Slide 29 Polling Question #4 What word is used in the STRATIFYcommand to break at a key field? BREAK PARSE KEY PRIMARY KEY Page 28 Slide 30 Group and Else Page 29 Slide 31 Simple Yet Effective Group/Else Reduces Processing Time / Passes ThroughTable OnceAllows Multiple IF StatementsGROUP IFGROUP ELSE / ELSE IF / ELSE / END Page 30 Slide 32 Key Words Search and Vendor to Employee Name Search Page 31 Slide 33 The Main Statement Page 32 JOIN PKEY Blank_Field_ON FIELDS RECORD_NUMBER_ON Search_ON_Field_CONV SKEY Blank_Field_Through WITH RECORD_NUMBER_THROUGH Search_THROUGH_Field_CONV IF FIND(ALLTRIM(Search_ON_Field_CONV),I nput_Search_Through_EXT.Search_THRO UGH_Field_CONV) TO "SEARCH_FILE_1" OPEN MANY Slide 34 Script Best Practices Page 33 Slide 35 Script Best Practices Set Safety On / OffSet field/file variables up front in the scriptCode in a text editor (i.e., Edit Pad Pro)Comment throughout the code ( you will forget what you did otherwise ) Page 34 Slide 36 Script Best Practices PAUSE statementsDelete All OKDelete temporary files at the end: Delete sorted.fil OK Delete format sorted OK Master script to DO SCRIPTS Page 35 Slide 37 Script Best Practices Number Your Scripts A01, A02, etc. SORT vs. PRESORTDialog Titles & Set Sizing of Dialog BoxesSET FOLDER to Include Tables in Folders ofChoice Page 36 Slide 38 Polling Question #5 What function allows you to searchTHROUGH a certain field in ACL? FIND () LOOK() RIGHT() MID() Page 37 Slide 39 Questions? Any Questions? Dont be Shy! Page 38 Slide 40 In the Queue Excel Pivot Tables and Graphing for Auditors June 25 Building Simple Continuous Monitoring in ACL July 2 Slide 41 AuditSoftwareVideos.com Videos accessible for FREE subscriptions Repeat video and text instruction as much as you need Sample files, scripts, and macros in ACL, Excel, etc. available for purchase Bite-size video format (3 to 20 minutes) Page 40 >> Professionally produced videos by instructors with over 20 years experience in ACL, Excel, and more Slide 42 Free Files for Today AuditSoftwareVideos.com June 7, 2012 Introduction to Scripting June 19, 2012 Intermediate Scripting October 9, 2012 Advanced Scripting Page 41 Slide 43 Thank You! Jim Kaplan AuditNet LLC 1-800-385-1625 Email:email@example.com@auditnet.org http://www.auditnet.org Richard B. Lanza, CPA, CFE, CGMA Cash Recovery Partners, LLC Phone: 973-729-3944973-729-3944 Cell: 201-650-4150201-650-4150 Fax: 973-270-2428973-270-2428 Email: firstname.lastname@example.org@richlanza.com http://www.richlanza.com Page 42