Import Bank Statement entries in Tally with Excel
Manually entering bank statement data into Tally Prime can be time-consuming and error-prone. However, with the tally built in import of excel you can achieve this with fever clicks. Based on the templates provided by the tally, I have created small excel tool that will create excel file for import. With this custom Excel tool, you can now effortlessly import bank statement entries in Tally, leveraging its powerful data import feature available from Release 4.0.
TallyPrime supports importing data from Excel files through a simple mapping process, aligning your bank statement details with TallyPrime’s fields using predefined templates. I have just simplified the mapping with excel VBA.
The excel tool does not create any xml or html. It just converts your simple bank statement entries in to required data format as per Tally’s requirement.
Please note without my excel tool also you can map bank statement with the inbuilt templates. But my excel tool streamlines the process for you.
Step by step guide to import excel bank statement in to tally:
-
Download the Free Tool
- Download the Excel tool for free.
-
Paste Your Bank Statement Data
- Open the tool and locate the sheet named “Separate Dr Cr Column”.
- Enter your bank ledger name as per tally company in “blue box” against “Your Bank Name in tally →” cell.
- Copy your bank statement details and paste (Paste special) them into this sheet column wise.
- Mandatory fields to include: Date [DD/MM/YYY], Statement Narration, Withdrawal (Dr), and Deposit (Cr).
- Optional fields: Sr, Balance and Transaction ID (you can leave these blank if not available).
- Do not enter anything in any other columns.
-
Assign Tally Ledger Names
- In the Ledger Name column (highlighted in yellow, H Column), enter the exact ledger names as they appear in your Tally company for each row.
- If you don’t have a ledger name or prefer to categorize it under suspense later, skip this step for those rows. If you skip it will auto assign “Suspense A/c” for all blank ledgers. Create “Suspense A/c” ledger in your company as it is.
- Note: Assigning ledger names here eliminates the need to manually select them in TallyPrime after importing.
-
Fill Voucher Details
- Click the “Fill Vch Details” button in the tool.
- This will automatically generate the necessary columns as per TallyPrime’s import template and validate your data for primary errors like each row must have either Dr or Cr etc.
- This is important step, as it convert your bank statements in tally’s template format.
-
Create the Import File
- Press the “Create Tally Data “ button.
- The tool will generate a new Excel file named “Bank Statement Import.xlsx” and save it in the same folder where the tool is located.
- Just copy the excel file path.
-
Import into TallyPrime
- Open TallyPrime (Release 4.0 or later) and select the company you want to import data into.
- Navigate to Import (Alt+O) > Transactions.
- Choose File Format: Excel (Spreadsheet).
- Provide the file path (copied earlier) of the “Bank Statement Import.xlsx” Excel file.
- Select file to import “Bank Statement Import.xlsx”
- Specify the worksheet name as “Bank Statement Import”.
- Mapping Template: Use the Default Template for column mapping.
- Preview the data to ensure accuracy.
- (Optional) Take a backup of your TallyPrime data before proceeding.
- Press Enter to complete the import.
Download the free excel to tally import tool here: ⏬
