元帳から借方貸方の不一致を見つけ出す

目標

複式簿記の元帳から始めて、借方と貸方が合っていない伝票1件を仕訳行1行まで絞り込み、元の記録を消さない方法で直したうえで、残高表まで元帳に合わせられるようになります。

なぜ重要なのか

銀行システムでは、残高は保存された値ではなく、元帳から計算して出す値です。性能のために勘定ごとに残高の列も持っていますが、それは元帳の写しであって原本ではありません。

この区別が調査で決定的になる理由は次のとおりです。残高1つはただの数字なので、その数字だけでは合っているか間違っているかを知る方法がありません。一方、複式簿記の元帳は自分自身を検証します。すべての取引が借方と貸方の両側に記録されるため、元帳全体の借方合計と貸方合計は必ず等しくなければならず、この恒等式が崩れていること自体が事故のサインです。そのサインは、伝票単位でさらにもう一段絞り込めます。

今回の事案は次のとおりです。顧客企業の運用担当者から「決算の残高が合わない」と連絡が来ました。3日分の元帳にずれが2か所あり、性質が互いに正反対です。一方は元帳が間違っていて残高表が合っており、もう一方は元帳が合っていて残高表が間違っています。この2つを切り分けずに手を出すと、直す側が逆になります。

訂正の方法も一般的なサービスとは異なります。間違った伝票をUPDATEで直してはいけません。元の伝票を鏡のように反転した反対仕訳を入れて相殺し、正しい伝票を新たに起票します。元帳に3枚が並んで残っていてこそ、2か月後の監査で何があったのかを説明できます。

ステップ

/root/bank/gen_ledger.pyを作成して実行し、/root/bank/ledger.dbを作ってください。勘定10個、伝票201件、仕訳行430行が入ります。
/root/bank/trial_balance.csvに勘定ごとの試算表を出力してください。ヘッダー行はcode,name,debit,credit,balanceで、balanceは借方合計から貸方合計を引いた値です。一度も動かなかった勘定も残します。
/root/bank/tb_totals.txtに4行書いてください。debit_total=、credit_total=、gap=は元帳から、cached_sum=はaccounts.cached_balanceの合計です。
伝票ごとに借方合計と貸方合計を比べてずれた伝票を探し、/root/bank/broken_entry.txtにentry_id=、debit=、credit=、gap=を書いてください。その伝票の仕訳行は、/root/bank/broken_lines.csvにline_id,entry_id,account_code,dc,amountのヘッダー行とともにそのまま書き出されます。
/root/bank/balance_diff.csvに、code,cached,ledger,gapのヘッダー行で、残高表と元帳の合算が異なる勘定だけを残してください。gapはcachedからledgerを引いた値です。
元の伝票には手を触れず、訂正伝票を2枚入れてください。反対仕訳はJE-20260826-0134-R、再起票はJE-20260826-0134-Cとして登録し、journalとjournal_lineの両方に入れます。
accounts.cached_balanceを元帳の合算で再計算して上書きし、/root/bank/recheck.txtにdebit_total=、credit_total=、gap=、unbalanced_entries=、original_credit=、cached_sum=、balance_diff_rows=の7行を書いてください。
/root/bank/ledger_report.mdに## 무엇이 틀렸나、## 어떻게 찾았나、## 어떻게 고쳤나、## 잔액 표는 왜 못 잡았나、## 재발 방지の5つの節でレポートを書いてください(見出しは韓国語で、順に「何が間違っていたか」「どう見つけたか」「どう直したか」「残高表はなぜ検出できなかったか」「再発防止」という意味です)。

参考

sqlite3 -readonly <파일> "<쿼리>"で読むと、調査中に誤って書き込むことを防げます(プレースホルダーはファイル名とクエリです)。
CSVに出力するときは、標準入力で.headers onと.mode csvを先に渡してください: sqlite3 -readonly ledger.db > out.csv <<'SQL' ... SQL。
符号付きの残高はSUM(CASE dc WHEN 'D' THEN amount ELSE -amount END)で一度に出せます。
よくある間違い1: ステップ2で、動きのない勘定を表から除いてしまいます。そうすると、「動きがない」と「勘定がない」が区別できなくなります。
よくある間違い2: ステップ6で、反対仕訳の金額を正しい金額で書いてしまいます。記録された金額のまま反転して初めて、間違いが正確に相殺されます。
よくある間違い3: ステップ7で、伝票単位の不一致が0になるはずだと期待してしまいます。元の伝票と反対仕訳が対になって残るのが正常です。

元帳のスナップショットを作る

/root/bank/gen_ledger.pyを作成して実行し、/root/bank/ledger.dbを作ってください。勘定10個、伝票201件、仕訳行430行が入ります。

まず/root/bankを作成し、その中でpython3を使ってsqliteのDBを作ります。テーブルはaccounts、journal、journal_lineの3つで、仕訳行は430行です。

試算表を作る

/root/bank/trial_balance.csvに勘定ごとの試算表を出力してください。ヘッダー行はcode,name,debit,credit,balanceで、balanceは借方合計から貸方合計を引いた値です。一度も動かなかった勘定も残してください。

勘定ごとに借方金額の合計と貸方金額の合計を別々に出し、balanceは借方合計から貸方合計を引いた値にします。この3日間に一度も動かなかった勘定も表に残る必要があるので、LEFT JOINを使ってください。

元帳と残高表をそれぞれ集計する

/root/bank/tb_totals.txtに4行書いてください。debit_total=、credit_total=、gap=は元帳から、cached_sum=はaccounts.cached_balanceの合計です。

4つの数字です。最初の3つはjournal_lineから、最後の1つはaccounts.cached_balanceから出ます。2つの表のずれの大きさが違うことが、このステップでの発見です。

伝票1件、仕訳行1行まで絞り込む

伝票ごとに借方合計と貸方合計を比べてずれた伝票を探し、/root/bank/broken_entry.txtにentry_id=、debit=、credit=、gap=を書いてください。その伝票の仕訳行は、/root/bank/broken_lines.csvにline_id,entry_id,account_code,dc,amountのヘッダー行とともにそのまま書き出してください。

伝票ごとにまとめて、借方合計と貸方合計を比べてください。HAVING句に差が0ではないという条件を付けると、1件だけ残ります。仕訳行は値を直さず、あるがままに書き出してください。

残高表と元帳の合算を突き合わせる

/root/bank/balance_diff.csvに、code,cached,ledger,gapのヘッダー行で、残高表と元帳の合算が異なる勘定だけを残してください。gapはcachedからledgerを引いた値です。

勘定ごとにaccounts.cached_balanceと元帳の合算値を並べて、違うものだけを残してください。gapはcachedからledgerを引いた値です。2行出ますが、ずれた理由は互いに正反対です。

反対仕訳と再起票で直す

元の伝票には手を触れず、訂正伝票を2枚入れてください。反対仕訳はJE-20260826-0134-R、再起票はJE-20260826-0134-Cとして登録し、journalとjournal_lineの両方に入れてください。

元の伝票は1行も触りません。反対仕訳は元の伝票の借方と貸方だけを入れ替え、金額は記録されたとおりに書いてください。そのあと、正しい金額で伝票をもう1枚入れます。

残高表を元帳から作り直す

accounts.cached_balanceを元帳の合算で再計算して上書きし、/root/bank/recheck.txtにdebit_total=、credit_total=、gap=、unbalanced_entries=、original_credit=、cached_sum=、balance_diff_rows=の7行を書いてください。

ずれたら、常に写しを直します。accounts.cached_balanceを元帳の合算で上書きしてください。そして、伝票単位の不一致が0になることを期待しないでください。反対仕訳が元の伝票と対になって残ります。

元帳の整合性レポートを書く

/root/bank/ledger_report.mdに## 무엇이 틀렸나、## 어떻게 찾았나、## 어떻게 고쳤나、## 잔액 표는 왜 못 잡았나、## 재발 방지の5つの節でレポートを書いてください(見出しは韓国語で、順に「何が間違っていたか」「どう見つけたか」「どう直したか」「残高表はなぜ検出できなかったか」「再発防止」という意味です)。

5つの節が必要です。「何が間違っていたか」の節には伝票番号と差額を、「どう直したか」の節には反対仕訳の伝票番号を、「残高表はなぜ検出できなかったか」の節には残高だけがずれた勘定の番号と金額を書いてください。