Debit and credit columns to one signed amount: a bank CSV method

For a bank statement using positive debit and credit columns, calculate signed amount as credit minus debit: money received is positive and money paid is negative. Confirm this convention against the statement itself before applying it. An accounting ledger may use different conventions.

Confirm the source convention before assigning signs

1. Keep debit and credit as separate source fields. Check that amounts are numbers, identify the currency and distinguish a blank field from a failed extraction.

2. Choose the output convention explicitly. For confirmed positive source values, compute credit minus debit in a new column; never overwrite the originals.

3. Flag rows where both columns contain a nonzero amount, signs already appear or the source is ambiguous. Review these rows rather than applying the formula blindly.

Fictional example

Fictional rows contain debit 75.20 with no credit, then credit 120.00 with no debit. Signed amounts are −75.20 and +120.00, for net +44.80. Starting from 500.00, the resulting balance is 544.80. If the debit field already contains −75.20, subtracting it would incorrectly create a positive payment: first identify whether it is a signed source column.

Make the conversion conditional on the source row

Separate a confirmed unused column from an unreadable amount. When the PDF clearly has a debit of 75.20 and no credit, you may use zero for the credit side of the derived calculation while leaving the raw credit field blank. If the extraction simply missed a printed credit, that row is not ready to calculate. An empty result in the working column is preferable to a confident but invented number.

If both columns contain nonzero values, keep the row in an exception list. Subtracting them immediately may conceal a shifted column or two transactions merged into one. Likewise, do not take the absolute value of every source amount: that would remove evidence that the source already uses signs. Decide once for a confirmed layout, then review any row that violates that layout.

Prove the direction before importing the CSV

Write the target rule beside the export, for example “receipts positive; payments negative; account currency EUR”. Check the import preview with one known payment and one known receipt. If the destination expects the opposite convention, change the derived output rule explicitly; do not change the preserved source columns. The Banca d’Italia glossary describes signed amounts, without specifying your destination’s import settings.

Use separate totals to detect mistakes that the net hides. A fictional debit 75.20 followed by its credit reversal 75.20 has net zero but two movements, debit total 75.20 and credit total 75.20. Keeping only a zero row would destroy that history. Acceptance therefore requires correct row directions and separate totals as well as the final balance.

Decide which rows are ready for subtraction

Apply this decision table to a layout that normally puts positive EUR amounts on one side only. “Blank” describes the raw cell; it says nothing by itself about whether the other side is known to be unused.

Raw debit	Raw credit	Evidence checked in the PDF	Derived result, EUR
75.20	Blank	Payment; credit side visibly unused	−75.20
Blank	120.00	Receipt; debit side visibly unused	+120.00
75.20	75.20	Two amounts on a row expected to have one	Not calculated; inspect alignment
−75.20	Blank	A sign already exists	Not calculated under this positive-column rule
Blank	Blank	The printed amount is unreadable	Not calculated; missing evidence
0.00	Blank	A zero is explicitly printed on the debit side	0.00 after confirming the row type

The zero row does not automatically describe a cancelled payment, and the two-amount row does not automatically describe an error made by the bank. First determine what the document actually says. A genuine different layout needs its own documented mapping.

Implement the rule without turning missing data into zero

For approved rows, create two reviewed numeric columns: D for debit and E for credit. Enter zero only for the opposite side confirmed unused in the PDF. In F, use =E2-D2 for row 2 and copy it only to rows approved for this convention. Keep the raw columns and a review-status column alongside them. A blocked row has no numeric result; do not put zero there just to keep a formula running.

For the first two fictional rows, D totals 75.20, E totals 120.00 and F totals +44.80 EUR. The identity sum(F) = sum(E) - sum(D) checks the transformation, while the PDF comparison checks the inputs. If the identity holds but a known payment appears positive, return to the chosen direction or source mapping. If it fails, inspect formula ranges and rows skipped in one total.

Reopen the exported CSV using an import preview and check these two amounts, currency, row count and exceptional rows. A two-row export with +44.80 is only the reviewed subset if other rows remain unresolved; do not label it a complete statement. Keep exceptions in the review workbook and release a complete export only when its coverage is established.

Checks and limits

Check separate debit and credit totals as well as the net. Test an incoming transfer, outgoing payment and reversal. Do not decide the direction from words such as ‘transfer’ alone. Preserve unusual source rows for review. The Banca d’Italia glossary documents signed transaction amounts; it does not impose one export convention on every bank.

Keep the original file unchanged and record any unresolved item before using the table.

Related checks

Check that fragments have not created extra transactions before assigning signed amounts. Bank statement descriptions split across rows: join them safely · Check a bank statement balance without hiding extraction errors.

BankStatementScan is in preparation. The site offers an access request and a fictional example; it does not currently convert your bank files. See the planned service.

Sources checked on 16 October 2026

• Banca d’Italia — Estratto conto.