ETL for Reconciliation Data Preparation

Cointab’s ETL-style data preparation feature helps finance teams clean, transform, and enrich Side A and Side B records before reconciliation runs. Instead of rebuilding spreadsheet logic for every period, teams can standardize fields, create derived columns, validate file formats, and prepare data in a reusable workflow that supports audit-ready reconciliation.

What the data transformation feature does

Reconciliation often starts with messy input files: inconsistent dates, changing reference formats, missing identifiers, and report structures that do not match exactly. Cointab gives finance teams a structured way to prepare those files before matching begins.

With this feature, users can:

• Standardize dates, amounts, and identifiers
• Clean and normalize transaction references
• Create calculated or derived columns
• Enrich primary records with supporting data
• Combine multiple inputs before reconciliation
• Validate whether uploaded files match the configured format

This makes the reconciliation workflow easier to manage and reduces the need for repeated Excel cleanup.

How it fits into the reconciliation workflow

Cointab is built around a clear reconciliation flow:

1. Upload or receive Side A and Side B data.
2. Map the required fields, such as date, amount, and identifiers.
3. Add supporting data if needed.
4. Create derived columns where business logic is needed.
5. Run reconciliation manually or on a schedule.
6. Review matched, partially matched, unmatched, and skipped records.

The transformation layer sits before the reconciliation engine, so finance teams can prepare data once and reuse the same setup for future periods.

Common data preparation tasks

Clean and standardize fields

Finance reports often use different naming patterns or formatting conventions across systems. Cointab helps teams normalize fields so the same reconciliation logic can be applied consistently.

Typical examples include:

• Converting date formats into a usable structure
• Trimming spaces and removing inconsistent text casing
• Normalizing order IDs, transaction IDs, or reference numbers
• Preparing amount fields for comparison

Build derived columns

Users can create derived columns on both sides of the reconciliation. These are calculated fields based on existing data and can be used as matching fields, amount fields, lookup fields, or output fields.

Examples include:

• Clean Order ID
• Net Amount
• Refund Amount as Negative
• Delivered Payment Amount
• Amount After Fee
• Combined Reference
• Normalized Transaction ID

Users can describe the logic in natural language, and AI helps generate the Excel-style formula.

Enrich with supporting data

Supporting data is not reconciled directly. It is used to prepare or enhance the primary reports before matching starts.

Examples include:

• Product master files
• Fee rate files
• Order metadata
• Marketplace mapping files
• GST or tax mapping files
• Delivery partner reference files
• Customer or vendor master data

This is especially useful when finance teams need to add context, merge reports, or complete missing values before reconciliation.

Why finance teams use it

Reduces manual spreadsheet work

A lot of reconciliation work is still done in Excel with formulas, VLOOKUPs, pivot tables, and copy-paste cleanup. Cointab replaces those repeated steps with a reusable workflow that is easier to review and maintain.

Improves consistency

When different team members prepare the same reconciliation in different ways, review becomes harder. Cointab applies the same field mapping and transformation logic every time, which improves consistency across periods and users.

Supports audit-friendly review

Because the transformation logic is part of the configured reconciliation workflow, finance teams can review how data was prepared before matching. That helps when preparing reports for internal review, partner follow-up, or audit support.

Makes recurring work reusable

Once the transformation logic is configured, it can be reused for future runs. Teams do not need to rebuild the same formulas, mappings, and cleanup steps every month.

Examples of where this is useful

The data transformation feature is useful across many common reconciliation workflows, including:

• Sales vs payment gateway reconciliation
• Marketplace sales vs settlement reconciliation
• Bank statement vs books reconciliation
• Vendor reconciliation
• Customer reconciliation
• COD delivery partner reconciliation
• ERP or ledger reconciliation

For example, a finance team may need to combine order data with payment data, generate a clean transaction reference, and calculate amounts after fees before matching the records. Cointab supports that kind of workflow without forcing the team to manage everything in separate spreadsheets.

File handling and validation

Cointab supports CSV, XLS, and XLSX uploads. For each primary report, users configure key columns such as:

• Header row
• Date column
• Amount column
• Reference or identifier columns

If a file does not match the configured format, the system can reject it with a clear error so users know what needs to be corrected. This helps reduce processing mistakes and keeps reconciliation inputs structured.

Built for both popular and custom reconciliations

Popular reconciliations

Popular reconciliations are pre-built templates for standard workflows such as sales vs payment, bank vs books, marketplace vs settlement, or COD delivery partner reconciliation. In these cases, Cointab already defines the file structure and transformation logic, so users can upload the required reports and run reconciliation.

Custom reconciliations

Custom reconciliations are built for business-specific workflows. Users can define Side A and Side B reports, add supporting data, create derived columns, and reuse the setup for future runs.

This is useful when the reports are unique to the business or when the workflow involves multiple files and custom matching logic.

Where AI helps in the transformation process

Cointab uses AI in a practical, finance-friendly way. In the transformation layer, AI supports users by helping them create formulas for derived columns and by making it easier to describe business logic in plain language.

AI can also assist later in the reconciliation process by analyzing open transactions, but it does not replace the need for review. If the evidence is weak, records remain unmatched rather than being forced into a questionable match.

How this improves reconciliation outcomes

When data is prepared properly before matching, finance teams can focus on the records that actually need attention. That typically means:

• Cleaner inputs
• Faster exception review
• Fewer formatting issues
• Easier repeat runs
• Better reporting for unmatched and partial items
• More reliable month-end or period-end close support

The goal is not just to transform data. The goal is to make reconciliation easier to run, easier to review, and easier to reuse.

Designed for recurring finance operations

Cointab’s transformation feature is part of a broader reconciliation workflow that can be used repeatedly across periods. Teams can set up the process once, then continue using it for monthly, quarterly, yearly, or custom reconciliation periods.

That makes the feature useful for finance teams that want to move beyond one-off spreadsheet cleanup and build a repeatable reconciliation process with clear inputs, clear logic, and reviewable outputs.