Convert Bank Statement to Tally

Importing bank statements into Tally

Accountants who work in Tally Prime often receive client bank statements as PDFs. Entering each line as a voucher by hand is the slowest part of month-end. Tally can import data from XML in its own format, and many practices use Excel to prepare data before importing it. This page covers both routes: a Tally XML file that contains the vouchers, and an Excel or CSV file for people who prefer to adjust data in a spreadsheet or use a mapping tool.

The XML we write follows the voucher import structure: for each bank transaction a Receipt voucher (money in) or Payment voucher (money out) with the bank ledger and a counter ledger, an amount, a date and the narration from the statement.

Steps

1. Upload the statement PDF above and review the table. Indian bank statements usually print dates as DD/MM/YYYY or DD-Mon-YYYY, and amounts with lakh-style grouping such as 1,25,000.00. Check that both read correctly in the preview.
2. Choose Tally XML and set the bank ledger name to match the ledger in your company, for example HDFC Bank Current A/c (the default is Bank Account). The counter ledger defaults to Suspense Account so that every voucher can be reviewed and reallocated.
3. Download the file and back up your company data. Then use Tally's import of data (Import, Data) to read the XML into a copy of the company first.
4. Open the Day Book, check the vouchers against the statement, and reallocate the Suspense entries to the right ledgers.

Why a Suspense counter ledger

A bank statement says what happened to the bank, not why. A payment of 18,000.00 to a vendor could be rent, a supplier invoice or a personal withdrawal. Rather than guess a ledger, the XML posts the other side to a single Suspense account. That keeps the bank ledger complete and reconcilable straight away, and you assign the correct ledgers in bulk afterwards. You can change the counter ledger name in the options if your company uses a different clearing ledger.

Numbers, dates and the balance check

• Indian number formats. Amounts such as 1,25,000.00 and 12,34,567.89 are read correctly, along with Dr and Cr suffixes.
• Dates. Day-first dates are detected from the statement. Dates in the XML are written in the YYYYMMDD form Tally expects.
• Reconciliation. Opening balance plus deposits minus withdrawals is compared with the closing balance printed on the statement, and the running balance is checked row by row where it exists.
• Narration. The statement description becomes the voucher narration, trimmed to a sensible length.
• Two-sided entries. Each voucher has one line on the bank ledger and one on the counter ledger, with equal and opposite amounts, so the voucher always balances.

Excel route for Tally users

If your workflow uses an Excel import add-in or you want to classify the transactions before importing, download the Excel or CSV version. It has Date, Description, Debit, Credit and Balance columns. Add a ledger column, filter by narration keywords and then import, or paste into your voucher entry template.

A practical month-end workflow

Convert each client statement as it arrives, keep the reconciliation banner green, and import the XML into the client company. Because every voucher posts to the same counter ledger, you can sort the Day Book by narration, group recurring items such as bank charges, GST payments, salary transfers and loan instalments, and reallocate them in batches. Over a few months the recurring entries become obvious, and a follow-up spreadsheet of narration keywords makes the next month faster.

Keep the downloaded files with your working papers. The file name includes the bank, the account ending and the period, so a folder of monthly files stays tidy for the audit trail.

Compatibility note

Tally releases differ in the import options they offer, and companies differ in their ledger names and masters. We generate the XML as a standard voucher import and recommend testing on a copy of the company before posting to live data. If a version of Tally rejects the file, send us the error message from the contact page and we will look at it. Tally is a trademark of Tally Solutions Pvt. Ltd. and this site is not affiliated with it.

More converters