Skip to main content
J
jparker24
August 27, 2020
Solved

Trial Balance by Class

  • August 27, 2020
  • 3 replies
  • 0 views

I 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.

Best answer by ASBSARA

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

3 replies

M
mv32
August 27, 2020

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:

  1. Go to the Reports menu and then select Accountant & Taxes, then Journal.
  2. Click the Customize Report button.
  3. On the Search Columns box, type Class and then put a checkmark.
  4. Click OK to save it.

 

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!

    J
    jparker24Author
    August 27, 2020

    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?

    MaryLandT
    MaryLandT
    August 27, 2020

    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:

    1. Open the report you need.
    2. Click Excel on the toolbar, then choose Create New Worksheet or Update Existing Worksheet. 
    3. Select the Advanced button to open the  Advanced Excel Options window. 
    4. Check or uncheck the boxes under Excel Options to remove or add those features.
    5. Click OK, then Export.

    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.

      A
      ASBSARAAnswer
      November 17, 2020

      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

      J
      jparker24Author
      November 17, 2020

      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.

      A
      AAgrawal121
      January 8, 2024

      Hey @jparker24 

      We have built a tool to transform such reports as per requirement for further analysis. If you have a use case, we can get on a call and you can use our tool. 

      R
      RoseBetter
      April 20, 2023

      I needed to come up with a way of exporting this, as we are migrating accounting systems and wanted to import the TBs by class - this works for PL only (BS would have to be run from the beginning of your balance sheet...). 

       

      follow these steps: 

      1. Report---> my accountant----> "transaction detail by account" (I switch to classic view).

      2. Enter the period you want for the TB (remember PL needs to be from the start of the financial period to the date you want the TB as at).

      3. click the settings wheel and have only the following ticked: debits, credits, class, and account number. (untick everything else).

      4. Then export this data to excel, highlight all and unmerge.

      5. filter for "totals" and delete all these rows

      6. Then copy down the GL codes into the blank cells - if you follow this formula step here it takes 2 seconds!

      https://www.excelcampus.com/functions/fill-down-blank-cells/

      7. I would then tidy up the data by copying and pasting values, then filtering on both debits and credits at the same time and delete any rows with nil in the the columns. 

      8. Then simply do a pivot on the nominal code and the class in tabular formatting -- you will have a TB by class, this way. 

       

      Formatting takes about 5-10mins. 

       

      You can double check everything by doing sumifs to the normal TB report. Again, I have just done this for the PL codes--- the BS codes were taken from just the TB report. 

       

      Hope this helps!! TIP: if you're going to be doing loads of these (we had to do 4 years worth) then I would format the report in quickbooks then save it to your custom reports so you're only having to change the date each time you run it. 

      Terms & ConditionsAccessibility statement