With growing globalization, evolving markets, and regulatory pressure to improve risk and control environments under rapidly changing business conditions, it is critical that organizations develop continuous monitoring programs. These continuous monitoring programs often require scripts to automate business processes. In this article, we discuss how to create a script in Analytics to automate a business process.
This article illustrates how to automate the review of procurement cards (Pcards) using an Analytics script. However, the same workflow can also be applied to a variety of other finance, IT, audit, compliance, and risk use cases.
Note
For the purposes of this solution guide, you don't need to understand scripting in detail. If you want to learn more, see Scripting in Analytics.
Where can I create scripts?
You can create scripts in Analytics.
The big picture
An Analytics project stores data analysis information, including tables and scripts.
A script imports, prepares, analyzes, and exports data to Results.
You can then take a script that you run ad hoc in Analytics, convert it to an analytic script (by adding an analytic header), and upload the script to Robots to run tasks on an automated basis.
Steps
Ready for a tour?
Let's take a closer look at these features in context.
Note
If you want to try out the workflow presented in this solution guide, you can download Excel data files (Pcard_Holders and Pcard_Transactions), and copy and paste the example script into Analytics. Alternatively, you can simply review the script to get a general sense of what it does.
1. Create a script
A script may be something as simple as running a command on a single field, or it may be substantial enough to perform the bulk of the work to achieve analysis objectives.
Tip
Typically, the best practice is to segregate tasks into separate scripts. For your convenience, we have presented data import, preparation, analysis, and export in a single script.
You are an internal auditor at Vandelay Industries, the country's premier latex manufacturer. To assist the employees at Vandelay in making small purchases of office supplies and computer equipment, management has implemented a Pcard program. The Pcard program requires an annual audit review to make sure that transactions comply with Vandelay policies.
You want to use a script to automate the key tasks involved in performing an analysis. You intend to export exception and transactional data to Results for reporting purposes.
Process
Scripting data import, preparation, and analysis
First, you create an Analytics project and add the two Excel data files to the same location where the Analytics project is saved. Then, you create a single script in Analytics called Pcard_Review that:
imports the Excel data files required for analysis
prepares data prior to analysis
analyzes data and identifies Pcards that exceed their monthly limits
Finally, you run the script in Analytics, which produces a series of source, prepared, and result tables.
Scripting the export of data to Results
You go to Results, and create two data containers:
Collection Procurement card review
Analysis Procurement card audit 2018
From Analytics, you manually export:
r_EmployeesOverMonthlyLimit to a new Employees over monthly limit table in Results
p_Pcard_Transactions_2018 to a new Procurement card transactions 2018 table in Results
Finally, you copy the EXPORT syntax from the Analytics log into the script.
Note
You manually export first to get the correct scripting syntax. Once scripted, the data export is repeatable on an automated basis. The EXPORT syntax contains the specific Results table IDs you require to run the data export portion of the script. You can copy the entire script below, and replace the two instances of "XXXXXX@us" with the unique Results table IDs.
COMMENT ************************************************* *** Script name: Pcard_Review *** Description: This script does the following: *** 1 - Imports Pcard_Holders and Pcard_Transactions information *** 2 - Prepares the above tables for analysis *** 3 - Identifies employees and cards that exceeded their monthly limits *** *** Written by: Your name, Vandelay - Today's date *** Version: 1.0 ************************************************* END SETSAFETYOFF SETSESSION CLOSEPRIMARY CLOSESECONDARY COMMENT ********************** ** This portion of the script imports the Pcard_Holders table and Pcards_Transactions table required for the Pcard review. ********************** END SETFOLDER /Data/_1_Source_tables COMMENT*** Import the Pcard_Transactions data IMPORTEXCELTO s_Pcard_Transactions "Pcard_Transactions.fil" FROM "Pcard_Transactions.xlsx" TABLE "Pcard_Transactions$" CHARMAX 150 KEEPTITLE COMMENT*** Import the Pcard_Holders data IMPORTEXCELTO s_Pcard_Holders "s_Pcard_Holders.fil" FROM "Pcard_Holders.xlsx" TABLE "Pcard_Holders$" CHARMAX 150 KEEPTITLE COMMENT ********************** ** This portion of the script prepares data for analysis. ********************** END SETFOLDER /Data/_2_Prepared_tables COMMENT*** Harmonize the card_number fields in the s_Pcard_Holders and s_Pcard_Transactions tables so you can use it as a key field in a later Join OPEN s_Pcard_Holders DEFINE FIELD c_card_number COMPUTEDSTRING(card_number, 16) OPEN s_Pcard_Transactions DEFINE FIELD c_card_number COMPUTEDSTRING(card_number, 16) COMMENT*** Extract dates in 2018 in the s_Pcard_Transactions table and send them to p_Pcard_Transactions_2018 OPEN s_Pcard_Transactions EXTRACTFIELDSALL IFBETWEEN(transaction_date, `20180101` , `20181231 235959`) TO "p_Pcard_Transactions_2018" OPEN p_Pcard_Transactions_2018 COMMENT*** Extract required fields in s_Pcard_Holders and send them to p_Pcard_Holders OPEN s_Pcard_Holders EXTRACTFIELDS employee_number employee_name email_address c_card_number limit_transaction limit_monthly TO "p_Pcard_Holders" OPEN p_Pcard_Holders COMMENT ********************** ** This portion of the script analyzes data to identify whether Procurement cards are exceeding their monthly limits. If cards are exceeding monthly limits it means controls are failing and follow-up and remediation is required. ********************** END SETFOLDER /Data/_3_Results_tables COMMENT*** Create a computed field to determine the month each transaction occurred in so you can identify cards that exceed their monthly limit OPEN p_Pcard_Transactions_2018 DEFINE FIELD c_month COMPUTEDZONED(MONTH(transaction_date),2) COMMENT*** Group transactions by card and month to calculate the monthly total for each card. OPEN p_Pcard_Transactions_2018 SUMMARIZEON c_card_number c_month SUBTOTAL transaction_amount TO "t_SumTransByCardMonth.fil" OPENPRESORT COMMENT*** Determine the monthly limit for each card by relating the t_SumTransByCardMonth OPEN p_Pcard_Holders INDEXON c_card_number TO "Pcard_Holders_on_card_number" OPEN t_SumTransByCardMonth DEFINE RELATION c_card_number WITH p_Pcard_Holders INDEX Pcard_Holders_on_card_number COMMENT*** Filter and extract issues where the monthly limit was exceeded OPEN t_SumTransByCardMonth SETFILTERTO transaction_amount > p_Pcard_Holders.limit_monthly EXTRACTFIELDSALLTO "r_CardsOverMonthlyLimit.fil" OPEN r_CardsOverMonthlyLimit COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit. OPEN r_CardsOverMonthlyLimit OPEN p_Pcard_Holders SECONDARY JOINPKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORTSECSORT CLOSESECONDARY OPEN "r_EmployeesOverMonthlyLimit" COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit. OPEN r_CardsOverMonthlyLimit OPEN p_Pcard_Holders SECONDARY JOINPKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORTSECSORT CLOSESECONDARY OPEN "r_EmployeesOverMonthlyLimit" COMMENT ********************** ** This portion of the script exports data to the Results app. ********************** END
COMMENT*** Export records of employees who are over their monthly limit to Results OPEN r_EmployeesOverMonthlyLimit EXPORTFIELDS c_month c_card_number COUNT email_address employee_name employee_number limit_monthly transaction_amount ACLGRCTO "XXXXXX@us" OVERWRITE COMMENT*** Export Pcard Transactions from 2018 to Results OPEN p_Pcard_Transactions_2018 EXPORTFIELDS transaction_number transaction_date transaction_amount source_currency merchant_number merchant_name merchant_location MCC country_code c_month c_card_number billing_currency account_id ACLGRCTO "XXXXXX@us" OVERWRITE
2. Add an analytic header to the script
An analytic header is a series of tags enclosed in a comment block at the start of an Analytics script. An analytic header is required for any analytic script that you intend to run in Robots.
Tags are declarative commands inside the analytic header that define inputs, outputs, and instructions for an analytic script. Tags allow the analytic script to run unattended, either immediately, or at a scheduled time.
Tip
An analytic header provides instructions for how to run the analytic script in Robots. Some tags are used by Robots to receive information and others specify what should be produced by the analytic script. For example, you may need to tell Robots which file it needs for an import or which results table to make available to end users.
Currently, you run the Analytics script on an ad hoc basis. You want to program the script to run on an automated basis. This will free you up from the time-consuming task of manually running the analysis.
Process
You convert the regular script to an analytic script by adding an analytic header to Pcard_Review that includes the following tags:
FILE specifies an Excel file that provides input for an analytic script running in Robots
PASSWORD creates a password input parameter for the analytic script
RESULT LOG ensures a log is generated every time the analytic script runs
RESULT TABLE ensures a results table is available to end users when the analytic script runs successfully in Robots
Since the analytic header now contains the PASSWORD tag, you also add the PASSWORD syntax to the portion of the script that exports data to Results.
Note
The password input parameter and the password parameter in the EXPORT command are linked by using the same numerical identifier:
COMMENT //ANALYTIC TYPE Analysis Pcard_Review This analysis identifies all cards and employees that exceeded their monthly Pcard limit and exports exceptions and transactions to Results. //FILE Pcard_Holders.xlsx //FILE Pcard_Transactions.xlsx //PASSWORD 1 Diligent One Access Token //RESULT TABLE r_CardsOverMonthlyLimit //RESULT LOG END COMMENT ************************************************* *** Script name: Pcard_Review *** Description: This script does the following: *** 1 - Imports Pcard_Holders and Pcard_Transactions information *** 2 - Prepares the above tables for analysis *** 3 - Identifies employees and cards that exceeded their monthly limits *** *** Written by: Your name, Vandelay - Today's date *** Version: 1.0 ************************************************* END SETSAFETYOFF SETSESSION CLOSEPRIMARY CLOSESECONDARY COMMENT ********************** ** This portion of the script imports the Pcard_Holders table and Pcards_Transactions table required for the Pcard review. ********************** END SETFOLDER /Data/_1_Source_tables COMMENT*** Import the Pcard_Transactions data IMPORTEXCELTO s_Pcard_Transactions "Pcard_Transactions.fil" FROM "Pcard_Transactions.xlsx" TABLE "Pcard_Transactions$" CHARMAX 150 KEEPTITLE COMMENT*** Import the Pcard_Holders data IMPORTEXCELTO s_Pcard_Holders "s_Pcard_Holders.fil" FROM "Pcard_Holders.xlsx" TABLE "Pcard_Holders$" CHARMAX 150 KEEPTITLE COMMENT ********************** ** This portion of the script prepares data for analysis. ********************** END SETFOLDER /Data/_2_Prepared_tables COMMENT*** Harmonize the card_number fields in the s_Pcard_Holders and s_Pcard_Transactions tables so you can use it as a key field in a later Join OPEN s_Pcard_Holders DEFINE FIELD c_card_number COMPUTEDSTRING(card_number, 16) OPEN s_Pcard_Transactions DEFINE FIELD c_card_number COMPUTEDSTRING(card_number, 16) COMMENT*** Extract dates in 2018 in the s_Pcard_Transactions table and send them to p_Pcard_Transactions_2018 OPEN s_Pcard_Transactions EXTRACTFIELDSALL IFBETWEEN(transaction_date, `20180101` , `20181231 235959`) TO "p_Pcard_Transactions_2018" OPEN p_Pcard_Transactions_2018 COMMENT*** Extract required fields in s_Pcard_Holders and send them to p_Pcard_Holders OPEN s_Pcard_Holders EXTRACTFIELDS employee_number employee_name email_address c_card_number limit_transaction limit_monthly TO "p_Pcard_Holders" OPEN p_Pcard_Holders COMMENT ********************** ** This portion of the script analyzes data to identify whether Procurement cards are exceeding their monthly limits. If cards are exceeding monthly limits it means controls are failing and follow-up and remediation is required. ********************** END SETFOLDER /Data/_3_Results_tables COMMENT*** Create a computed field to determine the month each transaction occurred in so you can identify cards that exceed their monthly limit OPEN p_Pcard_Transactions_2018 DEFINE FIELD c_month COMPUTEDZONED(MONTH(transaction_date),2) COMMENT*** Group transactions by card and month to calculate the monthly total for each card. OPEN p_Pcard_Transactions_2018 SUMMARIZEON c_card_number c_month SUBTOTAL transaction_amount TO "t_SumTransByCardMonth.fil" OPENPRESORT COMMENT*** Determine the monthly limit for each card by relating the t_SumTransByCardMonth OPEN p_Pcard_Holders INDEXON c_card_number TO "Pcard_Holders_on_card_number" OPEN t_SumTransByCardMonth DEFINE RELATION c_card_number WITH p_Pcard_Holders INDEX Pcard_Holders_on_card_number COMMENT*** Filter and extract issues where the monthly limit was exceeded OPEN t_SumTransByCardMonth SETFILTERTO transaction_amount > p_Pcard_Holders.limit_monthly EXTRACTFIELDSALLTO "r_CardsOverMonthlyLimit.fil" OPEN r_CardsOverMonthlyLimit COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit. OPEN r_CardsOverMonthlyLimit OPEN p_Pcard_Holders SECONDARY JOINPKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORTSECSORT CLOSESECONDARY OPEN "r_EmployeesOverMonthlyLimit" COMMENT*** Join r_CardOverMonthlyLimit with the p_Pcard_Holders table to identify the employees who exceeded their monthly limit. OPEN r_CardsOverMonthlyLimit OPEN p_Pcard_Holders SECONDARY JOINPKEY c_card_number FIELDS c_month c_card_number COUNT transaction_amount SKEY c_card_number WITH email_address employee_name employee_number limit_monthly TO "r_EmployeesOverMonthlyLimit" PRESORTSECSORT CLOSESECONDARY OPEN "r_EmployeesOverMonthlyLimit" COMMENT ********************** ** This portion of the script exports data to the Results app. ********************** END
COMMENT*** Export records of employees who are over their monthly limit to Results OPEN r_EmployeesOverMonthlyLimit EXPORTFIELDS c_month c_card_number COUNT email_address employee_name employee_number limit_monthly transaction_amount ACLGRCTO "XXXXXX@us" OVERWRITEPASSWORD 1 COMMENT*** Export Pcard Transactions from 2018 to Results OPEN p_Pcard_Transactions_2018 EXPORTFIELDS transaction_number transaction_date transaction_amount source_currency merchant_number merchant_name merchant_location MCC country_code c_month c_card_number billing_currency account_id ACLGRCTO "XXXXXX@us" OVERWRITEPASSWORD 1
3. Upload the script to Robots
Once you create an analytic script in Analytics, you can upload it to a robot in the Robots app to test and configure the task automation that you need. The action of uploading scripts from Analytics to Robots is called committing scripts.
Tip
You can use tools in Analytics to validate the analytic header syntax to ensure that it is correct. Perform the validation before committing scripts to Robots so that the analytic script does not fail when it runs.
You want to run the analytic script on an automated basis in the Robots app. You need to upload the script to the Robots app to get started.
Process
From the Analytics main menu, you select File > Commit Scripts. You select your organization, specify the name Pcard Review 2018 for the new robot, and click Create.
Finally, you enter the following commit message and click OK:
This is the initial upload of Pcard_Review, a script which is used to automate the review of Pcards.
Result
You have successfully uploaded the analytic script to a robot.
What's next?
Learn how to run the analytic script on an automated basis
The Robots app is used to automate repetitive tasks using scripts built in Analytics, and free up time and resources for you and your team to perform work of a more strategic nature.
Continue to build your knowledge on the concepts introduced in this article by taking the ACL 270 learning path.
Academy is Diligent's online training resource center. Academy courses are included at no extra cost for any user with a Diligent One subscription. For more information, see Academy.
A script imports, prepares, analyzes, and exports data to Results.
You can then take a script that you run ad hoc in Analytics, convert it to an analytic script (by adding an analytic header), and upload the script to Robots to run tasks on an automated basis.
See an example
