Excel to Tally Financials Realtime linking with Power Query
HTML-код
- Опубликовано: 25 янв 2025
- This tutorial describes how to link Financials (i.e. Balance Sheet, Profit Loss, Schedules) prepared in Excel to Tally.ERP 9. This approach uses Dynamically linked Trial Balance (as on specified date) linked to Tally using its XML integration with the help of Power Query of Excel
Github Project link of Excel Kida Power Query project (given below)
github.com/dha...
Direct link of power query file (of this github power query project)
github.com/dha...
Namaste, your session are really helpful. I request you to keep on sharing the new concepts which can ease the professional work.
This is ca article ... From ruby capital
Excel technical knowledge with ca amazing man
Explained absolutely great feature use by an expert..
Excellent , brilliant and very useful. thank you for your video
Mr. Dhananjay Sir, you are Very greate....
i am always follow you,
Sir in this case please note that rather providing "Parent Fild" just it adjust to "Parent" & "Primary Group" so the user can nevegate in so simple manner ...
Thanking you sir
Actually I have accessed Ledger table directly, which does not have field of Primary Group. I am too in process of figuring how exactly Primary Group can be derived (as certain limitations come in writing TDL in XML format)
Will update that Power Query when I figure out that mechanism via XML
I have figured out Primary Group column of Tally and have updated Power Query of "Trial Balance for the period" with it. Please give it a try
@@ExcelKida Thanking you 🙏🏻
It's really good to maintain real time financial report
Many thanks for this video and one suggestion can you pls add one more column in the TB for Primary Group also so that there would be no confusion if someone created custom parent group so there would be chances of confusion, if primary group is added to the TB there would be clarity.
If have included Primary Group field in the "Trial Balance for the period" power query, please check it out
github.com/dhananjay1405/excelkida-power-query-library/tree/master/tally
Mind blowing sir....
Respected sir,
Thanks for your kindness and positive support.
You have suggested great video and I have solved my problem.
Another question sir
In Trail balance it is possible with
1.Showing opening balance
2. showing Transitions
these two details.
I hope your can understand my problem,
Thanks in advance for you great support.
Very well explained.
very usefull sir thank you
You helped me great way
by this video
Great videos
Brilliantly worked thank you😊
Very informative...loving it ..tx
Simply Awesome
can we get sales voucher data, like stock item, rate qty
Happy Dussehra sir !
Good night sir !
Hi Dhananjay, If I want to design Trial balance in Power BI using Tally Prime, what is the way out to connect and extract on Power BI? Please advise thanks.
Power Query engine is common for both Excel & Power BI. You can copy-paste this M-language script into Power BI & achieve same result instead of Excel
Very Useful Sir 🌷
Sir we have maintained data cost center wise in tally is it possible to extract data cost center wise through power query?
Hats off ❤️🔥. Thanks a lot
very nice one.
How to update tally data in Excel on real time basis where tally data hain various projects
For projects you need to export cost centre data. This method supports limited data. You need to use my Tally to Database utility to export CSV files & then import into Excel. Feel free to check out Excel Dashboard playlist on the channel.
When I'm making changes in tally while not keeping the file open in background, and when I'm refreshing the excel, it is showing some error has occured and i should restart excel.any particular reason for this?
Can you share me the exact error screenshot over email (email ID mentioned at the end of video).
If I take a look of error, then I can troubleshoot the issue.
Excellent video.. Novel concept.. Keep it up..
1. Will this query code fetch the trial balance of active company in tally?
2. If there is no matching header account in excel then what to do? I mean we will have a std format in excel for PL and BS for all cos.. But in tally the head would be different for different cos..
Answer
1) Yes, active company (appears in bold letters if multiple companies are opened)
2) just add ,0 to XLOOKUP (4th parameter of function) to display zero instead of #N/A, which takes care of this situation. But any extra ledger in Tally needs to be added manually in Excel
@@ExcelKida
Can v create concept of grouping?
This ll help us automate the whole process irrespective of the type of TB..
Where v can classify specific expense type to a specific group say printing and stationery, postage and telegram, Fixed asset, current asset etc..
Which will enable us to keep a std form (by using this group) for PL and BS irrespective of what are the contents of TB..
If u can do a video on this it ll b really helpful..
Yes, for that an in-depth webinar will be taken. Currently you can apply filter to power query result
@@ExcelKida Sir When webinar on grouping is held ? Any link of webinar available?
Secondly can this be applied to group company _ Head office and branch merged together
Hi pl provide your whatsapp no
Hey, I got the following error- DataSource.Error: Unable to connect to the remote server
Details:
localhost:9000. How to fix it?
You need to enable Tally XML Server feature from Tally
Change it like this:
Gateway of Tally > Configure > Advance Configuration > Tally.ERP 9 is acting as = Both
@@ExcelKida thank you 🙂
Connecting via ODBC is also an option. Do you know how we can export the entire balance sheet generated by tally in excel , via PQ ? If I create a new ledger in tally say under indirect expenses , the same will not automatically update in excel and will require manual intervention.
ODBC suffers many limitation. Also it can extract only specific information which tally had made available. For Balance Sheet, I have already demonstrated video of Trial Balance via Google Sheet, which you can do even in Power BI using HTTP.Content function. It is a bit tricky, but achievable. But personally I prefer Google Sheet because for same you will need Excel 2016+
when we export the P and L report data in xml then some values showing in the minus and some in plus - is any specific reason .
+ Credit
- Debit
is there any power query version which works for all Tally versions (including oldest to newest)..???? for linking data..??
The video on which you have asked comment works for both Tally ERP / Tally Prime
@@ExcelKida I've Tally Gold also,on which its not working properly,i.e. all accounts names are appearing as wild charactors in single cell,in power query
Am using Tally ERP 9 and the configuration is set on port 9000. My error seems to be stemming from xml request on the body component. When I try to run the power query code, it returns this error- Could not find Company ''
Tally.ERP 9 not supported. You will have to upgrade to Tally Prime
Ver nice video and technique.
In my case I am getting all ledgers in trial balance having 0 balances also.How to remove them?
And for group company created in tally -- can we use this to prepare consolidated fin statements of
group company open in tally?
You can apply filter of Amount not equal to zero in Power Query
Group Company is a advance scenario. Yes it can be done, but requires specific customisation on case to case basis
Sir can you tell how to get fin statement of group company.Branch and HO two companies in tally. How to use this method to make their consolidated Bal sheet
I have received this type of query from many people. So, now I am planning to upload a Trial Balance query where you will have to specify target company of Tally
Hi in your code credit is positive and debit is negative.. If want reverse that is credit should be negative and debit should be positive.. Can i do that by changing some part of coding
3 ways to do this
1) In power query I have constructed large XML to fetch data from Tally where Closing Balance is given +/- sign, just reverse those sign (search closing word in that XML)
2) Don't change power query, instead insert one column in excel after data is loaded as, =-ClosingBalance field. Since it is dynamic table, Excel will automatically apply it to all rows each time you refresh data. Link that new column
3) Just change prefix - sign in front of VLOOKUP/XLOOKUP (easiest way)
Sir, very nice video.
how do i add alias in trial balance as separate column
will u please help because my financial is link with alias which is numeric code given to all ledger. so i will highly appreciate in trial balance along with ledger & group i also get alias as i have put all my numeric ledger code in alias.
Alias field I was not able to find in Tally Database Structure (may be they are using some different name internally for it). Will update you if I figure it out (in some Power Query). So, just keep an eye on that project
@@ExcelKida Thank you sir
Is it possible to link data up to particular period, say 01 Apr to 30Sep?
Yes, just change from/to date in Power Query
Good work i need to take all the stock item and closing balance is its possible.?
There is a Power Query for stock extraction in my open source project, which you can use to fetch all stock items, opening, closing, inward, outward etc (refer URL below)
github.com/dhananjay1405/excelkida-power-query-library/tree/master/tally
Hi sir, can you provide query to generate realtime "billwise summay of sundry debtor outstanding" in excel, thanks & regards
This realtime linking method does not support this
Excellent video sir thank you very much. It is work in only office 2016 and above version right? And i want to do this for maintaining a Budget vs expenses monthly basis is it possible
I didn't saw any specific field in ledger table of tally maintaining figure of budgeted amount. So it seems tally is processing data of many other tables to achieve this. This method is very lightweight & suffers many limitations
Hi can we change from date also like presently this code is written for annual trial balance what if we want monthly or quarterly trial balance
Yes, this thing in on my roadmap of future videos, I will be including From parameter too, as it will deal on preparing comparative statement in 1 click. Separate power query will be uploaded on my open source project once video is ready
@@ExcelKida will await!
Hi can you share power query for qtrly TB
Power Query for Trial Balance for a period range has been uploaded. You will find a file by name "trial-balance-for-period.pq" (link given below)
github.com/dhananjay1405/excelkida-power-query-library/tree/master/tally
Just ensure to change both, From and To value, in that power query with your own dates you want
Can we extract Sales & purchase register data into excel?
You can extract transactions, but not specific Sales/Purchase register report like displayed by Tally. I will try if XML commands can be sent to Tally for exporting both these reports
Sir what will happen if we are running 2 tally sessions and different companies which data excel will get fetch
Things that happen in this in 2 different scenario
1) If you open 2 different companies in a single Tally instance - Data of active company will get fetched
2) If you open 2 instances of Tally with companies selected - Tally XML Server will crash & nothing will be fetched (because same port cannot be used twice by Tally)
Hi can we also extract columnar sales report?
I am currently trying to explore this, if we can even grab the Purchase / Sales register from Tally XML Server. Their TDL documentation for XML data reports extract commands is a bit vague, so I was able to harness very limited data. Will definately post power query / video of this when I get solution to this
@@ExcelKida thanks...
Sir will this code work on tally prime too
Yes, because XML Server capability is same for both Tally ERP 9 & Tally Prime
I have tried to trail balance via Power query using your code. But after sometime I have tried the same with same excel file but it fetched with only one entry as profit & loss a/c. Even after using new sheet produce same result. Why?
Then I tried the same query code for Group Company in latest version of tallyerp. That too produced the same one line entry as p/l a/c only. I don't know where the error occurred. I am using latest MS office 365 updated Oct2020. kindly help me to solve this issue
Otherwise what if the same trial balance in text format to power query? That code may also helpful for me. Thank you
Please reply
1) I have excluded company name in Power Query (to keep it dynamic), so Tally will fetch Ledger balances of company, which is active.
2) Also, sometimes Power Query loads value from cache, but they give refresh button to discard that result and re-call for fresh data.
3) Whenever you create a new company in Tally by default there is Cash & Profit/Loss Ledger, so this may even indicate some blank company is being fetched
Even if that does not resolve, feel free to whatsapp me. I am even planning for a Google Group, for such discussion, so that even others who may have faced such issue, get access to our discussion & solutions on this
Hi Sir, This is very helpful.
Thanks a lot
My query is how can we date selection dynamic like I enter date in one cell in Excel and power query generate trial balance based on that this will be very helpful if possible as we don't need to go to query advance editor and change the date.
Yes you can, but for that you need to manually tweak Power Query M Query scripting, which is tricky. I prefer keeping my video max possible simple, as already this is technical thing which not everyone is aware of. M Query itself is a big topic. So to keep video understandable to majority viewer, I demonstrate simple things only.
Sir what if a company has a different type of ledger and spell mistakes
You need to streamline ledger names then. It's a one time activity, but it's benefits are long lasting as you progress in future.
@@ExcelKida Thank you Dhananjay Sir Ji
Please make video on tds working real time linking because every month we have to do working
Any way out to exclude ledgers with zero balance
There is a filter icon in Power Query, choose not equal to zero
Can we automate cost center report form tally
No, not using this technique. For that Tally to Database Server approach is required.
Keep an eye on the channel, I will be creating a video for all such reports in future
@@ExcelKida okay, thanks I am waiting
Hi Sir, your videos are wonderful. I am having challenge in pulling the groups in order of levels. Like I have created groups in order of Current Assets > Loans & Advance > Travel Advances > then two separate groups of Local Staff Advances and Expat Staff Advances. While pulling the data to excel Travel Advances group is missing, only Local Staff Advances and Expat staff Advances and Primary group as Loans and advances is coming. How could I pull the group Travel Advance. Also Sundry Creditors / Debtors are coming as Primary and Secondary Group instead of Current Asset and Sundry Debtor / Creditor. Thanks a lot
Sundry Debtor / Creditor will be primary group only, Current Assets is a very vague description. Current Asset will be the first level parent not primary group (just think from reporting point-of-view to understand its true meaning).
Go to Display > List of Accounts > Group, if you are able to locate your Travel Advance group their, then it group-table power query will pull it for sure
Sir Can We add PAN Number And GSTIN Filed in TB Report?
Yes you can. But for that you will have to carefully modify the XML portion of text to include both of them, which is quite tricky & prone to error. Instead I will suggest, you the my Tally to Database utility, to generate CSV files of Tally data. That utility generates a csv file named "mst_ledger.csv" where you would find PAN & GSTN field.
Video link for the same
ruclips.net/video/qRxMwrmp7MA/видео.html
Just follow the steps till first 5 min for generation of CSV files
Utility can be download from the page
github.com/dhananjay1405/tally-database-loader
Thank u so much sir. I am also need to grab data from GSTR3B & GSTR- 1 to excel. How can do sir? Pls help me
For GSTR-1 video is already on Excel Kida channel, please browse/search it from video list. That video will also be applicable for GSTR-2A
GSTR-3B import was demonstrated in private webinar, as it is PDF import & is a bit lengthy
@@ExcelKida ok sir, can we import in fixed excel template ?
@@ExcelKida can please share GSTR 3B private webinar link
Private webinar are announced on official FB page
facebook.com/excelkida
I am planning for short video for GSTR-3B PDF import. GST website itself summerizes 3B in annual button menu. Power Query is required only if you want to dig into each & every item of 3B for complete year analysis
@@ExcelKida Thank u sir
Hello. The idea to link tally live with PQ is great. Thanks. When I try the code now in PQ to fetch the Trial Balance, I could not get the correct values! Appreciate if you can help!
What error exactly you are getting ?
Can we fetch monthly data as on particular date also with this ?
yes definately, there is a power query in my project, which can fetch Account Ledger balances of specific date range. You can utilize that Power Query for monthly fetching of balances (it also has Opening, Net Debit, Net Credit fields)
I need this power query how to pull on a particular date balance, can you provide me ?
Just modify toDate variable in that Power Query to achieve this
Can we import all tally transactions into excel?
You can import via Power Query accounting transactions from Tally into Excel, via XML format into Excel. That video is in my future list. But let me share, this process sometimes crashes excel due to heavy RAM usage
Excel Kida thank you
Wonderful!
Sir, can we extract day book using power query?
Yes you can, but result obtained is in tree format (in XML), which power query cannot easily convert to tabular format. I have also seen power query crashing sometimes when tally gives huge data
@@ExcelKida can we have small example video sir?
kya purchase ya sales register ko tally ki selected date ke anusar laya ja sakta hai, ya kewal power query ke andar jakar hi date select kar sakte hai?
No, for that you will require Google Sheet to Google BigQuery sync. Shortly I will be creating that video too, where you can generate purchase/sale register
hi sir can u advise bill date in tally
Didn't get the question? Please describe in detail (you can DM on number given at the end of video)
Can it be possible in Tally Prime? And can it work with different trial balances open in Tally?
Yes it works in Tally Prime because core functionality is not changed in Tally Prime. Give it a try
@@ExcelKida i have tried but only the same trial balance extracted which was the first extracted one even i changed the company. How can it be applied to different company?
Can we get outstanding payment from tally using power query …. With Gross Value, tds amt and net payable etc.
Never tried. I will update the GitHub project with Power Query if I am able to do this successfully
Doesn't work Tally Prime 2.01
Yes it's a known bug of Tally. I have already reported it to Tally Solutions, and they have said they will try to correct it in next version. You need to revert back to version 2.0.0, where it works perfectly.
Sir, can you please share power query based on Tally Prime?
It runs for both Tally Prime & Tally ERP.9, as Tally has not changed XML Server capability in Prime
Pls give inventory power query link with excell
I will definitely upload Power Query for accesing inventory table of tally. Currently experimenting with it
Check out Stock Summary (actually it is detailed) Power Query added in my project, which will can fetch stock items
github.com/dhananjay1405/excelkida-power-query-library/blob/master/tally/stock-summary-for-period.pq
will try your code to fetch, Good work!
Do give it a try, and keep visiting GitHub project, I will add few more power queries
Waiting for your Realtime Daybook.........
Day book is a heavy thing for Excel (as output is in tree format), so I am figuring out, how it can be converted to tabular format using TDL
Doesn't work with Tally Prime
It works perfectly for my Tally Prime. Ensure that you don't open Tally ERP & Prime both at the same time
It's tally to excel but not *Excel to Tally*
I would say Google Sheet to Tally open-source project is upcoming. I am not planning to support Excel as of now
first of all
Thank you very much to provide very good and very useful video with free of cost
is it no possible transaction detail trial balance it displayed only
if it is possible please tell me how it is possible
I am waiting for your positive replay (if possible please share your mobile number i want to talk with your sir)
If you want to pull transaction of individual ledgers using Power Query, video is already available on channel
ruclips.net/video/-p0nt_d6V2E/видео.html
If you go through the Power Query project, I have even uploaded one Power Query, which can fetch Trial Balance for specific period with Op Bal, Net Debit, Net Credit, Clo Bal. Check out this Power Query on my open-source project
github.com/dhananjay1405/excelkida-power-query-library/tree/master/tally
You will not find video for above Power Query of TB for specific period, as I figured out this approach a bit late. But this Power Query is almost similar. Try this. My mobile & email is mentioned at the end of video
9028463366 / info@excelkida.com
Sir how to get party GSTIN in addition to this information?
for that, I will insist to use Tally to Database utility, where I have already defined this field in mst_ledger table. I have deliberately kept this method simple, since it has limited purpose of preparing Financials, which has nothing to do with GST Numbers. Your requirement is of MIS, for which utility is there.