Linking Tally with Microsoft Excel
If you are using tally and preparing reports from Tally Data, then have you observed, any time, there is additional work load or repetition of work. Means to generate reports from tally you need to first update tally data and then tally data is exported in Excel Format for data manipulation. And the same process is followed each time when there is any updates in source data (Tally) and linking of exported data with report take significant time. By doing in this way one can say that you still taking same amount of time as you had taken for first time and at the time increments your boss may ask “what efficiency you have if same amount of time is taken for completing a work even after repeating for more than 100s times”.But after reading this article you will able not only to reduce your work load to prepare report but efficiency will also increase.
In this article I will explain the steps required to connect Tally with Microsoft Excel and how to tackle the linking problem, if happen during this process.
NOW LET SEE HOW IT WORKS
Step 1: Open a tally and select any company
Step 2: On Gateway of Tally click “F12” for configuration settings
Step 3: Select Advance Configuration
Step 4: Ensure that ODBC Server is enabled
e
Step 5: Now Open Microsoft Excel and go “Data Tab” select “From Other Sources” and then “From Microsoft Query”
Step 6: Select “Tally ODBC _XXXX* and click OK
Step 7: The Next window will like as mentioned and drag down cursure TAB until “Ledger” is appeared or you can click “L” until “ledger is appeared.
Step 8: Now expand “Ledger” by clicking on ‘+’ symbol
Step 9: Drag down the cursure once again until $Name is appeared and hit enter
Step 10: Follow the same step mentioned in last step for $Parent, $_ThisYearBalance and for $_ThisYearBalance and click on next
Step 11: Click Next
Step 12: Click Next
Step 13: Click Finish
Step 14: The next window will like as mentioned, in this step there three types of options are there that is Table, PivotTable Report and Pivot Chart. If you want’s to create direct pivot table then you can choose too, but in this article I have decided to go with default option “Table”. And then click “OK”
Step 15: Now you will see that all ledger along with their current balance and previous balance are imported. (Note all ledgers which have debit balance comes with Negative Sign and credit balances have a positive balances.
Now let’s check it accuracy, from above snap short I have seen Imported data shown -59,055/- (Debit Balance) in Bank A/c and ledger balance in tally also have the same figure.
Bank balance matched with imported data. Therefore, the source as well as information is reliable and can used as basic/raw data for various types of reports.
After this someone may ask what after happen if after importing data from Tally there are some updation/modification in tally data, then I need to repeat 1 to 15 Steps once again.
Answer is NO, after making entries in tally you just need refresh the imported data.
For Example, after data importation, I realized that one entry was missing to be entered in tally. Cash of Rs. 34,000/- was withdrawal from bank on 31st December 2014 and I make the entry for the same.
By doing this entry cash will increase will increase by 34000 and accordingly bank balance will reduce by the same amount. Now go on excel sheet and right click on table and select refresh.
Instantly your Excel sheet will be updated.
Note: in some cases there may be error in first like “No Driver is not specified” Then that case you need to follow these step first before importing data from Excel
Step 1: Right click on Tally icon and select properties
Step 2: Click on open file location
Step 3: Right click on reodbc 32/64 and click on run as administrator.
Then follow the 1 to 16 steps as mentioned above.
Note 1: If ODBC file is not there then you need reinstall tally once again
Note 2: Above steps are valid for all versions Tally and Microsoft Excel
I like to read your comments about your like, dislike or have any further query relating to Microsoft Excel or Tally.
The author can also be reached at Camukeshkumar6610@consultant.com
To increase your knowledge base and add value to your skills, enroll for CAclubindia's Online Excel Course for Beginners to Advanced Learners now.
CAclubindia
