November 9, 2006

SQL-Ledger: Impressive capabilities, but needs polish

Author: Mayank Sharma and Conrad Canterford

SQL-Ledger is a popular free accounting application with a rich set of features. It's written in Perl and stores your accounting information in a PostgreSQL database, which makes deployment much easier when you have users who work on different machines. Like GnuCash, supports double-entry accounting. Unlike GnuCash, however, it appears to be squarely aimed at the small business community, boasting multiple user support, multiple company support, point-of-sale entry, accounts receivable and payable, and stock tracking. It has a good list of supported languages (29, according to the Web site), and by virtue of its HTML interface is usable on practically any modern operating system -- or indeed a whole range of different operating systems simultaneously.

Installation is straightforward; just download a tarball to /usr/local/sql-ledger and run the setup.perl script. It automatically downloads and installs the latest version of SQL-Ledger and updates Apache's configuration file. The script doesn't check for dependencies -- you'll need to do that beforehand. On an Ubuntu Dapper box, one of the authors runs it with Perl 5.8.7, along with the libwww-perl library, which provides an API to the Internet, PostgreSQL 8.1, and Apache 2 with the libapache2-mod-perl module to make it process Perl code. SQL-Ledger also needs a Perl database interface library, libdbi-perl, and a database driver for PostgreSQL server, libdbd-pg-perl, which is available under the universe repository.

The setup script prompts for the user and group running the Web server. In Ubuntu, Apache 2 is run by the www-data user and group. Upon completion, the script adds the necessary Alias directives to a sql-ledger-httpd.conf file placed under /etc/apache2 and makes appropriate changes to Apache's httpd.conf. Although Apache 2 uses the new apache2.conf file, it maintains the old one for backward compatibility and to support third-party applications that look for it, including SQL-Ledger.

Once the automated script has done its work, you need to refer to the included README to prepare your installation by adding a user and database to PostgreSQL. The document will then guide you to the administration panel of SQL-Ledger, where you are asked to create a dataset for your company. Next you need to associate this dataset with a Chart of Accounts. SQL-Ledger includes several account templates for various kinds of businesses. There are general charts for several countries, including Austria, Hungary, Poland, Spain, Italy, UK, US, and Canada. Then there are business-specific charts such as Sweden_Church_Society, Sweden_Agriculture, US_Service-Company, and US_Manufacturing.

Finally, you must add a user and associate it with a dataset.

When you log in into SQL-Ledger as a user, you'll be greeted with an interface divided into two frames. The left frame houses the navigation menu, broken down into several sections and sub-sections. The right frame contains the main screen where you can enter data. This works well enough on a screen at 1400x1050 resolution, as there is plenty of room on the screen for the full display of data in the right-hand pane, but space in the data pane might get a bit cramped at 1024x768 or lower resolutions. If you are switching over from a fancy Windows-based accounting package, SQL-Ledger might look too plain for you, but for what it lacks in looks, it more than makes up with its functionality.

The bigger hassle (for us, at least) is that the data pane was almost completely unused for navigation. It displayed nothing until you had drilled down through the menu system -- anything from two to four clicks in the menu. In their defense, the menus do expand and stay expanded, so you only need to drill down once per session for each screen or report, but it was still annoying. This is certainly not an interface you can use without a mouse. We could happily live without the pretty graphs and the command-line-only imports, but that menu system is just horrible.

For example, to get a list of all vendors you have to click on AP -> Vendors -> Reports -> Search before finally clicking on the Continue button in the data pane to access the list. It would be sensible to use the data pane much earlier in the process. For example, you could show a list of active vendors (or the last 20 used, or something) when clicking the Vendors menu. That would save a lot of clicking. Similar logic applies to Customers and products too.

There are some nice features, but they seem to be a little inconsistently applied. Take the selection of Customers, Vendors, or products. In most places where you need to enter a customer or vendor ID, the software will offer you a drop-down box to select from a list. That's a good solution where there is only a small selection to choose from. A nice innovation in the selection of product IDs was the ability to enter a partial code. You are then presented with a screen where you can select which product you want from those matching the partial. If you didn't match anything, it assumed that you were entering a new product and went through a new product entry process, which may not be what you want, but apart from that it was very nice. Unfortunately, it was really overkill when we were testing with just four products -- a dropdown box would have sufficed. What we would have liked to see was a combination of the two (for Customers, Vendor, and products, at least). Use the dropdown box for small numbers (perhaps a user-configurable level?) and use the partial match functionality for larger numbers.

Just to confuse matters, if there isn't anything that matches the requirements for the field (for example, if you have no vendors configured and you go to the Enter Transaction screen under the Accounts Payable menu), it displays a text box as it does for products, but instead of allowing you to enter a code and commence creating a new record as it does for products, it gives you an error message. If it is going to do that, just displaying a message on the screen that first comes up (the Enter Transaction screen, in my case) would be far better.

SQL-Ledger boasts of support for multiple companies and users. Unfortunately, users are tied to a single company. We would like to be able to log in, then select the company we wish to operate on, but you need a separate user for each company.

While SQL-Ledger does a good job to get you up and running with the charts of accounts, you'll need to remove a few accounts and add a couple of your own by making a visit to System -> Charts of Accounts -> List Accounts, which will not only list the accounts, but will also let you customize them.

Imports, reports

A few issues fairly quickly came to light when we attempted to load some sample data to test with. There are several migration scripts available to import your data from another accounting application into SQL-Ledger, but there are no import options accessible through the online screens. To import data you need to use a command-line tool -- and that completely defeats the whole purpose of a multiuser, platform-independent interface. There are command-line tools downloadable from the Web site to import from GnuCash (which didn't work for me without modification), and for a couple of versions of Quickbooks. There is also a generic script which, from its description, looks like it imports from CSV files. It is certainly not clear to me that it will support QIF. Needless to say, it does not support any form of online banking.

A strong point of SQL-Ledger is its reports. Every account has a report module attached that can be customized to display details of your choice between any time period you choose. The reports can be printed or sent as a PDF, PostScript, or HTML attachment.

The reports are functional, but they lack any of the pretty-picture features (bar charts, pie charts, etc.) that senior managers and venture capitalists like. While the option selections and the range of reports seem quite comprehensive, it's unfortunate that they are scattered all through the menus; that would become tiresome quickly.

If you send bills over email, you can do that right from SQL-Ledger, and with a cover letter. This is also useful for keeping backups. If your business needs to track sale and purchase orders, or make or request quotations, these too can be printed, emailed, or saved through a single click with SQL-Ledger.

The templates for the reports are all editable and can be modified to change placement of a particular item or to carry your company logo. They can be found under the /usr/local/sql-ledger/templates directory in HTML, TeX, or text format.

More than one person in most organizations will need to access an accounting application. Log in as admin to add users and associate them with the company (dataset). But you wouldn't want your clerk that handles sale and purchase to look at your balance sheet or to send reports. A user's activity and access to accounts can be easily controlled by the administrator by removing entire menu headings or only specific items under them. The user privilege levels appear to be flexible and should be able to be adapted to most people's requirements.

Talking of security, SQL-Ledger can be configured to comply with the generally accepted accounting principles (GAAP) by forcing users to post reversing entries instead of deleting transactions. You can also set accounting periods, which once closed cannot be edited.

If you require, there is also a fairly usable point-of-sale (POS) module that can be hooked up with a bar code reader for inputing item details.

Documentation and support

SQL-Ledger is an open source application. There's a FAQ and installation instructions on the download page for various platforms available for free, and there's also a user and several active regional mailing lists on which the developer pops in occasionally with his inputs.

Although this should suffice for most users, if you are using it for a commercial house, it will help if you buy the documentation and the support that comes along with it. This is the catch with this program. While the program is free software, to get the documentation (other than installation instructions), you must purchase it. This is the developers' main source of revenue. Having to pay for the documentation irks us, especially when the documentation is important to make proper use of the program. The developer appears very negative towards community-based documentation, presumably to encourage you to buy theirs:

Community based documentation does exist to some extent however the reader is cautioned not to rely on such information because it may be incorrect. Some of the information we found is really old and if no references that this applies to version so and so appear it's pretty hard to figure out if it can be used. We know but you don't. It is not our job to check wikis and correct mistakes. With over 500,000 pages floating around on the Internet it would be a full time job just to look at everything.

There is a uses mailing list that you can join so you can ask questions of other users, and we have seen comments from users that suggest that the list is active and helpful. If you want more immediate assistance, the Web site has only very superficial information. We did find a couple of helpful third-party Web sites, but none had a great deal of detail. We also found a useful FAQ.

You can try playing around with SQL-Ledger without installing through the online demo.


We were a little disappointed in SQL-Ledger. We were expecting a more polished program. If you need an accounting solution that supports multiple users with configurable access levels, or access across a variety of operating systems, install it and have a play. The basis for a good program is there, it just needs a bit of polishing. It's easy to install and use, is Web-based and secure, can handle multiple users, and is customizable to the core. The biggest thing its been accused of missing is a payroll management module. Additionally, the chances of it hooking up with your bank are quite slim, which is actually a lack of effort on part of the bank than the application.

While SQL-Ledger is multi-lingual and can handle currency conversations, it's best suited for users within the US and EU. Furthermore, though it can handle any type and size of organization or even multiple organizations and manufacturing units, it isn't the ideal application to keep track of an individual's personal finances.


  • Enterprise Applications
Click Here!