Page 1 of 1

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

PostPosted: 3-Oct-2019 2:01am
by lenraphael
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.

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

PostPosted: 3-Oct-2019 1:48pm
by TheAnswerMan
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.

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

PostPosted: 6-Oct-2019 7:36pm
by makbo
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.

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

PostPosted: 7-Oct-2019 7:26am
by sjrcpa
Yes. I use a trial balance for virtually all business and nonprofit tax returns.

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

PostPosted: 7-Oct-2019 8:25am
by lenraphael
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.