bookeeper

集計スクリプト例↓
#!/bin/bash

BEGIN="2025-01-01"

function bsheet() {
  cat <<EOS
    SELECT *
    FROM (
      SELECT
        SPLIT(entry.name, 0) AS name_,
        SUM(journal.amount)  AS volume
      FROM journal RIGHT JOIN entry ON journal.entry=entry.id
      WHERE entry.type='$1'
      GROUP BY name_
    )
    WHERE volume != 0;
EOS
}
function bsheet_sub() {
  cat <<EOS
    SELECT *
    FROM (
      SELECT
        SUBSTR(entry.name, LENGTH('$2/')+1) AS name_,
        SUM(journal.amount)  AS volume
      FROM journal RIGHT JOIN entry ON journal.entry=entry.id
      WHERE entry.type='$1' AND SPLIT(entry.name, 0)='$2'
      GROUP BY name_
    )
    WHERE volume != 0;
EOS
}
function pl() {
  cat <<EOS
    SELECT
      SPLIT(entry.name, 0) AS name_,
      SUM(journal.amount)  AS sum
    FROM journal
      RIGHT JOIN entry ON journal.entry=entry.id
      RIGHT JOIN tx    ON journal.tx   =tx.id
    WHERE tx.date >= '$2' AND entry.type='$1'
    GROUP BY entry.type, name_;
EOS
}
function pl_sub() {
  cat <<EOS
    SELECT
      SUBSTR(entry.name, LENGTH('$3/')+1) AS name_,
      SUM(journal.amount)              AS sum
    FROM journal
      RIGHT JOIN entry ON journal.entry=entry.id
      RIGHT JOIN tx    ON journal.tx   =tx.id
    WHERE tx.date >= '$2' AND entry.type='$1' AND SPLIT(entry.name, 0)='$3'
    GROUP BY entry.type, name_;
EOS
}
function asset_trans() {
  cat <<EOS
    SELECT
      date,
      SUM(asset_sum)
        OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
      SUM(realnet_sum)
        OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
      SUM(risked_sum)
        OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM (
      SELECT
        tx.date AS date,
        SUM(
          CASE WHEN entry.type="A" THEN journal.amount ELSE 0 END
        ) AS asset_sum,
        SUM(
          CASE WHEN entry.type="A" THEN journal.amount ELSE -journal.amount END
        ) AS realnet_sum,
        SUM(
          CASE WHEN entry.type="A" AND SPLIT(entry.name, 0)="investment" THEN journal.amount ELSE 0 END
        ) AS risked_sum
      FROM journal
        RIGHT JOIN entry ON journal.entry=entry.id
        RIGHT JOIN tx    ON journal.tx   =tx.id
      WHERE    entry.type in ("A","D")
      GROUP BY tx.date
    );
EOS
}

python -m bookeeper ./db.sqlite  \
  --read tx.txt  \
  --output dst/financials.txt \
\
  --text "# ASSET"  \
  --sql "$(bsheet A)"  \
  --text "
"  \
  --text "# ASSET (INVESTMENT)"  \
  --sql "$(bsheet_sub A investment)"  \
  --text "
"  \
  --text "# DEBT"  \
  --sql "$(bsheet D)"  \
  --text "
"  \
  --text "# NET"  \
  --sql "$(bsheet N)"  \
  --text "
"  \
  --text "# EXPENSE"  \
  --sql "$(pl E $BEGIN)"  \
  --text "
"  \
  --text "# REVENUE"  \
  --sql "$(pl R $BEGIN)"  \
  --text "
"  \
  --text "# EXPENSE (FOODS)"  \
  --sql "$(pl_sub E $BEGIN food)"  \
  --text "
"  \
  --text "# EXPENSE (INVESTMENTS)"  \
  --sql "$(pl_sub E $BEGIN investment)"  \
  --text "
"  \
  --text "# REVENUES (INVESTMENTS)"  \
  --sql "$(pl_sub R $BEGIN investment)"  \
  --text "
"  \
  --text "# ASSET TRANS (ALL TIME)"  \
  --sql "$(asset_trans)"