Engineering principles for building financial systems
substack.wasteman.codes
substack.wasteman.codes
This may also mean promoting them to a named part of the business-domain in code, with their own functions, unit-tests, stuff like "fetch Rounding Strategy Suite by country code", etc.
> Use integer representations of time. This one is a little controversial but I stand by it. There are so many libraries in different technologies that parse timestamps into objects, and they all do them differently. Avoid this headache and just use integers. Unix timestamp, or even integer based UTC datetimes work perfectly fine.
Warning: This only works for times that are either firmly in the past or which represent a future delay-offset which is totally under your own exclusive control.
For example, suppose your company has a 48 hour cancellation policy. You can probably compute a future timestamp and use that, it won't depend on whether a customer is about to hit a DST transition or leap-seconds or whatever. nsition.
In contrast, the nation of Ruritania may have their tax year ending at December 30th 17:30 Ruritania Time, but you don't reeeeealy know how many seconds from now that will happen: Tomorrow the Ruritania congress may pass a law altering their tax-schedule or their entire national time-keeping. This means your underlying source-of-truth needs to be the matching conditions, and that often means storing the time-zone.
In this case, the technical outgrowth might be heroic over-weekend crunch: Write some code that finds every affected timestamp in the DB, reconstructs the various code-chains that created them, divines an "original meaning", calculates the difference, and somehow apply changes in a way that doesn't make auditors angry or lock a key table for and hour or leads to data-corruption from race conditions...
> Granularity of your financial amounts should ...
... comply with relevant regulations.
I worked on a gambling webapp a long time back - we were bound by government regulations on gambling licences to perform all calculations at a precision of one ten thousandth of a cent, and were only permitted to round to cents once at the very end just before displaying a dollar amount to a person.
(This suited me down to the ground because I pointed out to everybody that Javascript couldn't be guaranteed to treat numbers and calculations in a way that would keep the regulators auditors happy, and managed to push _every_ calculation involving dollar amounts off to the Java backend team.)
We also used integer UTC milliseconds for time, so we'd have good enough for the auditors timestamps showing who placed a valid bet at (say) 12:34:59.9997 and who else place an invalid bet at 12:35:00.0003 for a wager with a cut off time of 12:35. (We asked for and got a ruling from the regulators that the time that mattered was the time the API call was processed by the backend, since network latencies between the webapp and the backend platform were impossible to predict. I have no idea if the backend had millisecond accurate time, that wasn't my problem and I _so_ didn't want to have them make it my problem by asking the question.)
I haven't been in that situation, but I imagine I would reach for a "what if it was snail-mail" analogy. "It's dangerous to honor whatever time the bettor wrote by hand, the postmark-time may not be fair if the mailman got delayed because of a half-illegible address or random USPS delays... So how about we go for when it was actually delivered to us?"
Did you not consider to use decimal math? According to MDN, the largest int in Javascript is 2^53 – 1. If you steal four digits for your fraction, that still leaves a huge number. I will assume that no one was gambling more than one billion currency units, so you had plenty of space. Do all your calcs with ints, then divide by 10,000 for a final result.
Ref: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
I suspect there's an important set of gotchas lurking in here around the difference between actually doing calculations with integers---which is not possible in Javascript--versus doing calculations with floating-points that happen to represent (safe) integers.
Javascript can represent dollar values up to $2.25 billion at a resolution of ten-thousandths of a cent without any loss of precision.
I would want all money calculations done by the backend team as a matter of policy. But it's not a technical limitation.
let a = 35
let b = -34.99
console.log(a+b)
// output: 0.00999999999999801For serious work, it’s worth noting that leap seconds are not representable in this format. Many financial applications can get away without representing leap seconds, but this is fundamentally a kludge. If you actually need to represent any point in time (according to UTC or any particular timezone), use a representation that actually represents what you want.
This is especially true for future times. If you mean “10 AM, August 1, 2025, New York time”, then represent that! Do not kludge it as an integer, do not use a different time zone as a proxy, do not use UTC, and don’t use a representation that contains the terms “standard” or “daylight”. If you do this wrong, you will eventually regret it. Bonus points for also being able to represent future leap seconds, I suppose, but I’ve never encountered any need to do that.
Keep in mind that those properly represented future times are not guaranteed to ever happen. Time zone definitions can and do change.
(And, since this is finance, you also get to deal with bank holidays, past, present, and future. Your legal department may have opinions about what should happen when a future date, that you thought wouldn’t be a bank holiday, turns out to be a bank holiday after all.)
(Also, since this is finance, sometimes people will want to translate a time into a date. This can be a nontrivial operation. The date associated with a timestamp may or may not matter for actual business purposes, and various parties may or may not agree about what date a certain time belongs to and may or may not implement their opinion correctly.)
When storing date/time stamps, I prefer to persist into two separate fields: (a) date/time using UTC time zone and (b) original time zone offset, e.g., UTC+8. When debugging, it is useful to know the original time zone offset. And when querying and processing data, it is easier to use UTC time zone.
They’re certainly unusual.
> If so, it is safe to ignore them.
Really? Leap seconds have happened, and they could plausibly happen again. If so, during those leap seconds, events will occur. Do you need to record the time of those events?
> When storing date/time stamps, I prefer to persist into two separate fields: (a) date/time using UTC time zone and (b) original time zone offset, e.g., UTC+8.
That covers most bases for past events. It is inappropriate for many future events.
Hmm, maybe a good system library would have separate types for timestamps that were actually observed (or interpolated or otherwise computed after the fact, etc) and for times of events that may not have occurred at the time of computation.
According to the standard for increasing leap seconds, they interact with the opening auction of the Tokyo Stock Exchange by delaying it one second.
if you say so, but don't whinge if someone pulls off a major heist during a leap second by counting on you to ignore it
> "Leading up to the 2015 leap second event, there was concern within the financial industry that similar issues could lead to a disruption to trading operations across exchanges, clearinghouses, brokers, investors and key service providers. The Commodity Futures Trading Commission asked U.S. futures exchanges to provide details on how they planned to approach the leap second event. Some exchanges published plans to delay the opening of trading. Several exchanges in Asia have also announced details regarding how their systems will adjust their clocks before or after the leap second, and will retain normal trading hours."
Offsets rotate throughout the year.
If you store only the offset, you can’t necessarily apply a delta to the time and localize it to the origin locale.
But UTC doesn’t work at all for a calendar app, where a calendar entry could for example span a calendar day, in some specific time zone, especially if it is in the future and there is not yet a mapping between that future date and UTC.
And last: I really like your example about Ruritania and the 48 cancellation policy. Sure, the country name is intended as a joke, but in the Real World all kinds of crazy things like this. Even Islamic calendars can be quite complex in 2024 -- knowing what days will be holidays in the future is a non-trivial task.
So don't use a time-like object. Instead use a condition.
1. ACID, so you don't have to invent it.
2. Arbitrary precision numeric data types with vetted operations and rounding modes.
3. It does time as well as anyone can
4. Your computations and reporting can be done entirely in SQL.
5. When you get good at SQL or hire someone who is, the reporting is elegant.
6. It's as fast as it needs to be, and a good database architect can make it extremely fast.
7. It has all of the tooling to do disaster prevention and recovery.
I've built so many financial systems for the biggest multinationals in the world, and I have never regretted doing the bulk of the work in the database. Coca-Cola, GE, UPS, AIG, every iteration of the Tycos, basically every pharma with their crazy transfer pricing schemes... Whenever I experienced a performance problem, there have been way to address it that were far easier than what would have been to reinvent all of the hard tech that underlies consistent, accurate financial systems.
Be aware that a good rationale for choosing the database but you should enforce a specific precision per table otherwise you can get nasty accounting bugs because you're adding a 3.0095 amount with an 9.99 amount.
But the broader point is that this piece is about accounting-related systems that don't typically deal with hard volume, speed, or latency constraints. The term "finance" can mean the corporate "finance & accounting department" but you want to differentiate that from "finance" the industry. The headline does really tell you what actual material is.
Right, like, if you're a programmer going into a project-for-finance/accounting-team at your large megacorp, and don't know if this article is the right subject area, just ask the finance/accounting person about "year end close" and see if they get a thousand yard stare or not. If yes, this is the right article lol.
Guess there wasn't really anything in the article to say if it was for internal (operational) or external (financial)
[1] Fast equities trading and slow complex exotics and bonds trading/pricing/risk. Strictly speaking the fast thing was algo trading[2] rather than actual HFT but we were counting microseconds etc so it's similar in the time constraint.
[2] The distinction I would draw is algo trading is algorithmic execution of client orders in a brokerage setting vs HFT is proprietary trading like you would do in a hedge fund. In algo trading a lot of thought goes into how you can ensure your clients are not able to be ripped off by HFTs in the market, so they are sort of the adversary even though a lot of what you're doing is similar from an execution pov.
That is part of the reason financial systems backed by SQL are so complicated and fragile while old COBOL code keeps going.
RDBMS tutorials always use ATMs as an example, but you can't 'roll back' with ATMs, you have to use compensating actions.
ACID is useful in many contexts, but actually adds complications to financial transactions.
Think of the ledger as being the source of truth, with the DB as simply a materialized view.
It has applications with edge caching in web apps too. Events as the source of record can help get rid of cache invalidation challenges compared to cache aside or write back.
Two sides errors that don't impact the trial balance is something to search for if you want to know why an ACID DB as the source of truth is problematic in accounting.
> The three main goals of your accounting system are to be (1) Accurate, (2) Auditable and (3) Timely.
You forgot "consistent", which is very different from "accurate". 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). These time dimensions are often why there are multiple, layered, ledgers to store transactions. An accounting system needs to provide consistent views that depend on the dimension you want to look at (e.g. a trade that is filled but not settled counts for front office accounting, not for back office accounting).
> If there is an event that occurred but is not in the financial record, than the system is not complete.
The base assumption of every (working) accounting system I have worked with is the reverse. There WILL be transactions that flow through the system in an untimely manner, your system needs to handle that, thus the need for multiple layers of ledgers, and the necessity to batch reconciliation processes between these layers.
You will book trades after they are filled. You will pay materials before inputing the bill. Etc.
> If you are only working with dollars, representing values in cents might be sufficient. If you are a global business, prefer micros or a DECIMAL(19, 4).
If you are a global business only working with euro and dollar maybe. Otherwise most FX markets quote to 8 decimals, and that's not counting swaps. I would recommend using at least 8 decimal places for storage.
> Delay currency conversion as long as you can. Preemptively converting currencies can cause loss of precision.
No! Delay currency conversion _until conversion occurs_! An accounting system does not convert currencies at will. There is no dollar equivalent to euro, you either have dollars or euro. And it's not a matter of rounding errors, cash management is part of legal and accounting duties.
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.
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.
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.
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.
Worst, these identifiers can even be point-in-time. I usually store them as you do, with an additional start/end date.
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.
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.
As someone who works in accountancy (as a bookkeeper/accountant) I have to be brutally honest and say that I've been very disappointed in the UI of all the accounting software packages I've used. None of them give me the immediacy or simplicity of a well organised filing cabinet, they really don't.
I'm not sure what a good solution would look like, but I can't help thinking that making the double-entry system more visible and apparent to the user would be a good start.
Indeed.
> I'm not sure what a good solution would look like
What is interesting is that on the business reporting side (which is in any case deeply enmeshed with the accounting system side) there is a defacto standard (xbrl and its various specifications [1]) which via web technologies (iXBRL [2]) provides a path towards generic web UI patterns.
[1] https://www.xbrl.org/int/gl/2016-12-01/gl-framework-2017-PWD...
While I managed our start ups finances, I so much preferred to stay doing everything in Excel over having to use our accountants system. And I get a glimpse into SAP from time to time as my wive is an accountant. It's incredible how shitty those systems are from the user perspective.
But I guess, this is what you get when the user is not also the customer.
No. Designing a system that is always up and running and can process small amounts of data constantly is a completely different problem from designing a system that runs occasionally with a lot of data. For one thing, your output formats are usually different in the latter case (maybe you're creating a PDF for example). Also the high availability requirement just makes things different at the design level.
Finally, the author claims it's not hard to switch between batch and streaming. With a large volume of preexisting data, this is just not true. For example, if you make a REST API call for each document in a DB, it can take days or months to load that. If batching together documents isn't a possibility, how do you move data between stores easily? (This data movement is often required when switching between batch and streaming.)
Sounds like you really went though a rough time building the invoicing system!
I have read many times about the difficulties of dealing with decimals/rounding but never found an article that nailed the explanation as well as you did
Something I've found valuable is on is managing euler units/ratios (e.g. proportions 10^1, percentages 10^-2, basis points 10^-4) . Enforcing a standard that all interchange, data storage has either the same scale, _or is stored with its scale (and not in the name)_ will reduce errors hugely significantly.
This sounds like a type system issue (and the linked Etsy post is also a type system issue), or more precisely languages / systems with a loose type system where you can just pretend a time is a number without having to explicitly say what that number means.
Conventions are good, but (real) type systems are better because they check everything every time.
The main difference is whether the compiler will yell at you, or whether it'll be a runtime error. But the important point is that your mixing up won't go through and silently do the wrong thing.
However, beware as the result of your test will also be seen by auditors, so if you refactor a system, it is better to have a write/solve approach than write all your tests firsts and solve them afterwards.
For example, if you had three monthly payments of $5, $10, and $10, you might book something like:
Cash (5) Expense 8.33 Deferred (3.33)
Cash (10) Expense 8.33 Deferred 1.67
Cash (10) Expense 8.33 Deferred 1.67
All three of those blocks of entries balance, but the sum of expenses is 24.99 instead of 25.
I’m not sure there’s a way around this issue if you’re forced to use two decimal places. Luckily the discrepancy is immaterial. I’d love to know if anyone else has encountered this problem.
I followed this mantra when building a trading system. This was the worst engineering decision I ever made - it added complexity and runtime overhead, plus readability was reduced when debugging.
If your language has a proper decimal type like BigDecimal in Java or Decimal in Swift, I'd suggest using it. They offer perfect precision for financial applications, are battle tested and are a natural way to represent financial amounts. Yes, Java BigDecimal is unweildy, but it works.
If you're using JavaScript however, definitely use integers to represent financial amounts.
Correct me if I'm wrong. The last I checked, BigDecimal in Java falls short when it comes to the calculations. ( very big and very small numbers). It does do well on rounding. There might be other libraries that are better when it comes to calculations. If those libraries don't exist integers are the way the go.
If you could describe how to represent all possible financial values using integers in a clean manner, I'd love to know. It gets messy very quickly if you're dealing with dollars at two decimal places and BTC at 8 decimal places.
Some of their systems stored - and therefore the APIs - presented interest rate as a simple number (5% was 5) where other used a decimal (5% was 0.05) and still others used basis points (5% was 500).
One of their VPs told me they once saw an unexpected uptick in loan applications and dug in to find out the quoted rate was 0.05%
Consistency is key.
Actually, as another commentor mentioned, you can and should just choose something that represents what you want. In many cases it will be an object with various domain-specific fields. If you need to be sure that "3.99" always comes back as "3.99", you can use a string, which doesn't have any issues with precision in the storage format, and will usually map exactly to how humans deal with the concept. (Not necessarily recommending this, but it's worth considering.)
Similarly with JavaScript precision issues, to take another example that's been mentioned here - if you need numbers to be precise and the performance cost is acceptable, JavaScript can compute to arbitrary precision just as well as any other language. Just don't assume you're forced to convert your concept of a number to use the built-in numeric types and operations.
Decision making factors that in sure but system design absolutely shouldn’t. There is no reason why accounting systems can’t be accurate down to the penny. That’s what computers are good at after all.
Only place where I could see system like relevance is in presentation- showing revenue on millions etc
Decimal floating-point gives you the range to cover everything but is generally very inefficient to process.
What you usually want is fixed-point decimal, with the caveat that the scaling factor will be different per asset.
You could probably use arbitrary precision rational numbers (eg as implemented in Python https://docs.python.org/3/library/fractions.html) for all your accounting needs, and it would still be fast enough.
That's a lot to process, so yes, it matters.
If you want to do financial pricing of lots of instruments, performance is important.
If you want to do the accounting for one company (that is not trading in millions of financial instruments but, say, is building and selling widgets), you have far fewer numbers on your books.
Do you still owe me a tenth-of-a-cent? Even if you do, does anyone (me, you, the taxing authorities) care? In fact, does anyone want to go through the effort of maintaining that debt? Even if I repeat this with 100,000 customers, I'm out $100. Again, does anyone care?
Because it matches the requirements of the domain being modeled.
Most business to business and business to consumer transactions require that amounts are limited to some number of decimal positions (e.g. in US it's 2 decimal positions).
If you create a system document/transaction like an invoice that doesn't adhere to this rule then your software is not properly modeling the domain (i.e. it's a design flaw).
If you decided to leave it unrounded internally, then you are basically storing an incorrect value and delaying the calculation of the correct value until consumption (which is typically hundreds to thousands of consumers).
But what is the gain to storing the wrong value? Nobody wants it and it just complicates the entire system.
Very few values have exact decimal and binary floating point representations.
Centralize from where the ts is set (the db is a great place to do this.) Don't let client-code set the timestamp.
Or perhaps a Kafka partition / commit logs with infinite retention.