📈

MoneyForwardのCSVをBigQueryのテーブルに突っ込んでLooker Studioでデータを視覚化するお話

2024/02/04に公開

Ruby

Money Forwardを長らく使っているんですが、最近もう少し細かく数字を追いたいなーっていう気持ちになったのです。
例えば「『趣味・娯楽』の『漫画』と『教養・教育』の『書籍』だけを抜き出してグラフ化したい！」...とか。
こういう細かい集計はMFでは無理なので、CSVからグラフ化することにしました。

ちなみにCSVは1ヶ月の家計簿画面の下部にあるボタンから簡単に取得できます。ダウンロード機能を作ってくれてありがとうMF。

できあがったもの

先にできあがったものを。こんな感じでLooker Studioで欲しいグラフをゲットできました。やったね。

作成手順

BigQueryにテーブル準備

こんな感じのテーブルを作ります。

MFのCSVを見るとそれぞれのデータにIDが振られている様子。このIDはrecord_idとしてテーブルに入れておきます。こうすれば無邪気に複数回INSERTしてもrecord_idでDISTINCTすれば重複回避ヨシ！

INSERT文の作成

Shift_JisをUTF-8に変換
BQのデータ形式に合わせて変換
SQL文作成

...を毎月手作業でやるのはつらすぎるのでスクリプトを作ります。

ダウンロードしてきたCSVをmf.csvにリネームし、上記コードのapp/filesディレクトリに設置。
スクリプト走らせてSQL作成。ヨシ！

cd app
ruby main.rb

BigQueryにINSERT

最初は手作業でBQコンソールにSQLを貼り付けて実行したのですが、これすら面倒くさい。
というわけでローカルからさくっとINSERTできるようにします。

gcloud CLIを入れてgcloud initで設定を済ませ、bqコマンドでドン。

bq query -use_legacy_sql=false < 生成したSQLファイルパス

BigQueryのコントロール、gcloud initで初期設定さえすませてしまえばローカルからbq使うのがめっちゃ楽ですね。

Makefile化

さてこれでコマンド2つ叩けばMFのCSVからBQのテーブルに入るようになったのですが。2回叩くのもめんどくさいのでMakefile作って1個にします。

これで、appディレクトリ内でmake applyと打つだけでCSV作成とBQへのINSERTが一発でできるようになりました。やった、HAPPY。

Looker Studio

データソースをBigQueryにして、あとは好きなクエリでグラフを作るだけ。
ソースがBQなのでかなり柔軟にデータ加工できる。グラフの種類豊富。で楽しいです。

運用

1コマンドでCSV -> BQへのインポートができるようになったので

MFからCSVファイルをダウンロードする
CSVをリネームしてスクリプトディレクトリに入れる
make applyする

この3ステップでデータ新規追加ができるようになりました。これならまぁ続けられそうかな？

FAQ

BigQueryのテーブルスキーマください

リポジトリにいれてるので参考にどうぞ

1ヶ月に何度もインポートすることある？

あり(え)ます。

例えば今日は2/3ですが、カード会社に1月末分のデータが反映されていなかったりします。
毎月、半ばくらいにインポートすれば確実でしょうけど、今日みたいに月初に前月データを見たいなーってときはとりあえず今あるデータを入れておき、半ばにもう一回いれるとよいでしょう。

当然BQ側ではデータが重複するわけですが、MF側でIDを振ってくれているのでそのカラムでDISTINCTかけるなりして重複排除しましょう。

そんなこんなで

グラフ化楽しいです。Money Forward使っている方、自前でグラフ化いかがですか？