Connect with and learn from others in the QuickBooks Community.
Join nowI am a QB desktop user and need to modify the trial balance report. I want to use the raw data contained in the trial balance report to be the underlying data in a financial model. I need the individual rows where the account and debit/credit information is contained to include at a minimum Class, but date would also be helpful.
What other reports have QB users found to be helpful for downloading historical financial data and loading it into a projection model?
One suggestion I found online suggested running the IS & BS detail reports and combining them to get a trial balance proxy with class. The issue i'm running into with this is the report formatting when I export to excel. There's too many rows and columns with subtotals that make rolling this into a pivot table impossible.
Solved! Go to Solution.
You may be done with your project by now, but you can do this in seconds with a 3rd party app.
QuickBooks does not have a Trial Balance that separates out the expenses by class name.
The Trial Balance is not in one column where Debits are Positive and Credits are Negative.
The account numbers are embedded with the account name.
This makes data analysis clunky and time-consuming.
I am an accountant who wrote a utility called TBX Trial Balance Exporter back in 2004, which gives you these options. I don't see any sign that the T/B will be changed in Desktop.
Here is a link to the QuickBooks Desktop App page: Enjoy!
https://desktop.apps.com/apps/167772/tbx-trial-balance-exporter#!overview
Hi there, @jparker24.
Let me share some insights on how to get this kind of report in QuickBooks.
Instead of running the Trial Balance report, you can pull up the Journal reports showing the underlying Class and especially to show up the dates. This report also contains the debits and credits of the account.
Here's how:
Once done, you will now view everything else you need for the report.
I'll add this article as a reference on how to personalize a report in QBDT: Customize reports in QuickBooks Desktop. It includes links that cover basic information about reports like setting up and modify Scheduled reports.
In case you wanted to remove other columns for a cleaner view, you can follow the steps above and uncheck the column names.
Feel free to tag me (@Jovychris_A) if you need further assistance in pulling up QuickBooks reports. I'm always here to help. Stay safe and well!
Thank you, this is very helpful. My only issue with this report now, is that when i go to export to excel, the line breaks inbetween transaction numbers make this again not usable in a pivot table.
Based on my reading, it seems like the best way to get a proxy trial balance that includes class is to download the PL and BS detail reports and exclude net income. These also come with terrible formatting for a data dump to excel.
Do you know of a way to change report formats in QB so excel downloads work cleanly?
Allow me to provide additional information about customizing reports in QuickBooks Desktop (QBDT), jparker24.
QBDT allows you to format the data you want to see when you export a report to Excel. To do so, you'll need to use the Advanced Excel Options feature to control the look on your statement.
Let me show you how:
I've added this page: Export reports as Excel workbooks for more details on how to format a statement before exporting it.
Stay in touch if you need additional information by commenting below. I'm always glad to help.
Thank you for these replies. I'm getting much closer to what I need, but I'm still not quite there yet. My issue lies with these line breaks for subtotals. I only care about the individual entries. Manually deleting breaks in order to turn this into a pivot table isn't a long term solution. Thanks to all the other guidance I'm now able to manipulate the columns how I need, but I'm still hung up on these subtotals.
Thanks for getting back to us, @jparker24.
We're unable to remove the line breaks for the subtotals on the Journal report. Though, we can run the Transaction Detail by Account report. This way, the line breaks will no longer appear in Excel.
Here's how:
Then, we can now export the report to excel. I'll guide you how:
I've included a reference below:
Here's a guide in exporting your reports to excel from QuickBooks Desktop: Export reports as Excel workbooks in QuickBooks Desktop.
You may also want to consider memorizing reports. Doing so helps save the current customized settings for easy access in the future.
Stay in touch with us if you have other QuickBooks related concerns. We're always here to help you.
The excel output still has row totals by account. This will not load properly into a pivot table. I agree that the transaction detail by account is 99% of the way there. I just need a simple way to download the raw data without any row breaks for totals and subtotals. My columns include Trans #, Type, Date, Num, Name, Memo, Paid Through, Account, Class, Clr, Split, Debit, Credit, Balance and Account Type.
You may be done with your project by now, but you can do this in seconds with a 3rd party app.
QuickBooks does not have a Trial Balance that separates out the expenses by class name.
The Trial Balance is not in one column where Debits are Positive and Credits are Negative.
The account numbers are embedded with the account name.
This makes data analysis clunky and time-consuming.
I am an accountant who wrote a utility called TBX Trial Balance Exporter back in 2004, which gives you these options. I don't see any sign that the T/B will be changed in Desktop.
Here is a link to the QuickBooks Desktop App page: Enjoy!
https://desktop.apps.com/apps/167772/tbx-trial-balance-exporter#!overview
Thank you for this. I wound up just pressing ahead with the transaction detail report and manipulating the outputs to work for me. It's still shocking that QB wouldn't make a tool for this. Exporting the trial balance with class and date information is a common way to load historical financial information into projection models.