If you have recently set up new business operations, you might be looking for solutions that can help you to seamlessly manage the different types of functions such as accounts, finance, procurement etc.
While excel has been a professional standard across companies for decades, moving on to newer technologies such as Tally.ERP 9 for managing large amounts of data can help you to save a lot of time and effort. Tally also automates repetitive tasks and prevents manual errors. Thus, the benefits of Tally are many.
But, just don’t take our word for it, here is a comprehensive read on – Excel or Tally, which will help you to make an informed decision. This blog also provides detail on excel to tally import. So, without further ado, let’s get started!
A] Basic Difference Between Tally and Excel
While Tally is an exclusive business accounting and inventory management software, Excel features graphing tools, calculation, data representation, pivot tables, analysis function and a macro programming language called Visual Basic for Applications.
Each has a specific role to play. However, as automated software, Tally provides data integrity, which may not be feasible if you are managing multiple spreadsheets. Also, learning different features in excel can be a challenge. As such, when it comes to excel vs accounting software, Tally.ERP 9 (now TallyPrime) is a better option that can assist you as your business grows.
B] Excel and Tally – Which is Better?
- With Tally, you don’t need to focus on basic accounting functions as Tally already covers them. It provides multilingual operations and offers government supported formats for seamless management.
- Tally helps to automatically update your cash flow, balance sheet, profit and loss account etc.
- It can help you with stock maintenance, book-keeping, ratio analysis, and bank reconciliation.
- It can help you with voucher & invoice preparation, cash flow presentation, receipt & expenditure account preparation, and profit & loss balance sheet preparation.
- Tally is also easy to use & manage and offers a range of online functions and processes for businesses.
But, when it comes to preparing Financial Statements (FS), Excel is an ideal choice as you can customise the format to suit your needs. However, Tally.ERP 9 has good integration capabilities. So, with the excel to tally import utility tool, you can import any Excel data to Tally, which you can then format as per your requirement.
Read here to know how to use Tally.
C] How to Import Data from Excel To Tally.ERP 9?
To begin with, you need to;
- Step 1: Create all the required ledgers in Tally under a named group. For instance, you can create a ledger called ‘ABC Account’ under the group ‘XYZ Creditors’.
- Step 2: Similar to the vouchers that need to be imported, you need a create a sample voucher in Tally. For example, create a Journal voucher for ABC Account for ₹5000.
- Step 3:Export the sample voucher from Tally. Select display and open Day Book. Use the function Alt+E; this will export the Day Book into XML format.
(Click on the above ‘how to use tally’ link to know how to create ledgers in Tally)
Note: The above steps is to create a sample template for import. The exported file will be saved as “DayBook.xml” in the Tally folder.
Things to keep in mind when preparing data to import in Excel file
- All columns (data fields) must be in the exact order as they are in the XML file. The date field will be in YYYY/MM/DD text format.
- Numbers used should not be comma-separated or currency formatted.
- There can only be two decimals. You can use the ‘=ROUND’ function to achieve this.
- To prevent any errors while importing, ensure that the ledger names used in Excel match with Tally. You can export the ledger masters from Tally for this.
Now, to import data from Excel to Tally.ERP 9, you need to; Open the XML file exported from Tally. For this, you can use notepad or advanced editors (Notepad ++)
- Step 1: From the XML file, copy this portion from <TALLYMESSAGE xmlns:UDF=”TallyUDF”> to </TALLYMESSAGE> to a new file. This portion needs to be replaced, while the header and footer XML codes remain unchanged.
- Step 2: Tally inserts unique identifiers, which needs to be removed. Here, you don’t have to remove the tags, just the values from the tags <VOUCHER> and <GUID>. Additionally, remove this entire tag <EFFECTIVEDATE>xxxx</EFFECTIVEDATE>.
- Step 3: Both the static data (the XML tags) and the variable data (the values) should be separated into lines. You need to remove the linefeeds while ensuring that the XML tags (before & after the data) are on a single line.
- Step 4: Between each column of data in Excel, you need to insert one column.
- Step 5: You need to copy each static data line and paste it in the first row (of the respective columns) in the Excel sheet.
- Step 6: To combine data of all the columns in a row, use the concatenate function =CONCATENATE. Next, copy this combined data and paste it in the original XML file in the same position and save the new XML file.
- Step 7: Select import data in Tally, enter the XML filename & path and press enter. The imported vouchers will be available in the Day Book.
Check out this Video Tutorial : https://manuthrissur.blogspot.com/2019/09/import-vouchers-from-excel-to-tally-new.html
To sum up;
As you can see, the Excel to Tally import format is quite simple once you get to know the basics. Thus, make the switch from Excel to Tally, as it is a better option that provides value for money.