How to parse QB trial balance excel export GL acct #'s

Key tips and advice the working tax pro can use.
#1
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
When QB and probably QBO export trial balance reports, they append the account title to the gl account number (if you use account numbers in that QB client)

A lot of trial balance apps require gl account numbers or at least prefer them.

Every so often I try to use either TR's WPCS or Go System TB to send trial balance numbers directly to business tax returns.

The TR WPCS when running in Virtual Office (aka VO cloud server based) has issues with directly importing any data from QB because the VO WPCS is not running on same machine as QB Desktop and has to open QB Desktop. Theres a standalone utility, but that seems to have issues too.

The QB clients I have don't use fixed length account numbers.

I'm thinking that the only way to parse easily would be to manually insert a separator character such as comma or hyphen after the gl account number, and then use the Excel function to split that column into two separate ones, one with just the account number.
 

#2
Posts:
99
Joined:
14-May-2019 3:57pm
Location:
Idaho
I haven't done it in a while in QB, but I used to use the little dot · between account number and account name and then do a text to column based on that dot. It usually worked well as long as there were not a lot of sub accounts.
 

#3
makbo  
Posts:
6840
Joined:
23-Apr-2014 3:44pm
Location:
In The Counting House
I find it funny that I was once asked, less than ten years ago, what a "trial balance" was in a tax prep job interview, and I didn't have a precise definition available at the tip of my tongue (and incidentally, glad I didn't get the job anyway, but that's another story). Does anyone really care about "trial balance" anymore, in this day and age of online double entry accounting programs? I thought it was only of concern to paper-and-pencil accountants.
 

#4
sjrcpa  
Posts:
6471
Joined:
23-Apr-2014 5:27pm
Location:
Maryland
Yes. I use a trial balance for virtually all business and nonprofit tax returns.
 

#5
Posts:
886
Joined:
26-Feb-2016 10:14pm
Location:
Oakland CA
TAM, I totally overlooked that little dot as a separator to parse on. Will try that. Meanwhile, i did get the TR utility to work to import directly from QBD. When it succeeded, a wierd QBD certificate screen opens up that you have to accept. It's something that gets triggered the first time one accessess that client for that version of QBD. Also found in preferences, company, data integration. Never knew that existed.

Makbo, i used to be like you. A friend with a financial audit background, originally from Canada where back in the day had won the "Haskins Sells" gold medal for highest score on the chartered accountant exam, always used the old ATB DOS app for every tax return, except for simplest individual ones. He even had an arcane way of using ATB as a timekeeping app.

But everytime i prepare a return that needs a balance and I skip doing a trial balance, I take more time futzing with the input with side calcs, then it would taken to whip out a trial balance in Excel.
 


Return to Tax Prep: Important tips and advice



Who is online

Users browsing this forum: No registered users and 11 guests