Broker Statement Import & Reconciliation

Importing broker statements from CSV or Excel, reviewing staged rows and reconciling them with the ledger.

A profile describes how one broker's statement is read. You build it once, from a real statement, and reuse it every month. Profiles are versioned: every batch records the profile version and a JSON copy of it, so an old import can always be explained.

What is supported

Limits (system parameters): technovision_investment_import.max_file_mb (default 25) and technovision_investment_import.max_rows (default 250,000).

No named broker's layout is claimed. The profiles shipped are marked SAMPLE and match only the fictional sample files. A profile for a real broker is "verified" only after importing that broker's real statements and reconciling them.

Statement kinds

• Transactions — trades, deposits, withdrawals, fees, dividends. These become ledger events.
• Position snapshot — holdings at a date. Evidence for reconciliation.
• Cash snapshot — balances at a date. Evidence for reconciliation.

A snapshot never creates purchases or deposits. The only exception is an explicit opening balance (see below).

Building a profile (the import wizard)

• Investments → Statements → Import a statement. Choose portfolio, broker account, file and statement kind.
• Analyse file. The wizard reports encoding, delimiter or sheet, the header row (detected by matching English/Arabic header words; set it explicitly if wrong) and shows the first rows verbatim.
• Columns. Suggested from the headers; correct them. A column number (1-based) overrides the header text.
• Transaction types. Every distinct value in the type column is listed. Map each to buy, sell, deposit, withdrawal, fee, dividend — or ignore (e.g. an "Opening balance" line). Transfers between accounts are recorded with the Transfer wizard, not imported.
• Dates. Choose the format. The wizard suggests formats with evidence (e.g. "25/03/2026 can only be day-first") and says when a column is ambiguous. It never picks one silently: 03/04/2026 is read only under an explicit format.
• Numbers. Decimal and thousands separators, whether (1,000) is negative. Arabic-Indic digits and the Arabic decimal/thousands separators are always understood. A number that does not fit the profile (e.g. 1.234,50 under a point-decimal profile) is an error, not a guess.
• Signs. Type gives direction (default): magnitudes as printed, the type decides the direction. Signs are meaningful: money leaving must be negative and money arriving positive, and a contradiction is an error.
• Totals row. If the statement prints totals, give the label (e.g. Total or الإجمالي). The totals are compared with the rows and a mismatch blocks the commit.
• Stage rows for review. Nothing is committed yet.

Reviewing a batch

The batch shows every row original next to normalized, with its issues.

Duplicate control

A reviewer (Reconciliation Reviewer role) decides each candidate — import it (separate event) or skip it (already in ledger) — with a reason; the decision is audited.

Instruments

Unknown symbols appear as errors. Resolve instruments lets an operator create the instrument, or map a broker's own code to an existing instrument (stored as that broker's identifier). Symbols are resolved as of each row's date, so a reassigned ticker never lands on the wrong company.

Committing

Commit is refused while there are batch-level errors or undecided candidates. With invalid rows it is refused unless Allow partial commit is ticked; the invalid rows then stay staged — never marked imported.

Up to 2,000 rows commit immediately; larger files are processed by the background job in atomic chunks of 500. If a worker stops, committed chunks stay committed and the rest resume; a statement row can never produce two events (database-enforced).

Download results exports every row with its outcome, issues and decision (formula-safe CSV).

Opening balances (migration)

Import the broker's position and cash snapshots at your go-live date with "This is the opening balance" ticked:

• positions become opening-position events (the snapshot must give total cost), cash becomes an opening-cash event, dated on the statement date;
• the statement date becomes the account's migration cut-off: later statements' activity dated on or before it is refused as "already part of the opening balance";
• only one opening positions import and one opening cash import per account.

Agree the cut-off with your accountant. If the general ledger already holds the investments, keep the accounting policy's opening mode on already in the ledger so nothing is posted twice.

Reconciliation from a batch

Reconcile on a transaction batch compares it with the ledger over the batch's own period; on a snapshot batch it compares positions or cash at the snapshot date. See the user manual, "Reconciliation".

Reconciliation

Reconciliation → Reconciliation runs (or Reconcile on a batch):

Each discrepancy has a category (missing, in ledger but not in evidence, duplicate candidate, amount difference, mapping problem, settlement timing, policy difference, unresolved), expected/actual/difference, owner, age, suggested action and drill-down to the statement row, the event and the journal entry. Ambiguous matches are kept ambiguous with all candidates.

States: new → investigating → resolved or accepted difference (both need a reason) → reopened if needed. A decision never changes a ledger event. To correct the ledger, Propose adjustment creates a draft event with evidence that an administrator must confirm.