How to import a bank statement into rent-tracking software

Reconciling rent payments by hand — opening a bank statement, scrolling for deposits, copying amounts into a spreadsheet — is the single largest chore in tracking rent. It's also the easiest one to automate, because every Indian bank lets you download a statement as CSV or Excel.

The friction isn't the download. It's that no two banks format the file the same way. A serious importer has to handle that automatically. Here's what's involved.

The exports each Indian bank produces

Rough survey of what comes out of the major retail banks when you click "Download statement → CSV" or "Download → Excel":

• HDFC. CSV with two amount columns — Withdrawals and Deposits. Date is DD-MM-YYYY. Free-text narration column. Six metadata rows at the top before the actual table.
• ICICI. CSV, also two-column for credit/debit, but different headers (Withdrawal Amount, Deposit Amount), different date format, different separator on some statement types.
• SBI. Often gives you XLS (older Excel). Single signed Amount column where deposits are positive and debits negative. Date column is sometimes YYYY-MM-DD, sometimes DD-MMM-YYYY.
• Axis, Kotak, IDFC First. All slight variations of the above. Some use ; as the delimiter. Some quote the narration field, some don't.
• UPI app exports (PhonePe, Paytm). CSV, but the preamble can have ten lines of marketing copy before the header row.

A landlord who banks at three institutions has three different shapes of file to deal with. A naive importer that hard-codes one bank's columns fails on the other two.

What a good importer has to do

The pipeline, top to bottom:

1. Detect the delimiter

Read the first ten lines, count occurrences of ,, ;, tab, and |, pick whichever produces the most consistent column count. Sounds trivial; saves a class of bugs where a semicolon-delimited file gets parsed as one giant column.

2. Skip preamble rows

Most bank exports start with account holder, branch, statement period, sometimes a header logo as text. Walk down from row 0, skip rows that don't look like a header (mostly numeric, or no recognisable column-name keywords like date, amount, credit, narration), and start parsing from there.

3. Identify column roles

For each column header, match it (case-insensitive) against a list of known synonyms:

• Date ← "date", "txn date", "value date", "posting date"
• Credit ← "credit", "deposits", "deposit", "cr", "cr amount"
• Debit ← "debit", "withdrawals", "withdrawal", "dr", "dr amount"
• Amount (signed) ← "amount", "txn amount" (only if no separate credit/debit columns)
• Description ← "narration", "remarks", "particulars", "details"
• Reference ← "ref", "ref no", "utr", "tran id", "chq no"

If a header doesn't match anything, fall back to asking the user.

4. Detect the date format

Take the first ten non-empty values in the date column and try parsing them with each known format (dd-MM-yyyy, dd/MM/yyyy, yyyy-MM-dd, dd-MMM-yyyy). Pick whichever parses every value into a plausible year (between 2000 and the current year).

5. Convert into a single canonical shape

Whatever the file looked like, normalise to a list of records with { date, amount, description, referenceId }. Positive amount means money in (a credit). From here, downstream code doesn't care what bank the file came from.

6. Match credits to tenants

For every credit, search the description for tenant names, phone numbers, account numbers, or UTR fragments you've previously associated with that tenant. Show the user a confidence score per match (high/medium/low) and let them confirm or override.

7. Save the mapping

Hash the column headers (so the same bank's statement always produces the same fingerprint) and remember the user's column choices. Next time they import the same bank's statement, skip the mapping step entirely.

What we built

Nexus Landlord ships exactly this pipeline. The first time you upload an HDFC statement, it auto-detects everything; if a guess is wrong (say, your bank renamed a column), you correct it in a small mapping screen and tick "save as profile". From the second statement onwards, that bank is one click — drop the file, confirm the matches, click "import."

No subscription, no cloud upload — the parsing happens on your laptop. The only thing that ever leaves your machine is the licence-key check at first launch.

If you're rolling your own

For a spreadsheet workflow without dedicated software:

• Use one column called tenant_id and tag every credit row with the tenant it belongs to. Don't rely on a free-text "Notes" field.
• Use a pivot table grouped by tenant + month for "who owes me what".
• For HDFC specifically: filter the Deposits column to non-empty, filter Narration by tenant name. That's roughly 80% of the automation a dedicated app gives you.

The math on whether the manual version is worth it is in the Excel vs. rent software piece. Short answer: under five units yes, over ten units almost never.