Questions? We have answers.

QuickBooks Online: Transform the general ledger

  • Updated

Summary

This guide goes through the step by step process of transforming the QuickBooks Online Journal report into a format suitable for ingestion into MindBridge.


Notes/Prerequisites

This guide presumes that data has been exported from QuickBooks Online in the following format, as detailed in our QuickBooks Online: Export the general ledger.

mceclip1.png

 


Transform the data

The Problems

  1. In order to ingest a file into MindBridge, the data needs to be in an ungrouped format. This means that the columns have data present on every row. A QuickBooks Online Journal report is grouped by transaction, which means this missing data needs to be populated.
  2. The Account ID is a mandatory field in MindBridge, however there aren't always account numbers present in QuickBooks Online. Where the account number isn't present, we'll need to use the account name instead.
  3. There are subtotal rows present which cannot be utilized by MindBridge. These will need to be removed.

The Solution

  1. Populate the Date, Transaction Type, No. and Name fields with the relevant data from the rows above.
  2. Fill in the blank Account Number values with the values from Account.
  3. Exclude extraneous total/subtotal/blank rows.

Items of Note

  • We will put the revised columns at the end of the file.
  • Date and Transaction Type are always present on the first row of the transaction group, but No. and Name are sometimes blank for a group. See row 2 in the example above.
  • Our logic will be to take the value from the original column if we are on the first row of the transaction, or the previous value we found if we are not on the first row of the transaction.

Ungroup column Date

mceclip2.png

=IF(LEN($B2)>0,IF(LEN(B2)>0,B2,""),M1)
  • LEN($B2)>0

    tests if there is a value in column B by testing if the length is greater than 0. Since as we observed, Date is always present on the first row of the transaction group, this serves as our check if we are on the first row of the transaction. We put $ in front of the B for ease of copying the formula to the rest of the columns to drag down (Transaction Type, No., Name), as we will always be checking the presence of Date to check if we are on the first row of the transaction.

  • IF(LEN(B2)>0,B2,"")

    returns the date if it is present, "" (empty string) if it is not. This can be simplified to simply B2 for the Date column because the Date is always present on the first row of the transaction group, but this logic is used to be able to copy and paste the same formulas for No. and Name, which, as noted above, are not always present. Because Excel will display 0 instead of blank when referencing an empty cell, we have to tell it to return "" in order for it to display an empty cell.

  • We then need to copy this formula down, either using the fill handle or by copying the formula, selecting the empty cells and pasting the formula in.

Ungroup columns Transaction Type, No. & Name

mceclip3.png

Formulas in N2, O2 & P2 as follows:

=IF(LEN($B2)>0,IF(LEN(C2)>0,C2,""),N1)
=IF(LEN($B2)>0,IF(LEN(D2)>0,D2,""),O1)
=IF(LEN($B2)>0,IF(LEN(E2)>0,E2,""),P1)

 

Revised Account No.

Our logic to fill in the blanks for Account No. in column Q will be to take the original Account No. from column G if it is present (length is greater than 0), Account from column H if it is not (length is not greater than 0):

mceclip4.png

=IF(LEN(G2)>0,G2,H2)

 

Ungroup columns Created, Created By

Using the same logic as before where the date in column B is always present for the transaction group. The Created column utilizes a date format that MindBridge cannot ingest, so we will use a formula to extract just the date value from this string.

mceclip6.png

=LEFT(IF(LEN($B2)>0,IF(LEN(K2)>0,K2,""),R1),10)

 

Filling the blank values in Debit and Credit columns

MindBridge does not support the blank values left in the debit and credit columns, they must be populated with a number. In order to fix this we can select all the blank cells by highlighting columns I & J, hitting ctrl+g and clicking special. We can then select Blanks, type 0 and hit ctrl+Enter to fill all the empty cells with a zero.

mceclip7.png

 

Filter to keep only transaction rows

We observe that every transaction row in the original file has an Account. So with filters turned on, filter out blanks from column H (Account):

mceclip5.png

 

Copy and paste values into a new workbook

Starting at cell B1, hit Ctrl-Shift-End to get to the end of the file, and adjust the position as necessary), then copy, then paste special values in a new workbook. You can then delete any duplicate grouped columns. In the end you will be left with a worksheet that looks like the below:

mceclip8.png

 

The formatting is now complete and the data is ready to be ingested into MindBridge. You can find the link to our import guide below which will go into more detail about column mapping and transaction ID generation.

 


Previous Step: QuickBooks Online: Export the general ledger

Next Step: QuickBooks Online: Import the general ledger into MindBridge

 


Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.

Was this article helpful?