Bank statements

A member imports a statement into one bank, card or cash account of their books, then matches its lines to entries and reconciles to the statement’s own closing balance. This page is the contract for the classic statement formats; JSON exports from open-banking APIs are on Open-banking exports.

Preview

UPC Accounting is in Preview. United Pet Club is not affiliated with the banks or software named here; see the overview.

What every statement becomes

Whatever the format, a statement is read into the same shape:

Statement field	Meaning
Account	The account number, kept masked to its last four characters
Institution	Public identifiers only: routing / transit number, BIC, sort code
Currency	The statement’s currency
Period	Start and end dates
Opening balance	When the format carries one (OFX does not)
Closing balance	The booked closing balance: the target of the reconciliation
Available balance	Read and shown, not reconciled against

Line field	Meaning
Transaction id	The bank’s own id; the duplicate key when present
Posted date	The booking date; a value date only when it differs
Amount	Signed from the account holder’s side: + money in (deposit, refund, card payment), - money out (withdrawal, purchase)
Counterparty	Payee, payer or merchant, and their account masked to the last four
Memo, reference, bank code	Free text; cheque number, end-to-end id or creditor reference; the bank’s transaction code
Running balance	When the file prints one
Status	Booked, pending or informational
Original amount	The instructed amount and currency when the bank converted

Which lines are imported

Only booked lines in the account’s currency with a non-zero amount are imported. Pending lines, informational lines, zero amounts and lines in another currency are counted and reported as skipped: they never enter the reconciliation balance. Lines dated on or before the bank account’s opening-balance date are skipped too, because the opening balance already includes them.

Duplicates

The duplicate key is the bank’s transaction id. Without one, it is a hash of the date, the amount and the memo (else the counterparty); a key that repeats in one statement is numbered in file order, so two identical purchases on one day stay two lines. An overlapping statement imports only its new lines, and the same file byte for byte is refused. A transaction id that repeats inside one file is numbered the same way and reported.

Detection

The format is read from the file’s content, not its name, with one exception: an OFX file named .qfx or .qbo (or carrying INTU.BID) is reported as Quicken or QuickBooks Web Connect. The member can always pick the format by hand. Order of precedence for a CSV or spreadsheet: a layout the member saved, a bank preset they pick, the account’s default saved layout, then detection.

OFX 1.x / 2.x, QFX, QBO

Versions 1.0.2, 1.0.3 and 1.6 (SGML, with the OFXHEADER:100 block) and 2.0.x to 2.3 (XML, with <?OFX … ?>). QFX and QBO are the same content. The SGML body is decoded in the header’s CHARSET / ENCODING; unknown empty tags are handled.

Read as	From
Account	BANKACCTFROM/ACCTID, or CCACCTFROM/ACCTID (a CCSTMTRS is a credit card)
Account type	ACCTTYPE
Institution	BANKID, else SONRS/FI/FID, else INTU.BID
Currency	CURDEF
Period	BANKTRANLIST/DTSTART, DTEND
Closing balance, date	LEDGERBAL/BALAMT, DTASOF
Available balance	AVAILBAL/BALAMT
Transaction id	FITID
Posted date / value date	DTPOSTED (the calendar date as written) / DTUSER, else DTAVAIL
Amount	TRNAMT, already signed from the holder’s side
Line currency	CURRENCY/CURSYM, else CURDEF
Counterparty	NAME, PAYEE/NAME, EXTDNAME; account from BANKACCTTO / CCACCTTO
Memo / reference / code	MEMO / CHECKNUM or REFNUM / TRNTYPE

• CORRECTACTION DELETE or REPLACE of a CORRECTFITID in the same file is applied; one pointing at an earlier file is reported, not applied.
• A STATUS error with no statement is shown with the bank’s own message.
• OFX carries no opening balance.

OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
<BANKMSGSRSV1><STMTTRNRS><TRNUID>1<STATUS><CODE>0<SEVERITY>INFO</STATUS>
<STMTRS><CURDEF>CAD
<BANKACCTFROM><BANKID>000000000<ACCTID>000123451234<ACCTTYPE>CHECKING</BANKACCTFROM>
<BANKTRANLIST><DTSTART>20260901<DTEND>20260930
<STMTTRN><TRNTYPE>DEBIT<DTPOSTED>20260903<TRNAMT>-45.67<FITID>202609030001<NAME>KIBBLE WHOLESALE<MEMO>Invoice 1042</STMTTRN>
<STMTTRN><TRNTYPE>CREDIT<DTPOSTED>20260905<TRNAMT>1200.00<FITID>202609050001<NAME>JANE DOE<MEMO>Grooming package</STMTTRN>
</BANKTRANLIST>
<LEDGERBAL><BALAMT>5154.33<DTASOF>20260930</LEDGERBAL>
</STMTRS></STMTTRNRS></BANKMSGSRSV1></OFX>

ISO 20022 camt.052 / 053 / 054

BkToCstmrAcctRpt (camt.052, intraday report), BkToCstmrStmt (camt.053, statement) and BkToCstmrDbtCdtNtfctn (camt.054, notification). Versions .001.02 to .001.08 are tested; a later version of the same shape is read with a note. Version differences handled: Sts as text (up to .05) or Sts/Cd (.06 on); BIC or BICFI; party names under Dbtr / Cdtr directly or under Pty (.08); dates as Dt or DtTm.

Read as	From
Account	Acct/Id/IBAN, or Acct/Id/Othr/Id
Currency, name, type	Acct/Ccy, Nm, Tp/Cd
Institution	Acct/Svcr/FinInstnId/BICFI or BIC
Statement id, period	Id, FrToDt
Opening balance	Bal of type OPBD (else PRCD)
Closing balance	CLBD (camt.052 without one: ITBD)
Available balance	CLAV (else ITAV)
Transaction id	Ntry/AcctSvcrRef (else the single TxDtls/Refs/AcctSvcrRef)
Posted / value date	BookgDt (pending: ValDt) / ValDt
Amount	Amt, negative when CdtDbtInd is DBIT
Counterparty and account	Credit: UltmtDbtr / Dbtr, DbtrAcct; debit: UltmtCdtr / Cdtr, CdtrAcct
Memo	RmtInf/Ustrd (joined), else Strd/AddtlRmtInf, AddtlTxInf, AddtlNtryInf
Reference	EndToEndId (unless NOTPROVIDED), else CdtrRefInf/Ref, ChqNb, InstrId, TxId, NtryRef
Bank code	Domn-Fmly-SubFmly (for example PMNT-ICDT-ESCT), else Prtry/Cd
Status	BOOK booked; PDNG / FUTR pending; INFO informational
Original amount	AmtDtls/InstdAmt in another currency
Split parts	Each TxDtls of a batch entry

TxsSummry/TtlNtries/NbOfNtries is checked against the entries. A camt.054 made only of INFO entries imports nothing; its lines are reported as skipped.

<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.08">
  <BkToCstmrStmt>
    <GrpHdr><MsgId>MAPLE-2026-09</MsgId><CreDtTm>2026-10-01T06:00:00</CreDtTm></GrpHdr>
    <Stmt>
      <Id>MAPLE-2026-09-001</Id>
      <FrToDt><FrDtTm>2026-09-01T00:00:00</FrDtTm><ToDtTm>2026-09-30T23:59:59</ToDtTm></FrToDt>
      <Acct><Id><IBAN>DE00000000000001234567</IBAN></Id><Ccy>EUR</Ccy></Acct>
      <Bal>
        <Tp><CdOrPrtry><Cd>OPBD</Cd></CdOrPrtry></Tp>
        <Amt Ccy="EUR">4000.00</Amt><CdtDbtInd>CRDT</CdtDbtInd><Dt><Dt>2026-08-31</Dt></Dt>
      </Bal>
      <Bal>
        <Tp><CdOrPrtry><Cd>CLBD</Cd></CdOrPrtry></Tp>
        <Amt Ccy="EUR">3954.33</Amt><CdtDbtInd>CRDT</CdtDbtInd><Dt><Dt>2026-09-30</Dt></Dt>
      </Bal>
      <Ntry>
        <Amt Ccy="EUR">45.67</Amt><CdtDbtInd>DBIT</CdtDbtInd><Sts><Cd>BOOK</Cd></Sts>
        <BookgDt><Dt>2026-09-03</Dt></BookgDt><ValDt><Dt>2026-09-03</Dt></ValDt>
        <AcctSvcrRef>2026090300001</AcctSvcrRef>
        <BkTxCd><Domn><Cd>PMNT</Cd><Fmly><Cd>ICDT</Cd><SubFmlyCd>ESCT</SubFmlyCd></Fmly></Domn></BkTxCd>
        <NtryDtls><TxDtls>
          <Refs><EndToEndId>INV-1042</EndToEndId></Refs>
          <RltdPties><Cdtr><Pty><Nm>Kibble Wholesale GmbH</Nm></Pty></Cdtr></RltdPties>
          <RmtInf><Ustrd>Invoice 1042</Ustrd></RmtInf>
        </TxDtls></NtryDtls>
      </Ntry>
    </Stmt>
  </BkToCstmrStmt>
</Document>

SWIFT MT940 / MT942

Read with or without the {1:}{2:}{4:} envelope, several messages per file, separated by - or $ lines. An MT942 is recognized by :34F: / :13D: and no :60:. Amounts use a decimal comma.

Read as	From
Account, institution	:25: (a BLZ/account pair is split; a trailing currency is stripped)
Statement id	:20: plus :28C:
Opening / closing balance	:60F: or :60M: / :62F: or :62M:
Available balance	:64:
Currency	The balances, else :34F:, else the :25: suffix
Posted / value date	:61: entry date MMDD (year from the value date, across year end) / value date YYMMDD
Amount	Mark C / D, RC (negative), RD (positive), EC / ED (pending); the funds code is skipped
Bank code	The 4-character type (NTRF, NMSC…)
Reference	The customer reference (unless NONREF)
Transaction id	The bank reference after // (unless NONREF)
Counterparty, account, memo	:86: German ?NN subfields (?32+?33 name, ?31 account, ?20-?29 and ?60-?63 purpose, SEPA SVWZ+, EREF+); Dutch / Belgian /NAME/, /CNTP/, /REMI/, /EREF/; otherwise the free text

:20:MAPLE20260930
:25:12345678/0001234567
:28C:00042/001
:60F:C260831EUR4000,00
:61:2609030903D45,67NTRFNONREF//BANKREF0001
:86:177?00SEPA-UEBERWEISUNG?20SVWZ+Invoice 1042?32Kibble Wholesale
:61:2609050905C1200,00NTRFNONREF//BANKREF0002
:86:166?00SEPA-GUTSCHRIFT?20SVWZ+Grooming package?32Jane Doe
:62F:C260930EUR5154,33
-

BAI2

BAI cash-management balance reporting, version 2: records 01, 02, 03, 16, 88, 49, 98 and 99. Amounts carry implied decimals from the currency’s ISO minor units (120000 USD = 1,200.00).

Read as	From
Account, currency	03 account number and currency (else the 02 currency, else USD)
Opening / closing / available	03 summary type codes 010 / 015 / 045
Statement id, institution	01 file id, 02 originator
Amount	16 amount; type codes 100-399 are money in, 400-699 money out
Posted / value date	The 02 as-of date / the value date of funds type V
Transaction id / reference	Bank reference / customer reference
Memo, counterparty	The text, with 88 continuations joined; ORIG CO NAME: in ACH text
Bank code	The type code

Funds types 0, 1, 2, Z, V, S and D are parsed. A type code outside 100-699 (a bank’s custom 9xx) has no standard sign: the line is listed as informational with an error note and is not imported. The 49 account control total is checked.

01,MAPLEBANK,MAPLEGROOM,260930,2300,1,,,2/
02,MAPLEGROOM,021000021,1,260930,,USD,2/
03,0001234567,USD,010,400000,,,015,515433,,/
16,165,120000,0,BANKREF0002,CUSTREF2,ACH CREDIT ORIG CO NAME:JANE DOE
16,475,4567,0,BANKREF0001,1042,CHECK 1042
49,1040000,4/
98,1040000,1,6/
99,1040000,1,8/

Bank CSV and Excel downloads

A CSV, .xlsx or .xls file is read through a column layout: which column holds the date, the amount (or a money-out and a money-in column), the description and so on, plus the date order, the decimal mark, whether to flip signs (card downloads that show purchases as positive), a fixed currency, and whether balances are amounts owed. A workbook is read from its first sheet unless the member picks another.

Built-in bank layouts

These presets follow each bank’s online-banking “download transactions” CSV as the banks’ help pages and common converters describe it. Banks publish no formal specification and change exports without notice, so detection scores each preset against the file and falls back to the generic reader below.

Preset	Header row	Columns	Notes
RBC	Yes	Account Type, Account Number, Transaction Date, Cheque Number, Description 1, Description 2, CAD$, USD$	M/D/YYYY; signed; USD$ rows are in USD; one statement per account number
TD	No	date, description, withdrawal, deposit, balance	MM/DD/YYYY
BMO	Yes, after a “Following data is valid as of” line	First Bank Card, Transaction Type, Date Posted, Transaction Amount, Description	yyyyMMdd; card number masked
BMO Mastercard	Yes	Item #, Card #, Transaction Date, Posting Date, Transaction Amount, Description	Purchases positive, so signs are flipped
Scotiabank	Yes	Filter, Date, Description, Sub-description, Status, Type of Transaction, Amount, Balance	Debit / Credit type signs the amount; Pending status
Scotiabank (classic)	No	date, signed amount, ”-”, type, description
CIBC	No	ISO date, description, debit, credit, [masked card number]
Desjardins	No	caisse, transit-folio, account code, yyyy/MM/dd, sequence, description, cheque, withdrawal, deposit, interest, capital, advance, repayment, balance	CAD
Chase checking	Yes	Details, Posting Date, Description, Amount, Type, Balance, Check or Slip #	Signed
Chase card	Yes	Transaction Date, Post Date, Description, Category, Type, Amount, Memo	Purchases already negative
Bank of America	Yes, after a summary block	Date, Description, Amount, Running Bal.	”Beginning / Ending balance as of” become the opening / closing balance
Bank of America card	Yes	Posted Date, Reference Number, Payee, Address, Amount	Reference Number is the transaction id
Wells Fargo	No	date, signed amount, ”*”, check number, description

The generic reader

A file no preset explains is read by header names, in English, French, German and Spanish:

Field	Header names recognized (English)
Date	date, transaction date, posting date, posted date, date posted, booking date, …
Value date	value date
Description	description, transaction description, details, memo, narrative, particulars
More description	description 2, sub-description, notes, additional information
Counterparty	payee, payee name, merchant, merchant name, name, counterparty, beneficiary
Amount	amount, transaction amount, value, net amount, amount (cad), amount (usd)
Money out / money in	debit, withdrawal(s), money out, paid out / credit, deposit(s), money in, paid in
Debit-credit type	debit/credit, dr/cr, type of transaction, transaction type
Balance	balance, running balance, account balance
Reference	reference, ref, check number, cheque number, check #
Transaction id	transaction id, transaction reference, bank reference, fitid, id
Currency	currency, ccy
Account	account number, account, card number
Status	status, state

• Without a header row the columns are found by their values: the dates, one signed amount column or a debit / credit pair that never fill on the same row, the balance, and the longest text column.
• A debit-credit type column (DEBIT / CREDIT, DR / CR, D / C, S / H) signs the amounts only when every value is such a word and the amounts are not already signed.
• Opening and closing balances come from the running balance of the oldest and newest booked lines, in either sort order. A status containing “pending” or “authorized” (or “en attente”) marks a pending row, which stays out of the balance; any other status is booked.

If your software writes a bank CSV for UPC Accounting, this layout reads with no mapping at all:

Date,Description,Payee,Amount,Balance,Transaction ID,Currency,Status
2026-09-03,Invoice 1042,Kibble Wholesale,-45.67,3954.33,TX-0001,CAD,Booked
2026-09-05,Grooming package,Jane Doe,1200.00,5154.33,TX-0002,CAD,Booked

Use ISO dates, a signed amount (money in positive), a stable transaction id, and UTF-8.

The simple CSV fallback

A CSV no layout can place is read as a simple statement: a header row with a date column (date, posted, datum, fecha, buchungstag), an amount column (amount, amt, montant, betrag, importe, bedrag) or both a debit and a credit column, and optionally a description and a balance. The member can also choose this reading by hand: Simple CSV (date, description, amount).

Refused

File	What happens
An accounting-software export, statutory file or UPC template	Refused, with a pointer to the import from other software
A payroll register	Refused: payroll files do not import as statements
OFX investment statements (INVSTMTRS)	Reported, not read
QIF	Not read
Excel 5.0 / 95 (.xls BIFF5) and password-protected workbooks	Refused
camt messages other than 052, 053 and 054	Not read
Any XML with a <!DOCTYPE>	Refused
A file with several accounts, none matching the bank account	Refused, naming the accounts it holds
The same file imported before	Refused
Over 10 MB or 50,000 lines	Refused