Turn bank statement debits and credits into signed amounts

Decide what positive and negative mean

A signed amount is useful only when its convention is explicit. The examples below use a deposit account: a positive amount increases its balance and a negative amount decreases it. This is a statement-data convention, not a universal definition for every bookkeeping debit or credit.

Receiving systems can specify their own import rules. Xero’s CSV instructions, for example, use one amount column with inflows positive and outflows negative. Check the destination instructions before building a mapped copy; do not assume a column named Debit always contains already-negative numbers.

Work through four fictional rows

Start with an opening deposit-account balance of $1,000. A $250 deposit increases it to $1,250. A $40 purchase reduces it to $1,210. A $10 merchant refund increases it to $1,220, and a $5 fee reduces it to $1,215.

The signed values are +250.00, -40.00, +10.00 and -5.00. Their net movement is +215.00. The refund is positive because it returns money to this account; the fact that its description refers to an earlier purchase does not make it another withdrawal.

Avoid applying a minus sign twice

Suppose the source has separate unsigned columns: Money out is 40.00 and Money in is blank. Treating the blank as zero for this confirmed transaction gives 0.00 minus 40.00, or -40.00. Preserve the blank in the source fields even if the calculation uses zero.

If the source amount is already -40.00, negating it again creates +40.00 and reverses the movement. Parentheses, a trailing minus sign or a debit marker may also carry meaning. Establish how the source expresses direction before converting text into a number.

• Keep both source amount columns when the statement provides them.
• Do not sum debit, credit and signed amount together; they are alternative representations.
• Investigate a row with both money-in and money-out values rather than silently choosing one.
• Keep currency and account identity with the working data.

Use the balance difference to locate, not invent, a correction

In the fictional sequence, interpreting the $40 purchase as +40 creates a closing result of $1,295 instead of $1,215. The $80 difference is twice the purchase amount because the row moved from a subtraction to an addition.

That pattern suggests a sign check, but a total alone cannot identify the correct row. Two mistakes can offset each other. Confirm the printed amount and direction before changing a value, and compare every transaction rather than adjusting a convenient number until the balance matches.

Keep a review copy and a destination copy

Fulla’s sample CSV includes signed amounts and original debit and credit fields. Inspect those together with its fictional PDF to see the distinction. When preparing an accounting import, map a separate copy to the columns that system accepts.

Keep the richer review file so a future question can be traced to the original amount and source page. If you extract a real PDF, complete the row review and approve the revision before exporting it.