Best in class reporting solutions from ERPs

Friday, 17 January 2025

Data insertion-Tally(approaches)

Problem statement: Many softwares look to insert data into Tally from their application.This blog looks at issues and approaches for the same.

There arer two ways of inserting the data into Tally(as per Tally site):

a.) Tally development code

b.) Tally xml.

Tally xml: For insertion into Tally from an external application there are two aspects that need to be taken care of.

a.) Insertion and update of Masters

b.) Insertion, update and delete of Vouchers.

Attached is the process step for doing the same using XML:

Insertion and updates of masters:

a.) Export the copy of the masters by using the daybook or the ledger master copy in xml format.

b.) Use Tally as a webserver and assign a port for the same.

c.) Decide on the scripting language to be used to insert the data into Tally. We chose python. The data insertion could be done directly from the python web form or from the database.

Insertion/updation/deletion of vouchers:

For example to insert the data from an excel sheet to Tally using python the following code could be used:

from flask import Flask, request, redirect, url_for, flash, render_template_string

import requests

import pandas as pd

# Load Excel file and read the data into a DataFrame

file_path = 'C:\Tally_difference\sample_import\sample_purchase_import.xlsx' # Replace with your Excel file path

df = pd.read_excel(file_path)

# Tally server URL

tally_url = 'http://localhost:9000'

# Filter rows where the 'index' column is greater than 5

df_filtered = df[df['index'] >= 1]

for index, row in df_filtered.iterrows():

# Collect data from form

Remoteid=row['Remoteid']

company_name = row['Companyname']

voucher_type = row['Vouchertype']

voucher_date = row['Voucherdate']

party_ledger = row['partyledgername']

ledger_name = row['Ledgername']

amount = row['Amount']

# XML payload

xml_data = f"""<ENVELOPE>

<TALLYREQUEST>Import Data</TALLYREQUEST>

</HEADER>

<BODY>

<REPORTNAME>Vouchers</REPORTNAME>

<SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>

</STATICVARIABLES>

</REQUESTDESC>

<DATE>{voucher_date}</DATE>

<VOUCHERTYPENAME>{voucher_type}</VOUCHERTYPENAME>

<PARTYLEDGERNAME>{party_ledger}</PARTYLEDGERNAME>

<ALLLEDGERENTRIES.LIST>

<LEDGERNAME>{ledger_name}</LEDGERNAME>

<AMOUNT>-{amount}</AMOUNT>

</ALLLEDGERENTRIES.LIST>

<ALLLEDGERENTRIES.LIST>

<LEDGERNAME>{party_ledger}</LEDGERNAME>

<AMOUNT>{amount}</AMOUNT>

</ALLLEDGERENTRIES.LIST>

</VOUCHER>

</TALLYMESSAGE>

</REQUESTDATA>

</IMPORTDATA>

</BODY>

</ENVELOPE>"""

# Send data to Tally

headers = {'Content-Type': 'application/xml'}

response = requests.post(tally_url, data=xml_data.encode('utf-8'), headers=headers)

print(f"Status Code: {response.status_code}")

print(f"Response Content: {response.text}")

Explanation:

Tally is declared as webserver

# Tally server URL

tally_url = 'http://localhost:9000'

Excel sheet is run row by row
The xml request is run. The unique row in the excel row is identified by rowid( REMOTEID="{Remoteid}). The given remoteid helps capture insert, update and deletes for vouchers.
Response code sends the requests to post the data into Tally.

Insertion/updation of ledgers: for the case of ledgers Tally has a clause called @@dupkeycombine as specified in the sample xml request.

from flask import Flask, request, redirect, url_for, flash, render_template_string

import requests

app = Flask(__name__)

app.secret_key = 'your_secret_key' # Replace with a strong key for production

# Tally server URL

tally_url = 'http://localhost:9000'

# HTML template as a string

html_template = """

<!DOCTYPE html>

<head>

<title>Tally Ledger Entry Form</title>

</head>

<body>

<h1>Tally Ledger Entry Form</h1>

{% with messages = get_flashed_messages(with_categories=true) %}

{% if messages %}

<ul>

{% for category, message in messages %}

<li class="{{ category }}">{{ message }}</li>

{% endfor %}

</ul>

{% endif %}

{% endwith %}

<label for="company_name">Company Name:</label><br>

<label for="ledger_name">Ledger Name:</label><br>

<label for="ledger_parent">Ledger Parent:</label><br>

<button type="submit">Submit to Tally</button>

</form>

</body>

</html>

"""

@app.route('/')

def index():

return render_template_string(html_template)

@app.route('/submit', methods=['POST'])

def submit_data():

# Collect data from form

company_name = request.form['company_name']

ledger_name = request.form['ledger_name']

#reserved_name = request.form['reserved_name']

ledger_parent = request.form['ledger_parent']

#ledger_name = request.form['ledger_name']

#amount = request.form['amount']

# XML payload

xml_data = f"""<ENVELOPE>

<TALLYREQUEST>Import</TALLYREQUEST>

<ID>AllMasters</ID>

</HEADER>

<BODY>

<DESC>

<IMPORTDUPS>@@DUPKEYCOMBINE</IMPORTDUPS>

<SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>

</STATICVARIABLES>

</DESC>

<DATA>

<OLDAUDITENTRYIDS.LIST TYPE="Number">

</OLDAUDITENTRYIDS.LIST>

<PARENT>{ledger_parent}</PARENT>

<TAXCLASSIFICATIONNAME> Not Applicable</TAXCLASSIFICATIONNAME>

<TAXTYPE>Others</TAXTYPE>

<GSTTYPE> Not Applicable</GSTTYPE>

<APPROPRIATEFOR> Not Applicable</APPROPRIATEFOR>

<SERVICECATEGORY> Not Applicable</SERVICECATEGORY>

<EXCISELEDGERCLASSIFICATION> Not Applicable</EXCISELEDGERCLASSIFICATION>

<EXCISEDUTYTYPE> Not Applicable</EXCISEDUTYTYPE>

<EXCISENATUREOFPURCHASE> Not Applicable</EXCISENATUREOFPURCHASE>

<LEDGERFBTCATEGORY> Not Applicable</LEDGERFBTCATEGORY>

<SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>

<LBTREGNDETAILS.LIST> </LBTREGNDETAILS.LIST>

<VATDETAILS.LIST> </VATDETAILS.LIST>

<SALESTAXCESSDETAILS.LIST> </SALESTAXCESSDETAILS.LIST>

<GSTDETAILS.LIST> </GSTDETAILS.LIST>

<HSNDETAILS.LIST> </HSNDETAILS.LIST>

<LANGUAGENAME.LIST>

<NAME.LIST TYPE="String">

<NAME>{ledger_name}</NAME>

</NAME.LIST>

</LANGUAGENAME.LIST>

<XBRLDETAIL.LIST> </XBRLDETAIL.LIST>

<AUDITDETAILS.LIST> </AUDITDETAILS.LIST>

<SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST>

<EXCISETARIFFDETAILS.LIST> </EXCISETARIFFDETAILS.LIST>

<TCSCATEGORYDETAILS.LIST> </TCSCATEGORYDETAILS.LIST>

<TDSCATEGORYDETAILS.LIST> </TDSCATEGORYDETAILS.LIST>

<SLABPERIOD.LIST> </SLABPERIOD.LIST>

<GRATUITYPERIOD.LIST> </GRATUITYPERIOD.LIST>

<ADDITIONALCOMPUTATIONS.LIST> </ADDITIONALCOMPUTATIONS.LIST>

<EXCISEJURISDICTIONDETAILS.LIST> </EXCISEJURISDICTIONDETAILS.LIST>

<EXCLUDEDTAXATIONS.LIST> </EXCLUDEDTAXATIONS.LIST>

<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>

<PAYMENTDETAILS.LIST> </PAYMENTDETAILS.LIST>

<BANKEXPORTFORMATS.LIST> </BANKEXPORTFORMATS.LIST>

<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>

<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>

<LEDGERCLOSINGVALUES.LIST> </LEDGERCLOSINGVALUES.LIST>

<LEDGERAUDITCLASS.LIST> </LEDGERAUDITCLASS.LIST>

<OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>

<TDSEXEMPTIONRULES.LIST> </TDSEXEMPTIONRULES.LIST>

<DEDUCTINSAMEVCHRULES.LIST> </DEDUCTINSAMEVCHRULES.LIST>

<LOWERDEDUCTION.LIST> </LOWERDEDUCTION.LIST>

<STXABATEMENTDETAILS.LIST> </STXABATEMENTDETAILS.LIST>

<LEDMULTIADDRESSLIST.LIST> </LEDMULTIADDRESSLIST.LIST>

<STXTAXDETAILS.LIST> </STXTAXDETAILS.LIST>

<CHEQUERANGE.LIST> </CHEQUERANGE.LIST>

<DEFAULTVCHCHEQUEDETAILS.LIST> </DEFAULTVCHCHEQUEDETAILS.LIST>

<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>

<AUDITENTRIES.LIST> </AUDITENTRIES.LIST>

<BRSIMPORTEDINFO.LIST> </BRSIMPORTEDINFO.LIST>

<AUTOBRSCONFIGS.LIST> </AUTOBRSCONFIGS.LIST>

<BANKURENTRIES.LIST> </BANKURENTRIES.LIST>

<DEFAULTCHEQUEDETAILS.LIST> </DEFAULTCHEQUEDETAILS.LIST>

<DEFAULTOPENINGCHEQUEDETAILS.LIST> </DEFAULTOPENINGCHEQUEDETAILS.LIST>

<CANCELLEDPAYALLOCATIONS.LIST> </CANCELLEDPAYALLOCATIONS.LIST>

<ECHEQUEPRINTLOCATION.LIST> </ECHEQUEPRINTLOCATION.LIST>

<ECHEQUEPAYABLELOCATION.LIST> </ECHEQUEPAYABLELOCATION.LIST>

<EDDPRINTLOCATION.LIST> </EDDPRINTLOCATION.LIST>

<EDDPAYABLELOCATION.LIST> </EDDPAYABLELOCATION.LIST>

<AVAILABLETRANSACTIONTYPES.LIST> </AVAILABLETRANSACTIONTYPES.LIST>

<LEDPAYINSCONFIGS.LIST> </LEDPAYINSCONFIGS.LIST>

<TYPECODEDETAILS.LIST> </TYPECODEDETAILS.LIST>

<FIELDVALIDATIONDETAILS.LIST> </FIELDVALIDATIONDETAILS.LIST>

<INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST>

<TCSMETHODOFCALCULATION.LIST> </TCSMETHODOFCALCULATION.LIST>

<LEDGSTREGDETAILS.LIST> </LEDGSTREGDETAILS.LIST>

<LEDMAILINGDETAILS.LIST>

<ADDRESS.LIST TYPE="String">

<ADDRESS>NANANA</ADDRESS>

</ADDRESS.LIST>

</LEDMAILINGDETAILS.LIST>

<GSTRECONPREFIXSUFFIXDETAILS.LIST> </GSTRECONPREFIXSUFFIXDETAILS.LIST>

<CONTACTDETAILS.LIST> </CONTACTDETAILS.LIST>

<GSTCLASSFNIGSTRATES.LIST> </GSTCLASSFNIGSTRATES.LIST>

<EXTARIFFDUTYHEADDETAILS.LIST> </EXTARIFFDUTYHEADDETAILS.LIST>

<TEMPGSTITEMSLABRATES.LIST> </TEMPGSTITEMSLABRATES.LIST>

<VOUCHERTYPEPRODUCTCODES.LIST> </VOUCHERTYPEPRODUCTCODES.LIST>

<LEDADDRESS.LIST> </LEDADDRESS.LIST>

<DEFMULTIPLETOPHONENO.LIST> </DEFMULTIPLETOPHONENO.LIST>

</LEDGER>

</TALLYMESSAGE>

</DATA>

</BODY>

</ENVELOPE>

"""

# Send data to Tally

headers = {'Content-Type': 'application/xml'}

response = requests.post(tally_url, data=xml_data.encode('utf-8'), headers=headers)

# Handle response

if response.status_code == 200:

flash("Data submitted to Tally successfully.", "success")

else:

flash("Failed to submit data to Tally.", "error")

return redirect(url_for('index'))

if __name__ == '__main__':

app.run(debug=True, use_reloader=False)

These are the key points to be attached with the xml import for vouchers.

Tally import with tdl:

The data for masters and transactions can also be imported with tdl.

In case of TDL it becomes a little bit more complex since the matching of the ledger names code needs to be added. Sample function is given below:

[function: Add_Stockitem]

00: Walk Collection: testcoll

0X: IF : $$IsEmpty:@@itemexists

Variable:setdate:Date

Variable:setrate:Rate

00A:Set:setdate:$$Date:"01-04-2023"

01E:Set:setrate:$$AsRate:"100"

01A: New Object: StockItem

01BA: Set VALUE:NAME:$$LocaleString:$item_name;$item_name

01BC: Set VALUE:PARENT:$item_type;$item_name

01BD: Set VALUE:CATEGORY:$sub_category_name

01BD1: Set VALUE:DESCRIPTION:$sku_code

01BE: Set VALUE:GSTAPPLICABLE:$$LocaleString:"Applicable"

01bF: Set Value: BASEUNITS:$$LocaleString:$Uom

01bG: Set Value: NARRATION:$$LocaleString:$sku_code

;; Set cost & selling price

0010 : INSERT COLLECTION OBJECT : STANDARDCOSTLIST

0011 : Set Value : Date : "1-11-2017"

0012 : Set Value : Rate : $$TgtObject:$$AsRate:@@RateVal1

0013 : Set Target : ..

0014 : INSERT COLLECTION OBJECT : STANDARDPRICELIST

0015 : Set Value : Date : "1-11-2017"

0016 : Set Value : Rate : $$TgtObject:$$AsRate:@@RateVal2

0017 : Set Target : ..

01D1:SAVE Target

01D:End If

;02: END WALK

02: END WALK

021CC:Create Target

Addition of voucher using TDL: for addition of voucher using tdl we have yet to figure outt how to use remoteid. Hence the same has been excluded in this blog.

Name: Apoorv Chaturvedi

email: support@turbodatatool.com;suppport@mndatasolutions.com

Phone:+91-8802466356

youtube channel:www.youtube.com\@mndatasolutions2042

Wednesday, 15 January 2025

Mobile app developers/analytic app developers-optimizing the incremental load from Tally-reduce the time by 99 percent through datawarehouse

Problem statement: App developers have synced the initial data from Tally. Now incremental data is to be captured. Here we discuss the approaches for transactions and masters for the same. We would also look at how the datawarehouse can help reduce the incremental times by more than 99%.

Load through odbc: in case the application works through odbc then one would need to do a full extract each time since the odbc represents a full snapshot. There are more than 160 odbc extracts in Tally. Hence the process of incremental extract would need to be handled in the following manner:

a.) Nightly load

b.) Load over the fortnight.

Load through xml: Many developers like to sync the reports directly through the xml. Complete voucher extraction could put in a lot of load on the system. Hence it is recommended that the extraction of the voucher be done on a day by day basis(https://mndatasolutionsindia.blogspot.com/2025/01/reducing-time-for-mobile-refresh-tally.html).

Difference between ledger vouchers and voucher register: It is to be noted for the developer that the complete sales movement is captured in the ledger vouchers(drill down from the sales accounts in Tally to ledger vouchers). Voucher register gives movement by voucher types[including inventory movements] and hence it could exclude the journal movements.

Ledgervouchers is a parameterized report by svcurrentcompany, svfromdate and svtodate.

Voucher register is movement by vouchertypes only[including inventory]. The sales numbers in the voucherregister might not match the voucherledger movement(all required movement types).

Alternatives for the developer:

a.) Extract the profit and loss report by using the following xml-sample has repeatvariables also.

Thereafter based on the year month selected drill down to the required voucher based on lledgerprimary. Alternatively based on the svfromdate and svtodate and company name the request can be sent to Tally to retrive the required sales vouchers:

sample xml code added.

Here ledgername is the input parameter.

Bottleneck: to extract the voucher (from which voucherledger report is extracted) one could need to extract the same on a day by day basis so that Tally does not hang. The same process of synchronization could take a lot of time.

Process of reduction of execution time:

we recommend using a datawarehouse and using the event based activities in Tally to capture the changes in transaction and masters(https://mndatasolutionsindia.blogspot.com/2025/01/reducing-time-for-mobile-refresh-tally.html). This will capture the changes as the entries into the voucher or the entries into the masters are happening.

Note: RAM of atleast 16 GB is required.

Internet connection needs to be always on[else synchronization could not happen]

Concurrancy impact would need to checked. If there are a lot fo developers then we need to check the load on the datawarehouse.

The onsite cost of hosting the data onto the database also needs to be checked.

Our recommendation would be to load the data onto a desktop tool like Powerbi or desktop database like sql server and then sync with the mobile app.

Can event based activities be used with xml export: as far as we know the same cannot be done with the usage of embedded tdl code.

Name: apoorv chaturvedi

email:support@mndatasolutions.com;support@turbodataotol.com

Phone:+91-8802466356

youtube channel:www.youtube.com\@mndatassolutions2042

Best in class reporting solutions from ERPs

Friday, 17 January 2025

Data insertion-Tally(approaches)

Wednesday, 15 January 2025

Mobile app developers/analytic app developers-optimizing the incremental load from Tally-reduce the time by 99 percent through datawarehouse

Data insertion-Tally(approaches)

Blog Archive

Labels