Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

This is a modified Dynamics GP report (see download) I’ve provided to my clients for years that can be exported to a text file and imported into excel that includes the customer ID, name, and the aging columns. Through GP security, you can only have one modified report for each user and company if you’re running a shared modified reports dictionary for each workstation. What this means is if you already have an existing modified historical aged trial balance detail report you may have to provide a separate login or shortcut to this report to have access to both. Here’s a screenshot of the report in excel.

You have to add totals and grand total line, be sure to exclude the “Original Amount” column.

Payables:

Download report

 

Receivables:

Download report

 

After you download the package(s) from above by right-clicking and choose ‘save as’, you can import it in version 10, 2010, 2013 by going to Microsoft Dynamics GP>>tools>>customize>>customization maintenance and choose import. Before doing so, be sure to back up your modified reports to ensure nothing is overwritten.

If you need the summary trial balance export packages just create a comment below and I will email them to you.

This entry was posted in Dynamics GP. Bookmark the permalink.

114 Responses to Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

  1. Rebecca Walker says:

    Hi Steve,

    I have tried to import the file as explained, but am getting an error stating “unable to open customizations dictionary”. Do you have any thoughts on how to correct this as I need to pull the historical TB for AR and AP into excel and your package seems to be the most efficient I have found.

  2. Steve says:

    You can’t have other users in the reports dictionary you are importing to. This means if you are sharing a single reports dictionary across all users everyone has to be out of GP during the import process, that’s usually why that error message comes occurs.

  3. Rebecca Walker says:

    Thanks. I was able to import after your assistance, however, my report doesn’t show as yours does. I get a messy report, and assume it is a formatting issue.

  4. Rebecca Walker says:

    How do I get this to display as you have above? I get a bunch of lines that do not format as your example above.

    As it stands, I have to go in and reorganize the entire excel spreadsheet and this will take many hours of time. My apology, but this has been the most difficult thing ever.

  5. Steve Reese says:

    Rebecca,
    Assuming you imported the package correctly and set security, you should not have to do any formatting – the data should look just like the screenshot in my post. My guess is you’re choosing the wrong format (delimiter) when exporting to GP or when importing from excel. Here are some things to try – when exporting from GP on the report destination window the file format should be tab or comma delimited, when importing to excel be sure to use the same delimiter. Open excel first, then choose the file to start the import wizard. I prefer tab-delimited.

    Steve

  6. Leyna says:

    Hi Steve,

    Thank you very much for this information! I tried to download the AR aged balance package by clicking on the download button. However, when clicking on it, it does appear some sort of script on screen letting me to save it only in txt and html format”
    Would you have any tips to download this package properly” Thank you very much for your information!

  7. Mario Fiamenghi says:

    HI Steve

    Thanks for providing the reports. I have created the summary payable and receivable my self. Please let me know if you want a copy to share.

    Kindly

  8. Andrew says:

    Hi Steve,

    Once you have downloaded the report and have it imported into Excel how do you run this report?

    Thanks,
    Andrew

  9. David says:

    Hi Steve,

    Would you mind sending the summary trial balance export packages.

    Thank you!
    David

  10. Becky Siebert says:

    Hi –

    We have GP v2015. I would love to be able to get the AP Historical Summary TB that could be printable in Excel please, please, please!!!!

    Thank you!

  11. Steve says:

    Andrew, sorry I missed your comment. The file you download from my website is a modified version of the historical aged trial balance called a “package”. You need to import it to GP from the customization maintenance under tools/customization window. From that point you will edit security to run the report to a delimited file format that you can import into excel.

    Sincerely,
    Steve

  12. Collin says:

    Hi Steve,

    This looks like a great resource for Reconciliation. You had mentioned above to comment to request a Summary Trial Balance. Can you email these packages? Thanks!

  13. shifan says:

    Hi Steve,
    Could you please send me the summary trial balance reports for AR and AP? Thanks so much

  14. Justin says:

    Hi Steve,

    Can you please send me the Payables Summary Historical Aged Trial Balance export package?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *