Month-End Bank Reconciliation

Ticket FIN-2318, Brandt & Voss Industriebedarf GmbH, Hamburg (a fictional distributor of industrial supplies) From: Henrike Brandt, Head of Finance To: Controlling

I can't sign off the month until the cash work is done. For the month of the ERP export I need the reconciliation of our two cash G/L accounts against the bank statements, the revaluation of the USD account, the journal for the items that only the bank shows, and the cash application for AR. Do it the way last month was done: that close is in data/reference_case/, and I approved it. Write it as a function so that we can run it every month.

I'm in meetings most of the day. Ask me what you need to know, but I can answer a few questions, not dozens.

Deliverable

Write solve(files) in solution.mjs. files maps each path under data/ to its text contents, for example data/bank_a/camt053_….xml, including data/reference_case/ and any files that have nothing to do with the close. Close the month of the ERP export in data/erp/ and return the object described under Output.

The graders run solve on other months from 2026 and 2027, generated the same way with different transactions, rates and mistakes. Those datasets have no data/reference_case/ folder, so solve must not depend on it.

Working on this challenge

• Questions. Henrike decides how the close is done, and some of what decides it isn't written down anywhere in the folder. Ask her with vibrometer ask "your question". You have 6 questions, she answers only what you ask, and a vague question gets a question back.
• Follow-up. Run vibrometer update from time to time. Anything new from Henrike arrives in your folder with a CHANGE.md. The graders always test the latest requirements.

Input files

• data/erp/: the house banks, ERP exports of the cash G/L postings, G/L opening balances, customer open items, payment terms and the ERP's account determination.
• data/bank_a/: files from Elbtal Bank (EUR current account). data/bank_b/: MT940 files from Meridian Trust (USD account).
• data/fx/: ECB reference rates. data/vendors.csv: the vendor master. data/notes/: emails and other documents.
• data/reference_case/: last month's inputs, approved_reconciliation.json and the approval note.

Standards

• ISO 20022 cash management messages: camt.053 (several versions) and camt.052, and the External Code Set for Bank Transaction Codes.
• SWIFT MT940, including the structured field :86: used by German banks (Die Deutsche Kreditwirtschaft).
• ISO 11649 structured creditor references. SAP posting keys 40 and 50.
• ECB euro foreign exchange reference rates. HGB section 256a and IAS 21 on translating foreign currency balances at the closing rate.

Output

{
  "period": "2026-10",
  "accounts": [
    {
      "glAccount": "102100", "bankAccount": "DE00200400600000000000", "currency": "EUR",
      "bankClosing": 120000.5, "bankClosingEur": 120000.5, "glClosing": 118000,
      "adjustedBank": 119500.5, "adjustedBook": 119500.5, "difference": 0, "matched": 21,
      "unmatchedBank": [{ "ref": "20261012000552", "bookingDate": "2026-10-12", "valueDate": "2026-10-12", "amount": -41.26, "amountEur": -41.26, "reason": "bank_charge" }],
      "unmatchedLedger": [{ "doc": "1500034815", "postingDate": "2026-10-19", "amount": -10122.4, "amountEur": -10122.4, "reason": "duplicate" }]
    }
  ],
  "matches": [{ "glAccount": "102100", "bankRef": "20261008000550", "ledgerDocs": ["1500034780", "1500034784"], "rule": "batch" }],
  "fxRevaluation": [{ "glAccount": "102200", "currency": "USD", "balance": 250000, "bookEur": 221000, "rate": 1.1, "revaluedEur": 227272.73, "gainLoss": 6272.73 }],
  "journal": [{ "glAccount": "102100", "postingDate": "2026-10-12", "type": "bank_charge", "ref": "20261012000552", "amount": -41.26, "amountEur": -41.26, "offsetAccount": "000000" }],
  "cashApplication": [{ "glAccount": "102100", "bankRef": "20261005000561", "valueDate": "2026-10-05", "customer": "C20000", "invoices": ["INV-2026-01234"],
      "openAmount": 1000, "received": 980, "discount": 20, "difference": 0, "status": "discount" }]
}

The values only show the shape; approved_reconciliation.json has the same shape.

• period is the month being closed, YYYY-MM. accounts has one object per row of house_banks.csv, sorted by glAccount; bankAccount is the account identifier without spaces, in upper case. bankClosing is the bank's closing booked balance, glClosing the G/L balance as the ERP shows it, and difference is adjustedBank - adjustedBook. matched is the number of objects in matches for the account.
• unmatchedBank (sorted by bookingDate, ref, reason) and unmatchedLedger (sorted by postingDate, doc) are the open items, signed positive for money in, with an amountEur. A bank ref is the entry's ID from the statement. Each open item has a reason; the reasons, rules, types and statuses are the ones that the approved file uses, plus any that Henrike gives you.
• matches is sorted by glAccount, then bankRef, with ledgerDocs in ascending order.
• fxRevaluation has one object per account whose currency isn't EUR, sorted by glAccount. rate is the ECB reference rate, in units of the currency per EUR as published. gainLoss is revaluedEur - bookEur.
• journal holds the entries that Controlling posts, sorted by glAccount, postingDate, ref, type. amount is the effect on the cash G/L account in the account currency, positive for a debit. offsetAccount takes the other side.
• cashApplication has one object per customer payment, sorted by glAccount, then bankRef. invoices are the invoice numbers that the payment names, as the open items write them, sorted. difference is received - openAmount + discount.
• Dates are YYYY-MM-DD. IDs, account numbers and document numbers are strings. Money is a number rounded to cents, half away from zero.

The solution has to be one file that imports nothing except node: built-ins. It gets no file system or network access, and every input arrives through files.