[HN Gopher] Accounting for Developers, Part II
___________________________________________________________________
Accounting for Developers, Part II
Author : qin
Score : 280 points
Date : 2022-08-24 14:36 UTC (8 hours ago)
(HTM) web link (www.moderntreasury.com)
(TXT) w3m dump (www.moderntreasury.com)
| racl101 wrote:
| This has been so useful and educational!
|
| Thank you!
| icepopo wrote:
| Is chart of accounts usually a flat structure, or can it look
| more like a tree? I'm thinking of grouping similiar accounts
| together to see what's the balance on single ones vs the group.
| meekaaku wrote:
| Its a tree. Eg:
|
| Expense
|
| - Salaries
|
| - Rent
|
| -- Warehouses
|
| -- Retail space
|
| - Utilities
| photochemsyn wrote:
| > "We hope to publish guides about more complex use cases
| (lending, insurance, etc.) in the future."
|
| 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...
| kojim wrote:
| That's a cool idea. Any other deep-dive guides on
| fintech/payments that would be helpful for readers here? We're
| always looking for new topics to write about.
| janci wrote:
| The website is so fast I did not notice it redrawed when I
| clicked Part I. I'm impressed.
| RicoElectrico wrote:
| It's Javascript URL manipulation trickery, there's an event
| listener attached to that link.
| cyral wrote:
| It's running on NextJS which pre-loads the content of each link
| in the viewport unless you tell it not to, so it's loading a
| .json file containing the article content immediately, making
| the page transition instant when and if you click it.
| jasonbarone wrote:
| Oh that's so great to hear, thank you! We use Next.js and
| content is statically generated.
| pkrumins wrote:
| Here's my approach to accounting: If $moneyIn >
| $moneyOut: Print("hells yeah") Else:
| Print("oh shit")
| PopAlongKid wrote:
| Over a sufficiently long time period, net profit equals cash in
| minus cash out.
| chris-orgmenta wrote:
| Ha. But a pedantic take at face value:
|
| 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
| hum3hum3 wrote:
| I did like Charles Dickens who has Mr Micawber say 'Annual
| income 20 pounds, annual expenditure 19 [pounds] 19 [shillings]
| and six [pence], result happiness. Annual income 20 pounds,
| annual expenditure 20 pounds ought and six, result misery.'
| 3pm wrote:
| A quote from a related blog post: "Eventually I figured it out:
| basic accounting is just graph theory. Accounts = Nodes,
| Transactions = Edges"
|
| 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...
| contingencies wrote:
| Klepmann is correct but practically you don't control external
| accounts thus cannot authoritatively determine if they either
| exist, have ceased to exit, or the contents of their ledgers.
| Thus, a large number of transactions will always have hanging
| references. This ultimately dictates the need for a "settlement
| state", which should be modeled as a state machine with careful
| transitions. Reversible transactions, fees, taxes and discounts
| then come in to play, some of which may be shared between
| parties, some of which are not calculable before the fact.
|
| 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...
| hum3hum3 wrote:
| Thank you. Those are some interesting references. I do like
| your reference of state machines at the edges of the
| accounting model. Definitely the case in payments systems. I
| don't like how Modern Ledger goes straight to credits and
| debits whereas Klepmann has a graduated approach. I have been
| thinking about writing a bit more about this.
| nirav72 wrote:
| Wish there was similar guide explaining the basics of cooperate
| accounting for developers. As a dev that has to occasionally work
| on integrating web apps with our erp system, I still get lost
| when the erp guys or biz people talk about various accounting
| processes.
| rudasn wrote:
| Don't waste this opportunity to ask people around you and learn
| more stuff.
|
| Personally I think that's the main reason for working on any
| project, let alone being your full time job.
| keeptrying wrote:
| Love the simplicity of their system but don't like the price tag.
|
| Anyone know of cheaper alternatives to ModernTreasury.com ?
|
| Funtionality needed: ledger + integration with payments.
| eatonphil wrote:
| TigerBeetle is an open source ledger database you might be
| interested in.
|
| Not sure about payments integration though.
|
| https://github.com/coilhq/tigerbeetle
| keeptrying wrote:
| Thanks!
| travisG23 wrote:
| Mentioned this above but I'm a co-founder of Proper Finance
| https://www.properfinance.io where are building integrated
| ledgering and reconciliation software for fintech companies. We
| are happy to help where we can - the team were early engineers
| at Marqeta where we built the ledger, as well as reconciliation
| and financial controls. Shoot me a note -
| travis@properfinance.io
| superzamp wrote:
| Don't want to hijack the thread here but we're building an
| open-source platform that includes ledgers and a scripting DSL
| for money movements at Formance, with payments connectors in
| beta and coming up for recon - always keen to chat
|
| https://docs.formance.com
| deshp wrote:
| I'm on the growth team at Modern Treasury. Using our Ledgers
| and Payments products together could definitely help here.
| Ledgers is actually free to get started. The startup plan
| includes 5000 transactions a month and is free forever and the
| enterprise plan has volume-based pricing. We'd love to chat
| more about your use case and pricing. Feel free to drop a note
| - pranav@moderntreasury.com.
| [deleted]
| pwpw wrote:
| As a CPA and software developer, I've been wondering if I should
| build my own product out, but there seems to be a number of
| options available in the ledger space. However, I'm surprised
| there isn't more of an overlap in software engineering and
| accounting. There's a bit of overlap in the fields that scratch
| the same itch. Although the theory behind accounting is a lot
| more interesting than most of the work in my experience.
| macintux wrote:
| I've often thought that had I been born into a world without
| computers I'd have been an accountant.
|
| 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.
| mamcx wrote:
| Making a ledger is step 0 at building ERP-like apps so many of
| us don't see it as big deal (until the complexities of it
| arise!).
|
| 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..
| memset wrote:
| I'm actually the founder is a startup trying to bridge the gap
| between Eng and finance. I'd love to hear your perspective on
| what a good solution would look like!
| taylorhou wrote:
| would love to connect as we're evaluating ledgers for rental
| properties. there are a gazillion management softwares with
| "accounting" ledgers but they were always an after thought.
| we're interested in building the defacto single source of truth
| ledger for real estate. taylor at apmhelp.com
| Naga wrote:
| I'm also a CPA and develop software now (there are dozens of
| us!). I think part of this is that the perception among devs is
| that accounting is too complicated, and therefore can be
| radically simplified in software, which can be true, but
| largely doesn't meet real world scenarios. The problem is that
| accounting is generally complicated because business is
| complicated. I've seen software that throws away the ability to
| add more than 2 line items to a transaction. I've also seen
| software that goes too far the other way and automates every
| step of a process (okay great, how can we show the auditors now
| that this works?). It's a pretty classic misunderstanding of
| the domain, where most CPAs don't speak software, so there's a
| severe lack of communication (which probably makes CPAs who do
| understand software very valuable!).
|
| 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.
| mason55 wrote:
| > _The problem is that accounting is generally complicated
| because business is complicated._
|
| 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.
| pwpw wrote:
| That's great insight. When I was at big 4, I helped look over
| in house software and translate it for auditors to gain
| reasonable assurance that the software was correctly
| implementing accounting processes.
|
| 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.
| NIL8 wrote:
| Could some of these issues you mentioned be helped by
| having the ability to make notes in each accounting stage
| of a particular customer's file?
| travisG23 wrote:
| Hey I'm co-founder of Proper Finance
| https://www.properfinance.io we're building integrated
| ledgering and reconciliation software for fintech companies.
| Happy to be helpful where we can - most of the team were early
| engineers at Marqeta where we worked on the core ledger, as
| well as reconciliation and financial controls - so happy to
| provide perspective. shoot me a note - travis@properfinance.io
| infogulch wrote:
| _Accounting For Developers, Part I_ | 641 points | 7 days ago |
| 188 comments | https://news.ycombinator.com/item?id=32495724
| perlgeek wrote:
| Does anybody know a good SQL / DDL schema for a double entry
| accounting system?
| ruuda wrote:
| I would like to know about this as well. I struggled with this
| for a while for a prediction market app that I'm building.
| Eventually I ended up with [1]. I am somewhat pleased with it,
| but it does feel unwieldy to work with. I have some vague hope
| that somebody who actually implemented banking software would
| know of an obvious and elegant schema.
|
| 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...
| perlgeek wrote:
| Thanks!
|
| 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?
| ruuda wrote:
| Yes, that's a fair summary.
|
| 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.
| yobbo wrote:
| > ... if you have a transaction that involves three
| accounts, that would be split up into two balanced
| mutations, right?
|
| 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.
| vineyardmike wrote:
| GnuCash has a SQL backend, so perhaps you can pick it apart (or
| use it as-is as a library?).
|
| https://wiki.gnucash.org/wiki/SQL
| meekaaku wrote:
| The heart of double entry accounting is extremely simple.
| Forget about asset/liability/expense. Money always flows from
| one account to another. What goes out from account_1 must go
| into another account(s). Typical tables:
|
| 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)
| meekaaku wrote:
| To build a chart of accounts, you can have a parent column in
| accounts table. Account balances is just:
|
| 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.
| ruuda wrote:
| This looks nice, but it doesn't enforce in the schema that
| all transaction lines sum to zero. Is that a problem in
| practice? Or is it one of those things where if you get it
| wrong, you tend to notice immediately because everything
| breaks (as opposed to silently creating or destroying
| currency that goes unnoticed for a long time)?
| perlgeek wrote:
| I guess you could have a stored procedure that checks for
| balanced transactions before inserting (or before
| committing, at least), with SECURITY DEFINER, and not give
| anybody else permissions to insert or modify the
| transaction lines table.
|
| 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).
| yobbo wrote:
| It is bad practice in terms of software engineering, but
| some "real world" apps are implemented like this. They do
| verification in the "middleware".
| meekaaku wrote:
| Its not a problem in practice. There might be a way to
| enforce that through database constraints, but in practice,
| checking transaction lines sums to 0 in business logic is
| not that hard. Having said that, you can run accounting
| entry sanity checks on the entire database. As previously
| said
|
| 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.
| yobbo wrote:
| This sort of schema requires a process to verify the sum
| before committing, and verification is annoying to achieve
| with constraints. Instead, you might consider something like:
|
| transaction_line (id, transaction_id[fk], dr_account_id[fk],
| cr_account_id[fk], amount)
| meekaaku wrote:
| The problem with this will be when you have 3 accounts
| involved on a transaction. Eg, you take a sales receipt
| with part bank transfer, part cash.
|
| 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.
| [deleted]
| yobbo wrote:
| There exists cases where it is problematic, but your
| example is fine. In this case, you would use individual
| transaction lines to represent _payments_ , rather than
| sales, which is closer to reality. But you can group
| transactions in any way you want.
|
| With sales tax, you would have:
|
| cr: sales, dr: cash, $100
|
| cr: cash, dr: tax, $10
| meekaaku wrote:
| The above looks like $70 was taken from cash and
| deposited to bank. Thats not whats happening in the real
| world. Since cash is fungible, one could ignore that. But
| if it was cheque + bank-transfer or something else that
| leaves a record, then that wont work. Or when you include
| VAT/GST/Sales tax. Or when you pay a loan monthly payment
| of $1000 that needs to be split to principal and
| interest. etc.
| yobbo wrote:
| Yes, it is best if transactions correspond to verifiable
| events. Complicated situations can be modelled by letting
| events be represented by their own "accounts", in a
| separate charter.
|
| 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.
| gen220 wrote:
| The experimental directory of `beancount` has some code for
| converting beancount data structures into SQL structures [1].
|
| 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
___________________________________________________________________
(page generated 2022-08-24 23:00 UTC)