Click to download the Connector from Here

https://workdrive.zohoexternal.com/external/8fb8315cec93c88ac25c12fee952e19aab501f47fcc1453d710a070abe21bfd8

Tally-Excel- PowerBI- Power Query ODBC Connector

This Tool will help to import Tally Data (Master Data and Transactional Data) to excel /Power BI/ Power Query.

Steps in Connecting the TDL In Tally and Import in Excel

Step 1 Download the File Named Connection.tcp and paste in any folder in your PC

Step 2 Connecting TDL Copy the Folder path and Load Any company in Tally and Navigate to F1 Help >> TDL's & Addons or Press Ctl + Alt + T in Gateway of Tally Menu

Then Press F4 and Paste the path and select the Connection.tcp File

Step 3 Enabling ODBC in Tally

Navigate to F1 Help >> Settings >> Connectivity and Press Enter

Then Set the Following Options

Tally prime act as : Both

Enable ODBC : Yes

Port : 9000 or any other port of your choice

How to Connect to Excel through Microsoft Query

Step 4 Running Tally in Administrator Mode

• After connecting the TDL file, Make sure You have Completed Step 1 to 3 as above Enabling ODBC RUN TALLY IN ADMINISTRATOR MODE IN WINDOWS
• Then Open Excel and Navigate to Data >> Get Data >> Other Sources >> Microsoft Query >> Then Click on TALLYODBC_9000 press OK in the Choose Data Source If you have any error in this step then please go through Steps 1 to 3 properly

Step 5

Selecting the Table in the tally database

The following tables one by one

1. A_Sirc_Leder_Detailed_7_1 - Master Data 2. A_Sirc_Vourcher7_1 - Transaction Data

After Selecting press the > button to load all the fields or the selected field Then Click Next 3 times And finally Click Finish so that the data loaded in Excel

For Doubts in connecting to excel refer to the tally documentation

https://help.tallysolutions.com/tally-prime/data-exchange-tally-prime/extracting-master-data-to-microsoft-excel-using-odbc-tally/

Tdl Documentation - Community Version 7.1 (For ICAI Members)

Tally Definition Language (TDL) is the development language of Tally Products. TDL is developed to provide the user with the flexibility and power to extend the default capabilities of Tally, and integrate them with the external applications. TDL provides a development platform for the user. The entire user interface of Tally.ERP 9 is built using TDL. TDL as a language, provides capabilities for Rapid Development, Rendering, Data Management, and Integration.

TDL Can Help Chartered Accountants in the following Ways

1. Ability to generate Custom reports from Tally

2. Real-Time integration with Excel / PowerBi / Tableau with the ODBC Access

3. Reduce the time to prepare Fianancial Statements from Tally.

4. Identify 269SS and 269T Transaction in Tally

5. Complete GST Audit in less than 30 Minutes from Tally.

6. Ledger Scrutiny With Advanced filters in Tally

7. Do ageing analysis from a partly Bill reference enabled enviornment.

8. Get All Transaction and Ledger Data in Excel (Even a Very Large Data) in few clicks

9. Get Critical Data Analysis for Tax audit Report

10. Analyse the Utilusation of Funds in a business enviornment.

11. There are many further Benifits being explored by the Author

Fields Present in the Ledger Table

Fields Present in the Transaction Table.

How to Connect to PowerBI

For Doubts in connecting to excel refer to the tally documentation

https://help.tallysolutions.com/tally-prime/data-exchange-tally-prime/extracting-master-data-to-microsoft-excel-using-odbc-tally/

Important Queries from the above data source - Transactions

SELECT $Key, $MasterId, $AlterID, $VoucherNumber, $Date, $VoucherTypeName, $_Narration, $Reference, $ReferenceDate, $IsDeemedPositive, $NatureOfVoucher, $HasCashFlow, $EnteredBy, $AlteredBy, $AlteredOn, $SUpdatedDate, $SUpdatedTime, $LedgerName, $Amount, $DrAmount, $CrAmount, $Ledger_Parent, $Ledger_PrimaryGroup, $Party_LedgerName, $PARTY_GST_number, $Ledger_Master_GST_Number, $LedgerMasterGSTINGSTINtype, $HasBankEntry, $RComp_Name, $Year_Selected_from, $Year_Selected_to, $Company_number, $Led_Master_id, $Led_Alter_Id, $Led_IS_Revenue, $Path_of_the_CurrentCompany FROM A_Sirc_Vourcher7_1

Important Queries from the above data source - Master data - Ledger

SELECT $Name, $_PrimaryGroup, $Parent, $OpeningBalance, $ClosingBalance, $_PrevYearBalance, $IsRevenue, $PartyGSTIN, $MasterId, $alteridd, $RComp_Name, $Year_Selected_from, $Year_Selected_to, $Company_number, $Path FROM A_Sirc_Leder_Detailed_7_1

Get the List of ledgers with forensics data

Select $Name, $Createdby, $CreatedDate, $Masterid, $Alterid, $Alteredon, $Alteredby, $Updateddatetime, $LastVoucherDate, $Parent, $_PrimaryGroup, $$AscrAmt:$openingBalance, $$AscrAmt:$_ClosingBalance from ledger order by $Alterid desc