Accounting for Developers, Part II
moderntreasury.com
moderntreasury.com
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.
Yes.
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.
... achieving what, exactly? How is the set of outbound transactions already stored associated with a given external destination not precisely the same information? This is needless data duplication, AKA database design no-no 101.
Also, duplicating the data does not make it more correct or more authoritative. Do not confuse presentation with data. A rose by any other name would smell as sweet.
Edit: I can't reply to your response so will reply here. I suggest reading some basic database design books. Good luck.
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.
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..
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.
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.
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.
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.
SELECT SUM(amount) /* this should sum to zero */
FROM transaction_line
---
Also to identify any non-balancing transaction is easy:
SELECT tx.id, tx.date, SUM(amount) tx_sum
FROM transaction tx
INNER JOIN transaction_line txl ON txl.transaction_id = tx.id
GROUP BY tx.id
HAVING tx_sum != 0
---
This will identify them even if caused by your business logic bug, database bug, disk corruption etc.
This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
But yes, that is a downside, and if I were to write such a thing I'd make sure to have at least two mechanisms to avoid / detect errors (like, one validation in business logic and/or stored procedure, plus regular monitoring for transactions that don't add up to zero).
transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)
Sales Cr $100
Cash Dr $30
Bank Dr $70
Your approach will have:
Sales Cr $70, Bank Dr $70
Sales Cr $30, Cash $30
That looks like two sales, which is not really the case.
With sales tax, you would have:
cr: sales, dr: cash, $100
cr: cash, dr: tax, $10
In your example, you might let the monthly payment be represented by its own account, with three transactions (bank payment, interest, principal).
The purpose is to make the database constraints enforce double entry verification. If you don't need this, it can be made simpler as you suggest.
[1] Lots of use-cases: People paying partly by credit card, partly in cash. People using vouchers which are recorded as a negative transaction. Group of people splitting a bill and each doing a CC transaction for their bit. None of these are multiple sales, because recording each transaction as a single sale wreaks havoc on the inventory control.
Typical accounting GUIs present transactions and ledgers such that it becomes intuitive for business users to enter sales/invoices. Entering the above transaction in those GUIs might cause confusion.
The schema here is used by an online system recording business events as a type of log of user interactions. It lives in a sql database, and thus needs to rely on row constraints to guarantee double-entry. Transactions are never entered manually or randomly.
The effect of split transactions can be achieved by using an intermediate account. Those accounts have balances which are not guaranteed to be zero, but that is unavoidable and sometimes OK and intended.
The benefit of row-level guarantees on balancing is considerable, even if it somewhat complicates the application layer (the layer that translates your first ledger into your second ledger, and vice versa).
If you're storing a transaction that needs to balance across N rows, the only solution is at the "application level" (i.e. a stored procedure, or some middleware in code), which is unfortunately more difficult to reason about.
How does this handle the fact that credits increase the value of some accounts (e.g. sales, equity) but decrease the value for others (e.g. assets)?
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
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?
Three or more "mutations", but these might be grouped together in whatever way you want. For this purpose, the meaning of "account" is up to you define. You might call them "accounting objects" representing subscriptions, contracts, invoices, and so on.
Account balances (per transaction) can only be calculated sequentially in the order of transactions, which becomes a bottle neck at some rate of transactions.
Performance is not something I'm worried about for my app, maybe a few dozen people would use it at the same time, and I run everything at serializable isolation level anyway. But I can imagine that for processing real-world payment volumes, at some point you need to sacrifice the balanced-by-construction property for performance.
One issue I noticed is that there is some freedom in the representation of transfers. You can pick a canonical representation by demanding that the amount is positive, but then you have to make a case distinction everywhere in code. Often the code becomes much simpler if negative amounts are allowed. But it does make the credit/debit more confusing, and it goes against the observation in part 1 of the series, that accounting systems rarely work with negative numbers. I wonder why though.
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