Monthly Archive for February, 2007

Here’s the second in a series of reviews of open source accounting packages – part of my mini-survey investigating whether there is any merit in open-source software for accounting.

SQL-Ledger Introduction

SQL-Ledger is a system for accounting, order processing and stock control and follows a traditional design – with the interesting exception of being a web application. It uses PostgreSQL, a respected and mature open source database system, for data storage, although if you already use Oracle or DB2, these databases can also be used with SQL-Ledger if preferred.

The software is freely available from DWS Systems Inc., the primary developers, who also provide documentation, support and customization services for a fee. It is now apparent in only my second accounting system review that this business model (aiming for wider market share by making software available for free and then aiming to profit from related products and services) is common for open-source accounting systems.

The prices for support of open-source systems are inevitably similar to the prices for support of commercial systems and so businesses of any appreciable size will not gain a significant cost advantage in using open-source systems over the cheaper commercial products available. (This is part of the reason why the cost advantage of using Linux over Windows is so debatable.) The decision on which route to go will always need to be made specifically for each individual business.

Although the documentation for SQL-Ledger can only be obtained by spending US$ 190, I found a useful introduction to SQL-Ledger provided by Metatron Technology Consulting, which discusses system capabilities, usage guidance and customization options. If you are considering SQL-Ledger for yourself, that document would be a very good place to start.

Installation

Being a web application, all installation is to a server. Client PCs need no software installed since they use only a web browser to access the system. On the server, SQL-Ledger is platform-agnostic as long as the dependencies (Perl with database modules, Apache, PostgreSQL and LaTeX) can be met. These are all readily available for both Windows and Unix-based servers. Alternatively, you can do what I did and obtain a “virtual appliance” as a single package and run it in a VMware host.

Having booted the virtual server, I ran the update script supplied with SQL-Ledger which updated my installation to the then current version 2.6.22. I understand from the SQL-Ledger website that a version 2.7 is available with some new features, although oddly they don’t list this as their latest version and the automatic upgrade didn’t download it. I interpret this to mean that it’s not ready for production use.

Configuration

Much of the setup and configuration for SQL-Ledger can be done from the web browser. An administration page is provided for setting up databases and users, following which you can log in to the actual system and set up your required accounts, codes, parts and services, etc. There are, however, some configuration options that need to be made in a traditional Unix-style text file, the most important of which is setting up printers. This is inevitably a bit messy, particularly if printing from a Unix server to a Windows printer. It would be much better if this sort of thing were fully handled by an extra admin screen.

SQL-Ledger can handle multiple companies, each of which is held in a separate database. Multiple user ids can be created for each company, but each user id can only access one company. Therefore, if you want one user to use two companies they will require two separate user ids.

Loading data. For small businesses it may be practical, if tedious, to enter all accounts, customers, suppliers and products one by one into the appropriate screens. If lots of data needs to be imported, some scripts are available on the main website to read data from tab-delimited files directly into SQL-Ledger’s database. These, however, are heavily-caveated and so probably require some serious tinkering to do the job correctly. Again, this task would be much better handled by a user-friendly screen in the application.

For setting up opening balances the only option available is to enter normal transactions. SQL-Ledger recommend posting a supplier invoice to set the stock levels for each item, posting a GL journal to set up the opening trial balance and presumably posting all open sales and purchase invoices, unmatched receipts and payments, etc. as originals.

In Use

The menu of commands in SQL-Ledger is displayed in a column on the left of the screen, with sub-menus that can be expanded or collapsed by clicking on the header. This is simple but effective and easy to use.

The main body of the screen is where most of the action takes place. Data is presented cleanly, if not particularly stylishly, and all screens follow a common pattern which quickly becomes familiar.

The various enquiry and report screens are all prefaced by a configuration screen where search-text, report filters and columns to be included are specified, resulting in good flexibility in the data that is presented on screen. However, it would be much less cumbersome if the system remembered the report settings made between one use of the report and the next – as it is, you need to re-specify them each time.

One error occured whilst I was using SQL-Ledger, related to updating the average cost for a stock item when posting a purchase invoice. This didn’t seem to have any wider ramifications and other than that the system appeared stable and reliable.

Order Processing

Transactions can be started at any stage, so in the sales process, for example, you can start with a quote, an order or an invoice. Entering data is straightforward and it’s possible to view a lookup screen of parts or services to find the ones required for the order.

When displaying an order or invoice on screen, several related actions can be performed by clicking on buttons across the bottom of the screen. The actions presented are independent of order status, so the opportunity to progress transactions just as you like is very fluid.

Superficially useful, this fluidity strikes me as being very dangerous. Not only is any action allowed at any time, but no warnings or second chances are ever given prior to an action being performed – regardless of how unwise that action might be.

Here’s an example of what I mean:-

• having entered a sales order you may then process a shipment, but no invoice is generated until you specifically ask for one – there is no warning that an order hasn’t been invoiced
• after shipment but before invoicing, you could choose to close an order – no warning is given on this action
• from this situation (assuming you realize that you are missing revenue) all is not lost. Simply search for and display the closed order and then press the sales invoice button to generate an invoice – no warning is given
• But you could do this any number of times, generating multiple invoices for the same order – and again no warning is given on this action.

Such fluidity is a double-edged sword but the downside edge is much sharper than the upside one. Careful patrol of open orders and diligent following of procedures are necessary to ensure transactions are processed correctly.

A problem worse than this becomes apparent when examining the effects of shipping and receiving stock items. On processing a shipment or receipt, the on-hand stock balances are adjusted accordingly but no accounting entry for stock movements is recorded until the order is invoiced. Therefore the reported stock list quantities and values will not reconcile with the balance sheet stock values if any invoices are outstanding – a highly undesirable circumstance.

SQL-Ledger doesn’t track individual shipments and receipts on an order, so if a part-shipment is made, followed by a second shipment to complete the order there is no subsequent way of knowing the detail of what happened.

Accounting

Regarding accounting functionality, some features that I consider desirable are missing:

• there is no batch holding and processing facility – each transaction is entered and posted directly (although version 2.7 claims to include a “voucher system”, which may be what I am looking for)
• there is no period management independent from the date for each transaction and there is no way to close a period in a sub-ledger whilst leaving other ledgers open for further postings
• there is no facility for holding budget data

Sales and purchase ledgers integrate automatically and fully with the general ledger so there is no separate task to transfer data from sub-ledgers at the end of each period.

Stock receipts and shipments are posted directly to the general ledger using account codes that are defined individually for each stock item. This provides useful flexibility for posting different product ranges to different accounts for analysis, but less flexibility for non-routine stock transactions which may need to be recorded to different accounts.

Department codes can be created for segmentation of the accounting data. Departments can be specified either as cost centres or profit centres, depending on whether just expenses or both revenues and expenses require analysis. These department codes can then be used to classify sales transactions, purchase transactions and accounting journals.

There is no general ledger transaction report other than the report on transactions in a single account. A broader GL transaction report would commonly be used to trace bookings across several accounts and as a general accounting audit report.

When selecting invoices for payment, each one must be selected individually: there’s no routine for selection based on due dates or anything else. In fact, the due date doesn’t even appear on the payment selection screen.

Viewing of transactions on a customer or supplier account is either awkward or not possible – I couldn’t find a report showing both invoices and payments within a customer account.

Closed period control is possible, implemented by entering a cut-off date prior to which no transactions can be made or amended. This affects all system areas uniformly.

Year-end processing is a very simple affair. A date and description must be provided, following which the system generates a year-end journal. Processing is very abrupt so some care is required, although it’s always possible to open up the audit control and amend or delete the journal.

Standard financial reports are extremely basic. It is possible to define reports in a style that you require, but the tools used for this are HTML and LaTeX together with some system codes for picking up the required values (which presumably are only documented in the $ 109 manual). This is all too hard to warrant further investigation and I’m sure other accountants will feel the same.

Conclusion

Functionality falls short in a number of the areas that I would look for, which I listed in my introductory post.

Requirement	Score
Include GL, SL, PL	1
User-defined account codes	1
Period accounting	0
Good year-end close controls	1
Hold >= 2 years of data online	1
Efficient journal entry screens	0
Separate journal entry from journal posting	0
Import journals from external files	0
Budget data with efficient management	0
Efficient and informative account enquiry	0
Effective report generator	0
Access to underlying data	1
Provision for full data export	0

So that’s a score of 5 out of 13 – pretty poor.

SQL-Ledger is likeable for its ease of use, clear screens and freedom of action. But this freedom of action is also a major concern for both financial and inventory control. The features provided are disappointingly simple – more so than one would expect given the marketing copy on the SQL-Ledger website. I guess for small service businesses that don’t need to track many stock items it’s perhaps worth a try, otherwise you should probably look elsewhere. I’m going to.