Finance | Project Operations, Human Resources, ...

auto pull TB balances into excel from Dynamics via add in

(1) Share

Report

Posted on by DB-04061420-0

Hi,

Recently we have started exploring more and more the full functionality of Dynamics 365. One thing that we have not been able to find is a way to automatically pull a trial balance amount based off the entity, period, and account via a formula or other automatic driver directly into excel. Does anyone know a way to do this?

I have the same question (0)

All responses (5)

Answers (0)

Frank Hamelly | MVP... 46,625 Moderator on at

Like (0)

Report
Copy link

Link copied!

What do you mean by "automatically"? Completely automated? Why do you need to do this? There may be multiple options.

Was this reply helpful? Yes No
DB-04061420-0 8 on at

Like (0)

Report
Copy link

Link copied!

@Frank Hamelly - By automatically I mean by hitting the refresh button or updating the inputs. Ideally every excel spreadsheet should have the following set up:

A B

1 Entity Entity A

2 TB Account 123456

3 Month April

4 Year 2025

It would then be able to pull in the balance from the correct entity and account

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Moderator on at

Like (1)

Report
Copy link

Link copied!

I see. Here are a few options you'll want to explore:

1. Create a trial balance report in Financial reporter. Generate it and export to Excel. This isn't real time or near-real time as the data in FR gets updated about 1x hourly.

2. Check out the "Trial balance with transaction details" report. This is refreshed every time you run it and renders in Excel. It's under General ledger Inquiries and reports. It's an Electronic reporting report and requires a bit of config in ER to use it.

3. Also look at "Business Performance Analytics". This is a new feature from MSFT that provides canned Power BI reports in a report portal. The reports are refreshed upon running them and can be exported to Excel. The canned reports can also be copied and modified to suit your needs. I understand Excel reporting will also be a feature in BPA, but it doesn't seem be present yet. What is Business performance analytics - Finance | Dynamics 365 | Microsoft Learn

Was this reply helpful? Yes No
DB-04061420-0 8 on at

Like (0)

Report
Copy link

Link copied!

Hi Frank,

Appreciate you taking the time to outline those steps for me. It is not quite what I am looking for. With other ERP systems there is typically a plug in that would allow for balances to be pulled into excel spreadsheets without going through all of those steps. Typically there is a set up similar to the screenshot below that would then pull in the May 2024 balance for account 123456 from entity 1000. Is this something maybe to set up a query for?

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Moderator on at

Like (0)

Report
Copy link

Link copied!

No such plug-in exists for F&O Daniel, unfortunately. Trial balance queries in F&O are notably complex because TB data isn't stored anywhere. The balances are calculated on the fly each time you generate the TB.

Was this reply helpful? Yes No