Hacker News Reader

> You forgot "consistent"

I once had an auditor tell me that the books are allowed to be wrong, but should be wrong consistently. I've even run into the timestamp issue, which generally turned out to be a non-issue unless the wrong date was used (like order date vs. shipping date for revenue recognition) or a considerable number of errors caused dates to end up in a different reporting period. If you're dates are consistently a few hours off, then they'll be consistently off across reporting period boundaries and that can easily be explained to an auditor.

> the necessity to batch reconciliation processes between these layers

I've never seen a reporting period closed without accountants making journal entries for manual adjustments. These can often be material changes.

> An accounting system does not convert currencies at will

This is correct, but for internal reporting you might need to. An accountant and cash flow will capture the important conversion rate when the funds are expatriated, but your finance dashboard showing sales in euros will have an executive that wants to see it in US dollars. It's impractical to use the conversion rate from the transfer that could happen weeks or months after the reporting period, so you come to a compromise of using a X-day FX moving average that's "close enough" and everyone's happy. What goes in your 10-K/Q will be a different story.

> I've never seen a reporting period closed without accountants making journal entries for manual adjustments. These can often be material changes.

The fiscal view is just one of the layers your accounting system takes into considerations.

You typically perform fiscal reconciliation when the fiscal year ends, your bookings are often reconciliated end of day, your settlements usually between 1 or 2 days (depending on the currency), your FX hedge often quarterly to enter IMM interest rate swaps, your front office is often optimistic realtime accounting (i.e. you consider what has been promised just now to already be out of the stock), etc.

> This is correct, but for internal reporting you might need to.

Right but this is more some form of Bi on top of the accounting system than part of it. For the accounting system itself, you will often want to know precisely the cash per currency so that you can actually hedge it. e.g. if you're ultimately getting paid in dollar and have exposure to some salaries you pay in euro.

Seems to be like you're in violent agreement with the OP on all those points, just phrasing the same truth differently.

> Most financial transactions have multiple time dimensions: a trade is requested, booked, filled, matched, reconciliated, paid, settled at different times. These times often don't even follow a perfectly logical order (you can request and fill a trade and book it an hour later).

Similar issues exist with transaction identifiers. Every party involved with any aspect of a transaction may assign that aspect of the transaction zero or more identifiers, those identifiers may or may not be unique over any particular set of transactions and/or events related to transactions, and the sources of those identifiers may or may not even correctly document what, exactly, those identifiers identify. (And their representatives may think they know, and they may be wrong.) Your own code may need to generate identifiers for events associated with transactions, and you may or may not want to leak information in your choice of identifiers.

For new designs, my suggestion would be to treat these as one-to-many relations. Have a table along the lines of (transaction id, event, identifier issuer, identifier name, identifier). You might also need a timestamp in there, and you may need to throw in a row id of some sort as well. You might want to make an allowance for identifiers having variable type — are they bytes? Unicode? something else? You could plausibly get away with making these be JSON keys in an event object instead, but this gets awkward when someone tells you an identifier after the fact.

Spot on.

Worst, these identifiers can even be point-in-time. I usually store them as you do, with an additional start/end date.

There is an interesting tension in software between measurement and validation. The two concerns are often conflated, resulting in software systems that cannot measure the world as-it-is. The world (more precisely, measurements of the world) may be in (and often is in) an 'invalid' or 'inconsistent' state. Software can only help you detect and resolve such states if it is capable of representing them.

If you are only doing accounting (and not like high frequency trading), you can even consider using arbitrary precision rational numbers internally. They are most likely fast enough.

However, similar to what you suggested about currency conversion, your business logic might already dictate to you exactly what kind of precision and rounding rules you should be using; and deviating from that might cause havoc.

> No! Delay currency conversion _until conversion occurs_!

Sometimes these are legal requirements one way or the other. In my country, invoices in other currencies are based on the ECB rate of the date of the invoice, no matter what the actual value is when converted.

That doesn’t contradict the advice; the discussion above is about the accounting entries, not the invoice. An invoice issue is a commercial event, and the amount of the invoice is the actual value of the invoice, and will be the amount debited to accounts receivable and credited to income when the accounting entries are written, and must be in the reporting currency of that entity, so a conversion does occur on that date of issue.

If a different amount is accepted later in payment, then that difference is entered as an adjustment to income, probably coded to an account with a name similar to “gains/losses due to currency fluctuations”.

Do not confuse the commercial document/event for the accounting entries. The latter is a product of the former and they explain each other. In principle the ledgers can be entirely reconstructed from the primary records of commercial activity. The distinction is made even more clear by the existence of processes that validate one from the other viz. reconciliation and audit.

The broader takeaway is that your database of commercial activity must not use the accounting entries as its primary record. In my view the only data structure these two share is the chart of accounts.