bookeeper

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

YEAR_START="2025-01-01"
MONTH_START="2025-02-01"

function bsheet() {
  cat <<EOS
    SELECT *
    FROM (
      SELECT
        entry.name          AS name,
        SUM(journal.amount) AS volume
      FROM journal RIGHT JOIN entry ON journal.entry=entry.id
      WHERE entry.type='$1'
      GROUP BY entry.name
    )
    WHERE volume != 0;
EOS
}
function bsheet_trans() {
  cat <<EOS
    SELECT
      date,
      SUM(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 -journal.amount END
        ) AS 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
}
function pl() {
  cat <<EOS
    SELECT
      entry.name          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, entry.name;
EOS
}
function pl_trans() {
  cat <<EOS
    SELECT
      STRFTIME('$2', tx.date) AS period,
      SUM(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 tx.date >= '$YEAR_START' AND entry.type='$1'
    GROUP BY period;
EOS
}

touch ./db.sqlite
python -m bookeeper ./db.sqlite  \
  --read tx.txt  \
\
  --output dst/bsheet-asset.txt \
  --sql "$(bsheet A)"  \
\
  --output dst/bsheet-debt.txt \
  --sql "$(bsheet D)"  \
\
  --output dst/bsheet-net.txt \
  --sql "$(bsheet N)"  \
\
  --output dst/bsheet-trans.txt \
  --sql "$(bsheet_trans)"  \
\
  --output dst/pl-expense-1m.txt \
  --sql "$(pl E $MONTH_START)"  \
\
  --output dst/pl-expense-1y.txt \
  --sql "$(pl E $YEAR_START)"  \
\
  --output dst/pl-revenue-1m.txt \
  --sql "$(pl R $MONTH_START)"  \
\
  --output dst/pl-revenue-1y.txt \
  --sql "$(pl R $YEAR_START)"  \
\
  --output dst/pl-trans-expense-1m.txt \
  --sql "$(pl_trans E '%Y-%m')"  \
\
  --output dst/pl-trans-expense-1d.txt \
  --sql "$(pl_trans E '%Y-%m-%d')"  \
\
  --output dst/pl-trans-revenue-1m.txt \
  --sql "$(pl_trans R '%Y-%m')"  \
\
  --output dst/pl-trans-revenue-1d.txt \
  --sql "$(pl_trans R '%Y-%m-%d')"

function categorize() {
  cat - | awk -F, '{split($1, terms, "/"); a[terms[1]]+=$2} END {for (y in a) printf "%s,%d\n", y, a[y]}'
}
categorize < dst/pl-expense-1m.txt > dst/pl-expense-1m-categorized.txt
categorize < dst/pl-expense-1y.txt > dst/pl-expense-1y-categorized.txt

sed -i '1s|^|name, volume\n|' dst/bsheet-asset.txt
sed -i '1s|^|name, volume\n|' dst/bsheet-debt.txt
sed -i '1s|^|name, volume\n|' dst/bsheet-net.txt
sed -i '1s|^|date, volume\n|' dst/bsheet-trans.txt
sed -i '1s|^|name, volume\n|' dst/pl-expense-1m.txt
sed -i '1s|^|name, volume\n|' dst/pl-expense-1m-categorized.txt
sed -i '1s|^|name, volume\n|' dst/pl-revenue-1m.txt
sed -i '1s|^|name, volume\n|' dst/pl-expense-1y.txt
sed -i '1s|^|name, volume\n|' dst/pl-expense-1y-categorized.txt
sed -i '1s|^|name, volume\n|' dst/pl-revenue-1y.txt
sed -i '1s|^|date, volume\n|' dst/pl-trans-expense-1m.txt
sed -i '1s|^|date, volume\n|' dst/pl-trans-revenue-1m.txt
sed -i '1s|^|date, volume\n|' dst/pl-trans-expense-1d.txt
sed -i '1s|^|date, volume\n|' dst/pl-trans-revenue-1d.txt