dennis
236053cd7f
Kategoriserer danske regnskabs-/ERP-skills, ERP-data-afstemnings-
skill og to devops-workflows (gitea-issue-agent, kanban-workflows)
som Radix-medlemmers AI-agenter kan dele.
Struktur:
skills/<kategori>/<skill-navn>/SKILL.md
skills/<kategori>/<skill-navn>/{references,templates,scripts}/
Indekseret af index.json (genereret af scripts/rebuild_index.py).
Kør `python3 scripts/rebuild_index.py --check` i CI for at fange
synkroniseringsfejl.
74 lines
3.8 KiB
Markdown
74 lines
3.8 KiB
Markdown
# Bank Reconciliation — Django Implementation Guide
|
|
|
|
Practical implementation notes for extending a two-source Coop↔Uniconta reconciliation webapp with bank CSV support.
|
|
|
|
## Model extensions
|
|
|
|
Add these fields to `Transaction` (one migration):
|
|
```python
|
|
account_number = models.CharField(max_length=50, blank=True, db_index=True)
|
|
bank_text = models.TextField(blank=True)
|
|
valuta_date = models.DateField(null=True, blank=True)
|
|
```
|
|
|
|
Also extend `SourceFile.source` choices and `Transaction.source`/`match_type` choices to include `("bank", "Bank")` and `("bank_total", "Bank-samlet")`.
|
|
|
|
## Bank CSV parser
|
|
|
|
Key implementation in `apps/import_/parsers.py`:
|
|
|
|
- **Encodings**: Try `latin-1` first, then `utf-8`. Most Danish bank exports use latin-1.
|
|
- **Delimiter**: `;` (semicolon)
|
|
- **Header mapping**: Fuzzy-match by lowercasing, stripping null bytes and quotes:
|
|
- `dato` → date column
|
|
- `valør`/`valuta` → value date column
|
|
- `tekst`/`beskrivelse` → text column
|
|
- `bel` (prefix) → amount column
|
|
- `saldo` → balance column
|
|
- `reference`/`bilagsrefer` → reference column
|
|
- **Date parse**: `DD.MM.YYYY` → split by dots → `datetime(int(parts[2]), int(parts[1]), int(parts[0])).date()`
|
|
- **Amount parse**:
|
|
1. Strip `\xa0` (nbsp), `\u202f` (narrow nbsp), `" DKK"`
|
|
2. If `.` and `,` both present → remove dots, replace comma with dot
|
|
3. If only comma → replace with dot
|
|
4. Convert to `Decimal`
|
|
- **Bulk create**: Use `Transaction.objects.bulk_create(txns_to_create)` for performance
|
|
- **Account extraction**: Regex `r"(\d{8,})"` on text field to find kundenummer/kontonummer
|
|
|
|
## Bank ↔ Coop match (many-to-one)
|
|
|
|
Group bank payments by `(account_number, year, month)` and match against the sum of unmatched Coop transactions in the same bucket.
|
|
|
|
```python
|
|
bank_txns = Transaction.objects.filter(source="bank", year=year).values(
|
|
"account_number", "year", "month"
|
|
).annotate(total=Sum("amount"), count=Count("id"))
|
|
|
|
for bt in bank_txns:
|
|
coop_sum = Transaction.objects.filter(
|
|
source="coop", year=bt["year"], month=bt["month"],
|
|
match_status="unmatched", account_number=bt["account_number"],
|
|
).aggregate(s=Sum("amount"))["s"]
|
|
|
|
if coop_sum is not None and abs(bt["total"] - coop_sum) <= TOLERANCE:
|
|
# Create MatchGroup linking bank bucket to Coop bucket
|
|
...
|
|
```
|
|
|
|
**Note**: Bank amounts are positive (indbetalinger), Coop RE/Bankoverførsel amounts are negative. Compare absolute values. Set `match_type="bank_total"`.
|
|
|
|
## Views/templates changes
|
|
|
|
- **Dashboard**: Add `bank_count`, `bank_sum` cards; add `bank_count` / `bank_sum` columns to monthly overview table
|
|
- **Upload form**: Add `<option value="bank">Bank (Kontobevaegelser .csv)</option>`
|
|
- **Reconciliation list**: Add `bank` to source filter; render bank badge with `.badge-warning`
|
|
- **Run match page**: Document match phases: Reference → ZV↔Faktura → RE/Betaling → General → Many-to-one → Bank-total
|
|
|
|
## Discovered pitfalls
|
|
|
|
1. **Migrations not detected**: If `makemigrations` reports "No changes detected", check whether a prior manual migration already captured the changes. Run `migrate --check` first.
|
|
2. **Docker file path mismatch**: When testing via `docker compose exec`, container filesystem may differ from host. Use the web UI upload form for file import testing.
|
|
3. **Bank text homogeneity**: All transactions from the same counterparty often have identical text (e.g. `"Coop 0010014808 -SE MEDD."`). Text similarity is useless for matching. Rely on `account_number` + date+amount grouping.
|
|
4. **Bank negatives are rare but critical**: Negative bank amounts (~1-2%) represent bonus/modregning/retur. Do NOT match these against positive Coop invoices. Flag separately for bonus reconciliation.
|
|
5. **Bank total may differ slightly from Coop sum**: Normal difference is ±0.1% (fees, rounding). Use tolerance matching (e.g. ±5 DKK) for bank-total matches.
|