What Goes Wrong When You Convert an Indian Bank Statement to Excel

A client emails a six-page PDF on the 28th and you need it in the books by the 5th. Converting it looks like two minutes of work. It usually takes the evening.

The PDF isn’t the real problem. Indian bank statements have a few habits that most converters weren’t built for, and each one quietly damages the output.

The file won’t open

Banks lock emailed statements in different ways, and the first few minutes often go on guessing passwords.

HDFC uses the Customer ID and nothing else: eight to ten digits, no date of birth. Many other large banks build the password from the date of birth, sometimes with digits from the registered mobile number, which is exactly why HDFC files trip people up. HDFC credit card statements have their own rule again, usually the first four letters of the cardholder’s name in capitals followed by the last four digits of the card.

HDFC is also a good example of the next trap. NetBanking will let you download a statement in several formats, Excel included, but a statement sent by email only ever comes as a PDF, and that’s the version clients forward. For HDFC files, a tool that does HDFC bank statement PDF to CSV in one step handles the password and the layout before you see the output.

The description is one long string

Open an Indian statement and the narration column looks like this:

UPI/DR/5120938XXXXX/SURESH P/ICIC/98XXXXXX21/Payment

That one cell holds the direction, a reference number, the counterparty’s name and bank, a phone number or UPI handle, and a remark. The ledger needs the name, which sits in the fourth field. A basic converter drops the whole string into one cell and leaves the untangling to you, three hundred rows at a time.

SBI’s Relationship Summary statements add another wrinkle. The bank cuts every narration off at about fifty characters, so “Payment” shows up as “Payme” on every row. That truncation happens before the file reaches you, so no converter can put the missing text back.

What you want is the full narration kept in one column and the party name pulled out into another. The narration is your audit trail. The name is what goes in the books.

Dates that aren’t really dates

Indian statements write dates day first, so 04-07-25 is the fourth of July. Converters built around American statements read it as April 7th and reverse your year without any warning.

Even when the order comes out right, the dates often land in Excel as text. They look fine until you sort the column and the 1st of every month bunches together at the top, which is the quickest test there is. SUMIFS on a date range returns zero for the same reason.

This is where the choice of tool matters most. A decent bank statement converter to Excel should give you real date cells in the order the bank used, so sorting and pivot tables work the first time.

Rows that aren’t transactions

When a description doesn’t fit on one line, it wraps onto the next. A converter that reads line by line turns that leftover text into a new row with no amount, and you end up with blank rows scattered through the sheet and a balance that won’t tie.

Some rows look like data and aren’t. There’s usually an opening balance line with no debit or credit, and the column header repeats at the top of every page. SBI’s Relationship Summary puts two full pages of account summaries ahead of the first transaction, so a tool that grabs the first table it finds pulls the wrong one.

Scroll the output once before you use it, looking for any row that has a description and no amount.

Scans and passbook photos

Some clients don’t use netbanking and send a phone photo of their passbook instead. That goes through OCR, which misreads whatever it can’t see clearly. A smudged 8 turns into a 3, a faint decimal point disappears. Ask for the netbanking PDF whenever the client can get one.

Getting it into Tally

Most practices convert to Excel and import from there. It works, but Tally has to know the voucher type for every row, and a spreadsheet can’t tell a Payment from a Contra on its own. Money going to a supplier is a Payment. Money going to another of the client’s own accounts is a Contra. A signed amount column doesn’t show that difference, so someone ends up classifying rows by hand in the mapping screen, every month.

The other route skips the spreadsheet. A PDF to Tally XML converter builds the vouchers directly, working out the type from the direction of each transaction and filling in the party name from the narration. Import the XML through Tally’s import menu and the entries arrive classified. The menu names differ between TallyPrime releases and ERP 9, so check yours.

Check the balance before anything goes in

Take the opening balance, add the credits, subtract the debits, and compare the result with the closing balance printed on the statement. If they don’t match, a row dropped out or split during conversion, usually one of the wrapped ones. It takes a minute, and it catches the problem before it turns into a reconciliation that won’t close three weeks later.

Watch the second import

The balance check works on the converted file. It can’t tell you that the same file already went into Tally last week.

Duplicates usually come from overlapping statements. A client sends March, then a few weeks later sends a statement running from mid-March to mid-April, because that’s what the bank’s date picker produced. Import both and the second half of March is in the books twice. Tally usually won’t stop you.

Before importing, look at the last transaction date already sitting in the bank ledger and make sure the new file starts after it. Some tools check incoming rows against what’s already in Tally before sending anything. If yours doesn’t, that check is on you.

Take a backup of the company before every import, too. Restoring a backup takes a minute. Unpicking a bad import by hand can take the rest of the day.

What to ask the client for

Most of the problems in this article start before the file reaches you, so the cheapest fix is a better request.

Ask for the netbanking PDF, not a photo. If the bank lets them download the whole period as one file, even better. Separate monthly files are fine as long as the dates don’t overlap.

Ask for every account. Clients forget the second current account, or the old savings account they barely use anymore. Transfers between their own accounts are Contra entries, and you can only post those properly when you have both sides.

Ask for the password in a separate message, and tell them what it probably is. Plenty of HDFC customers don’t realise their Customer ID opens the statement, and a two-line note saves a day of back and forth.

Keep the original

Once the entries are in Tally it’s tempting to delete the spreadsheet and move on. Keep it, and keep the PDF.

The clean party name is what you need for posting. The raw narration, with its reference number, is what you need when a question comes up months later. A vendor says they were never paid, or an auditor asks where a large credit came from, and the reference number is how you trace the payment back through the bank. A tidied-up name like “Suresh P” won’t do that on its own.

Save the PDF next to the converted file, filed by client and month. When the question comes, finding both should take seconds.