Firstcaribbean Bank Validates Balance Sheet Process with Intelligence Suite

[AlteryxAdvocacy]

Author:

Tammy Cumberbatch

Role:

Intermediate Systems Developer

Company:

FirstCaribbean International Bank

Overview of Use Case

The EC ACH (Eastern Caribbean Automated Clearing House) balancing process is done manually daily. This has proved to be extremely time consuming for the person who performs the function. We are using process automation with Alteryx Intelligence Suite to convert the ECACH PDF files to improve the efficiency of the process and eliminate the need for manual effort.

Describe the business challenge or problem you needed to solve

It takes the user approximately 1½ to 2 hours to complete the process of looking at each individual session file per EC territory and manually keying in the amounts into an Excel balance sheet.

Describe your working solution

The objective was to improve the efficiency of the process, eliminate the need for manual effort, reduce paper usage and improve timelines of validation. Entering an incorrect total could result in the re-review of PDFs for the territory to ensure the correct balances are captured. So, we started using the Alteryx Intelligence Suite to format and simplify the process.

The officer goes into the EC ACH Gateway portal and downloads the session reports. 

• There are 4 session reports per territory and there are 6 territories. 
• In the past each session report would have been printed accounting for 18,648 sheets of paper per year, to mitigate this the decision was made to store the session reports in a folder on the shared drive.  
• The reports being printed now are the EOD which consist of 6 sheets and the balance sheets which consist of 3 sheets per territory. 

Workflow steps:

1. Using the directory tool, I referenced the UNC (Universal Naming Convention) path to the files and in the ‘File Specification’ box I specified the file extension *.pdf
2. Using the sort tool, I sorted by the creating time in descending order, to bring the most recent files to the top of the folder list
3. The ‘Creation Time’ field was displayed in date/time format, using the DateTime tool, I converted the ‘Creation Time’ field to ‘yyyy-MM’dd’ format
4. Using the summarized tool, I groupd the files based on the ‘DateTime_Out’ column now structured as ‘yyyy-MM-dd’ and the ‘FullPath’ which also contained the names of each file (there are 6 territories each having 4 files)

5. The PDF input tool is used to retrieve the full path from the summarized output.
6. With the PDF Image to text tool, I transformed the PDF image to text format which would then allow us to utilize the text to columns tool to split the text into rows, ensuring I remove any unwanted fields which contained no data or null records if the row or column contained no records.
7. Using the formula tool, I retrieved the name of the file, date from the file name and the session identifier from the file name.

8. Using the find replace tool we wanted to replace each instance of an acronym referencing the Country with the fill country name
9. With the text to column tool, I then split the rows of data into columns and retained any additional data in the last column

10. For this section I used a series of multi-row formulas to assign specific naming conventions to dedicated rows.  E.g.  In the PDF specific section would have had headers (transaction identifiers) referring to cheques and sub section headers capturing whether a transaction was returned, paid, collected or cancelled, I wanted to assign per section where the header referenced a particular action, then assign that action to each row.
11. I also followed this same method for assigning the currency as well.

12. For this section we combined certain fields which captured the transaction identifiers.  Therefore, where we would have captured the word cheque in a separate column and whether it was paid, returned, collected or cancelled in a separate column, I combined those fields, which now becomes a status field showing (cheque paid, cheque cancelled, cheque collected, cheque returned).  Removed any addition rows or columns no longer required and extracted to either a shared directory or published the workflow output to the Tableau Server.

Describe the benefits you have achieved

Reduction of paper usage and time constraints:

• 6,048 sheets of paper per year
• 378 working hours saved per year
• 100% paper reduction
• Significant reduction in effort required to review PDFs

[DawnDuong]

Impressive results indeed - thanks for sharing the use case!

[sireeshagandam]

Good