家計簿つけるの大変じゃないですか？

家計簿つけるのってとても大変ですよね。ですが、今はキャッシュレスの時代！
電子で決済した明細はネット上からダウンロードできるので、そのデータを用いて Grafana で可視化してみたいと思います。

Grafana とは？

サーバーやアプリケーションなどの監視用ダッシュボードを作成できるオープンソースのソフトウェアです。工場の運転状況の監視等にも使われる場合が多いのではないでしょうか。普段の仕事では、Microsoft の PowerBI というデータの可視化ツールにお世話になっているのですが、プライベートではロマンあふれるかっこいい Grafana を使ってみたいと思い、選定しました。

開発環境

wsl
docker

使用技術

Grafana
PostgreSQL(Grafana が参照する DB です。)
A5:SQL Mk-2(DB クライアントソフトです。)
Python(csv からデータを取り込んで DB に入れるように使います。)

システム構成

開発環境構築

環境変数がハードコーディングされていますが、最終的には変更します。

docker-compose.yml

version: "3.7"

services:
  grafana:
    image: grafana/grafana:latest
    container_name: "grafana"
    ports:
      - "3000:3000"
    volumes:
      - grafana-data:/var/lib/grafana

  postgres:
    image: postgres
    container_name: "postgres"
    restart: always
    environment:
      POSTGRES_DB: grafana
      POSTGRES_USER: grafana
      POSTGRES_PASSWORD: grafana
    volumes:
      - postgres-data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

volumes:
  grafana-data:
  postgres-data:

上記のような、docker-compose.yaml ファイルを用意して、docker compose up コマンドを実行し、Grafana と PostgreSQL を立ち上げます。

docker compose up -d

A5m2 で PostgreSQL への接続テストを行いました。

支出管理対象とテーブル定義

DB にまずは、各管理対象ごとに極力 csv の構造のまま突っ込みます。参照時にビューを作成して成型する楽な運用にします。

ゆ〇ちょ銀行

データファイル形式

ゆ〇ちょダイレクトは月をまたいでまとめて csv ダウンロードできるのでとてもいいですね。下記のようにヘッダーも月ごとに分かれていたりしないので、このまま DB に突っ込めます。

取引日,入出金明細ＩＤ,受入金額（円）,払出金額（円）,詳細１,詳細２,現在（貸付）高,

テーブル定義

CREATE TABLE my_finance.japan_post_bank (
    transaction_date DATE,
    transaction_id TEXT,
    deposit_amount INTEGER,
    withdrawal_amount INTEGER,
    detail_1 TEXT,
    detail_2 TEXT,
    current_balance INTEGER
);

COMMENT ON COLUMN my_finance.japan_post_bank.transaction_date IS '取引日';
COMMENT ON COLUMN my_finance.japan_post_bank.transaction_id IS '入出金明細ID';
COMMENT ON COLUMN my_finance.japan_post_bank.deposit_amount IS '受入金額（円）';
COMMENT ON COLUMN my_finance.japan_post_bank.withdrawal_amount IS '払出金額（円）';
COMMENT ON COLUMN my_finance.japan_post_bank.detail_1 IS '詳細１';
COMMENT ON COLUMN my_finance.japan_post_bank.detail_2 IS '詳細２';
COMMENT ON COLUMN my_finance.japan_post_bank.current_balance IS '現在（貸付）高';

COMMENT ON TABLE my_finance.japan_post_bank IS 'ゆうちょ銀行';

楽〇カード

csv が下記のようにヘッダーと値にちゃんと対応していて使いやすそうな csv ですね。

"利用日","利用店名・商品名","利用者","支払方法","利用金額","支払手数料","支払総額","5月支払金額","6月繰越残高","新規サイン"

しかし、毎月支払金額と繰越残高の月が替わるので、このままでは共通化できません。そのため、DB に入れるのは支払総額までの列にします。

DB インサート時に気づいたのですが、円以外のやり取りは以下のように特殊なレコードが付加されるようです。

"2023/09/17","GOOGLE*COLAB利用国SG","本人","1回払い","1179","0","1179","1179","0","*"
"","現地利用額　　　　　　１１７９．０００変換レート　　　１．０００円","","","","","","","",""

なにこれ？と思い調べたところ、googlecolab の契約でした。幸いひと月だけ試しに契約しただけだったので、手動で余分な行を消しておきました。

〇井〇友カード

Shif-Jis でエンコードされているおかげか、wsl で開くと文字化けします。エンコードし直さないといけませんね。さらにはヘッダーが個人情報に、フッターが合計値になっているせいで csv の加工の手間が増えました。
　
DB インサート時にまたもや発覚したのですが、キャッシュバックがあると以下のように特殊な行になってしまうので、例外処理を実装する必要がありました。

2023/07/31,キャッシュバック（ポイント交換）,,,,-8388,

ペイ系と SUICA

PayPay と RakutenPay は csv でダウンロードする機能がなく、断念いたしました。
SUICA は PDF でダウンロードできたのですが、PDF をパースするのが手間なので一旦保留します。

データを PostgreSQL へ挿入

Python を使ってデータを DB に入れます。のちのち、月に一回自動で web からダウンロードしてくれるようにしたいですね。Python のコードは本題ではないので、割愛します。

データの加工

今回の運用では、テーブルをデータソースとして Python 側で加工はしません。DB 側のビューで加工して DB 構造を柔軟に変えていきつつ運用していきます。大規模な DB には向きませんが、小規模で運用する分には問題ないと考えています。

クレジットカードの統合とカテゴリ分け

まずは支出のクレジットカード達を UNION ALL で縦に結合します。そして、支出総額の多い支出先からカテゴリ分けしていきます。

WITH credit_card_payment AS(
    SELECT
        rc.transaction_date,
        rc.store_or_item_name,
        rc.total_amount
    FROM
        my_finance.rakuten_card rc
    UNION
    ALL
    SELECT
        smc.transaction_date,
        smc.store_name,
        smc.payment_amount
    FROM
        my_finance.sumitomo_mitsui_card smc
)
SELECT
    ccp.store_or_item_name,
    SUM(ccp.total_amount)
FROM
    credit_card_payment ccp
GROUP BY
    ccp.store_or_item_name
ORDER BY
    SUM(ccp.total_amount) DESC;

上位 3 位までの支出先をお見せすると、第一位はモバイル Suica でした！楽天キャッシュと僅差です。残念なのが Suica も、楽天キャッシュも細かい明細を csv で取得することができないので、これからはちゃんとクレカで決済できるものはクレカで決済するようにしていきたいです。

上記の結果をもとにカテゴリ分けしてマトリックス化していきます。分けやすさ優先です。

モバイルスイカ=交通費:Transportation
電気:Electricity
ガス:Gas
水道:Water
インターネット/電話料金:Internet/Phone
それ以外:Others

そしてできた SQL が以下です！これを Grafana 側で参照してグラフ化していきたいと思います。

CREATE OR REPLACE VIEW my_finance.categorized_credit_payment_matrix AS(
SELECT ccp.transaction_date,
CASE WHEN ccp.category = 'Gas' THEN ccp.total_amount END AS Gas,
CASE WHEN ccp.category = 'Electricity' THEN ccp.total_amount END AS Electricity,
CASE WHEN ccp.category = 'Water' THEN ccp.total_amount END AS Water,
CASE WHEN ccp.category = 'Internet' THEN ccp.total_amount END AS Internet,
CASE WHEN ccp.category = 'Transportation' THEN ccp.total_amount END AS Transportation,
CASE WHEN ccp.category = 'Others' THEN ccp.total_amount END AS Others
FROM my_finance.categorized_credit_payment ccp
);

Grafana で家計の収支を可視化

Grafana へログイン

docker で立てた Grafana へアクセスします！localhost:3000 です。もうログイン画面がかっこいいですね！

データソース設定

Grafana の Data sources に PostgreSQL を登録します。(コンテナ間なので、DB の Host 名は postgres です。)

ダッシュボード作成

新しいダッシュボードを作成し、visualization を追加します。そして、code エディターに SQL を記述してグラフに必要なテーブルを引っ張ってきます。
棒グラフや折れ線グラフ、ゲージグラフ、テーブルを作成してダッシュボードを完成させました！(かなりぼやかしました)

Grafana の素晴らしい所

個人的に Grafana で便利だなと思ったのがデータを引っ張ってくると、ビジュアルのサジェストを出してくれる所ですね。なのでどのビジュアルがいいかをまとめて見られるのがありがたいです！

まだまだ、Grafana を触り始めたばっかりなので、気づけていない魅力がたくさんあるんだろうなととても期待しています。
今後も様々なデータを Grafana で可視化していきたいと思います。

今後の展望

Web から csv ダウンロードの自動化

Selenium 等を使って Web から月に一回自動的に csv を引っ張ってきて、それを DB に入れるまでを自動化してみたいです。

Category 分けの自動化

明細を見ていると、食費はどうにか店名から自動判別してくれるようにしたいなと思っています。なので、マスタ作成を自動で行ってくれる技術を探していきたいですね！

pay 系ではなく、クレカ決済の優先

csv で明細を出してくれるクレカ中心の生活にしていきたいです。