That would be appreciated. It'd be nice to see the minimal complexity needed for something like a local credit union that managed customer savings and checking accounts, as well as home and car loans.
More ambitious would be a central banking app, in particular how does double-entry accounting work when a central bank (i.e. the Fed) is doing 'quantitative easing' and using their helicopter money to buy up Treasury bonds and mortgage-backed securities in order to keep major banks and government solvent? What's the private bank's balance sheet look like when they use the central bank money for stock buy-backs instead of for increasing commercial lending?
For example, the Fed says it's unloading the mortgage-backed securities it bought up in 2008-2009. That's got to be some convoluted accounting:
https://www.marketplace.org/2022/06/02/why-the-federal-reser...
How online payments actually work behind the scenes, complete flow from sender to receiver. That deep-dive would be really useful.
https://martin.kleppmann.com/2011/03/07/accounting-for-compu...
Also probably worth checking out Martin Fowler's writing on accounting.
https://martinfowler.com/apsupp/accounting.pdf
https://www.amazon.com/Analysis-Patterns-Reusable-Object-pap...
Fowler's approach is amusing in that, in classic UML style, he models things which are optional in an authoritative way as if they are requirements, thus muddying the waters even further. While his adjustment implementations are interesting as a basis for feature comparison, there's a lot to be said for simplicity, and this effectively requires throwing out what the bean-counters are used to and reconsidering the need from scratch. The default correction is another transaction, and this requires no special implementation.
New systems recommendation:
(1) For account identification, use IIBAN which provides IBAN-compatible account identification and checksums and is an open system @ https://github.com/globalcitizen/iiban
(2) For all accounting, use UTC.
(3) For transaction identification, use UTC second of origination (UTCSO) + account of interest (AOI; eg. IIBAN) + intra-second transaction identifier (ISTI).
Free thoughts on forward-looking accounting systems @ https://raw.githubusercontent.com/globalcitizen/ifex-protoco...
True. But it doesn't actually matter.
> Thus, a large number of transactions will always have hanging references.
No, it doesn't need to be any dangling references. Because you model external accounts with an internal account (node) in your ledger.
The other factor is that while accounting software as a whole sucks, but at least for larger businesses, its probably better to tell the auditors that you use quickbooks, versus some custom in house software. From a risk management perspective, that's a much easier value proposition for the board/management.
This is it. From a high level, accounting lends itself very well to software. You have accounts, money, and transactions, and it appears that the challenge is moving money between accounts and then adding everything up. Perfect domain for software, that's all stuff computers are very good at.
It's only once you start digging in with real companies that you realize the math is the easy part. The hard parts are the opposite of things that computers are good at - e.g. "given this change in rules that takes effect next year and is written in plain English, how do we account for this transaction?" Or "when an exception occurs, we need to define a process for how a human can handle it after month close."
The parts that are fun for a developer (look at the cool stuff the computer can do!) are not the parts that are valuable to a user; the valuable parts are super tedious and boring.
A buddy of mine is working on a YC-funded company (https://www.keeper.app/) that's designed around assisting the lives of bookkeepers using quickbooks. I think it's a great angle because I agree with you that quickbooks is the most logical option for an average company. Where software could really be improved in my eyes is SAP. Boy does their software make me want to hurl... But they have such a strong hold in the industry.
This is a bit tangential, but my biggest insight when conducting walkthroughs with the client's accountants was that there is so much valuable knowledge that is internalized in singular individuals. I'd have an accountant show me their month end close process with links between 5 Excel worksheets. Totally illogical flow and only that person understood how to follow the process from start to finish. There would be situations like randomly multiplying a line item by 32 because of some piece of paper on their desk that they had written down years ago. These people had been at the company for 20+ years. I have to think there's a better system for handling accounting processes like that. Weeding through a messy code base can be a nightmare, but going through someone else's accounting worksheets that are crucial for tying out the financial statements can be nearly impossible. I suppose eventually the audit team has to decide what's material, make a judgement call, and move on.
Also it could be very local-dependent.
Here in Colombia how we approach it is different to how I see people in USA do it.
What I don't know is how make it both easy, universal, practical and pluggable.
If for example is a out-of-band solution (aka: a micro-service) will be a total NO for many of potential users..
On high school career day I attended a talk by an accountant, and unfortunately he lived up to the stereotype. One of the most (superficially, anyway) boring people I’ve ever met.
Anyone know of cheaper alternatives to ModernTreasury.com ?
Funtionality needed: ledger + integration with payments.
Not sure about payments integration though.
accounts (id, name)
transaction (id, date) /* some call it journal */
transaction_line (id, transaction_id[fk], account_id[fk], amount)
I use -ve amount for credit, +ve for debit. That way when you do SUM(trascation_line.amount) it would come to 0.
This also cleanly maps to database transaction too where all transaction_line rows and transaction row should be atomic.
If you want multi-currency support, instead of amount column, it needs to be currency_code, currency_rate, amount_in_currency, amount_in_basecurrency ( i know we don't need this all, but sometimes you want to record the amount as entered, eg EURO 52.10 u want to record as entered even if your base currency is USD)
SELECT account.name, SUM(amount) balance
FROM account ac
INNER JOIN transaction_line tl ON tl.account_id = ac.id
GROUP BY account.name
You can cache this balance values with a current_balance column on accounts table
Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.
transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)
The summary of my approach is:
* A table with accounts. * A table with account balances and an index on (account id, id), so you can efficiently query the current balance. * A table with transactions. * A table with mutations. Mutations have an amount, and reference a credit account, debit account, and transaction. (So one transaction can consist of multiple mutations.) * The account balances table list the post-balance, but also references the mutation that caused it to be that new value.
All of these tables are append-only. I later added another layer, with transactions and subtransactions, but I'm not sure if this was a good idea.
[1]: https://github.com/ruuda/hanson/blob/351e8e9bc5c96a9c1dc76fd...
If I understood your explanation and schema correctly, a mutation itself is balanced, and if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
The advantage I see with this design is that a mutation (and thus a transaction) is always balanced (you store the amount only once, and credit account and a debit account).
The disadvantages seem to be that the transaction itself doesn't explicitly list the total changes to an account explicitly, and that for each account you have to join the mutations twice (once for the credit side, once for the debit side) to get to re-calculate the current amount.
Storing both the current balance in the account means you cannot have concurrent updates to one account, so you must rely on row-level locking for consistency. (Which sounds a bit like a potential bottleneck, if you have something like a company-wide Cash account that is involved in lots of transactions, as in the ModernTreasury blog post).
Does that seem like a fair summary to you? Are there other trade-offs you have noticed?
You could also take a look at the data structures used by GNU Ledger (e.g. account [2]) and transcribe them to SQL. A bit more work, but probably educational.
[1]: https://github.com/beancount/beancount/blob/1f180e7176808139...
[2]: https://github.com/ledger/ledger/blob/master/src/account.h
Personally I think that's the main reason for working on any project, let alone being your full time job.
Expense
- Salaries
- Rent
-- Warehouses
-- Retail space
- Utilities
Thank you!
If $moneyIn > $moneyOut:
Print(“hells yeah”)
Else:
Print(”oh shit”)This doesn't work on a low level, such as: If you're looking at an account you want to balance to 0, e.g. a suspense account, prepayment account, etc.
Also doesn't work with practical application, such as: Catering for a profitability target e.g. ($in / $out) > 1.1