Open-source personal finance manager. Self-hosted, privacy-first.
import csv
import hashlib
import io
import re
import uuid
import warnings
import xml.etree.ElementTree as ET
from xml.parsers import expat
from datetime import datetime
from decimal import Decimal
from bs4 import XMLParsedAsHTMLWarning
from ofxparse import OfxParser
from sqlalchemy import or_, select
from sqlalchemy.ext.asyncio import AsyncSession
from app.core.config import get_settings
from app.models.account import Account
from app.models.bank_connection import BankConnection
from app.models.category import Category
from app.models.rule import Rule
from app.models.transaction import Transaction
from app.schemas.transaction import TransactionImport, FailedRow
from app.services import reconciliation_service, recurring_match_service
from app.services.credit_card_service import apply_effective_date
from app.services.category_service import get_assignable_category_ids
from app.services.rule_engine import apply_rule_actions, evaluate_conditions, merge_notes
from app.services.rule_service import apply_rules_to_transaction, preview_rules_for_transaction
from app.services.fx_rate_service import stamp_primary_amount
from app.services.payee_service import get_or_create_payee
from app.services.transaction_match_service import find_unique_transaction_match
# Descriptions used by some Brazilian banks (e.g. Banco do Brasil) for
# balance-summary rows that arrive as <STMTTRN> blocks but are not real
# transactions. Matched case-insensitively against MEMO/NAME.
_OFX_BALANCE_ROW_DESCRIPTIONS = (
"saldo anterior",
"saldo do dia",
"saldo final",
"s a l d o",
)
def _decode_ofx_bytes(content: bytes) -> tuple[str, str]:
"""Decode OFX content to text, trying UTF-8 then falling back to Latin-1.
Returns (text, encoding) so callers can re-encode consistently later.
"""
try:
return content.decode("utf-8"), "utf-8"
except UnicodeDecodeError:
return content.decode("latin-1"), "latin-1"
def _patch_empty_fitids(text: str) -> str:
"""Synthesize a FITID for STMTTRN blocks that have an empty/missing one.
Banco do Brasil (and a few other Brazilian banks) emit balance-summary
rows as <STMTTRN> blocks with empty <FITID> tags, which makes ofxparse
abort the entire import with "Empty FIT id (a required field)". We patch
each affected block with a deterministic synthetic FITID so parsing
succeeds; balance rows are filtered out later by description.
"""
def _replace(match: re.Match) -> str:
block = match.group(0)
fitid_match = re.search(r"<FITID>([^<\r\n]*)", block, re.IGNORECASE)
has_value = fitid_match and fitid_match.group(1).strip()
if has_value:
return block
seed = hashlib.sha1(block.encode("utf-8", errors="replace")).hexdigest()[:16].upper()
synthetic = f"SYNTH-{seed}"
if fitid_match:
return block[: fitid_match.start(1)] + synthetic + block[fitid_match.end(1):]
# No FITID tag at all — inject one right after the opening <STMTTRN>
return re.sub(
r"(<STMTTRN>)",
rf"\1\n<FITID>{synthetic}",
block,
count=1,
flags=re.IGNORECASE,
)
return re.sub(
r"<STMTTRN>.*?</STMTTRN>",
_replace,
text,
flags=re.IGNORECASE | re.DOTALL,
)
_OFX_SGML_ENCODING_RE = re.compile(
r"(^|\r?\n)\s*ENCODING\s*:\s*([^\r\n]*)", re.IGNORECASE
)
_OFX_SGML_CHARSET_RE = re.compile(
r"(^|\r?\n)\s*CHARSET\s*:\s*([^\r\n]*)", re.IGNORECASE
)
# Values of ENCODING that ofxparse 0.21 handles without crashing.
_OFX_USASCII_VARIANTS = frozenset({
"USASCII", "ISO88591", "ISO8859-1", "ISO885915", "ISO8859-15",
"CP1252", "WINDOWS-1252", "WINDOWS1252",
})
_OFX_UTF8_VARIANTS = frozenset({
"UNICODE", "UTF8", "UTF-8",
})
def _normalize_ofx_encoding(text: str, encoding: str) -> str:
"""Fix SGML headers with ENCODING values that crash ofxparse.
ofxparse 0.21 only handles USASCII, UNICODE and UTF-8. Any other value
(e.g. ISO-8859-1, WINDOWS-1252, or a misspelling) leaves its local
``encoding`` variable unbound, raising ``UnboundLocalError`` inside
``handle_encoding()``.
This function normalises **only the preamble** (everything before the
first ``<``) so transaction memo content is never modified. Latin-1
decoded files get ``ENCODING:USASCII``; the ``CHARSET`` distinguishes
the two byte layouts Python's ``latin-1`` codec can represent, so
ofxparse decodes the same bytes the same way the source declared:
``CHARSET:1252`` when the file declared WINDOWS-1252/CP1252 (whose
0x80-0x9F range holds real typographic characters — em dash, curly
quotes — that ISO-8859-1 leaves as undefined control codes), and
``CHARSET:8859-1`` otherwise. UTF-8 files get ``ENCODING:UTF-8`` with
``CHARSET:NONE``.
"""
preamble, first_tag, body = text.lstrip("\ufeff \t\r\n").partition("<")
if not first_tag or not preamble.strip():
return text
raw_match = _OFX_SGML_ENCODING_RE.search(preamble)
if not raw_match:
return text
raw_value = raw_match.group(2).strip().upper().replace("-", "").replace(" ", "")
# Only the three exact values that ofxparse 0.21 handles are left alone.
# Everything else — including valid IANA names like ISO-8859-1 — must be
# rewritten so ofxparse doesn't crash on the UnboundLocalError.
if raw_value in ("USASCII", "UNICODE", "UTF8"):
return text
# Unknown or unsupported encoding — rewrite to match the actual byte
# encoding so ofxparse decodes consistently.
if encoding == "latin-1":
new_encoding = "USASCII"
new_charset = "1252" if raw_value in ("CP1252", "WINDOWS1252") else "8859-1"
else:
new_encoding = "UTF-8"
new_charset = "NONE"
new_preamble = _OFX_SGML_ENCODING_RE.sub(
lambda m: f"{m.group(1)}ENCODING:{new_encoding}", preamble, count=1,
)
new_preamble = _OFX_SGML_CHARSET_RE.sub(
lambda m: f"{m.group(1)}CHARSET:{new_charset}", new_preamble, count=1,
)
return new_preamble + first_tag + body
def _ensure_ofx_sgml_header(text: str, encoding: str) -> str:
"""Prepend a legacy OFX 1.x SGML header for OFX 2.x files that omit it.
OFX 2.x is plain XML that goes straight into its first tag, with no
colon-delimited SGML header block (e.g. Erste Bank's "MS Money Sunset
Deluxe" export). ofxparse only looks for encoding hints in the bytes
preceding the file's first "<"; when that's empty it silently assumes
ASCII and crashes on any non-ASCII byte. Prepending a synthetic SGML
header routes the file through ofxparse's existing, correctly-working
SGML decode path instead of its broken auto-detection (see
https://github.com/jseutter/ofxparse/issues/133).
The trigger is therefore "nothing precedes the first tag", not "starts
with <?xml": the XML declaration is optional in XML 1.0, so an OFX 2.x
file may open with just its <?OFX ... ?> instruction, or with <OFX>
itself, and those hit the same ofxparse bug. Anything else already has a
legacy header, and a file with no tag at all is left for ofxparse to
reject on its own terms.
`encoding` must match whatever the caller will re-encode `text` with,
so the declared header and the actual bytes stay consistent.
"""
preamble, first_tag, _ = text.lstrip("\ufeff \t\r\n").partition("<")
if not first_tag:
return text
if preamble.strip():
# File already has a header — normalise the ENCODING value so
# ofxparse doesn't crash on values it doesn't handle (e.g.
# ISO-8859-1, WINDOWS-1252). Only the preamble is touched;
# transaction body content is preserved verbatim.
return _normalize_ofx_encoding(text, encoding)
if encoding == "latin-1":
enc_lines = "ENCODING:USASCII\r\nCHARSET:8859-1\r\n"
else:
enc_lines = "ENCODING:UTF-8\r\nCHARSET:NONE\r\n"
header = (
f"OFXHEADER:100\r\nDATA:OFXSGML\r\nVERSION:102\r\nSECURITY:NONE\r\n"
f"{enc_lines}COMPRESSION:NONE\r\nOLDFILEUID:NONE\r\nNEWFILEUID:NONE\r\n\r\n"
)
return header + text
def _preprocess_ofx(content: bytes) -> bytes:
"""Apply text-level fixups ofxparse needs before it can parse the file."""
text, encoding = _decode_ofx_bytes(content)
text = _patch_empty_fitids(text)
text = _ensure_ofx_sgml_header(text, encoding)
return text.encode(encoding, errors="replace")
def _is_balance_summary_row(description: str | None) -> bool:
if not description:
return False
normalized = description.strip().lower()
return any(normalized.startswith(prefix) for prefix in _OFX_BALANCE_ROW_DESCRIPTIONS)
def parse_ofx(content: bytes) -> list[TransactionImport]:
"""Parse OFX file content and return transactions."""
content = _preprocess_ofx(content)
# ofxparse 0.21 intentionally parses normalized SGML/XML with html.parser
# and still calls BeautifulSoup's findAll alias. Keep this compatibility
# boundary local; remove it when ofxparse adopts the supported soup API.
with warnings.catch_warnings():
warnings.filterwarnings(
"ignore",
message=(
r"^Call to deprecated method findAll\. \(Replaced by find_all\) "
r"-- Deprecated since version 4\.0\.0\.$"
),
category=DeprecationWarning,
module=r"^ofxparse\.ofxparse$",
)
warnings.filterwarnings(
"ignore",
category=XMLParsedAsHTMLWarning,
module=r"^ofxparse\.ofxparse$",
)
ofx = OfxParser.parse(io.BytesIO(content))
transactions = []
for account in ofx.accounts:
for txn in account.statement.transactions:
raw_payee = getattr(txn, 'payee', None) or None
description = txn.memo or txn.payee or "Unknown"
if _is_balance_summary_row(description):
continue
external_id = getattr(txn, 'id', None)
# Synthetic IDs are added only to make ofxparse happy; do not
# persist them as external_id since they are not stable bank
# identifiers.
if external_id and external_id.startswith("SYNTH-"):
external_id = None
transactions.append(TransactionImport(
description=description,
amount=abs(Decimal(str(txn.amount))),
date=txn.date.date() if hasattr(txn.date, 'date') else txn.date,
type="credit" if txn.amount > 0 else "debit",
external_id=external_id,
payee_raw=raw_payee,
))
return transactions
# QIF "D" lines carry no format metadata; US-first order is the historical
# default. The Quicken apostrophe variants are always month-first.
_QIF_FALLBACK_DATE_FORMATS = [
'%m/%d/%Y', '%d/%m/%Y', '%Y-%m-%d',
"%m/%d'%Y", "%m/%d'%y",
'%m/%d/%y', '%d/%m/%y',
]
def _qif_date_formats(date_format: str | None, raw_dates: list[str]) -> list[str]:
"""Decide the strptime formats for a QIF file's dates, once per file.
An explicit user choice is strict (like parse_csv): only that format and
its 2-digit-year variant are accepted. Otherwise the order is inferred
from the whole file — a first component > 12 can only be a day, so the
file is DD/MM; per-line first-match parsing would silently mix MM/DD and
DD/MM within a single import for the ambiguous days 1-12.
"""
if date_format and date_format in DATE_FORMAT_MAP:
fmt = DATE_FORMAT_MAP[date_format]
return [fmt, fmt.replace('%Y', '%y')]
saw_day_first = saw_month_first = False
for value in raw_dates:
match = re.match(r"^(\d{1,2})/(\d{1,2})/\d{2,4}$", value)
if not match:
continue
first, second = int(match.group(1)), int(match.group(2))
if first > 12 >= second:
saw_day_first = True
if second > 12 >= first:
saw_month_first = True
if saw_day_first and not saw_month_first:
return ['%d/%m/%Y', '%d/%m/%y'] + _QIF_FALLBACK_DATE_FORMATS
return list(_QIF_FALLBACK_DATE_FORMATS)
# QIF sections whose blocks describe something other than a transaction:
# account headers, category and class lists, memorized entries, securities
# and prices. Their fields reuse transaction tags ("TBank" is an account
# type, not an amount), so reading them as records would report errors for
# rows that were never meant to be imported.
_QIF_NON_TRANSACTION_HEADERS = (
'!account', '!type:cat', '!type:class', '!type:memorized',
'!type:security', '!type:prices', '!type:template',
)
def _qif_records(text: str) -> list[tuple[int, list[str]]]:
"""Split QIF text into transaction records, line by line.
Each record is (1-based line of its first field, its stripped field
lines). A record ends at a line that starts with "^"; a "^" inside a
payee or memo is part of that field, not a separator. Header lines
("!Type:Bank", "!Option:AutoSwitch") are not fields, and blocks under a
non-transaction header are left out.
"""
records: list[tuple[int, list[str]]] = []
current: list[str] = []
start = 0
skip = False
def close() -> None:
if current and not skip:
records.append((start, list(current)))
current.clear()
for number, raw in enumerate(text.splitlines(), start=1):
line = raw.strip()
if not line:
continue
if line.startswith('^'):
close()
continue
if line.startswith('!'):
lower = line.lower()
if lower.startswith(('!option', '!clear')):
continue
close()
skip = lower.startswith(_QIF_NON_TRANSACTION_HEADERS)
continue
if not current:
start = number
current.append(line)
close()
return records
def parse_qif(content: bytes, date_format: str | None = None) -> tuple[list[TransactionImport], list[FailedRow]]:
"""Parse QIF file content and return transactions.
date_format: optional explicit format (see DATE_FORMAT_MAP keys); when
omitted, the day/month order is inferred from the whole file.
Malformed records (missing/unreadable date or amount, or amount fields
that disagree) are not guessed at: they come back in the second element
so the preview can show them per line, like the CSV path does.
"""
# Try UTF-8 first, fall back to Latin-1 for legacy software (e.g. Microsoft Money)
try:
text = content.decode('utf-8-sig')
except UnicodeDecodeError:
text = content.decode('latin-1')
transactions = []
failed_rows = []
records = _qif_records(text)
raw_dates = [
line[1:].strip()
for _, lines in records
for line in lines
if line.startswith('D')
]
date_formats = _qif_date_formats(date_format, raw_dates)
for record_line, lines in records:
txn_date = None
date_raw = None
amount_raws: list[str] = []
amounts: list[Decimal] = []
payee = None
memo = None
for line in lines:
tag, value = line[0], line[1:]
if tag == 'D':
date_raw = value.strip()
for fmt in date_formats:
try:
txn_date = datetime.strptime(value.strip(), fmt).date()
break
except ValueError:
continue
elif tag == 'T' or tag == 'U':
raw = value.strip().replace(',', '')
amount_raws.append(raw)
try:
amounts.append(Decimal(raw))
except Exception:
pass
elif tag == 'P':
payee = value.strip()
elif tag == 'M':
memo = value.strip()
# A block with neither an amount field nor a parseable date is not
# a record (e.g. a "Date:January 2026" preamble line, whose tag is
# 'D' but whose value is not a date).
if not amount_raws and txn_date is None:
continue
description = payee or memo or ""
if txn_date is None:
failed_rows.append(FailedRow(line_number=record_line, description=description, raw_value=date_raw or "", error_reason="invalid_date"))
continue
if not amount_raws:
failed_rows.append(FailedRow(line_number=record_line, description=description, raw_value="", error_reason="no_amount"))
continue
if not amounts:
failed_rows.append(FailedRow(line_number=record_line, description=description, raw_value=amount_raws[-1], error_reason="invalid_amount"))
continue
if len(set(amounts)) > 1:
# Two amount fields that disagree: "the last one that parsed"
# used to win, which may not be the real value of the record.
failed_rows.append(FailedRow(line_number=record_line, description=description, raw_value=", ".join(amount_raws), error_reason="conflicting_amount"))
continue
amount = amounts[-1]
transactions.append(TransactionImport(
description=payee or memo or "Unknown",
amount=abs(amount),
date=txn_date,
type="credit" if amount > 0 else "debit",
payee_raw=payee,
))
return transactions, failed_rows
def _camt_entry_lines(content: bytes) -> list[int]:
"""1-based source line of every <Ntry> start tag, in document order."""
lines: list[int] = []
parser = expat.ParserCreate()
def start(name: str, _attrs: dict) -> None:
if name.rsplit(':', 1)[-1] == 'Ntry':
lines.append(parser.CurrentLineNumber)
parser.StartElementHandler = start
try:
parser.Parse(content, True)
except expat.ExpatError:
return []
return lines
def parse_camt(content: bytes) -> tuple[list[TransactionImport], list[FailedRow]]:
"""Parse CAMT.052/CAMT.053 (ISO 20022) XML file content.
Returns the parsed transactions plus the entries that had to be
dropped, each with a reason, so the preview can show them per record
instead of the file coming back short with no explanation.
"""
root = ET.fromstring(content)
# Detect namespace dynamically
ns_match = re.match(r'\{(.+?)\}', root.tag)
ns = ns_match.group(1) if ns_match else ''
nsmap = {'ns': ns} if ns else {}
def find(element, path):
"""Find element with or without namespace."""
if nsmap:
parts = path.split('/')
ns_path = '/'.join(f'ns:{p}' for p in parts)
return element.find(ns_path, nsmap)
return element.find(path)
def findall(element, path):
if nsmap:
parts = path.split('/')
ns_path = '/'.join(f'ns:{p}' for p in parts)
return element.findall(ns_path, nsmap)
return element.findall(path)
def find_text(element, path):
el = find(element, path)
return el.text if el is not None else None
# Failed rows are labelled "Line" in the preview, so an entry is reported
# by the line of its opening <Ntry> tag. Only when the raw scan and the
# parsed tree disagree on the entry count (which should not happen) does
# it fall back to the entry's position in the file.
entry_lines = _camt_entry_lines(content)
entry_total = sum(1 for el in root.iter() if el.tag.rsplit('}', 1)[-1] == 'Ntry')
def line_of(n: int) -> int:
if len(entry_lines) == entry_total:
return entry_lines[n - 1]
return n
failed_rows = []
entry_no = 0
transactions = []
# Navigate: Document > BkToCstmrStmt > Stmt > Ntry (CAMT.053, end-of-day statement)
# Fallback: Document > BkToCstmrAcctRpt > Rpt > Ntry (CAMT.052, intraday report —
# same Ntry sub-schema, different root/container element). Several European banks,
# including German Volksbanken/Raiffeisenbanken, only offer CAMT.052 exports.
stmts = findall(root, 'BkToCstmrStmt/Stmt') or findall(root, 'BkToCstmrAcctRpt/Rpt')
for stmt in stmts:
for ntry in findall(stmt, 'Ntry'):
entry_no += 1
# Description up front so a dropped entry can carry it in its
# failure report.
description = (
find_text(ntry, 'NtryDtls/TxDtls/RmtInf/Ustrd')
or find_text(ntry, 'NtryDtls/TxDtls/RltdPties/Cdtr/Nm')
or find_text(ntry, 'NtryDtls/TxDtls/RltdPties/Dbtr/Nm')
or find_text(ntry, 'AddtlNtryInf')
or "Unknown"
)
# Entry status: BOOK (final) vs. PDNG/INFO (pending/informational).
# CAMT.052 intraday reports commonly include PDNG entries for
# transactions that haven't settled yet; the same transaction is
# reported again as BOOK once it settles. Skip anything that
# isn't BOOK to avoid importing it twice. Status is either a
# plain code (<Sts>BOOK</Sts>) or wrapped (<Sts><Cd>BOOK</Cd></Sts>)
# depending on the schema version. Check the wrapped form first:
# in pretty-printed XML the <Sts> element's own text is the
# whitespace before <Cd>, which would otherwise mask the real code.
status = find_text(ntry, 'Sts/Cd') or find_text(ntry, 'Sts')
if status and status.strip().upper() != 'BOOK':
continue
# Amount
amt_el = find(ntry, 'Amt')
if amt_el is None:
failed_rows.append(FailedRow(line_number=line_of(entry_no), description=description, raw_value="", error_reason="no_amount"))
continue
try:
amount = Decimal(amt_el.text)
except Exception:
failed_rows.append(FailedRow(line_number=line_of(entry_no), description=description, raw_value=(amt_el.text or '').strip(), error_reason="invalid_amount"))
continue
# Credit/Debit indicator
cdt_dbt = (find_text(ntry, 'CdtDbtInd') or '').strip().upper()
# Anything that is neither CRDT nor DBIT is not a direction:
# importing it as a debit would flip the amount's sign, so skip
# the entry like the other malformed ones above.
if cdt_dbt not in ('CRDT', 'DBIT'):
failed_rows.append(FailedRow(line_number=line_of(entry_no), description=description, raw_value=cdt_dbt, error_reason="invalid_direction"))
continue
txn_type = "credit" if cdt_dbt == "CRDT" else "debit"
# Date: try BookgDt/Dt then ValDt/Dt
date_str = find_text(ntry, 'BookgDt/Dt') or find_text(ntry, 'ValDt/Dt')
if not date_str:
failed_rows.append(FailedRow(line_number=line_of(entry_no), description=description, raw_value="", error_reason="invalid_date"))
continue
try:
txn_date = datetime.strptime(date_str.strip(), '%Y-%m-%d').date()
except ValueError:
failed_rows.append(FailedRow(line_number=line_of(entry_no), description=description, raw_value=date_str.strip(), error_reason="invalid_date"))
continue
# Extract currency from Ccy attribute on Amt element
txn_currency = amt_el.get('Ccy') or None
transactions.append(TransactionImport(
description=description,
amount=abs(amount),
date=txn_date,
type=txn_type,
currency=txn_currency,
))
return transactions, failed_rows
DATE_FORMAT_MAP = {
'DD/MM/YYYY': '%d/%m/%Y',
'MM/DD/YYYY': '%m/%d/%Y',
'YYYY-MM-DD': '%Y-%m-%d',
}
# Securo fields a CSV column can be mapped to. Used to validate the
# user-supplied column_mapping and to drive the import-UI dropdowns.
CSV_MAPPABLE_FIELDS = (
'date', 'description', 'amount', 'type',
'category', 'currency', 'fx_rate', 'inflow', 'outflow',
'payee', 'external_id', 'notes',
)
def _sniff_csv_dialect(text: str):
"""Detect the CSV dialect (delimiter/quoting), falling back to comma."""
try:
return csv.Sniffer().sniff(text[:4096], delimiters=',;\t|')
except csv.Error:
return csv.excel
def detect_csv_columns(content: bytes) -> list[str]:
"""Return the CSV header column names exactly as they appear in the file.
Used by the import preview so the UI can offer accurate column-mapping
dropdowns instead of guessing headers client-side.
"""
text = content.decode('utf-8-sig') # Handle BOM
dialect = _sniff_csv_dialect(text)
reader = csv.DictReader(io.StringIO(text), dialect=dialect)
return [f.strip() for f in (reader.fieldnames or []) if f and f.strip()]
def parse_csv(
content: bytes,
date_format: str | None = None,
flip_amount: bool = False,
inflow_column: str | None = None,
outflow_column: str | None = None,
column_mapping: dict[str, str] | None = None,
) -> tuple[list[TransactionImport], list[FailedRow]]:
"""Parse CSV file content and return transactions.
Attempts to detect common column formats:
- date, description, amount
- data, descricao, valor (Portuguese)
Options:
- date_format: explicit date format (DD/MM/YYYY, MM/DD/YYYY, YYYY-MM-DD)
- flip_amount: negate all parsed amounts
- inflow_column/outflow_column: use split columns instead of single amount
- column_mapping: explicit Securo-field -> CSV-header map. Any field
present here overrides auto-detection; unmapped fields still auto-detect.
"""
text = content.decode('utf-8-sig') # Handle BOM
dialect = _sniff_csv_dialect(text)
reader = csv.DictReader(io.StringIO(text), dialect=dialect)
# Normalize field names
fieldnames = [f.lower().strip() if f is not None else "" for f in (reader.fieldnames or [])]
# Map common column names
date_cols = ['date', 'data', 'dt', 'transaction_date', 'data_transacao']
desc_cols = ['description', 'descricao', 'desc', 'memo', 'historico', 'lancamento']
amount_cols = ['amount', 'valor', 'value', 'quantia']
type_cols = ['type', 'tipo', 'transaction type', 'transaction_type']
category_cols = ['category', 'categoria']
currency_cols = ['currency', 'moeda', 'currency_code']
fx_rate_cols = ['fx_rate', 'fx_rate_used', 'taxa_cambio', 'exchange_rate', 'taxa']
payee_cols = ['payee', 'merchant', 'beneficiary', 'beneficiario', 'pagador']
external_id_cols = [] # External ID must be mapped explicitly
notes_cols = ['notes', 'nota', 'observacao']
# Normalize the user-supplied column mapping (Securo field -> CSV header).
mapping = {
field: value.lower().strip()
for field, value in (column_mapping or {}).items()
if field in CSV_MAPPABLE_FIELDS and value and value.strip()
}
def find_col(candidates):
for c in candidates:
if c in fieldnames:
return c
return None
def resolve_col(field, candidates):
"""Resolve a CSV column for a Securo field.
An explicit user mapping always wins; otherwise fall back to
auto-detection against the known column-name candidates.
"""
mapped = mapping.get(field)
if mapped:
if mapped not in fieldnames:
raise ValueError(
f"Mapped column '{mapped}' for field '{field}' not found in CSV. "
f"Available columns: {', '.join(fieldnames)}"
)
return mapped
return find_col(candidates)
date_col = resolve_col('date', date_cols)
desc_col = resolve_col('description', desc_cols)
# In split mode, we don't require a single amount column. The inflow/outflow
# columns may come from the explicit args or from the column mapping.
inflow_col = (inflow_column or mapping.get('inflow') or '').lower().strip() or None
outflow_col = (outflow_column or mapping.get('outflow') or '').lower().strip() or None
use_split = bool(inflow_col and outflow_col)
if use_split:
if inflow_col not in fieldnames or outflow_col not in fieldnames:
raise ValueError(f"Inflow/outflow columns not found in CSV. Available columns: {', '.join(fieldnames)}")
amount_col = None
else:
amount_col = resolve_col('amount', amount_cols)
type_col = resolve_col('type', type_cols)
category_col = resolve_col('category', category_cols)
currency_col = resolve_col('currency', currency_cols)
fx_rate_col = resolve_col('fx_rate', fx_rate_cols)
payee_col = resolve_col('payee', payee_cols)
external_id_col = resolve_col('external_id', external_id_cols)
notes_col = resolve_col('notes', notes_cols)
if not date_col or not desc_col:
raise ValueError(
f"Could not detect CSV columns. Found: {', '.join(fieldnames)}. "
f"Expected columns like: date, description, amount (or Portuguese equivalents: data, descricao, valor)"
)
if not use_split and not amount_col:
raise ValueError(
f"Could not detect amount column. Found: {', '.join(fieldnames)}. "
f"Expected a column named: {', '.join(amount_cols)}"
)
# Determine date formats to try
if date_format and date_format in DATE_FORMAT_MAP:
date_formats = [DATE_FORMAT_MAP[date_format]]
else:
date_formats = ['%Y-%m-%d', '%d/%m/%Y', '%d-%m-%Y', '%m/%d/%Y', '%d.%m.%Y']
# Decide the decimal separator once per file from every amount cell, so
# "25,000" next to "1,500.50" reads as twenty-five thousand rather than
# being guessed in isolation.
amount_fields = [c for c in (inflow_col, outflow_col) if c] if use_split else [amount_col]
decimal_separator = infer_decimal_separator(
v
for r in csv.DictReader(io.StringIO(text), dialect=dialect)
for k, v in r.items()
if k is not None and k.lower().strip() in amount_fields
)
transactions = []
failed_rows = []
for row in reader:
# Normalize row keys, and missing cells along with them. A row with
# fewer cells than the header leaves the rest as None, which every
# .strip() below raises on and which FailedRow.raw_value rejects. That
# turned a single short row into a 400 for the whole file, which is
# exactly the case this parser is meant to report row by row. An
# absent cell reads as an empty one.
row = {
k.lower().strip() if k is not None else "": ("" if v is None else v)
for k, v in row.items()
}
# Parse date
date_str = row[date_col].strip()
txn_date = None
for fmt in date_formats:
try:
txn_date = datetime.strptime(date_str, fmt).date()
break
except ValueError:
continue
if not txn_date:
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=date_str, error_reason="invalid_date"))
continue # Skip invalid dates
# Parse amount
if use_split:
inflow_raw = (row.get(inflow_col, "") or "").strip()
outflow_raw = (row.get(outflow_col, "") or "").strip()
inflow_str = normalize_amount(inflow_raw, decimal_separator)
outflow_str = normalize_amount(outflow_raw, decimal_separator)
# A filled cell that survives no normalization is garbage, not
# zero: report it like the single-amount path does for
# invalid_amount instead of silently importing it as nothing.
inflow = outflow = None
bad_cell = None
if inflow_raw:
try:
inflow = Decimal(inflow_str) if inflow_str else None
except Exception:
inflow = None
if inflow is None:
bad_cell = f"inflow: {inflow_raw}"
if bad_cell is None and outflow_raw:
try:
outflow = Decimal(outflow_str) if outflow_str else None
except Exception:
outflow = None
if outflow is None:
bad_cell = f"outflow: {outflow_raw}"
if bad_cell is not None:
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=bad_cell, error_reason="invalid_amount"))
continue # Skip invalid amounts
if inflow and inflow > 0 and outflow and outflow > 0:
# Both columns positive cannot be read as credit-versus-debit
# without guessing; make the user fix the file instead of
# importing the wrong direction.
raw_val = f"inflow: {inflow_raw}, outflow: {outflow_raw}"
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=raw_val, error_reason="ambiguous_amount"))
continue
if inflow and inflow > 0:
amount = inflow
txn_type = "credit"
elif outflow and outflow > 0:
amount = outflow
txn_type = "debit"
else:
raw_val = f"inflow: {inflow_raw}, outflow: {outflow_raw}"
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=raw_val, error_reason="no_amount"))
continue # Skip rows with no amount
else:
amount_str = normalize_amount(row[amount_col], decimal_separator)
try:
amount = Decimal(amount_str)
except Exception:
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=row[amount_col], error_reason="invalid_amount"))
continue # Skip invalid amounts
if flip_amount:
amount = -amount
raw_type = row.get(type_col, '').strip().lower() if type_col else ''
if raw_type in ('credit', 'debit'):
txn_type = raw_type
else:
txn_type = "credit" if amount > 0 else "debit"
amount = abs(amount)
# Extract optional category, currency and fx_rate from CSV columns
category_name = row[category_col].strip() if category_col and row.get(category_col) else None
txn_currency = None
txn_fx_rate = None
if currency_col and row.get(currency_col):
txn_currency = row[currency_col].strip().upper() or None
fx_raw = (row.get(fx_rate_col) or "").strip() if fx_rate_col else ""
if fx_raw:
fx_str = normalize_amount(fx_raw)
# A rate the user typed has to be a rate: an unparseable or
# negative cell used to be dropped silently, importing the row as
# if it were already in the account's currency. A blank cell or a
# zero still means "no rate given" (files often fill the column
# with 0 on rows in the account's own currency), which is how a
# zero rate was already treated at import time.
try:
rate = Decimal(fx_str) if fx_str else None
except Exception:
rate = None
if rate is None or rate < 0:
failed_rows.append(FailedRow(line_number=reader.line_num, description=row.get(desc_col, "").strip(), raw_value=fx_raw, error_reason="invalid_fx_rate"))
continue
txn_fx_rate = rate or None
txn_payee = row[payee_col].strip() if payee_col and row.get(payee_col) else None
txn_external_id = row[external_id_col].strip() if external_id_col and row.get(external_id_col) else None
txn_notes = row[notes_col].strip() if notes_col and row.get(notes_col) else None
transactions.append(TransactionImport(
description=row[desc_col].strip(),
amount=abs(amount),
date=txn_date,
type=txn_type,
currency=txn_currency,
fx_rate=txn_fx_rate,
category_name=category_name,
payee_raw=txn_payee,
external_id=txn_external_id,
notes=txn_notes,
))
return transactions, failed_rows
async def enrich_with_category_suggestions(
session: AsyncSession,
workspace_id: uuid.UUID,
transactions: list[TransactionImport],
) -> list[TransactionImport]:
result = await session.execute(
select(Rule)
.where(Rule.workspace_id == workspace_id, Rule.is_active == True)
.order_by(Rule.priority, Rule.id)
)
rules = result.scalars().all()
category_result = await session.execute(
select(Category).where(Category.workspace_id == workspace_id)
)
categories = category_result.scalars().all()
assignable_categories = await get_assignable_category_ids(session, workspace_id)
category_name_map = {str(c.id): c.name for c in categories}
category_name_to_id = {
c.name.strip().lower(): c.id
for c in categories
if c.id in assignable_categories
}
if not rules and not category_name_to_id:
return transactions
for txn in transactions:
proxy: Transaction = Transaction(
description=txn.description,
amount=txn.amount,
date=txn.date,
type=txn.type,
status="posted",
account_id=None,
payee_id=None,
notes=None,
category_id=None,
)
category_set = False
for rule in rules:
conditions = rule.conditions or []
actions = rule.actions or []
if evaluate_conditions(rule.conditions_op, conditions, proxy):
category_set = apply_rule_actions(
actions,
proxy,
category_set,
assignable_category_ids=assignable_categories,
)
# If rules did not set a category, apply the CSV category if found
if not category_set and txn.category_name:
csv_cat_id = category_name_to_id.get(txn.category_name.strip().lower())
if csv_cat_id:
proxy.category_id = csv_cat_id
category_set = True
if proxy.category_id:
txn.suggested_category_id = proxy.category_id
txn.suggested_category_name = category_name_map.get(str(proxy.category_id))
return transactions
async def import_transactions(
session: AsyncSession,
workspace_id: uuid.UUID,
user_id: uuid.UUID,
account_id: uuid.UUID,
transactions: list[TransactionImport],
source: str,
filename: str = "",
detected_format: str = "",
detect_duplicates: bool = True,
) -> tuple[int, int, int, uuid.UUID]:
"""Import transactions into an account in the given workspace.
`workspace_id` scopes tenant filters + stamps new rows. `user_id`
is the creator/author recorded on Transaction + ImportLog.
Returns (imported, skipped, excluded, import_log_id)."""
from app.models.import_log import ImportLog
included = [t for t in transactions if not t.excluded]
excluded_count = len(transactions) - len(included)
# Calculate summaries from included transactions only
total_credit = sum(t.amount for t in included if t.type == "credit")
total_debit = sum(t.amount for t in included if t.type == "debit")
# Create import log first to get its ID
import_log = ImportLog(
user_id=user_id,
workspace_id=workspace_id,
account_id=account_id,
filename=filename,
format=detected_format,
transaction_count=len(included),
total_credit=total_credit,
total_debit=total_debit,
)
session.add(import_log)
await session.flush() # Get the import_log.id
# Look up account currency for fallback
account_result = await session.execute(select(Account).where(Account.id == account_id))
account = account_result.scalar_one_or_none()
if account and account.connection_id:
await session.execute(
select(BankConnection.id)
.where(BankConnection.id == account.connection_id)
.with_for_update()
)
if account:
account_result = await session.execute(
select(Account).where(Account.id == account_id).with_for_update()
)
account = account_result.scalar_one()
account_currency = account.currency if account else get_settings().default_currency
# Build category name → id map scoped to the workspace, on the same terms
# the preview used: hidden categories excluded, names matched
# case-insensitively. Diverging here meant a CSV category the preview
# deliberately withheld was still persisted on the imported row, and that
# "Food" matched in the preview but not on import.
category_result = await session.execute(
select(Category).where(Category.workspace_id == workspace_id)
)
assignable_categories = await get_assignable_category_ids(session, workspace_id)
category_map = {
c.name.strip().lower(): c.id
for c in category_result.scalars()
if c.id in assignable_categories
}
imported = 0
landed: list[Transaction] = []
skipped = 0
matched_existing_ids: set[uuid.UUID] = set()
effective_format = (detected_format or source or "").lower()
should_detect_duplicates = detect_duplicates if effective_format == "csv" else True
for txn_data in included:
# Resolve currency: CSV value > account currency
txn_currency = txn_data.currency or account_currency
if should_detect_duplicates:
# Prefer an external ID (OFX FITID), with date retained because some
# Brazilian cards reuse one purchase FITID across monthly installments,
# and amount and type retained because some banks reuse one FITID for
# several distinct entries posted on the same day.
# Formats without unique IDs fall back to transaction fields; compare
# both descriptions because rules may have changed the displayed one.
if txn_data.external_id:
existing_statement = select(Transaction).where(
Transaction.account_id == account_id,
Transaction.external_id == txn_data.external_id,
Transaction.date == txn_data.date,
Transaction.amount == txn_data.amount,
Transaction.type == txn_data.type,
)
else:
existing_statement = select(Transaction).where(
Transaction.account_id == account_id,
Transaction.date == txn_data.date,
Transaction.amount == txn_data.amount,
Transaction.type == txn_data.type,
or_(
Transaction.description == txn_data.description,
Transaction.original_description == txn_data.description,
),
)
# `.first()` rather than `.scalar_one_or_none()`: the dedup key can
# legitimately match more than one row (e.g. a prior sync/import race
# left a duplicate, or a bank reuses one FITID across statements),
# and we only need to know whether *any* match exists. Requiring
# exactly one would raise MultipleResultsFound and abort the import.
if matched_existing_ids and not txn_data.external_id:
existing_statement = existing_statement.where(
Transaction.id.not_in(matched_existing_ids)
)
existing = await session.execute(
existing_statement.order_by(Transaction.created_at, Transaction.id)
)
duplicate = existing.scalars().first()
if not duplicate:
duplicate = await find_unique_transaction_match(
session,
account_id,
txn_data,
{"sync"},
exclude_ids=matched_existing_ids,
)
if duplicate:
matched_existing_ids.add(duplicate.id)
skipped += 1
continue
import_payee_id = None
import_payee_raw = getattr(txn_data, "payee_raw", None)
if import_payee_raw:
import_payee_entity = await get_or_create_payee(
session, user_id, import_payee_raw, workspace_id=workspace_id,
source="import",
)
import_payee_id = import_payee_entity.id
user_category_id = txn_data.category_id
suggested_category_id = txn_data.suggested_category_id
csv_category_id = (
category_map.get(txn_data.category_name.strip().lower())
if txn_data.category_name
else None
)
category_id = (
None
if txn_data.force_uncategorized
else user_category_id or suggested_category_id
)
incoming = Transaction(
user_id=user_id,
workspace_id=workspace_id,
account_id=account_id,
description=txn_data.description,
original_description=txn_data.description,
amount=txn_data.amount,
date=txn_data.date,
type=txn_data.type,
status="posted",
source=source,
import_id=import_log.id,
external_id=txn_data.external_id,
currency=txn_currency,
payee=import_payee_raw,
payee_id=import_payee_id,
category_id=category_id,
notes=getattr(txn_data, "notes", None),
)
apply_effective_date(incoming, account)
preview = await preview_rules_for_transaction(
session,
user_id,
incoming,
skip_category_rules=txn_data.force_uncategorized,
)
if preview.category_id is None and not txn_data.force_uncategorized:
preview.category_id = csv_category_id
# Normalize a detached candidate before either recurring match. If a
# generated placeholder already represents this occurrence, upgrade it
# in place; otherwise link the new row to an active recurring definition.
placeholder = await recurring_match_service.find_placeholder_for_incoming(
session,
account_id,
txn_data.amount,
txn_currency,
txn_data.type,
txn_data.date,
preview.description,
)
if placeholder and not placeholder.is_ignored:
placeholder.source = source
placeholder.external_id = txn_data.external_id
placeholder.import_id = import_log.id
placeholder.status = "posted"
# The rules already ran, against the incoming charge, to build
# `preview`. Fold that result in rather than re-running them
# against the placeholder: its description is the recurring
# definition's own wording, so conditions written for the bank's
# text would no longer match. Everything the user can already see
# wins, the charge only fills what is still empty, and only its
# provenance is recorded outright.
placeholder.original_description = txn_data.description
if placeholder.amount != txn_data.amount or txn_data.fx_rate:
# Matched under a tolerance, or the file says what the charge
# cost in the primary currency: the row now stands for what was
# actually charged, not for what the bill expected.
placeholder.amount = txn_data.amount
if txn_data.fx_rate:
placeholder.fx_rate_used = txn_data.fx_rate
placeholder.amount_primary = txn_data.amount * txn_data.fx_rate
else:
await stamp_primary_amount(session, user_id, placeholder)
if placeholder.category_id is None:
placeholder.category_id = preview.category_id
if import_payee_raw and not placeholder.payee:
placeholder.payee = import_payee_raw
if placeholder.payee_id is None:
placeholder.payee_id = preview.payee_id
placeholder.notes = merge_notes(placeholder.notes, preview.notes)
if preview.is_ignored:
placeholder.is_ignored = True
imported += 1
continue
recurring_link = await recurring_match_service.find_bill_for_incoming(
session,
user_id,
account_id,
txn_data.amount,
txn_currency,
txn_data.type,
txn_data.date,
preview.description,
)
incoming.recurring_transaction_id = (
recurring_link.id if recurring_link else None
)
if txn_data.fx_rate:
incoming.fx_rate_used = txn_data.fx_rate
incoming.amount_primary = txn_data.amount * txn_data.fx_rate
session.add(incoming)
await session.flush()
if should_detect_duplicates and not txn_data.external_id:
matched_existing_ids.add(incoming.id)
if recurring_link is not None:
recurring_match_service.advance_past(recurring_link, txn_data.date)
await apply_rules_to_transaction(
session,
user_id,
incoming,
skip_category_rules=txn_data.force_uncategorized,
)
if incoming.category_id is None and not txn_data.force_uncategorized:
incoming.category_id = csv_category_id
if not txn_data.fx_rate:
await stamp_primary_amount(session, user_id, incoming)
imported += 1
landed.append(incoming)
# Update import log with actual imported count
import_log.transaction_count = imported
# Invoices last, and as one batch. Unlike the recurring match above:
# which upgrades a placeholder in place and so must happen before the
# row is written: settling an invoice creates an allocation pointing
# at a transaction, which has to exist first.
await reconciliation_service.match_incoming(session, workspace_id, landed)
await session.commit()
return imported, skipped, excluded_count, import_log.id
# Currency symbols and ISO codes around a number ("R$", "$", "NGN ", " EUR").
_AMOUNT_EDGE_LEADING = re.compile(r'^[^\d\-+(),.]+')
_AMOUNT_EDGE_TRAILING = re.compile(r'[^\d\-+(),.]+$')
_COMMA_DECIMAL_TAIL = re.compile(r',\d{1,2}$')
_DOT_DECIMAL_TAIL = re.compile(r'\.\d{1,2}$')
_COMMA_THOUSANDS = re.compile(r'^\d{1,3}(,\d{3})+$')
_ZERO_COMMA_DECIMAL = re.compile(r'^0,\d+$')
_DR_CR_SUFFIX = re.compile(r'(?i)(?<![a-z])(dr|cr)\.?$')
# U+2212 minus, U+2012 figure dash, U+2013 en dash, U+FE63 small and
# U+FF0D fullwidth hyphen-minus: spreadsheet exports use them as a minus.
_UNICODE_MINUS = str.maketrans({c: '-' for c in '\u2212\u2012\u2013\ufe63\uff0d'})
def _strip_amount_decorations(amount_str: str) -> tuple[str, bool]:
"""Remove currency symbols, codes, whitespace and sign markers.
Returns the bare number and whether it was negative. A leading minus
(ASCII or a Unicode minus sign), accounting parentheses, e.g. "(12.50)",
or a trailing "DR" mark a negative amount; a trailing "CR" marks a
positive one.
"""
s = re.sub(r"[\s'\u00a0\u202f]", "", amount_str).translate(_UNICODE_MINUS)
# A standalone DR/CR suffix carries the sign, so read it before the edge
# strip below would drop it as a currency code. Codes such as "XDR" or
# "CRC" do not match.
marker = _DR_CR_SUFFIX.search(s)
if marker:
s = s[: marker.start()]
negative = False
while True:
before = s
s = _AMOUNT_EDGE_LEADING.sub("", s)
s = _AMOUNT_EDGE_TRAILING.sub("", s)
if len(s) >= 2 and s[0] == "(" and s[-1] == ")":
negative = not negative
s = s[1:-1]
elif s[:1] == "-":
negative = not negative
s = s[1:]
elif s[:1] == "+":
s = s[1:]
if s == before:
if marker:
negative = marker.group(1).lower() == "dr"
return s, negative
def infer_decimal_separator(values) -> str | None:
"""Infer the decimal separator used by a whole column of amounts.
Returns "." or ",", or None when the values give no clear signal, in
which case normalize_amount falls back to deciding cell by cell.
"""
votes: set[str] = set()
comma_thousands = False
for raw in values:
if not raw:
continue
s, _ = _strip_amount_decorations(str(raw))
if not s:
continue
if ',' in s and '.' in s:
votes.add(',' if s.rfind(',') > s.rfind('.') else '.')
elif _COMMA_DECIMAL_TAIL.search(s):
votes.add(',')
elif _DOT_DECIMAL_TAIL.search(s):
votes.add('.')
elif _COMMA_THOUSANDS.match(s) and not s.startswith('0,'):
comma_thousands = True
if len(votes) == 1:
return votes.pop()
if not votes and comma_thousands:
return '.'
return None
def normalize_amount(amount_str: str | None, decimal_separator: str | None = None) -> str:
"""
Normalize monetary string into a standard decimal format compatible with Decimal.
Currency symbols and codes around the number are dropped, and accounting
parentheses read as a negative amount. When decimal_separator is known
for the column ("." or ","), the other separator is treated as grouping;
otherwise the separator is guessed from the cell alone.
Example:
1.442,20 -> 1442.20
1,442.20 -> 1442.20
$40.00 -> 40.00
25,000 (decimal_separator=".") -> 25000
"""
if not amount_str:
return ""
amount_str, negative = _strip_amount_decorations(str(amount_str))
if not amount_str:
return ""
if decimal_separator == ',':
amount_str = amount_str.replace('.', '').replace(',', '.')
elif decimal_separator == '.':
if _ZERO_COMMA_DECIMAL.match(amount_str):
amount_str = amount_str.replace(',', '.')
else:
amount_str = amount_str.replace(',', '')
elif ',' in amount_str and '.' in amount_str:
if amount_str.rfind(',') > amount_str.rfind('.'):
amount_str = amount_str.replace('.', '').replace(',', '.')
else:
amount_str = amount_str.replace(',', '')
elif ',' in amount_str:
amount_str = amount_str.replace(',', '.')
return f"-{amount_str}" if negative else amount_str