How to Connect Tally with Excel to Get GST Summary | Tally to Excel GST Summary | Tally Prime to Excel Data Import | Tally to Excel Power query

 

Always required Excel in a business or accounting to maintain books of income, expenses and taxes etc. Excel is most usable software for calculations/presentations for a business. And also lots of businesses use Tally to maintain books properly. If it possible to connect Tally with Excel to fetch Tally Data into Excel then our most of  problems will be solved. This Tally to Excel data import method will be “Sanjeevani” for CA and accountants. 
So this can possible through Excel Power Query and Tally ODBC connectivity. To Import Tally Data into Excel most powerful and fastest way is Excel Power BI. We can import any report of Tally directly into excel through this Power BI method. 
After reading this post you will be able to import GST summary from Tally to Excel and can refresh it every time after any changes.
Required Tools/Codes to connect Tally with Excel:
1. Microsoft Excel
2. Tally Software
3. Tally ODBC URL
4. XML Schema for Export
5. Report Name

Follow below Steps to Import Data from Tally to Excel:
1. Open Tally and Excel
2. Go to Excel Data Tab – New Query – From Other Source – Blank Query

3. We can change Query Name in properties section at right hand side.
4. Go in Home Tab and in Advance Editor

5. Paste Below Code in Advance Editor:

let
    url = “http://localhost:9000”,
    _body = “<ENVELOPE>
<HEADER>
<TALLYREQUEST>Export Data</TALLYREQUEST>
</HEADER>
<BODY>
<EXPORTDATA>
<REQUESTDESC>
<STATICVARIABLES>
<SVEXPORTFORMAT>ASCII (Comma Delimited)</SVEXPORTFORMAT>
<SVFROMDATE>DATE1</SVFROMDATE>
<SVTODATE>DATE2</SVTODATE>
</STATICVARIABLES>
<REPORTNAME>GSTR2TrglActionWise DrillDown</REPORTNAME>
</REQUESTDESC>
</EXPORTDATA>
</BODY>
</ENVELOPE>”,
    FromDate = Date.ToText(#date(2022, 04, 01)),
    ToDate = Date.ToText(#date(2022, 04, 02)),
    body = Text.Replace(Text.Replace(_body, “DATE1”, FromDate), “DATE2”, ToDate),
    Source = Csv.Document(Text.Replace(Text.FromBinary(Web.Contents(url, [Content=Text.ToBinary(body, TextEncoding.Utf16), Headers=[#”Content-Type”=”text/xml;charset=utf-16″]]), TextEncoding.Utf16), “,#(cr,lf)”, “#(cr,lf)”), { “Particulars”, “GSTIN”, “Invoice Number”,”Date”, “Document Type”, “Invoice Value”,”Place of Supply”, “Note Number”, “Note Date”,”Status”,”Taxable Value”,”IGST Amt”,”IGST Eligible Amt”,”CGST Amt”,”CGST Eligible Amt”,”SGST Amt”,”SGST Eligible Amt”,”Cess Amt”,”Cess Eligible Amt”})
in
    Source

6. After pasting above code in advance editor press Done
7. Now you can see Tally reports in Excel Power query home
8. Go to Home Tab – Click on “Close and Load” or “Close and Load to…”
9. If select Close and Load to… then you can choose Excel reference sheet and cell to show Data.
10. Now you will find all your Tally data to related reports in Excel.
11. Refresh Excel sheet to recollect data from Tally
12. Change Date in Power query Advance editor code as per your requirement.

Watch above video for more details.

How to See and enable Tally ODBC Port : Tally ODBC


Leave a Comment