Building a web-based accounting software
August 6, 2005 4:16 AM Subscribe
I am tasked with writing a web-based accounting software in PHP-MySQL. My background in accounting is minimal. I understand enough of debits & credits & transactions and such, but I'd like to find some resources which could help me in working out the programming workflow, database relationships, etc, ie the technical parts of developing such a system.
I strongly suggest you do the operations research to map the flow of material, money and information in your client's business. An accounting package is a model of the client's financial activities. If it is a map of something else, there will be all sorts of problems because you will be delivering a square peg your client will have to hammer into their round hole.
Your basic data structure is transaction headers and details, inventory items, people and addresses (cardfile), chart of accounts, journal identifiers, etc.
Every header must be tied to a card and perhaps a prior transaction. The details must be tied to headers and in turn have accounts, and items tied to them. Inventory items can be recursive (widgets make assemblies, assemblies make components, components make sections, etc. ad nauseum).
If there is a CPA attached to the business, they need to specify the inventory valuation model being used and provide all the arithmetic used. If they start to play CYA and make you guess how to value inventory without providing precise information on how they figure it, kill them in a very public and messy way and leave the body where it can be seen by their colleagues. After you dispose of four or five in this manner, they may get the message or you will run out of CPAs and the problem will resolve itself.
If an accountant is brought into the project after the data structures have been implemented, they will probably be introduced to you and want to shake hands. Grasp their right hand firmly and quickly stab them in the heart with a pencil in your left hand. Do not hesitate. It helps if you take half a step backwards and use your right arm to pull them towards you while shoving the pencil upwards into the rib cage with a slight twisting motion of your left hand.
posted by warbaby at 7:45 AM on August 6, 2005
Your basic data structure is transaction headers and details, inventory items, people and addresses (cardfile), chart of accounts, journal identifiers, etc.
Every header must be tied to a card and perhaps a prior transaction. The details must be tied to headers and in turn have accounts, and items tied to them. Inventory items can be recursive (widgets make assemblies, assemblies make components, components make sections, etc. ad nauseum).
If there is a CPA attached to the business, they need to specify the inventory valuation model being used and provide all the arithmetic used. If they start to play CYA and make you guess how to value inventory without providing precise information on how they figure it, kill them in a very public and messy way and leave the body where it can be seen by their colleagues. After you dispose of four or five in this manner, they may get the message or you will run out of CPAs and the problem will resolve itself.
If an accountant is brought into the project after the data structures have been implemented, they will probably be introduced to you and want to shake hands. Grasp their right hand firmly and quickly stab them in the heart with a pencil in your left hand. Do not hesitate. It helps if you take half a step backwards and use your right arm to pull them towards you while shoving the pencil upwards into the rib cage with a slight twisting motion of your left hand.
posted by warbaby at 7:45 AM on August 6, 2005
It would be insane to perform such a project without the direct assistance of an accountant.
posted by caddis at 12:06 PM on August 6, 2005
posted by caddis at 12:06 PM on August 6, 2005
Of course, unlike warbaby, I would advocate working with, rather than murdering the accountant. It would be insane to attempt such a project without an accountant because the accounting aspect is the most important part of the problem; at the very least it is just as imortant as the programming aspects.
posted by caddis at 12:11 PM on August 6, 2005
posted by caddis at 12:11 PM on August 6, 2005
warbaby neglects to mention that (a) your pencil should be made of ash or aspen and (b) that you're going to need to dismember the corpse and bury it at a crossroads.
posted by snarfodox at 2:13 PM on August 6, 2005
posted by snarfodox at 2:13 PM on August 6, 2005
What kind of resources are you looking for - open source accounting code, books, web sites, on-line accounting classes, volunteers with a good accounting background?
Also, if I understand warbaby's argument, let me be the first to disagree (and I note that I am, among other things, a certified accountant) - accounting software can be as generic as you want it to be. For example, there are several allowable methods of depreciation. Generic software would allow a choice of method.
Which isn't to say that if you have a client who wants their own accounting system (which, in my opinion, is borderline-insane, given how little it costs to buy off-the-shelf stuff with thousands of person-years of work embedded in it), that you should write totally generic stuff. There are hundreds of specialized areas (accounting for warranties, for example) that may or may not apply to a given business; you obviously would need to pick and chose.
posted by WestCoaster at 4:15 PM on August 6, 2005
Also, if I understand warbaby's argument, let me be the first to disagree (and I note that I am, among other things, a certified accountant) - accounting software can be as generic as you want it to be. For example, there are several allowable methods of depreciation. Generic software would allow a choice of method.
Which isn't to say that if you have a client who wants their own accounting system (which, in my opinion, is borderline-insane, given how little it costs to buy off-the-shelf stuff with thousands of person-years of work embedded in it), that you should write totally generic stuff. There are hundreds of specialized areas (accounting for warranties, for example) that may or may not apply to a given business; you obviously would need to pick and chose.
posted by WestCoaster at 4:15 PM on August 6, 2005
I agree with caddis and WestCoaster, it seems crazy to try to write an accounting package without knowing anything about accounting. How much are they paying you to do this, anyway? Do they simply want a sort of cheezy finance tracker, or a real package?
Also, why does it need to be in PHP/MySQL? There might be some accounting API packages you could buy and tie into, probably running on windows (or IBM mainframes!). You can run apache/PHP on windows, also.
posted by delmoi at 6:30 PM on August 6, 2005
Also, why does it need to be in PHP/MySQL? There might be some accounting API packages you could buy and tie into, probably running on windows (or IBM mainframes!). You can run apache/PHP on windows, also.
posted by delmoi at 6:30 PM on August 6, 2005
Well, if it's an option, don't write it yourself.
Run, do not walk, to SQL-Ledger.
posted by baylink at 9:15 PM on August 6, 2005
Run, do not walk, to SQL-Ledger.
posted by baylink at 9:15 PM on August 6, 2005
Gnucash may be adaptable to your needs. Firstly, it's a really good example of a proper double entry accounting system, so playing with it will help you get your head around things. The source code is freely available, as are notes on the architecture.
Second, it's designed with a separate backend to facilitate repurposing as a hosted app. While there won't be PHP extensions, it could be worth looking into.
I too hear good things about SQL-Ledger.
posted by i_am_joe's_spleen at 12:32 AM on August 7, 2005
Second, it's designed with a separate backend to facilitate repurposing as a hosted app. While there won't be PHP extensions, it could be worth looking into.
I too hear good things about SQL-Ledger.
posted by i_am_joe's_spleen at 12:32 AM on August 7, 2005
Best answer: arrowhead, now you see what I've been saying about accountants is precisely correct. Inventory control and valuation is one of the hardest (and most often screwed up) aspects of financial databases. None of the bean-counters asked the slightest question about the business, instead, they immediately pitched in giving ignorant and useless advice, such as depreciation methods (which are trivial) and ignoring crucial issues (of which inventory purchasing, control and valuation are generally the most critical and specialized.)
Note that none of them read what I wrote and instead set up a bogus strawman which they gleefully knocked down.
I assume (and please tell me if this is not the case) that the reason you have been tasked is because there are no off-the-shelf packages which accurately capture the business you are in. This is often true of many businesses.
The double-entry transactions are relatively trivial and the available packages could save you a lot of time and trouble. Where your client's specifics come into play will be ccalculating Cost of Goods Sold (COGS) and there is no single generic method / datastructure for this. It's a top-down design decision that will strongly shape the rest of the project.
The other issue (in terms of usability) is the interface. Talk to the bookkeeper and others who are using the current software. You want to listen carefully to their description of the short comings, but you also want your interface to be as close as possible to what they are already trained to use. Do not impose a new business model because it is convenient for your programming, your job is give the client what they want and need, not to change the business for your convenience.
Does business negotiate prices with customers? If so, you will need to build a method for custom pricing -- usually a cross-product of items x cards (or groups of items / cards grouped by an identifier field in each table). This is where most generic packages fall down and you can shine. The really hot solution is to incorporate a rule-based system that will take either an individual record or a group of records on either side of the cross product and custom-price each item for each customer.
To return to the problem of stupid, arrogant accountants. By all means, if the business has an accountant who understands the client's operations, get them involved in the specification process. Kill any ones who care moreabout themselves than the client. Particularly kill any accountants who come in late to the project and start redesigning the business model for their convenience.
You may have correctly inferred I advocate Pirate methods over Ninja methods. It's all very well to dispose of troublesome accountants by slipping some tetrodotoxin from fugu testes into their bottled water. But it doesn't really get the job done. Everybody will be chattering about bad sushi or something, when the key problem is institutional. Much better to lash them to the muzzle of a cannon and splatter them all over the meeting room.
posted by warbaby at 9:09 AM on August 7, 2005
Note that none of them read what I wrote and instead set up a bogus strawman which they gleefully knocked down.
I assume (and please tell me if this is not the case) that the reason you have been tasked is because there are no off-the-shelf packages which accurately capture the business you are in. This is often true of many businesses.
The double-entry transactions are relatively trivial and the available packages could save you a lot of time and trouble. Where your client's specifics come into play will be ccalculating Cost of Goods Sold (COGS) and there is no single generic method / datastructure for this. It's a top-down design decision that will strongly shape the rest of the project.
The other issue (in terms of usability) is the interface. Talk to the bookkeeper and others who are using the current software. You want to listen carefully to their description of the short comings, but you also want your interface to be as close as possible to what they are already trained to use. Do not impose a new business model because it is convenient for your programming, your job is give the client what they want and need, not to change the business for your convenience.
Does business negotiate prices with customers? If so, you will need to build a method for custom pricing -- usually a cross-product of items x cards (or groups of items / cards grouped by an identifier field in each table). This is where most generic packages fall down and you can shine. The really hot solution is to incorporate a rule-based system that will take either an individual record or a group of records on either side of the cross product and custom-price each item for each customer.
To return to the problem of stupid, arrogant accountants. By all means, if the business has an accountant who understands the client's operations, get them involved in the specification process. Kill any ones who care moreabout themselves than the client. Particularly kill any accountants who come in late to the project and start redesigning the business model for their convenience.
You may have correctly inferred I advocate Pirate methods over Ninja methods. It's all very well to dispose of troublesome accountants by slipping some tetrodotoxin from fugu testes into their bottled water. But it doesn't really get the job done. Everybody will be chattering about bad sushi or something, when the key problem is institutional. Much better to lash them to the muzzle of a cannon and splatter them all over the meeting room.
posted by warbaby at 9:09 AM on August 7, 2005
One of things not mentioned yet is that there is no such thing as a monolithic "accounting" application. Typically, accounting software in larger organizations is done through a linked set of applications, including:
General ledger - the source of financial statements, the "higher" level view of accounting transactions. Other accounting systems are "subsidiary".
Accounts receivable: processes money owed, receipts. Summarized information (for example, a day's sales) are passed to the general ledger. Detailed information resides (only) in the AR system.
Accounts payable: purchase orders, receipt of goods, payments.
Inventory (merchandise): cost of goods, issues, receipts, returns, are tracked.
Fixed assets: location, value, depreciation are tracked.
Payroll
Not all of these are needed or need be elaborate - depends on the organization. And a transaction often processes through multiple systems. For example, buying a bunch of widgets for future resale gets processed through the accounts payable system, the inventory (merchandise) system, and (in consolidated form) is posted to the general ledger [as is everything].
For smaller, "integrated" accounting, these different systems are probably modules rather than separate applications, but it is still extremely helpful (I think) to consider them to be separate.
posted by WestCoaster at 10:06 AM on August 7, 2005
General ledger - the source of financial statements, the "higher" level view of accounting transactions. Other accounting systems are "subsidiary".
Accounts receivable: processes money owed, receipts. Summarized information (for example, a day's sales) are passed to the general ledger. Detailed information resides (only) in the AR system.
Accounts payable: purchase orders, receipt of goods, payments.
Inventory (merchandise): cost of goods, issues, receipts, returns, are tracked.
Fixed assets: location, value, depreciation are tracked.
Payroll
Not all of these are needed or need be elaborate - depends on the organization. And a transaction often processes through multiple systems. For example, buying a bunch of widgets for future resale gets processed through the accounts payable system, the inventory (merchandise) system, and (in consolidated form) is posted to the general ledger [as is everything].
For smaller, "integrated" accounting, these different systems are probably modules rather than separate applications, but it is still extremely helpful (I think) to consider them to be separate.
posted by WestCoaster at 10:06 AM on August 7, 2005
writing a web-based accounting software
arrowhead - if you feel like adding information or responding to any of the above, it would help a great deal to have a general sense of what type of application you're supposed to be writing. Sometimes the term "accounting" can be misleading. For example, an application to allocate revenues (for example) from subscriptions to (say) performances of a non-profit theatre group would, in some sense, be an "accounting" application, but it would not require double-entry transactions or even debits and credits. Subsidiary ledgers/systems don't require double-entry accounting; they simply have to feed such entries to the general ledger.
posted by WestCoaster at 9:30 AM on August 8, 2005
arrowhead - if you feel like adding information or responding to any of the above, it would help a great deal to have a general sense of what type of application you're supposed to be writing. Sometimes the term "accounting" can be misleading. For example, an application to allocate revenues (for example) from subscriptions to (say) performances of a non-profit theatre group would, in some sense, be an "accounting" application, but it would not require double-entry transactions or even debits and credits. Subsidiary ledgers/systems don't require double-entry accounting; they simply have to feed such entries to the general ledger.
posted by WestCoaster at 9:30 AM on August 8, 2005
Response by poster: Thanks to all for the reply.
WestCoaster - this is a full set accounting system (minus payroll) - with the typical GLs, ARs, APs, etc. There is no inventory. This is a transportation business, so they're pretty much concerned with the cost per job / delivery, revenue and profit. The costing's an integral part of the business, and is very much different from conventional businesses, hence the need to write a custom accounting module.
The accounting folks are a really a nice bunch of people - the only problem is that they were spewing all the accounting terms and logics that nobody understands :-)
posted by arrowhead at 2:18 AM on August 9, 2005
WestCoaster - this is a full set accounting system (minus payroll) - with the typical GLs, ARs, APs, etc. There is no inventory. This is a transportation business, so they're pretty much concerned with the cost per job / delivery, revenue and profit. The costing's an integral part of the business, and is very much different from conventional businesses, hence the need to write a custom accounting module.
The accounting folks are a really a nice bunch of people - the only problem is that they were spewing all the accounting terms and logics that nobody understands :-)
posted by arrowhead at 2:18 AM on August 9, 2005
arrowhead, I don't see an email in your profile. I've done accounting systems for a trucking / shipping firm (containers to ship and ship overseas) and a bus company. If I can be of help, feel free to email me.
posted by warbaby at 1:48 PM on August 9, 2005
posted by warbaby at 1:48 PM on August 9, 2005
« Older Best elevated city views by car | vector graphics perspective effect in Final Cut... Newer »
This thread is closed to new comments.
posted by Remy at 6:27 AM on August 6, 2005