User Reconciliation

​

User Reconciliation Flow

• Ingestion of data via file upload (CSV/XLSX)
• Entry of tailored matching and transformation rules, using a business-user friendly UI
• Saving of these rules for later system/user re-use
• Run Matching
• Dashboard View and Reports on reconciliation outcome data

​

Create a New User Reconciliation

​

​

Sample Scenario 1: Transaction Reconciliation

Data

Transaction Data 1: TSS_Statement.xlsx - Internal Ledger Transaction File

Transaction Date	Account Number	Transaction_Amount	Fee	STAN	RRN	Terminal ID	Description
2025-03-01	NGN9201386492758	8,200	82	382	3842	3PSYQLFK	POS Purchase
2025-03-01	NGN9201386493380	16,000	160	574	8537	W5MHTVZQ	POS Purchase
2025-03-01	NGN9203958716402	7,500	75	400	1560	9KQBRYHT	Funds Transfer
2025-03-01	NGN9203958716402	9,500	95	478	6190	0ZMLKQDP	POS Purchase
2025-03-01	NGN9203958716402	1,450	72	670	900	V4JS9WPK	POS Purchase
2025-03-01	NGN9204816539274	5,000	50	120	150	7LDXPRQK	POS Purchase
2025-03-01	NGN9204816539274	6,400	64	414	4625	1BNXHTZP	Bill Payment
2025-03-01	NGN9204816539274	13,400	134	606	9320	Z8KLP3RJ	Bill Payment
2025-03-01	NGN9205728913460	15,000	150	350	3060	8HTWJCRD	Deposit
2025-03-01	NGN9205728913460	5,000	50	542	7755	R9QK2XTL	Deposit
2025-03-01	NGN9207641823950	12,000	120	350	350	4MJTSWEP	ATM Withdrawal
2025-03-01	NGN9207641823950	22,000	220	446	5407	5GRTVJQM	ATM Withdrawal
2025-03-01	NGN9207641823950	8,700	89	638	1000	H2QTXNMF	ATM Withdrawal
2025-03-01	NGN9208162345791	3,000	30	210	2355	6VNZLQXM	POS Purchase
2025-03-01	NGN9208162345791	7,800	78	510	6972	2XJFWNHT	Funds Transfer
2025-03-01	NGN9208162345799	6,245	43	222	1234	2XJZZZHT	Funds Transfer

Source: Internal; Format: XLSX; Frequency: Daily

Transaction Data 2: TSS_ATM.xlsx - External Transaction File

Transaction Date	Account Number	Settlement_Impact	Fee	STAN	RRN	Terminal ID	Description	Settlement_Impact_Desc	Bank_Card_Brand	Currency_Name
2025-03-01	1234567801	5,000	50	200101	150	T001	POS Purchase	Amount_Payable	Verve	Naira
2025-03-01	1234567802	12,000	120	200102	350	T002	ATM Withdrawal	Amount_Receivable	Verve	Naira
2025-03-01	1234567803	7,000	70	200103	1560	T003	Funds Transfer	Amount_Payable	Verve	Naira
2025-03-01	1234567804	3,000	30	200104	2355	T004	POS Purchase	Amount_Receivable	Verve	Naira
2025-03-01	1234567805	15,000	150	200105	3060	T005	Deposit	Amount_Payable	Verve	Naira
2025-03-01	1234567806	8,200	82	200106	3842	T006	POS Purchase	Amount_Receivable	Verve	Naira
2025-03-01	1234567807	6,000	60	200107	4625	T007	Bill Payment	Amount_Payable	Verve	Naira
2025-03-01	1234567807	22,000	220	200867	5407	T008	ATM Withdrawal	Amount_Receivable	Verve	Naira
2025-03-01	1234567808	9,600	96	200109	6190	T009	POS Purchase	Amount_Payable	Verve	Naira
2025-03-01	1234567809	7,800	78	200110	6972	T010	Funds Transfer	Amount_Receivable	Verve	Naira
2025-03-01	1234567810	5,000	50	200111	7755	T011	Deposit	Amount_Payable	Verve	Naira
2025-03-01	1234567811	15,000	150	200112	8537	T012	POS Purchase	Amount_Receivable	Verve	Naira
2025-03-01	1234567812	13,400	134	200113	9320	T013	Bill Payment	Amount_Payable	Verve	Naira
2025-03-01	1234567813	9,100	91	200114	1010	T014	ATM Withdrawal	Amount_Receivable	Verve	Naira
2025-03-01	1234567814	7,000	70	200115	1088	T015	POS Purchase	Amount_Payable	Verve	Naira
2025-03-01	1234567815	11,084	50	200116	6890	T016	Deposit	Amount_Receivable	Verve	Naira
2025-03-01	1234567816	11,261	150	200117	7233	T017	POS Purchase	Amount_Payable	Verve	Naira
2025-03-01	1234567817	11,437	134	200118	7575	T018	Bill Payment	Amount_Receivable	Verve	Naira
2025-03-01	1234567818	11,614	91	200119	7918	T019	ATM Withdrawal	Amount_Payable	Verve	Naira
2025-03-01	1234567819	11,790	70	200120	8261	T020	POS Purchase	Amount_Receivable	Verve	Naira

Source:External; Format: XLSX; Frequency: Daily

Matching Rule Specification

Assume for the transaction reconciliation, matching between the two data sources involves the following fields and definition.

File 1	File 2	Fields Used	Rule Definition
TSS_Statement	TSS_ATM	RRN,RRN	RRN <EXACT_MATCH> RRN
TSS_Statement	TSS_ATM	Transaction_amount, Settlement_impact	Transaction_amount <AMOUNT_MATCH> Settlement_impact

Reconciliation rules leverage unique identifiers (RRN) and transaction amount to ensure high‑confidence match across theTSS_StatementandTSS_ATMfiles.

Transformation Rule Specification

Assume for the transaction reconciliation the following normalization/transformation on the transaction files.

File Name	Column Name	Description	Required	Normalize
TSS_Statement	RRN, Transaction_amount	Internal Ledger transactions	YES	CONCAT(RRN, STAN, TerminalID + Transaction_amount)
TSS_ATM	Retrieval_reference_nr, Settlement_impact	External ATM Transaction	YES	FILTER(Settlement_Impact_Desc IN Amount_Payable and Amount_Receivable) ABS(Settlement_impact) CONCAT(Retrieval_reference_nr, STAN, TerminalID + Settlement_Impact, Account_Number)

Rule 1: Normalization in tss_statement generates a unique concatenated key for full traceability down the line.
Rule 2: Normalization in tss_atm ensures that settlement impact considered are relevant, absolute values are considered, and unique concatenated keys are generated for easier traceability down the line.

Transaction Reconciliation using Recital

​

Handling Sample Scenario 1

Creating a new user reconciliation to handle the transaction reconciliation is easy and straight forward.

1

Navigate to the Reconciliation Page

Use the left menu on Recital to navigate to the Reconciliation page

2

Start New Reconcliation Flow

1. On the Reconcilation page, click on “New Reconciliation” button.
2. Select the “User Reconciliation” from the pop-up menu.

3

Complete the Flow Set Up

Set up the new flow by filling User Recon Diablog.Do the following:

1. Enter a representative name and description for the description
2. Specify currency and transaction start and end date in your source files as the currency, start data and end date
3. For each source column,

• Specify the Reconciliation type as “Transaction” in this case,
• Specify Source as “File upload” in this case),
• Specify File Format as “Others” in this case),
• Specify File Type as “XLSX” in this case
• Upload the TSS_Statement.xlsx for Source 1 and TSS_ATM.xlsx for Source 2

4. Click on “Set up”

The filled User Recon Set up should look like the following:Set up time could vary depending on the size of the records in the source files, from a few seconds to minutes.

4

Matching Rules

On the “User Recons” Page, switch to the “Matching Tab”, and set up the matching rules for the transaction reconciliation using the sample matching rules specified above.To add matching rules to the user reconciliation flow, do the following:

• Select Exact Match matching loic, and the “RRN” field in both source files for Rule 1
• Select “Amount Match Absolute” logic, then “Transaction Amount” field for the TSS_Statement file, and “to “Settlement_Impact” for the TSS_ATM file for Rule 2.
• Leave “And” as the default logic gate between the two rules entry fields. This implies a match will considered if and only if the RRN-to-RRN AND Transaction Amount-to-Settlement_Impact match in both transaction files.
• Save the rule for later re-use

5

Transformation Rules

On the “User Recons” Page, switch to the “Transformation” tab to set up the transformation rules.Let’s start with the TSS_Statement file transformation. Do the following:

• Select CONCAT on the “Apply Function” field. The Apply functions work similarly to excel functions that business users are already familiar with.
• Next select the TSS_Statement.xlsx on the “From file” field
• Then select the columns you want to concatenate in the order you want to them, e.g., for CONCAT(RRN, STAN, TerminalID+Transaction_Amount) normalization on the TSS_Statement file, select the RRN first, STAN second, TerminalID third, etc.
• Specify a name for the new column to place the transformed value
• And use the “with separator” and “operator text” to add a separator/prefix/suffix and the corresponding separator/prefix/suffix character

Next, let’s apply tjhe transformations rule on the TSS_ATM source data. Do the following:

• Click on the plus button besides the first transformation rule to add a new transformation rule.
• Add the CONCAT, ABS and FILTER transformations on the TSS_ATM files.

The completed transformation rule should look like this:

• Save the transformation rule for later re-use

6

Run Reconcilation

Click the Run button on the User Recon Flow Page to Run the reconciliation after entering the matching and transformation rules.

7

Reconciliation Outcome Data Review

After running a reconciliation, users are re-directed to the reconciliation flow list page.To navigate to the Reconciliation Run Summary data page, do the following:

1. Select your reconciliation flow name on the list.
2. On the reconciliation run list page, select the corresponding run or last run to see the associate reconcilation outcome data
3. On the Reconciliation Outcome summary page, you can view indepth data about the outcome of the reconciliation run.

• You can switch between File tabs,
• View the match/un-matched status of each record in a file
• While on a particular file, you can switch between Transaction Match and Un-Matched report with the top right drop down button.

8

Export Report

To download reconciliation reports, do the following:

1. Navigate to the Reconciliation Run List : Reconcilation Page > Select Reonciliation Flow > Reconciliation Runs List Page
2. Click download button beside the corresponding run

For the reconciliation reports, you get one file, with 4 sheets: #2 sheets for each files reconciled and unreconciled items

​

Sample Scenario 2: Settlement Reconciliation

Data

Reconcilled TSS_Statement.xlsx - Output Data from Sample Scenario 1

Transaction Date	Account Number	Transaction_Amount	Fee	STAN	RRN	Terminal ID	Description	Status
2025-03-01	NGN9201386492758	8,200	82	382	3842	3PSYQLFK	POS Purchase	RECONCILLED
2025-03-01	NGN9201386493380	16,000	160	574	8537	W5MHTVZQ	POS Purchase	UNRECONCILLED
2025-03-01	NGN9203958716402	7,500	75	400	1560	9KQBRYHT	Funds Transfer	UNRECONCILLED
2025-03-01	NGN9203958716402	9,500	95	478	6190	0ZMLKQDP	POS Purchase	UNRECONCILLED
2025-03-01	NGN9203958716402	1,450	72	670	900	V4JS9WPK	POS Purchase	UNRECONCILLED
2025-03-01	NGN9204816539274	5,000	50	120	150	7LDXPRQK	POS Purchase	RECONCILLED
2025-03-01	NGN9204816539274	6,400	64	414	4625	1BNXHTZP	Bill Payment	RECONCILLED
2025-03-01	NGN9204816539274	13,400	134	606	9320	Z8KLP3RJ	Bill Payment	UNRECONCILLED
2025-03-01	NGN9205728913460	15,000	150	350	3060	8HTWJCRD	Deposit	RECONCILLED
2025-03-01	NGN9205728913460	5,000	50	542	7755	R9QK2XTL	Deposit	RECONCILLED
2025-03-01	NGN9207641823950	12,000	120	350	350	4MJTSWEP	ATM Withdrawal	RECONCILLED
2025-03-01	NGN9207641823950	22,000	220	446	5407	5GRTVJQM	ATM Withdrawal	UNRECONCILLED
2025-03-01	NGN9207641823950	8,700	89	638	1000	H2QTXNMF	ATM Withdrawal	RECONCILLED
2025-03-01	NGN9208162345791	3,000	30	210	2355	6VNZLQXM	POS Purchase	RECONCILLED
2025-03-01	NGN9208162345791	7,800	78	510	6972	2XJFWNHT	Funds Transfer	RECONCILLED
2025-03-01	NGN9208162345799	6,245	43	222	1234	2XJZZZHT	Funds Transfer	UNRECONCILLED

Settlement Data from an External Source

Settlement Category	Account Type	Total Amount	Total Fees
POS Purchase	AMOUNT RECEIVABLE	43,150	0
Funds Transfer	AMOUNT RECEIVABLE	21,545	0
Bill Payment	AMOUNT RECEIVABLE	19,800	0
ATM Withdrawal	AMOUNT RECEIVABLE	42,700	0
POS Purchase	AMOUNT PAYABLE	0	0
Funds Transfer	AMOUNT PAYABLE	0	0
Bill Payment	AMOUNT PAYABLE	0	0
ATM Withdrawal	AMOUNT PAYABLE	0	0
SUM TOTAL		127,195	0
GRAND TOTAL		127,195	0

Settlement Reconciliation using Recital

​

Handling Sample Scenario 2

Creating a new user reconciliation to handle the settlement reconciliation is easy and straight forward.

1

Navigate to the Reconciliation Flow Page

1. Use the left menu on Recital to navigate to the Reconciliation page
2. Select the reconcliation name you want to work on from the flow list

2

Start New Settlement Reconciliation Run

To set up a Settlement Reconciiliation run,

• Switch to the Settlement Tab on the Reconciliation runs list
• Select “Run Settelement”

3

Settlement Reconciliation Run Set up

On the run settlement pop-up menu,

• Select the reconcileed transaction run that you want to run the settlement reconciliation against
• Select the transaction file that has all the transactions: TSS_Statement.xlsx in this case
• Specify the type of settlement file you are uploading – “Interswitch Computation Report” in this case. Recital recognizes the format of different types of settlement files from various providers/switches.
• Specify the file format - xlsx, and select the settelemnt file to be uploaded
• Click on “Upload Files”

4

Grouping

To match the transaction file to the settlement file, we need to select the correct column to use for grouping. On the Settlement Reconciliation page,

1. Switch to the Grouping tab
2. Select the “description” column on TSS_Statement since it contains the different types of transaction, and “settlement category” on the Settlement file. 3.Click on “Save Group”, so that you can re-use the group logic later.

5

Transformation

To accurately parse and group transactions in the transaction file, we need to specify the amount and date columns. On the Settlement Reconciliation page,

1. Switch to the Transformation tab
2. Click on More Actions Drop Down on the top menu and select transform columns
3. Select the date column in the transaction file, select “Date” as the Data Type, and select a date format. You can use “MDY”
4. Select the amount column in the transaction file – Transaction Amount for TSS_Statement. Check the Expected box
5. Save the transformation rule
6. Click on Transform.

6

Run Settlement Reconciliation

Click on Run Settlement.After running a settlement, users are redirected to the Settlement Reconciliation runs list page

7

Reconciliation Outcome Data Review

Select the last settlement run you just completed to see the outcome data once you see “completed” on the grid.The run outcome data page shows how each of transaction categories was reconcilled between the transaction and settlement files. E.g. We can see the POS transaction was partially reconcilled, and that of the 43,150 total amount in the settlement file, 16,200 was matched and 26,950 was un-matched to the transaction file.

8

Export Data

Click on the Export button on the run uutcome data page to export the result of your settlement reconciliation