dbt Advent Calendar 2023 の 14日目の記事になります。
長らくウォッチしてたカレンダーだったのでコミットできて嬉しい✌
qiita.com
タイトルからお察しかもですが、支払明細を整理・集計する "俺々家計簿" 的なものを dbt + duckdb + (Python) で作るお話です。
「ちゃんとお金を使えているか？」
日々の生活において非常に意識的かつ積極的にクレカや電子決済払いをしているタイプです。
最近は小さなスーパーや飲食店でもQRコード決済があり、友人同士の飲み会でもPayPay送金をするため本当に現金を使うシーンが限られてきました。
それらの電子決済情報はマネーフォーワード（マネフォ）に連携しておけば非常に細かい支払いも含めて全て電子的に記録・後からまとめて取得が可能になります。この記事ではそうして得られた2023年1月~11月の約1000件ほどの取引データをdbtで処理して中身を振り返るお話です。（dbtは仕事でもよく使うツールなので、もっと仲良くなるために趣味でも使ってみたかっただけです。）
元々は細かく家計管理をしたい性格ではなく、マネフォのトップにでる「資産総額」が大きく減ったりしなければokくらいの雑さでしか管理をしていません。
どちらかというと、漠然と未来に貯金をするわけではなく、「現在の生活の幸福度を上げるようなお金の使い方ができているか」「お金を良い経験に変えられているか」などを振り返るために今回の趣味開発をしてみようと思いました。
A面として実装の方法、B面ではその集計結果を元に今年は何にお金を使ったのかの振り返りポエムを書きました。
この記事ではdbtの基本部分はいろいろすっ飛ばしていますが、もし興味を持ちご自身でも動かしてみたいと思われたときはこちらの記事内容を事前に履修すれば簡単に理解・構築できると思います！
参考: ローカル環境のみ利用したdbtチュートリアル - Zenn
また、ここではMacOSで動かすことを前提としています。
もくじ
マネーフォワードから支払い明細データを取得する
可能な限りすべての支払い履歴を取得したいと考えたとき、やはりマネーフォワードが一番便利です。自分は有料プラン（月440円ほど）に入っているため、以下のことができます。
- ありとあらゆる口座・クレカ・その他金融関係サービスをマネフォに連携しまくる
- マネフォ形式に整形された履歴をcsvで一括ダウンロードする
マネフォのすごいところは連携先のサービスの多さで、メガバンクはもちろん、マイナーな地方銀行やCoinCheck（ビットコイン）、証券口座、iDeCoの年金口座など、ありとあらゆるサービス連携ができます。
また、今回重要な点は、それらの複数の連携先から取得できる情報を「共通のフォーマット」で一括ダウンロードできる点です。これがなかったら各サービスサイトをスクレイピングするなどが必要だったのでさすがにやりませんでした。マネフォ様々。
duckDBをインストールする
今回取り扱うのは個人的な決済データであるため、ここではBigQueryなどのクラウドにデータをあげず、ひとまずローカルで取り扱うことにしてみます。無料かつローカル、そしてdbtと組合せて使うのに評判がいいのは DuckDB みたいです。
参考: 🦆🦆🦆🦆🦆🦆DuckDB入門🦆🦆🦆🦆🦆🦆 - Zenn
DuckDBはpython版とCLI版のどちらでも利用できます。今回はCLI版を入れました。
$ brew install duckdb
$ ./duckdb #起動
csvをDuckDBにロードする
マネフォから取得したcsvファイルのunicodeはcp932になっています。DuckDBはutf-8しか受け取れないため、DuckDBの外でunicode変換を行う必要があります。
import pansdas as pd
df = pd.read_csv('収入・支出詳細_2023-10-01_2023-10-31.csv', encoding='cp932')
df.to_csv('kakeibo_20231001.csv', index=False, encoding='utf-8')
また、DuckDB CLI（Pythonでも）はデフォルトでは in-memory にテーブルを保存するため、CLIを終了するとデータは消えてしまいます。データを永続化するには、起動時にファイルを指定する必要があります。
ここでは例として kakeibo.duckdb というファイル名にします。
$ ./duckdb kakeibo.duckdb
DuckDBが起動したら、プロンプトの先頭が "D" となります。
sqlを発行してcsvファイルをテーブルとして保存します（最後のセミコロンを忘れない）
D create table kakeibo_20231001 as select * from read_csv_auto('kakeibo_20231001.csv');
保存できたか確認します。
D select * from kakeibo_20231001;
DuckDB からは cmd + d で抜けれます（DBサーバーのシャットダウン）。終了しても保存したテーブルは消えません。
その他、存在するテーブルの確認 show tables; や テーブルの削除 drop table <テーブル名>; もできます。
dbtのインストール
dbt-core と、 今回は DuckDBを操作するため dbt-duckdb をインストールします。
$ pip install dbt-core dbt-duckdb
dbtの初期設定
dbt init コマンドでdbtのプロジェクトを作成します。ここでは kakeibo_dbt というプロジェクト名にしてみます。
「使用するdatabaseは duckdbか？」と聞かれるので 1 と回答します。
$ dbt init kakeibo_dbt
Which database would you like to use?
[1] duckdb
すると一連のdbtファイルが自動で作成されます。
次に、dbtをインストールすると ~/.dbt/ という隠しフォルダが作られているので、その中にある profile.yml ファイルを編集し、databaseの場所を指定します。
kakeibo_dbt というプロジェクト名で dbt init したので、ファイルにはすでに以下が書かれているかもしれません。（なかったら作る）
kakeibo_dbt:
outputs:
dev:
type: duckdb
path: dev.duckdb
threads: 1
prod:
type: duckdb
path: prod.duckdb
threads: 4
target: dev
これを以下に書き換えます。path のところを kakeibo.duckdb がある場所に書き換えます。
kakeibo_dbt:
outputs:
dev:
type: duckdb
path: /Users/ysdyt/git_repositories/kakeibo_app/kakeibo.duckdb
threads: 1
prod:
type: duckdb
path: prod.duckdb
threads: 4
target: dev
これにより dbt がどのduckdb のdatabaseを見に行けばよいかわかるようになりました。
dbt の開発をする
dbtのお作用や基本的な使い方はこちらの記事を参考にしてください（とてもわかりやすいです！）。
ここでは主要なファイルを中心に説明します。
参考: ローカル環境のみ利用したdbtチュートリアル - Zenn
model の開発 - Staging層
今回は以下の様に、models フォルダ以下に staging, dwh, mart フォルダを作り、その下にモデルファイルを作成しています。
まず、データソースをインポートする Staging 層です。
ソース・ファイルを指定する schema_staging.yml の内容は以下。
version: 2
sources:
- name: main
description: "kakeibo-model"
tables:
- name: kakeibo_20231101
- name: kakeibo_20231001
- name: kakeibo_20230901
- name: kakeibo_20230801
- name: kakeibo_20230701
- name: kakeibo_20230601
- name: kakeibo_20230501
- name: kakeibo_20230401
- name: kakeibo_20230301
- name: kakeibo_20230201
- name: kakeibo_20230101
今回は1月から11月までの一月ごとに支払い明細のファイルを作成したため、面倒ですが一つつずテーブルを作っています。（1月から11月までまとめてマネフォでcsv出力をして1ファイルにしたほうが楽ではあるが、今回は1ヶ月ごとにデータ確認しながら行ったため。）
ここで指定している -name main とは DuckDB テーブルのスキーマ名を表しています。DuckDB CLI上で select * from information_schema.tables; を実行することでテーブルのスキーマを確認できます。
次に、stg_moneyforward_master.sql にて実際にデータの読み込みと、カラム名の変換だけを行います。
with
src_moneyforward_master as (
select * from {{ source('main', 'kakeibo_20231101') }}
union all
select * from {{ source('main', 'kakeibo_20231001') }}
union all
select * from {{ source('main', 'kakeibo_20230901') }}
union all
select * from {{ source('main', 'kakeibo_20230801') }}
union all
select * from {{ source('main', 'kakeibo_20230701') }}
union all
select * from {{ source('main', 'kakeibo_20230601') }}
union all
select * from {{ source('main', 'kakeibo_20230501') }}
union all
select * from {{ source('main', 'kakeibo_20230401') }}
union all
select * from {{ source('main', 'kakeibo_20230301') }}
union all
select * from {{ source('main', 'kakeibo_20230201') }}
union all
select * from {{ source('main', 'kakeibo_20230101') }}
),
final as (
select
計算対象::string as valid_frag
, 日付::string as payment_date
, 内容::string as detail
, 金額（円）::int64 as price
, 保有金融機関::string as financial_institution
, 大項目::string as category
, 中項目::string as sub_category
, メモ::string as memo
, 振替::string as transfer
, ID::string as id
from src_moneyforward_master
)
select * from final
マネフォからダウンロードしたcsvファイルの元々のカラム名は上記のような日本語カラム名になっているため扱いやすいように変換します。DuckDBでは 計算対象::string のような書き方で型変換します。
今回特に必要になるのは 日付, 内容, 金額, 保有金融機関 あたりのカラムになります。例えばこんな感じです。
- 日付: 支払日を指す。
2023-11-25 など。
- 内容: 何にお金を払ったか。
マクドナルドモバイルオーダー など。
- 金額: 支出の場合は
-720 などのマイナス表記になる。
- 保有金融機関: 決済方法。
三井住友カード など。クレカや銀行名、その他 Amazon.co.jp などのサービス名が並ぶことも。
Staging層ではゴミデータのクレンジングなどは行わず、一旦全てのデータをインポートするだけにします。
model の開発 - DWH層
Staging層の次はDWH層でデータの加工などを行います。目的に併せてデータを集約したりしますが、ここでは簡単に 有効な収入 を集めるモデルである dwh_income と、 有効な支出 である dwh_expense モデルを作ってみることにします。
dwh_income モデルは以下のような感じです。0円より大きい金額を収入とみなしざっくりと抽出したのち、細かくゴミデータを除外していきます。
with
import_moneyforward_master as (
select * from {{ ref('stg_moneyforward_master') }}
),
logical_income as (
select
*
from import_moneyforward_master
where
price > 0
),
clean_income as (
select
payment_date
, detail
, price
, financial_institution
, 'income' as label
from logical_income
where
not (financial_institution = 'Amazon.co.jp')
and not (detail = 'チャージ チャージ&ペイでチャージ,Visa LINE Payクレジットカード' and financial_institution = 'LINE Pay')
and not (detail = '送金受取' and financial_institution = 'LINE Pay')
（※ もろに人の名前付き口座名などがでてくるため適宜削除しました）
and not (detail = '定額自動入金' and financial_institution = 'hoge銀行')
and not (detail = '利息' and financial_institution = 'fuga銀行')
and not (financial_institution = '楽天市場(my Rakuten)')
order by payment_date
),
final as (
select * from clean_income
)
select * from final
さてここからが"家計簿集計"の真に面倒くさい部分です。
今回 "収入"としてカウントしたいのは、本業である会社からの給与や、その他本業外からの仕事収入だけにしたいとします。なのでそれ以外の "収入ぽくみえるもの" はゴミデータとして where文で除外したいです。
"収入っぽく見えるゴミデータ"とは、マネフォデータにおいては、例えば銀行間の振替送金や楽天市場, Amazon でのギフト券利用があります。これらは "収入" ではないものの、データ上は 「プラスのお金」としてカウントされます。これらを除外したいため、支払い内容や保有金融機関などを見て、細かく条件を特定して where文で消していきます。面倒くさくはあるのですが、マネフォ側である程度文字列を正規化してくれているおかげもあり、地道に書き出せばすんなり終わったりもします。
これが完了したあとのきれいになった出力は、意図したどおりの "収入一覧" が並んでおり壮観です。月々の給与額はもちろん把握していますが、1年分が1レコードずつ整然と並び、一目瞭然となると軽く感動します（1年よく頑張ったなぁ、みたいな）
全く同じ要領で、dwh_expense モデルも作ります。0円以下の金額を支出とみなしざっくりと抽出したのち、同じく細かくゴミデータを除外していきます。
with
import_moneyforward_master as (
select * from {{ ref('stg_moneyforward_master') }}
),
logical_expense as (
select
*
from import_moneyforward_master
where
price <= 0
),
clean_expense as (
select
payment_date
, detail
, price
, financial_institution
, 'expense' as label
from logical_expense
where
not (detail = 'AMAZON.CO.JP' and financial_institution = '三井住友カード')
and not (detail = '口座振替' and financial_institution = 'hoge銀行')
and not (detail = 'ラクテンカ-ドサ-ビス' and financial_institution = '楽天銀行')
order by payment_date
),
final as (
select * from clean_expense
)
select * from final
支出データ側で除外したいレコードの多くは「二重引き落とし」に相当するデータです。
マネフォでは、例えば Amazonのアカウントを連携することで買い物データがそのままマネフォに連携されます。具体的な「購入商品名」もマネフォ側が知ることができ情報もリッチになり便利です。一方で、そのAmazonの支払いを行ったクレカ会社からの決済額レコードも別途マネフォに記録されるため、一つの買い物に対して見た目上は二重の引き落としが発生しているような状態になります。今回はAmazon連携側のリッチなレコードを残し、クレカ会社側のレコードを除外するような処理を行っています。これと似たような処理を他の口座やサービスに対して行っています（除外条件を書く→結果を確認する→除外したいレコードを見つける→それを除外する条件を書く→ ... を満足するまで繰り返す）
余談ですが、マネフォなどの家計管理アプリがいまいちどっぷり使えないのはこれらの例外処理的なものを"良い塩梅"にやりきれないところかなと思っています。自分自身の口座間送金は相殺しててほしいし、上記のような二重引落しっぽいレコードも裏でいい感じにしておいてほしい、でも当然利用者によって残したい/残したくないの意図が違うので一律のルールや機能を強いるわけにもいかないというジレンマがあるのかなと思っています（いや、それはお前がアプリ機能を使いこなせてないだけやで、でしたらすみません。。）
model の開発 - Mart層
最後に、きれいになった収入・支出データを読み込んでjoinして終了です。
{{ config(
materialized='external',
location='/Users/ysdyt/git_repositories/kakeibo_app/export_data/mart_kakeibo_result.csv',
format='csv'
) }}
with
import_income as (
select * from {{ ref('dwh_income') }}
),
import_expense as (
select * from {{ ref('dwh_expense') }}
),
logical_union as (
select * from import_income
union all
select * from import_expense
),
final as (
select * from logical_union
)
select * from final
ここでは最終的なデータを csvファイルとして location の場所に別途出力するようにしています。
データを可視化する
せっかくなのでdbt特化型のBIである LightDash を使おうと思いました、が、どうやらLightDashが対応しているDatabaseの中にまだ DuckDB は含まれていないようでしたので泣く泣く撤退。
LightDashは、dbtで定義したmodelやmetricsをそのまま可視化することができるという特徴があり、まだまだ可視化方法は限られていて貧弱なようですが、見た目はLookerライクなので使いやすそうです。ubieさんも積極的に採用・利用されているそうです。
いくつか記事を参考にしましたが、以下あたりがわかりやすかったです。
ここでは大人しくPythonでいくつか集計・可視化します。そしてここからが個人的な1年間のお金の使い方振り返りポエムパートです。
（いろいろ生々しいのでぼんやり色々ボカシます。）
収支の累積の可視化
データがキレイなので収支の推移も簡単に出せます。その月時点での累積金額もプロットすると、トータルで赤字なのかどうか一目瞭然なので節約意識が高まる。。
import pandas as pd
df = pd.read_csv('mart_kakeibo_result.csv')
df_income = df[df['label'] == 'income']
df_income['payment_date'] = pd.to_datetime(df_income['payment_date'])
df_income = df_income.groupby(pd.Grouper(key='payment_date', freq='M')).sum().reset_index()
df_income['payment_date'] = df_income['payment_date'].dt.strftime('%Y-%m')
df_income.rename(columns={'price': 'price_sum'}, inplace=True)
df_income['label'] = 'income'
df_expense = df[df['label'] == 'expense']
df_expense['payment_date'] = pd.to_datetime(df_expense['payment_date'])
df_expense = df_expense.groupby(pd.Grouper(key='payment_date', freq='M')).sum().reset_index()
df_expense['payment_date'] = df_expense['payment_date'].dt.strftime('%Y-%m')
df_expense['price'] = df_expense['price'].abs()
df_expense.rename(columns={'price': 'price_sum'}, inplace=True)
df_expense['label'] = 'expense'
df_income_expense = pd.concat([df_income, df_expense])
import plotly.graph_objects as go
from plotly.subplots import make_subplots
fig = make_subplots(specs=[[{"secondary_y": True}]])
fig.add_trace(go.Bar(x=df_income_expense[df_income_expense['label'] == 'income']['payment_date'],
y=df_income_expense[df_income_expense['label'] == 'income']['price_sum'],
name='income'))
fig.add_trace(go.Bar(x=df_income_expense[df_income_expense['label'] == 'expense']['payment_date'],
y=df_income_expense[df_income_expense['label'] == 'expense']['price_sum'],
name='expense'))
fig.add_trace(go.Scatter(x=df_income_expense[df_income_expense['label'] == 'income']['payment_date'],
y=df_income_expense[df_income_expense['label'] == 'income']['price_sum'].cumsum(),
name='income_cumsum',
line=dict(color='blue', width=2)),
secondary_y=True)
fig.add_trace(go.Scatter(x=df_income_expense[df_income_expense['label'] == 'expense']['payment_date'],
y=df_income_expense[df_income_expense['label'] == 'expense']['price_sum'].cumsum(),
name='expense_cumsum',
line=dict(color='red', width=2)),
secondary_y=True)
fig.update_layout(title = '収入と支出の推移',
xaxis = dict(
dtick = 'M1',
tickformat='%_y年%_m月',
),
yaxis=dict(
title='金額',
tickformat=',d'
),
yaxis2=dict(
title='累積金額',
tickformat=',d',
showgrid=False,
),
legend=dict(
bgcolor='white',
bordercolor='gray',
borderwidth=1,
))
fig.show()
こんな感じのグラフが出せます。（数字とグラフの形はさすがにダミーにしてます）
大きな買い物ランキング
金額が大きい順に並べて眺めてみます。（※住宅ローンや積立投資などのルーティンな支出は除く）
import pandas as pd
df = pd.read_csv('mart_kakeibo_result.csv')
df_expense = df[df['label'] == 'expense']
df_expense.sort_values('price').head(10)
1位. 航空機チケットtoハワイ
6月末に大分遅めな新婚旅行でハワイに行ったときの飛行機代でした。このときが1ドル=144円と円高まっしぐらだったので飛行機も高かったんだ。。
2位. サーフボード（＆ウェットスーツなども一式）
去年末に藤沢に引っ越し、海までチャリで15分の距離になったのでサーフィンを始めたときに買ったやつでした。今年の夏は長かったため海も人だらけでデビューするには大混雑な年でした。元を取るために来年も続けないと（冬もやらないと上手くならないとずっと言われている...えぇ...）
3位. 電動チャリ
10年前くらい乗ったロードバイクをついに手放し、人生初の電動チャリ。シェアサイクルで乗ったときの楽さが忘れられずPayPayのポイント還元のときに奮発して購入したやつだった。湘南スタイルよろしく、サーフボードが積載できるように改造しました✌
4位. 京都のホテル1週間分
3月に転職をしたのでその前に1ヶ月弱ほど有給がありました。何をしようかなと思ったときに京都で意味もなくブラブラしたいなということで宿泊してました。いやー、意味のもなく京都ぶらぶらめっちゃ楽しかった。湘南・京都・福岡がいつか住みたい街top3。
5位. 人間ドック
お年頃だし癌家系なのでいよいよちゃんと受けるかーということでやったやつ。高かったなー。ひとまず何も見つからず安心。悪名高いバリウム検査もやり"人生"を体験した。来年からは胃カメラにする。
6＆7位. エアコン2台
年末に引っ越した後もケチってエアコンを買ってなかったが流石に夏は無理だろうと人生で初めてエアコンを買った。賃貸でデフォで付いてたあれが、買う+設置工賃がとても高いのだと初めて知って悲鳴を上げた
8位. 長野 Earthboat宿泊
Earthboat | 地球を肌で感じるサウナ付きタイニーホテル というのがある。以前宿泊した素敵なサウナハウスのオーナーが新規に立ち上げたビジネスで、サウナ好きとしては気になっていたのでソッコーで予約して行ってきた。オススメです。
9位. 航空機チケットto台湾
8月に台湾に行ったときの飛行機代。台湾なんて何回行っても良いですからね〜
あとはバラバラしたガジェットなどでした。
2023年は引っ越ししたり転職したりした関係で、そもそも収入も支出もかなりお金関係がガラガラしていた年なのでした。
奥さんとの旅行やアクティビティー、健康や家のことにお金を使ってたようなのでなかなか良いお金使いだったんではないかなと。
良いか悪いかさておき、趣味100%のデカい買い物とかもしてみたい（ファッション趣味の人がこだわりの服を買ったり、車好きの人が念願の車を納車したりしてるの人生楽しそうだなと思う）
今後
こうして結果を眺めてみるとアプリを超えて自作するのもやはり有益だなと感じました。これがデータ設置と dbt run コマンドだけで実行できるのがとても便利です。あとはいろいろあった手動部分も自動化したい。これらをクラウドに持っていき、1週間に一度位で自動実行してLINEに自動プッシュしてくれる君などを作ってみようかなと思います。プライベートデータで好き勝手に自分のためだけの道具を作るのは楽しいですね✌
