• #年次推移表
  • #Excel自動化
  • #Google Sheets API
  • #Python
  • #勘定科目マスター
  • #会計データ
開発eurekapu-nuxt4

会計ソフトの仕訳データから年次推移表を自動生成する

朝、会計ソフトAのスプレッドシートに並ぶ仕訳データを眺めていた。このデータからBS+PLの年次推移表を毎回手で組むのをやめたい。gen-annual-tableスクリプトを書き始めたところから、気づけばExcel版とGoogle Sheets版の二系統が動くようになるまで丸一日が過ぎた。

gen-annual-table: Excel版の開発

BS+PLの構造設計

年次推移表の構造を固めるのに時間をかけた。ヘッダー行の下に個別科目が並び、小計・合計・チェック行が続く。2022年度から2028年度まで7年分を横に展開する。

合計行の数式で最初につまずいた。科目のpruning（ゼロ行の非表示化）を入れると、行番号がずれて SUM(B10:B25) のような固定範囲指定が壊れる。sum_named 方式に切り替えて、名前付き範囲で集計対象を指定するようにした。pruning後もインデックスが正しく動く。

PLの経常利益・税引前利益は単純なSUMでは出せない。営業利益に営業外損益を加減する計算式を pl_calc として定義し、合計行から参照する形にした。

チェック行で不整合を検知する

BSの末尾にチェック行を置いた。「資産合計 - 負債純資産合計 = 0」を数式で計算し、ゼロ以外の値が出たらセルが赤く光る。このチェック行のおかげで、後工程で科目の配置を変えたときに即座にずれを検知できた。

入力データをExcel内に同梱する

生成したExcelに「入力_MF仕訳」シートを追加し、元データをそのまま貼り付けた。インプットとアウトプットが1ファイルで完結するので、後から「この数字はどこから来たか」を追うときにファイルを行き来しなくて済む。

Google Sheets版の新規開発

共通コアモジュールの切り出し

Excel版が動いた後、同じロジックをGoogle Sheetsにも出したくなった。最初はExcel版のスクリプトをコピーして書き換え始めたが、科目構造の定義やpruningロジックが丸ごと重複していることに手が止まった。

gen_annual_table_core.py として共通データモジュールを切り出すリファクタリングを実行した。BS・PLの科目構造、合計行の数式定義、pruningロジックをここに集約する。Excel版 (gen_annual_table.py) とSS版 (gen_annual_table_ss.py) は、このコアモジュールを import して、出力先だけを差し替える構造にした。

リファクタリング後、Excel版の出力結果が変わっていないことを確認してからSS版の開発に入った。

SS版: Google Sheets APIでの書き込み

SS版では gws CLI経由でGoogle Sheets APIを叩く。シート作成、セル書き込み、IB書式適用の3ステップを1つのシェルスクリプト gen_annual_table_ss.sh にまとめた。

命名規則は CF_年次推移表_{ソースSS名} とした。--title オプションで明示的にタイトルを指定できるが、未指定時はソーススプレッドシートのタイトルを自動取得して埋め込む。Google Sheetsにはバージョン履歴があるため、Excel版と違ってファイル名にタイムスタンプを入れる必要がない。

Excel版とSS版の値照合

両方の出力が揃った時点で、全セルの値を突き合わせた。Pythonスクリプトで Excel の各セル値と Google Sheets の各セル値を比較し、差分があれば報告する仕組みを組んだ。最初の照合で数箇所の不一致が見つかり、SS版の数式変換ロジックにバグがあったことが判明した。修正後、全セル一致を確認。

勘定科目マスターのJSON化

年次推移表の科目名をハードコードしていると、科目が増えたときに複数箇所を修正する必要が出る。会計ソフトAの勘定科目マスター（223件）をスプレッドシートから読み取り、scripts/cf-materials/mf-account-master.json に書き出した。

{
  "accounts": [
    {
      "code": "110",
      "name": "現金",
      "category": "asset",
      "parent": null
    },
    {
      "code": "110-001",
      "name": "小口現金",
      "category": "asset",
      "parent": "110"
    }
  ]
}

親子関係（勘定科目と補助科目）を parent フィールドで保持している。このJSONをコアモジュールから参照すれば、科目の追加・変更がJSON1箇所の編集で済む。

返済予定表の合計行バグ修正

年次推移表を眺めていて、支払利息の数字に違和感を覚えた。返済予定表の合計と突き合わせたところ、3,750円の差がある。

原因を追うと、TPL_返済予定表の合計行 =SUM(E20:E501) がRow 19を含んでいなかった。Row 19は初回返済時の据置期間利息3,750円が入っている行だ。=SUM(E19:E501) に修正し、D・E・F列すべてで開始行を揃えた。利息合計が156,188円から159,938円に変わり、返済予定表の個別行合計と一致した。

3,750円のずれは合計額に対して小さいが、チェック行がなければ気づかなかった。

書式の仕上げ

数値の見た目を整える作業にも手を入れた。

  • 数値カラムの列幅を18.125に統一し、スペーサー列は1.5に絞った
  • 全セルに上下中央揃え（vertical center）を適用
  • チェック行のA列を右寄せにして、ラベルと数値の視線が揃うようにした
  • 一部のセルで数値が stringValue として格納されていたのを numberValue / formulaValue に修正。文字列のままだとSUM関数が無視するため、これも照合時に発覚した

振り返り

一日で一番時間を使ったのは、共通コアモジュールの切り出しだった。Excel版が動いている状態でリファクタリングするのは勇気が要る。しかし、コピペで2つ目を作り始めた瞬間に「これは後で必ず片方だけ修正して不整合が出る」と手が止まった。結果的に、リファクタリングしてからSS版を作ったことで、照合時のバグ修正がコアモジュール1箇所の修正で両方に反映された。

返済予定表のバグは、年次推移表の数字を睨んでいて偶然見つけた。合計行のSUM範囲が1行ずれているだけで3,750円の差が出る。金額が小さいから放置してもよさそうだが、帳簿の信頼性は「全部合っている」前提で成り立つ。1円でもずれていたら他の数字も疑わなければならない。チェック行を入れておいてよかった。