Reconstruct QuickBooks Trial Balance with Perl

perl

perl (Photo credit: chillicheese)

This post is long past due.  Mainly want to get this out there so Google’s interweb crawlers can do their job and get this stuff indexed.

At my previous gig, for a start-up, we ran a fairly complex business / legal entity structure all on Intuit’s QuickBooks.  Something like 7 legal entities, international, tax structure, blah blah.  Like most start-ups, we were evolving very quickly, so scoping a “Big ERP” just didn’t make sense.  It was a project on the roadmap, but we needed a stop-gap.

We still had fairly complex needs in terms of running a consolidation (beyond the standard default “combining” functionality), setting up management dash boards, and the like.  Something like Oracle’s Hyperion was out of the question.  A company by the name of Jedox (German outfit) had a Business Intelligence platform called Palo that fitted the bill. Check Jedox out here.  The MS Excel integration is astonishing.  For accountants, the stuff dreams are made of.  And they open source the down-revs of Palo, while the newest release is commercial.  Great model.  Of course, check out Pentaho and Mondrian (here and here).  Likely, I’ll try Pentaho stuff going forward.

Anyway, this left us with the challenge of getting the QB data into the Palo cubes.  With QB Enterprise release 11, along with other minor changes (mainly just moving buttons around), they opened up their back-end DB for ODBC access.  Bingo – that meant we could write queries and stuff and extract info.  I was more excited than a nerd with a new pocket protector.

Then the next phase hit us.  We had to deconstruct how the heck QB built the trial-balance.  Searching Google yielded nothing informative.  Intuit’s forums were even more disappointing.  There was actually some stuff very, very disappointing there … thank god for TurboTax … but I digress …

So we began the process of “figuring it out.”

Some quick things to realize about how QB works internally:

  • Not surprising, everything has a sub-ledger.  Checks are in a sub-ledger.  Bills are in a sub-ledger.  Everything has a sub-ledger.  Refreshingly, still double entry accounting tho.
  • Conceptually, there is no “trial-balance” defined in the QB database.  Nor Balance Sheet, nor P&L, etc.   Basically, you are on your own for reporting.
  • So, as best I can tell, all reports are recreated each time from scratch based on the sub-ledger activity.  PITA because you have to build a query for every sub-ledger, but fine.  And they do not seem to expose the logic for determining which sub-ledgers are linked to which reports (but maybe we just gave up too early).  This is something you have to identify based on your business and how you use QB.

Armed with that introspection muscle,  you can then apply some fundamental accounting logic:

  • The balance for a balance sheet account as of a certain date is the sum of all historical debits and credits from all of the related sub-ledgers.  To illustrate, at least two sub-ledgers could impact cash: general journals and check disbursements.  So both sub-ledgers need to be considered when determining the cash balance for the TB.
  • The “income statement” accounts are the net debit/credit sum for each account, but only for the period being queried.
  • For the final calculation – all income statement accounts (in aggregate) need to be summed, since inception, and reflected in your retained earnings total as of the date you are generating the report (i.e. month-end).  If your company started in June 2004 and you are reporting October 2012, you’d sum all debits / credits from 6/1/2004 to 10/31/2012.
  • I find it easiest (from a systems perspective) to sign all asset accounts as positive, and all liability / equity accounts as negative.  With this method, you can quickly identify “balance” by summing all balance sheet accounts (including R/E), and seeing they equal zero.  If you don’t sum to zero, you missed a sub-ledger.

Always remember that each company is a little different, and each company will use ledgers differently (i.e. fixed assets, or AP modules etc.).  But this is the basic recipe.  We found it useful to generate the entire P&L detail for the purposes of identifying how ledgers impacted the trial balance report.  To do this, put a date range from since the beginning of time through today, and then double-click on the net income number.  This generates a massive P&L detail.  But the default report will give you a hint as to what “sub-ledger” it pulled the activity from for the purposes of generating the report (look at the “source” column).  Armed with that, dive back into the data and tailor your queries.  A similar exercise will prove useful for the balance sheet, too.

It will take some iterative development to make sure you have everything each run, and that your balance sheet stays in balance (that’s always the final check).  Always make sure you balance.  Sum Zero!

Some caveats about the script:

  • I hacked this together in Perl at light speed.
  • Being in an extremely controlled environment, with only 1 or 2 users, I didn’t care about security.
  • Uses Win32::ODBC to create the system DSN.  It’s very odd – but QB only allows DB access while a set of books is open.  So the DSN has to be created dynamically while the instance is open (otherwise the DB connection fails).  Rather than leave this to be a deal-killer, we built the script to create the DSN, and then delete it when the script is done.  There are some bugs here tho.  Clean out unused DSN’s periodically.  With this one, we specifically just went far enough to get something that worked.  There is very likely a more elegant way to address this issue.
  • Uses Perl’s DBI to execute queries; too many bugs with Win32::ODBC to execute queries.  Very strange results – DBI did the trick.

Everything else should be self-explanatory.  There are no warranties with the scripts. Make sure you understand what you are doing before you do it.  These scripts do not write anything to QB.

Anyway – if you are someone that is trying to get dirty with QB and figure out how it does stuff behind the scenes, then I hope this helps.

Good luck!  Ping me w/ questions: csteph9 at google’s gmail system.

DOWNLOAD

I don’t envision there being any maintenance that goes on with this code on my behalf.  Otherwise I’d throw it up on GIT or something.  This really exists (for me) as a blue print rather than a functioning system.  I encourage you to use it that way as well.  And if the scripts end up being useful, particularly the Palo REST API stuff, then please feed it back to the community.  W/ that being said …

License

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/

NO WARRANTY
THE PROGRAM IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW THE AUTHOR WILL BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Note: post-download change the file extension from .xls to .zip.

Script bundle: here.

#QB #QuickBooks #Perl #trialbalance #Palo #Jedox #REST #API

One thought on “Reconstruct QuickBooks Trial Balance with Perl

  1. Hi blogger, i must say you have high quality posts here.
    Your website can go viral. You need initial traffic only.
    How to get it? Search for: Mertiso’s tips go viral

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s