CAS Technology Solutions Group

CAS Technology Solutions Group

ACL NAMING, FILING & DOCUMENTATION REQUIREMENTS

February, 2014

CAS auditors are to adhere to the following filing and naming conventions for ACL Folders, Projects, Tables, Fields, Scripts, and Logs.

ACL FILING REQUIREMENTS:

Folders on ACL Drive:

1. In Windows Explorer, create a folder that is named for the audit on the “ACL drive” under ACL\20xx\directorname\

2. Inside the ACL\20xx\directorname\auditname folder, create 2 additional sub-folders: “Input Data” and “Results”. Put the original data files (before they are imported into ACL) into the “Input Data” folder, and put the outputs (i.e., whatever is exported to Excel) into the “Results” folder.

3. A copy of required ACL Project Documentation (as noted in ACL Documentation Requirements on page 3 of this document) should exist within the ACL project and should be attached to workpapers in CAAT wrap-up section (D.4), step 2.

4. After ACL work is complete, delete any unused files via Windows Explorer and delete all unused table layouts from ACL Project Navigator window. Zip all large FILs (i.e. files with more than 100,000 records) into a single zip file after ACL QA Review (if requested) is complete.

ACL NAMING REQUIREMENTS:

Projects:

When naming a new project in ACL:

IF THE PROJECT WILL BE USED FOR A SPECIFIC AUDIT:

Start the name with thecurrent year and audit ID number (YYYY####), which is the unique identifier of the audit or project
Follow the audit ID number with a succinct description of the related audit test

Examples: 20090010 Lending Limits.acl, 20090010 Daylight Overdrafts.acl

IF THE PROJECT WILL BE USED FOR VARIOUS AUDITS:

Start the name with theprefix “General_Use_”
Follow with a succinct description of the related audit test

Examples: General_Use_Cape_Approvals.acl; General_Use_ACH.acl

Tables:

The name of an ACL table will depend upon whether it is 1) original input, 2) working, or 3) final output.

1) Original input tables (OIN_). These contain original data from a data provider: TSG, business line, Technology Operations Services or a vendor. An original input table is considered to be the “base”, or starting point of your queries. When naming an original input table in ACL:

Preface the name with OIN_ (original input)
Follow OIN_ with a succinct description of the data in the file

Example: OIN_AFS

2) Working tables (WRK_). These contain data derived from an original (OIN_) table or tables, used repeatedly during the execution of scripts. An example of a working table is a new table created from merging two OIN_ tables. When naming a working table:

Preface the name with WRK_ (working)
Follow WRK_ with a succinct description of the data in the file

Example: WRK_AFS_and_Lending_File_Merge

3) Output tables (OUT_). These are the final output tables or reports.

Preface the name with OUT_ (output file or report)
Follow OUT_ with a succinct description of what is contained in the output table

Example: OUT_Lender_Limit_Exceptions

Fields:

When importing a table into ACL, you should use the names of the data fields provided by the data provider.

Field names can be changed when creating subsequent tables (WRK_ or OUT_). It is a good practice to keep the name the same as it is on the original input file; however, a field name on the original input file may be cryptic or confusing. In these cases, the field can be renamed to something that is meaningful to anyone else who will be using the ACL project.

For example, the original input file from the business line contains a field labeled “OB”, which has been identified as the obligor number. When you create subsequent tables using this field, you could rename it to “Obligor Number”, which is meaningful to the user.

COMPUTED (user-defined) fields, which are custom fields created by the user, should be readily identifiable on ACL tables. Common reasons for creating COMPUTED fields: to derive a result from using a mathematical formula (e.g., number of days = date1 – date2), to enhance code values with descriptions (e.g., product code DDA = Demand deposit, TDA = Time deposit, etc.) and to change the format of a field (e.g., to allow the joining of two tables in which the key fields have different formats).

When creating a new COMPUTED field:

Preface the name with C_
Follow C_ with a succinct description of the data field (e.g., C_PartialAccount)

When creating a new VARIABLE in a script (advanced ACL concept):

Preface the name with V_
Follow V_ with a succinct description of the variable (e.g., V_Counter1)

Scripts:

Scripts should be named in a manner that creates an outline in the ACL project navigator. In other words, scripts should be prefixed so that they appear in the project navigator in the order in which they are run, as shown below.

Example:

A01_Imports

A01a_Import (naming convention if “A01_Imports” executes this script)

A02_Processing

A02a _Extracts (naming convention if “A02_Processing” executes this script)

A02b_Joins (naming convention if “A02_Processing” executes this script)

A02b1_Summarize (naming convention if “A02b_Joins” executes this script)

A03_Outputs

Logs:

If multiple logs are being created in a single ACL project (for advanced ACL users), each log name should be prefaced with LOG_

Example: LOG_DuplicateInvoiceTest

ACL DOCUMENTATION REQUIREMENTS:

CAS auditors should document their ACL project to the extent that will enable:

Various audiences to understand the project.
The work to be re-performed.

Although ACL logs provide a complete history of most actions performed in a project, it is prudent to provide clarity of a data analysis with additional comments:

1. Copy and paste entire "ACL DOCUMENTATION TEMPLATE" from page 4 of this document into a new Word Document. Replace z’s in template below as appropriate and replace examples with actual descriptions appropriate to the project being documented. Attach completed Word doc to section D.4 (CAAT Wrap-up Steps, step #2) of workpapers.

2. In ACL project, create a new script named "_Documentation", then copy and paste the completed template from Word into the new script. WARNING: The format will appear to be unreadable at first. To make script readable, simply close script and save when prompted. Then re-open script. All formatting will have been automatically converted to plain text in the ACL script and will be readable. NOTE: The “_Documentation” script is not intended to be run but serves as a means of keeping documentation integrated into the ACL project. (Underscore in name of script will ensure that this script always appears first in Project Navigator window).

3. If scripting was performed within the ACL project, include a comment at the top of each script that briefly describes the purpose and processing of the script. NOTE: If the script performs multiple commands and/or calculations, include comments before each section of code within the script so that the process, purpose, and flow of the script can be understood by someone who has never before viewed it nor worked on the audit.

NOTE: "ACL DOCUMENTATION TEMPLATE" is now on page 4 of this document.

ACL DOCUMENTATION TEMPLATE

ACL Project Overview:

Audit Name: / zzzzzzz
Audit ID: / ####
Date ACL Project Developed: / zz/zz/zzzz or Month, Year
ACL Project Author(s): / e.g., Joe Jones
ACL Project Objective: / e.g., To compare SAR rpt with business line report and identify discrepancies; OR: To perform population analysis/sample validation.
ACL Analysis Description: / e.g. Basic Process was to filter out unneeded SEI codes and dates from OIN_table1 and then to join result with OIN_table2 to view matches and mismatches
ACL Project Path: / e.g., \\Vdmmn14glbsm03\Corpaudit_Prod01$\ ACL\2012\Wade\AP Audit\
ACL Project Name: / e.g., yyyy####_Vendor_Testing.ACL

Input #1:

ACL Table Name: / e.g., OIN_zzzzzzz
Original File Name: / e.g., zzzzzzz.xls
Person Providing Data: / e.g. Annette Troshynski, Sean Smith, Fred Jones
Department/Area Providing Data: / e.g., TSG, Treasury Services Department, the Operations Area of the Federal Reserve
Application/Source System: / e.g. FDW database OR AFS application
TSG Information Extract Request Nbr: / e.g., IE request 1234 OR N/A
As-Of Date of Data: / zz/zz/zzzz
Data Description: / e.g., File contains all loans with collateral that were new in 2011.
Manual Edit(s):
Records or fields added/deleted/modified after having obtained the data and prior to import. / e.g., Deleted all spreadsheet fields to the right of column Q prior to import into ACL. Original spreadsheet before edits is “abc.xls” in the “Input_Data” folder. Spreadsheet after edits is in same folder but is named “abc_2.xls.”
Integrity Check(s):
(i.e., Checks performed to confirm that data within the source dataset was imported accurately and completely. / e.g. Verified that ACL tables' record counts/field totals were as expected [by comparing ACL data to external source zzzzzzz] and/or [by comparing ACL data to original input file(s)] and/or [by asking DBA/TSG/business line programmer for expected record counts and/or field totals].

Output #1:

ACL Table Name: / e.g., OUT_zzzzzzz
Exported File Name: / e.g., zzzzzzz.xls
Data Description: / e.g., Output file contains the exceptions to business rules a.1-a.4 in document abc.doc in path 123; OR: Output file contains the result of filtering out fee codes x, y, and z, which are not in scope for this audit; OR: Output file contains the results of joining table OIN_zzzzzzz to table WRK_zzzzzzz.
Manual Edit(s):
Records or fields added/deleted/modified after ACL export and prior to usage / e.g., After ACL analysis was complete, deleted all rows from spreadsheet that had a Loan_Type (column G) of ‘xyz’ due to new information from the business line.

Output #2:

Validation of Outputs:

Integrity Check(s) For All Outputs:
Checks performed during processing to confirm that the outputs are accurate and complete / e.g., Ran “CLASSIFY” command on relevant fields to inspect all possible field values and to verify successful filtering prior to extract; OR: Ran count and total commands as appropriate to verify that SUMMARIZE/JOIN produced correct results

Special Procedures for Running Scripts:

ACL Scripting Notes: / e.g., Specific dates and loan types within the “A03_Processing” and “B01_CompareFiles” scripts should be reviewed and possibly changed prior to running these scripts.