f you are a part of a small business, either you or someone at your organization likely deals with QuickBooks. QuickBooks is the most broadly used accounting software in the small business arena. It is easy to use and has the basic functionality to satisfy the accounting needs of most small businesses.

The user operates QuickBooks on either the Windows or Macintosh platform via a graphical user interface (GUI) and can achieve most of what he needs for his business through this GUI. As the business grows, however, the owner or accountant of the business will bump into the problem of getting bogged down with certain QuickBooks tasks that are tedious and repetitive in nature.

An example of this problem is the process of moving a large number of bill line items from one account to another, such as merging two accounts for a particular expense or class of expenses into one account. The bill line items in question might have been classified under account #1 at one time and under account #2 at another time, whether by two different people or even the same person. If you try it, you will see that although QuickBooks allows you to rename an account and also to find all transactions (income or expense) in a given account, it does not allow you to find all or some bill line items in one account and move them into another account in one batch. Eventually Intuit may add this functionality, but some other function probably will always be missing and its omission will result in unnecessarily tedious procedures to accomplish basic tasks.

The way to overcome this inefficiency problem is to programmatically control QuickBooks. For several years now, COM has enabled you to control QuickBooks under Windows with the Win32 API. The automation interface has been getting richer each year and is reasonably feature-rich and capable in its latest incarnation, the QuickBooks SDK 5.0. The SDK includes library files exposing the API to control QuickBooks and tools for developing with and using the API.

Under the Hood of the QuickBooks API

The QuickBooks API has two means of access: via the Microsoft Foundation Classes (MFC) and via qbXML. Both of them are built upon the COM foundation. You can use either MFC or qbXML to communicate with and control QuickBooks. This article discusses qbXML because it is cross-platform and thus can work on Windows, Mac, and Linux. Once you know qbXML, it is very easy to use the QuickBooks MFC interface because the MFC interface is essentially the same as the qbXML interface, except it uses COM as the communications method instead of XML message-passing.

If you use Visual Basic or C#, you should look at using the MFC interface after going through the examples in this article because although the MFC interface lacks cross-platform portability and limits you to the Windows platform, if you already use Windows and plan to use nothing else, MFC makes development easier through programmer productivity-enhancing features such as IntelliSense and tight integration with Visual Studio or whichever IDE you use.

Solving the QuickBooks Inefficiency Problem

To see how you can solve the tedious task problem introduced previously, begin by downloading and installing the following in order:

1. If you do not already have QuickBooks, you can purchase it from http://quickbooks.intuit.com. This article assumes you are running at least QuickBooks Pro 2004. Anything newer is also fine. This article also assumes that you are running the U.S. edition of QuickBooks. There are differences between the U.S. edition and internationalized editions, and the examples in this article may not work on editions other than the U.S. edition.
2. Install the QuickBooks SDK 5.0 on the machine where QuickBooks runs. Make sure you choose to install Remote Data Sharing (RDS) as part of the QuickBooks SDK installation. The RDS option appears at the end of the installation as a checkbox labeled “Configure Remote Data Sharing on this machine.” By default it is on, so all you have to do is leave the checkbox checked. RDS allows you to control QuickBooks from another machine. The SDK needs to be running on the server where QuickBooks runs. The SDK also needs to be running on the client if you want to use the MFC interface. If you are going to use the SOAP web service interface via Remote Data Sharing (RDS), you do not have to install the SDK on the client. You do not need QuickBooks running on the client whether you use the MFC interface or qbXML interface.
3. The example in this article describes controlling QuickBooks from an application on the same machine as QuickBooks because that is simpler to set up. An upcoming related but separate article will describe remote control of QuickBooks from an application on a networked computer, which is more complex, but is likely to be the scenario under which you would operate for security reasons. It is also the scenario under which you would operate in order to interoperate with Linux (e.g. a web application running on Linux invokes QuickBooks running on Windows to retrieve invoice data).
4. Download ActiveState Perl. It is a free download. This article assumes ActivePerl 5.8.7 build 815, but later versions are fine and even some earlier versions are fine. This article uses Perl because it is cross-platform and has a nice library of pre-built code called CPAN (Comprehensive Perl Archive Network). The library includes code for processing XML and communicating via SOAP to a web service, both tasks you will need to perform in the following example.
5. Install the XML::LibXML and XML::LibXSLT modules by running the Perl Package Manager from the ActivePerl start menu group. You should see a command line interface. Follow these steps:
   • Type “repository add theoryx5 http://theoryx5.uwinnipeg.ca/cgi-bin/ppmserver?urn:/PPMServer58”
   • Type “install XML::LibXML”
     If the Perl Package Manager returns a list of results and asks you which match to install, type install XML::LibXML 1, which will tell PPM to install the first match, which should be XML-LibXML.
   • During the installation of XML::LibXML, if PPM asks if it should fetch dependencies, answer yes (usually you can do this by pressing enter to accept the default answer). You can also accept the defaults for the file locations if you need to install dependencies.
   • Type “install XML::LibXSLT”
     Again, accept the defaults when PPM asks whether to fetch dependencies and where to place the fetched dependencies.

   Once you have installed the XML modules for Perl, type quit at the prompt to exit the Perl Package Manager. You will use the XML and XSLT libraries to manipulate XML data for both generating requests to QuickBooks and processing responses from QuickBooks.

6. Download the accompanying example code for this article. It includes XML files, Perl code, and a QuickBooks company file to serve as sample data.

Make sure you have taken all these steps before proceeding.

Moving Many Bill Line Items from One Account to Another

The nice thing about using qbXML with the QuickBooks SDK is that the SDK comes with a tool for testing the XML for validity and also for submitting the XML to QuickBooks so it can process and retrieve the XML that QuickBooks returns. You can test a qbXML request without writing any code apart from the XML request message. Using its qbTest+ program, the QuickBooks SDK allows you to submit XML requests directly and receive XML responses directly without having to write code.

Unpack the example code file. Start up QuickBooks. Open “ABC Widgets LLC.QBW” in QuickBooks. If you use a version of QuickBooks later than 2004, you will be asked to upgrade the QuickBooks company file format. In that case, you should instruct QuickBooks to perform the upgrade. After opening the “ABC Widgets LLC.QBW” company file, start the qbTest+ program (located in the QuickBooks SDK start menu group under the Tools subgroup). Leave the “Company File” input blank. In the “Request File” input, click on Browse and specify all_bills_query.xml as the qbXML request document. Click on the “Open Connection” button. Click on the “Begin Session” button. The focus should return to QuickBooks.

QuickBooks alerts you that an unknown program is trying to access the data file. You must approve the access in order to continue. The permission-based access control method is how QuickBooks protects your data against unauthorized and possibly malicious programs. You should see a dialog asking whether to allow access to the company file by an application calling itself “SDKTest Plus 3.” Click “Yes, always.” QuickBooks will ask you to confirm because the external application is not signed with a digital certificate. In a production environment, such a warning should be heeded. Since we are in development, we expect to get a warning about a missing digital signature, so we will proceed regardless. Click “Yes.” The focus will return to qbTest+. Click “Send XML to Request Processor,” and qbTest+ will send the request to QuickBooks and then retrieve the response from QuickBooks. Click “View Output”.

You should see the XML response created by QuickBooks. It contains information from the company file. qbTest+ will also save the response to a file called QBResponse.xml. The file should be in the same directory as the request XML file. Examine the request by opening all_bills_query.xml in your browser or text editor. Compare the request to the response. From the request and response, you should be able to deduce that the request asked QuickBooks for all bills, including the line items describing the expenses comprising the line items on all of the bills. You should see multiple expenses in the “Printing” account from “Big Apple Events,” a fictitious company that puts on trade shows. Big Apple Events is one of the vendors that ABC Widgets uses.

In the sample QuickBooks company file for ABC Widgets, you will find expenses for a big trade show that ABC Widgets attended. Some of the expenses are categorized in the “Printing” account whereas others are categorized in the “Marketing” account. It is easy to understand how this might happen. Two or more bookkeepers may have different opinions about how things should be categorized. Even if the same person always does the books in QuickBooks, he or she may have different opinions at different times. I can think of numerous times when I have classified the same item in different QuickBooks accounts at various times. Ideally, all transactions involving item X would always be in the same account and classifications would be consistent. Even if you do not have this particular problem, you will likely run across situations where you need to make batch updates that QuickBooks does not handle efficiently via GUI, so this example should still prove useful.

Now that you have seen the request and response for a simple query by item and account, you can build the next piece of the solution. Before doing so, however, you should clean up by closing the qbXML Test+ application. Click “End Session,” followed by “Close Connection,” then “Exit.” Now you will write some code that reads the XML response provided by QuickBooks and iterates through the bills and bill line items. With each bill line item that satisfies your criteria of being linked to the vendor “Big Apple Events” and the account “Printing”, the code will generate a chunk of XML that requests that QuickBooks modify the bill line item so that it is instead linked to the account “Marketing.” All of the trade show expenses will then be consistently classified into the right accounts.

The qbXML request to update a bill line item looks like so:

                           1-1137620540          1137620540                      3-1137620540                          Bank Service Charges                                       

The edit sequence is used to confirm that you have the latest version of the record. Every time a change is made to an object, the edit sequence changes to a new number. When you run a query, QuickBooks sends back a response that includes edit sequences for all of the objects that can be modified. Your program should specify the same edit sequence when going back to QuickBooks and when asking QuickBooks to update a record. If QuickBooks detects that the edit sequence on the object you wish to modify is different from what you specified, it will know that your program made its request to modify the object based on possibly faulty information, so it will report a failure. Your program can then try to query the data again and perform the update if necessary, but using the new edit sequence.

Look at the code in update_bill_line_item_account.pl in your text editor. This code creates a qbXML request to retrieve all bills, including their bill line items. It then uses an XSLT stylesheet to iterate through the bills and their line items, searching for ones that are linked to “Big Apple Events” as the vendor and “Printing” as the account and generating a new XML document that requests QuickBooks to update the appropriate bill line items.

Three files comprise the small bill update application:

• The update_bill_line_item_account.pl file.
• The all_bills_query.xml file, which is the input to QuickBooks during the first pass, causing QuickBooks to return a list of all bills along with their associated line items.
• The update_bill_line_item_account.xslt file, which is the XSLT stylesheet that generates the XML request for the update operations that cause QuickBooks to modify the bill line items so they are linked to the new account of “Marketing” rather than “Printing.” If you are not familiar with XSLT, I recommend the tutorial and the reference at www.zvon.org.

Before running the update_bill_line_item_account.pl program, make sure QuickBooks is running and that it is open to the ABC Widgets company file. Once you have done that, run the update_bill_line_item_account.pl program (either by double-clicking or typing the filename at the command line). QuickBooks will pop up a dialog similar to the one that appeared when you ran qbTest+ the first time. Choose “Yes, always” to allow access to the update_bill_line_item_account.pl application (it calls itself “UpdateBillApp” in identifying itself to QuickBooks). After running the code, switch back to QuickBooks and look through the bills. You will see that all the bill line items that were previously associated with the trade show and the “Printing” account are now in the “Marketing” account. The number of changes this time around is small. With an older QuickBooks company file containing a thousand times the number of entries, automation would definitely be a real time saver.

The following is a line by line explanation of the important lines of the update_bill_line_item_account.pl program:

  my $qbxmlrp_const = Win32::OLE::Const->Load("QBXMLRP2 1.0 Type Library");

Loads the QuickBooks constants used as parameters in calls to QuickBooks (The program uses the localQBD and qbFileOpenDoNotCare constants later in the code.)

  my $request_processor = Win32::OLE->new("QBXMLRP2.RequestProcessor", sub {$_[0]->CloseConnection();}) or die "oops
";

Fetches a reference to a qbXML request processor, the object that will handle your qbXML requests

  $request_processor->OpenConnection2("", $name_reported_to_quickbooks, $qbxmlrp_const->{"localQBD"});

Asks the request processor to open a connection to QuickBooks

The first parameter is the appId, an identifier assigned by Intuit for use in its IDN Solutions Marketplace. The appId is only relevant if you develop software for QuickBooks that you intend to sell to other parties. Generally you will leave it as the empty string. The second parameter is the name displayed within QuickBooks when it asks the user to confirm that the client application should be permitted to access the data in the company file. The third parameter is the connection type. Possible options are localQBD, localQBDLaunchUI, remoteQBD, and remoteQBOE. The localQBD option tells the request processor to connect to QuickBooks running locally (i.e. on the same machine as the request processor and the client application). A value of localQBDLaunchUI tells the request processor to connect to a local QuickBooks and then request that the local copy of QuickBooks start up its user interface and prompt for the user to log in. A value of remoteQBD forces the request processor to open a connection to a remote instance of QuickBooks via RDS even when a local copy of QuickBooks is present. Normally the request processor tries first to connect to a local instance of QuickBooks. A value of remoteQBOE causes the request processor to connect to QuickBooks Online Edition. You must subscribe to the Online Edition in order to use this connection type.

  my $ticket = $request_processor->BeginSession($qb_company_file, $qbxmlrp_const->{"qbFileOpenDoNotCare"});

Begins a new session

You must begin a session before issuing one or more qbXML requests. The first parameter specifies a company file. A blank value tells QuickBooks to use whatever company file is currently open at the time of the call to BeginSession. Once you are ready to deploy the application to a production environment, you should explicitly specify a company file. The company file will usually look like C:Documents and SettingswchaoMy DocumentsABC Widgets.QBW. Some languages use the backslash as an escape character. In particular, Perl uses the backslash as an escape character. Thus, the company file just specified should actually appear as C:\Documents and Settings\wchao\My Documents\ABC Widgets.QBW in Perl code. The second parameter is the file mode. The possible values are qbFileOpenDoNotCare, qbFileOpenSingleUser, and qbFileOpen. The qbFileOpen option tells the request processor that the file must be opened in multiuser access mode. If that is not possible, the BeginSession call will fail. The return value is a ticket that must be passed to every subsequent call to the request processor. The ticket is used to identify the session and distinguish it from other simultaneous sessions.

  my $response_xml_string = $request_processor->ProcessRequest($ticket, $request_xml_string);

Asks the request processor to act on the XML request in the $request_xml_string variable

  $request_processor->EndSession($ticket);

Tells the request processor to end the session linked to the ticket in $ticket

You should get in the habit of ending sessions when you are done with them in order to free up resources. Even if you do not end your session explicitly, the request processor will end your session if your program shuts down cleanly. This is because you specified a destructor in the call to the new method of Win32::OLE. The destructor calls CloseConnection on the request processor object. If your program gets stuck before its sessions are closed, QuickBooks will refuse to close the company file until all sessions have ended. Explicitly ending the session minimizes the chance that QuickBooks will see a hung process as a lingering connection.

Most of the XSLT file (update_bill_line_item_account.xslt) should be obvious because it is passed through character for character to the resulting HTML, so I will cover only the important lines – the ones that implement our criteria of the vendor name matching “Big Apple Events” and the account matching “Printing.”

Selects only the bills that are linked to the vendor named “Big Apple Events”

Selects only the bills that have one or more expense lines linked to the account “Printing”

Selects only the expense lines that are linked to the account “Printing”

Free from Repetitive Tasks

Now that you have learned the basics of how to programmatically control QuickBooks, you will no longer be limited by time-consuming repetitive tasks. You should be able to do anything that can be described in code.