Hi!! Greetings and Warm Welcome to DAFI's first blog post (well, honestly this is also MY first blog post and I am quite excited!!)
We use Excel to do a whole lot of things with data (like analyzing, reporting, distributing, decision-making, etc.,). However, even before we can start to analyze data in Excel, the challenge most of us face is to get the right data into Excel, effectively and efficiently. A few years back, I faced such a challenge which, I believe, several of us would be facing quite frequently. Thought let me share it with you'll (Solution included, of course!!)
Our firm's senior partner who was about to complete a scrutiny assessment of a client before the tax officer, was at wit's end when the officer finally stated "Well, all this is OK, now there is only one FINAL REQUIREMENT." When the senior partner (gathering great courage!!) asked of the final requirement, he was told "We need a listing of ALL Sundry Debtors (Outstanding Balances) with the Debtor's Name, Address, Permanent Account Number (PAN) and their Closing Balances in a columnar format". 'Gone Bonkers?' thought the senior partner, as the listing would be over twenty thousand entries, since the client was a large privately held company. Very politely, the senior partner offered to provide such a listing for a few / several accounts as can be identified by the tax officer. "No, we want a listing of ALL Sundury Debtors with the stated details in a columnar format", the officer said adamantly.
When we shared this requirement with the client, the VP - Accounts simply stated "This should be easy, we already have this data in Tally*, why don't you'll retrieve it from there?" Hmm... sounds good, we thought, but only if Tally has a facility to export such data in a columnar format. To our disappointment, Tally didn't!! Now look at this, we have the data in a database, but there is no way to export it from the application front end since the application does not provide such an interface.. OMG.. i am sure you can feel our plight...
When a brainstorming session to identify the solution seemed to go nowhere, we concluded that the only way to meet the requirement was to REENTER the data in Excel in the requisite format. This would be a (at least) 5 day job. When asked who is going to do this work to REENTER data in Excel, "Mr. Vipul, is most suitable for this job" suggested a manager. "Why?" asked the senior partner. "Because he is the junior-most articled trainee!!" replied the manager.
"Vipul, there is some very important work. Although it is quite challenging, let me assure you there is going to be lot of learning...... " and this is how Vipul was motivated to re-enter data into Excel
Solution:
Well, think... there must be a better way to get that data from the tally database into Excel.........
And the better way is....
One can retrieve data in Excel from any database (including Tally or even SAP database) by using the ODBC feature. How? Here we go...
The following steps will help you get the all master data for every ledger account along with it's closing balance.
Steps:
1. Start Tally - select and Open the company of which you wish to retrieve the data
2. Start Excel
3. In Excel click <Data> --> <From Other Sources> --> <From Data Connection Wizard> (See 'Image below')
4. In the [Data Connection Wizard] dialogue box select <Other / Advanced> and click <Next>
5. In the [Data Link Properties] in the [Provider] tab, select [Microsoft OLE DB Provider for ODBC Drivers] and click <Next>
6. In the [Data Link Properties] in the [Connection] tab, select [TallyODBC_9000] from the drop down list box in the [1. Specify the source of data, Use data source name] (see Image below)
7. Click the Button <Test Connection>
8. You shall get a message, "Test Connection Succeeded." in a dialogue box "Microsoft Data link". Click <OK>.
9. Click <OK> in the [Data Link Properties Dialogue Box]
10. This should bring up the [Data Connection Wizard] dialogue box. Select the table <Ledger> and click <Next> (see Image below.)
11. Click <Finish>
12. And finally in the [Import Data] message box, click <OK>.
The said steps will get you all master data for every ledger account along with it's closing balance in Excel in a columnar format (see Image below).
Now, all you need to do is to filter out column BQ for Sundry Debtors! That's it!! You are done & it take less than 3-5 minutes!!!
Naturally, Mr. Vipul was the happiest person :)
A humble request:. Share & like if you find this useful. Your comments are welcome! Your contribution is appreciated!! Thank you!!!
DAFI provides training / consulting services in implementing advance Excel for Business, Assurance and Forensic Accounting. To know more visit: www.dafi.in
Notes:
1. Known Issues -
a. Some users may not get the option [TallyODBC_9000] in the drop down list box as stated in Step #6. AFAIK this issue has something to do with installation of Tally. A re-installation of Tally, in several cases, has helped. I do not know any other solution / workaround. Readers may share their experiences to benefit others.
b. Some users may get an error message after Step #8. Again, AFAIK this problem has something to do with installation of Tally. A re-installation of Tally, in several cases, has helped. I do not know any other solution / workaround. Readers may share their experiences to benefit others.
c. In some cases, after the last step i.e. step #12, Excel / Tally or both may crash. AFAIK in such cases only prayers help!!
*2. This example illustrates how ODBC connectivity feature of Excel can be used to retrieve data from a Tally Database. Tally is a popular Indian accounting (or ERP!!) application.
3. The same process (with appropriate changes) can be followed to retrieve data from any other RDBMS including Oracle / Microsoft SQL Server. Readers are requested to consult their Database Administrators for help and share their experiences.
4. Excel also saves the connection information within the workbook itself. Thus, if changes to data are made from Tally, the user can click the <Refresh All> button [<Data> --> <Refresh All> --> <Refresh All>] to update data into Excel!!
We use Excel to do a whole lot of things with data (like analyzing, reporting, distributing, decision-making, etc.,). However, even before we can start to analyze data in Excel, the challenge most of us face is to get the right data into Excel, effectively and efficiently. A few years back, I faced such a challenge which, I believe, several of us would be facing quite frequently. Thought let me share it with you'll (Solution included, of course!!)
Our firm's senior partner who was about to complete a scrutiny assessment of a client before the tax officer, was at wit's end when the officer finally stated "Well, all this is OK, now there is only one FINAL REQUIREMENT." When the senior partner (gathering great courage!!) asked of the final requirement, he was told "We need a listing of ALL Sundry Debtors (Outstanding Balances) with the Debtor's Name, Address, Permanent Account Number (PAN) and their Closing Balances in a columnar format". 'Gone Bonkers?' thought the senior partner, as the listing would be over twenty thousand entries, since the client was a large privately held company. Very politely, the senior partner offered to provide such a listing for a few / several accounts as can be identified by the tax officer. "No, we want a listing of ALL Sundury Debtors with the stated details in a columnar format", the officer said adamantly.
When we shared this requirement with the client, the VP - Accounts simply stated "This should be easy, we already have this data in Tally*, why don't you'll retrieve it from there?" Hmm... sounds good, we thought, but only if Tally has a facility to export such data in a columnar format. To our disappointment, Tally didn't!! Now look at this, we have the data in a database, but there is no way to export it from the application front end since the application does not provide such an interface.. OMG.. i am sure you can feel our plight...
When a brainstorming session to identify the solution seemed to go nowhere, we concluded that the only way to meet the requirement was to REENTER the data in Excel in the requisite format. This would be a (at least) 5 day job. When asked who is going to do this work to REENTER data in Excel, "Mr. Vipul, is most suitable for this job" suggested a manager. "Why?" asked the senior partner. "Because he is the junior-most articled trainee!!" replied the manager.
"Vipul, there is some very important work. Although it is quite challenging, let me assure you there is going to be lot of learning...... " and this is how Vipul was motivated to re-enter data into Excel
Solution:
Well, think... there must be a better way to get that data from the tally database into Excel.........
And the better way is....
One can retrieve data in Excel from any database (including Tally or even SAP database) by using the ODBC feature. How? Here we go...
The following steps will help you get the all master data for every ledger account along with it's closing balance.
Steps:
1. Start Tally - select and Open the company of which you wish to retrieve the data
2. Start Excel
3. In Excel click <Data> --> <From Other Sources> --> <From Data Connection Wizard> (See 'Image below')
5. In the [Data Link Properties] in the [Provider] tab, select [Microsoft OLE DB Provider for ODBC Drivers] and click <Next>
6. In the [Data Link Properties] in the [Connection] tab, select [TallyODBC_9000] from the drop down list box in the [1. Specify the source of data, Use data source name] (see Image below)
7. Click the Button <Test Connection>
8. You shall get a message, "Test Connection Succeeded." in a dialogue box "Microsoft Data link". Click <OK>.
9. Click <OK> in the [Data Link Properties Dialogue Box]
10. This should bring up the [Data Connection Wizard] dialogue box. Select the table <Ledger> and click <Next> (see Image below.)
11. Click <Finish>
12. And finally in the [Import Data] message box, click <OK>.
The said steps will get you all master data for every ledger account along with it's closing balance in Excel in a columnar format (see Image below).
Now, all you need to do is to filter out column BQ for Sundry Debtors! That's it!! You are done & it take less than 3-5 minutes!!!
Naturally, Mr. Vipul was the happiest person :)
A humble request:. Share & like if you find this useful. Your comments are welcome! Your contribution is appreciated!! Thank you!!!
DAFI provides training / consulting services in implementing advance Excel for Business, Assurance and Forensic Accounting. To know more visit: www.dafi.in
Notes:
1. Known Issues -
a. Some users may not get the option [TallyODBC_9000] in the drop down list box as stated in Step #6. AFAIK this issue has something to do with installation of Tally. A re-installation of Tally, in several cases, has helped. I do not know any other solution / workaround. Readers may share their experiences to benefit others.
b. Some users may get an error message after Step #8. Again, AFAIK this problem has something to do with installation of Tally. A re-installation of Tally, in several cases, has helped. I do not know any other solution / workaround. Readers may share their experiences to benefit others.
c. In some cases, after the last step i.e. step #12, Excel / Tally or both may crash. AFAIK in such cases only prayers help!!
*2. This example illustrates how ODBC connectivity feature of Excel can be used to retrieve data from a Tally Database. Tally is a popular Indian accounting (or ERP!!) application.
3. The same process (with appropriate changes) can be followed to retrieve data from any other RDBMS including Oracle / Microsoft SQL Server. Readers are requested to consult their Database Administrators for help and share their experiences.
4. Excel also saves the connection information within the workbook itself. Thus, if changes to data are made from Tally, the user can click the <Refresh All> button [<Data> --> <Refresh All> --> <Refresh All>] to update data into Excel!!
