Radix-Skills/skills/data-science/erp-data-reconciliation/references/bank-reconciliation.md

Bank Reconciliation — Three-Source Analysis (2026-05-26)

Reference for matching bank transactions against Coop SAP exports and Uniconta ledgers. This extends the two-source reconciliation with a third (bank) dimension that serves as the "ground truth" for actual cash flow.

---

Bank file format (Danske Bank CSV export)

Kolonne	Type	Brug
Dato	DD.MM.YYYY	Primær dato
Valør	DD.MM.YYYY	Sekundær dato
Tekst	string	Identifikation
(tom)	—	Padding
Beløb	+/- flot/DK	Primær beløb
Saldo	flot/DK	Sekundær — løbende saldo
Egen bilagsreference	string	Audit/trace

CSV separator: ; (semikolon) Encoding: latin-1 (CP1252) Decimal: Danish comma, Danish point-as-thousand-separator Negative: explicit minus in front

Typisk tekstmønster for Coop-indbetalinger:

• "Coop 0010014808 -SE MEDD."
• Samme kundenummer for ALLE transaktioner
• Stort set alle transaktioner har identisk tekst

---

Bank transaktionskategorier

Standard indbetalinger (~98%)

• Beløb fra ~30.000 kr til ~800.000 kr
• Sum 2024: +9.225.626 kr (129 stk)
• Sum 2025: +10.031.368 kr (118 stk)
• Udgør den "faktiske" Coop-omsætning der rammer banken

Negative poster — bonus/modregning/retur (~1-2%)

Dato	Beløb	Tekst	Type
13.06.2024	-18.750,00	COOP DANMARK AS	Bonus-tilbagebetaling
25.11.2024	-31,25	Coop 365	Lokal retur/modregning
22.04.2025	-128,00	DK 57014 Coop Kvickly Slagelse	Butiksretur
13.05.2025	-40,00	DK 50960 Coop Kvickly Slagelse	Butiksretur

Total negative: -18.949,25 kr

---

THREE-SOURCE MATCHING CONCEPT

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  Coop Excel     │     │  Uniconta Excel │     │  Bank CSV       │
│  (fakturaer)    │     │  (bogføring)    │     │  (indbetalinger)│
└────────┬────────┘     └────────┬────────┘     └────────┬────────┘
         │                       │                       │
         ▼                       ▼                       ▼
┌──────────────────────────────────────────────────────────────────┐
│          MATCH LAYER 1: Post-for-post (Coop ↔ Uniconta)          │
│  Reference+abs(amount)+date → 68% match rate                     │
├──────────────────────────────────────────────────────────────────┤
│          MATCH LAYER 2: Month-level (All 3 sources)              │
│  Sum(Coop) ≈ Sum(Uniconta) ≈ Sum(Bank) per month                │
├──────────────────────────────────────────────────────────────────┤
│          MATCH LAYER 3: Bonus reconciliation (separate)           │
│  Sum(KG + Kreditnota) per period ≈ Bank negative per release     │
│  (tolerate time lag — bank credit arrives later than postings)   │
└──────────────────────────────────────────────────────────────────┘

---

Bank-to-Coop / Bank-to-Uniconta matching

Month-level validation (PRIMARY)

Compare month totals across all three sources. If Coop and Uniconta diverge, the bank settles the dispute.

Måned	Bank (kr)	Coop (kr)	Uniconta (kr)	Status
2024-04	2.139.610	TBD	TBD	To verify
2024-05	1.632.814	TBD	TBD	To verify
2024-09	1.053.598	TBD	TBD	To verify

Interpretation rule:

• Bank ≈ Coop: period is captured correctly in Coop
• Bank ≈ Uniconta: period is correct in Uniconta
• Bank >> Coop: missing Coop postings (likely late/omitted)
• Bank >> Uniconta: missing Uniconta postings
• Bank << both: bonus/modregning not yet reflected in accounting

Many-to-one: Bank single payment = sum of Coop invoices

Each bank posting (e.g. 641.168,06 kr) is a consolidated transfer that aggregates many individual Coop invoices. Algorithm:

1. Take bank amount B on date D
2. Find Coop transactions in [D-30 days, D+3 days] that sum to B ± tolerance
3. This is a "subset-sum" / knapsack search:

   def find_combo(target, candidates, tol=5.0):
       # Greedy for small candidate sets
       # DP for larger sets
       candidates = sorted([c for c in candidates 
                           if abs(c.amount) <= target + tol])
       # ... subset sum with tolerance
   

4. If exact match: mark all matched Coop invoices as "grouped under bank payment"
5. If near-match (±500 kr): flag for manual review — likely partial payment or fees

---

Bonus / Rebate reconciliation

The mismatch:why Bank negatives don't match individual KG/Kreditnota

Coop KG-posteringer: 486 stk, total -1.101.849,97 kr Bank negative total: -18.949,25 kr (4 stk) Uniconta Kreditnota total: -4.593.829,17 kr Uniconta Rabat: 1 stk, -178.194,55 kr

Observation: Bank -18.750 kr does NOT match ANY individual Coop KG posting (nor any Uniconta Kreditnota). The nearest Uniconta Kreditnota is -18.961 (2025-03-21).

Interpretation: Bank negatives are consolidated credit notes or bonus payments that aggregate MANY smaller individual credit postings across a period (quarter or year). They arrive in the bank later than the individual postings.

Suggested bonus reconciliation strategy

1. Period-based grouping (not transaction-based):
   • Group Coop KG by quarter/year
   • Group Uniconta Kreditnota by linked Faktura-number
   • Group Uniconta Rabat by year
2. Compare aggregated totals per period with bank negatives per period:

   Coop KG per period = sum(KG amounts where Posting Date in period)
   Uniconta credit per period = sum(Kreditnota + Rabat amounts in period)
   Bank negative per period = sum(bank negative amounts with Value Date in period)
   

3. **Differences indicate:
   • Timing: bank credit arrives 1-3 months after postings
   • Pooling: quarterly/yearly bonus vs monthly individual credits
   • Source mismatch: some credits originate from Uniconta only, some from Coop only
4. Manual review for amounts > 50.000 kr discrepancies per period

Special: Uniconta "Rabat" -178.194,55 "Coop bonus 2023"

• Single large posting on 2024-01-16
• No matching bank negative from 2023 or early 2024 found
• Likely an accrual/adjustment recognized in 2024 for 2023 bonus, but the actual cash flow may have been handled differently (e.g. applied as discount on future invoices rather than direct bank transfer)
• Flag: accrual-type postings should NOT be matched against bank — they are non-cash adjustments. Separate them into "bonus accrued" vs "bonus paid".

---

Key insight: Three sources triangulate

Question	How to answer with 3 sources
"Is this invoice paid?"	Uniconta Faktura + Coop RE + Bank amount all agree
"Did bonus arrive in cash?"	Bank negative confirms, Coop KG shows breakdown
"Is Uniconta missing a posting?"	Bank has it but Uniconta doesn't → missing entry
"Is Coop missing a posting?"	Bank has it but Coop doesn't → missing entry
"Is this payment split?"	Bank single amount = sum of multiple Coop invoices
"Is there a bonus adjustment?"	Coop KG present, Uniconta Kreditnota present, but bank negative missing → accrual

---

Practical notes for the webapp

Bank import

• Import as a third source alongside Coop and Uniconta
• Store as Transaction(source="bank", ...) with minimal fields:
  • date, amount, text, original_value_date, original_balance
• Apply the same year filter (2024-2025) to exclude stray transactions

Bank-specific views

1. Month-level dashboard: Three-bar chart per month (Coop, Uniconta, Bank)
2. Divergence report: Months where |Coop - Bank| > tolerance
3. Bonus reconciliation: Separate tab for aggregated KG/Kreditnota/Rabat vs bank negatives
4. Many-to-one explorer: Click a bank transaction to see suggested Coop invoice combinations that sum to the bank amount

Data model extension for bank

class BankTransaction(models.Model):
    date = models.DateField()
    value_date = models.DateField(null=True)
    amount = models.DecimalField(max_digits=18, decimal_places=2)
    text = models.TextField()
    balance = models.DecimalField(max_digits=18, decimal_places=2)
    source_file = models.ForeignKey(SourceFile, ...)

Or reuse existing Transaction model with source="bank".

---

Performance / verification script

Use docker exec with the container's Python + pandas to parse bank CSVs and cross-check against already-loaded Coop/Uniconta data.

# Quick verification of month-level totals
import pandas as pd

def parse_bank_csv(path):
    df = pd.read_csv(path, sep=';', encoding='latin-1',
                     header=0, names=['Dato','Valør','Tekst','_','Beløb','Saldo','Ref'])
    df['Dato'] = pd.to_datetime(df['Dato'], format='%d.%m.%Y', errors='coerce')
    df['Beløb'] = df['Beløb'].str.replace('.', '').str.replace(',', '.').astype(float)
    return df

# Summarize by month and compare with Coop/month, Uniconta/month
# Run this as a Django management command or script inside the container