Syncing with external ledgers

Until now, we have focused only on the ledger within a company. However, a company also has external ledgers with partners. Every partner storing assets or liabilities for a company is an external ledger. The most common example is a bank for the bank accounts. In this week's issue, I'll focus on external ledgers in accounting.

External ledgers at partners

Earlier, we saw how we could internally manage a ledger of our own business. We also learned about sub-ledgers for relations. A partner can report transactions and a balance to their customers based on this sub-ledger. For a business, it is essential to include these sub-ledger transactions in their internal ledger.

You add the external ledger to the bookkeeping by copying the transactions reported by the partner. Then, periodically, you validate the resulting balance with the balance of the external ledger.

Synchronizing with an external ledger

In the past, partners provided the transactions from external ledgers with paper statements. The most common example is bank statements.

Since the rise of electronic banking, it has become easier to export statements and import them into accounting software. File formats like MT940 originated in the SWIFT network for end-of-day reporting. More recently, the ISO20022 standard has been gaining traction with a specific CAMT.053 format for financial institute to customer reporting. Someone imports these files and adds each transaction to the ledger account for the particular bank account. Depending on the size of the business, manual importing of transactions from external ledgers is done daily or yearly. In most cases, it was hard to build software around the assumption of the availability of the transactions.

╭───────────────────────────────────────────┬───────┬────────╮
│ Ledger account                            │ Debit │ Credit │
├───────────────────────────────────────────┼───────┼────────┤
│ balance_sheet.current_assets.bank_account │   0.0 │  100.0 │
│ balance_sheet.current_assets.to_reconcile │ 100.0 │    0.0 │
├───────────────────────────────────────────┼───────┼────────┤
│                                           │ 100.0 │  100.0 │
╰───────────────────────────────────────────┴───────┴────────╯

In most situations, the transactions are reconciled later, so we use a temporary ledger account to collect all reconcilable transactions. In a future issue, I'll tell you more about the reconciliation of transactions.

In recent years the connection with external ledgers is changing fast. The PSD2 regulations in the EU unlocked API connections with banks. Aggregators like Ponto allow easy API connections with many European banks through one API connection. Neo banks like bunq and Revolut provide real-time APIs for transaction reporting. Due to the programmed connections, transactions from external ledgers are available earlier and more accurate. Software engineers can benefit from this.

Validating an external ledger

The external and internal ledger needs to be in sync. The slightest error can cause nasty situations. Someone who worked with manual imports in the past knows how time-consuming it can be to track down the missing transactions. Therefore, it is essential to validate the external ledger regularly.

Most accountants will check the external ledger during the closing of the books after a year. In addition, most banks provide an End of Year statement, which contains the start and end balance of the bank account. These balances should match with the internal balance. If they don't match, an error arises.

Modern APIs report balances during a year. It is wise to validate the external and internal ledger more often. Sometimes it is even possible to find the error automatically and fix it without user interference.

More complex external ledgers

With the rise of PSD2, connecting with banks in de EU has become much more manageable. In the US, startups like Plaid provide easy access to bank transactions. Although bank accounts are a majority of external ledgers in bookkeeping, other external ledgers are still time-consuming.

Creditcards still don't have one standard format for reporting transactions. Nor do they provide a single API for automatically retrieving statements. However, neo-banks like bunq luckily provide credit card statements through their API.

If a business is active in cryptocurrency, this is also an external ledger. Technically the blockchain is already unlocked. The foreign currency aspect poses a challenge, just like foreign currency bank accounts are challenging in accounting—more on this in a later issue.

Payment Service Providers are another example of an external ledger. Most PSPs provide good APIs to report in transactions. In some cases, the APIs are not usable as an external ledger. For example, an external ledger should also report a payout to another bank account as a credit from the external ledger. Many PSPs only report transactions in their APIs, making an external ledger connection challenging to manage.

Global coverage

External ledgers are, in my opinion, one of the more challenging parts of global accounting. Double-entry accounting is the same in all countries. The ledger account system is comparable, with slight variations across countries. As soon as you want to serve people worldwide, you want to connect with as many external ledgers as possible. Without automatic external ledger synchronization, you cannot achieve innovative real-time accounting.

Although PSD2 has unlocked the gates at banks, the directive doesn't provide an API specification. The lack hereof causes many banks to have different implementations. Hopefully, initiatives like the Open Banking Standard will bring standardization to this industry.

At Moneybird, maintaining connections with external ledgers is a significant part of our day-to-day jobs. And we only need to support the Dutch market. If you need to synchronize with external ledgers in your software, I'd advise finding an aggregator covering many of the required external ledgers. Building and maintaining only one API connection with the aggregator will save you a lot of time!

Next issue

After importing transactions from external ledgers, the next step is to reconcile them in our bookkeeping. Reconciliation is a topic for a future issue. Next week, I'll present a use case for double-entry accounting in Payment Service Providers. If you haven't, subscribe to receive the issue in your mailbox as soon as it releases.