昨年のエントリ
pythonでクレジット支払い集計ツールを作りたい - paloma blog
でクレジット払いの集計をやったのですが、
今年も半分終わってしまったので改めて前半の集計をしたいと思います。
私はほとんどクレジット・SUICA払いのためこのような集計で簡単家計簿をつけようと思ったのですが、
やりかけで止まってしまっていたのをふと思い出したので、本自宅PJを進めます。
(いまだに家計簿は苦手でつけてません)
やること
- 月次で支出を計算する
- 支払いの分類分けをして固定費と変動費を明確にする
- カード会社の月次csvファイルを利用する
とりあえず月にいくら支出があるのか、半年でいくら使ったのかを計算します。
支出を分類分けして減らせそうなところをあぶりだす目的でもあります。
カード会社のサイトからCSVで月次の支払い明細をダウンロードできるため、これを利用して集計します。
CSVなんでpandasとかでちょちょっとできそうな気もしますが、今回は久しぶりにSQLを触りたいと思います。
前回もpython経由でわざわざSQL叩いてたのでそれなら直接でいいですよね。
環境
サブ機 Windows10でやります。
OS
PS C:\Users\masashi> Get-WmiObject Win32_OperatingSystem
SystemDirectory : C:\WINDOWS\system32
Organization :
BuildNumber : 17763
RegisteredUser : user
SerialNumber : 00330-80000-00000-AA775
Version : 10.0.17763
Databaseはsqlite3
PS C:\Users\masashi> sqlite3.exe --version
3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
月1で手動実行で確認できればいいのでDBはsqliteを使います。
わざわざサーバは立てるほどでもないかなという。
sqliteのインストールは割愛します。
Windows機なので今回はpowershellでsqlite叩きます。
処理方式の整理
たいそうな処理はしませんが一応。
- CSVファイルを整形
- 支払いの分類分けをする
- 分類ごとに集計する
- 月次になるよう集計期間を設定する
- 期間内の小計、合計を出す
これだけ。
DB作成、インポート
CSVファイルをsqliteにインポートします。
そのままでは使いづらいのでインポート前に少し加工してます。
- SJIS -> UTF-8へ変更
- ヘッダの追加
- 現金払いの固定費(家賃、光熱費)の追加
- 家賃と光熱費はカード払いしてませんので月末に挿入します。
光熱費は8000円～9000円くらいの可変ですが10000円固定にしましょう。
余ったら好きに使っていいことにします。
- 必要行以外の削除
- 末行に合計金額の行が記載されているので、集計を行うのに不要なフィールドは削除しておきます。
- 職場の定期代も削除してあります。
整形後のCSVはこんな感じ。
全角英数字が使われているところが日本のシステムっぽいですw
生活圏がモロバレになってしまいますが、まあいいでしょう。
(宣伝ですが、"スウイ―ト カヴアカヴア"というバーは雰囲気も良くておすすめです。私もちょくちょく通ってます)
私のクレジットは25日でいったん締まりますが、今回はサンプル的に１ファイルでやります。
１月分の支払いなので2月の支払いファイルになります。
date,contents,payment
2018/12/31,インターネットイニシアティブ,1798,,,,
2018/12/31,ヤフージャパン,498,,,,
2019/01/01,Ｊ：ＣＯＭ ２サービスパック利用料,5431,,,,
2019/01/05,ＪＲ西日本 ５４８９サービス,14240,,,,
2019/01/05,ミーナ津田沼,6285,,,,
2019/01/06,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/07,イトーヨーカ堂,1894,,,,
2019/01/09,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/11,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/11,１２月分 ａｕ電話利用料,3049,,,,
2019/01/14,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/15,イトーヨーカ堂 （サインレス）,3667,,,,
2019/01/17,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/18,イオンリテール,2995,,,,
2019/01/20,イトーヨーカ堂 （サインレス）,3635,,,,
2019/01/21,ＮＥＴＦＬＩＸ．ＣＯＭ,1296,,,,
2019/01/22,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/23,ミーナ津田沼,3447,,,,
2019/01/24,イトーヨーカ堂 （サインレス）,2609,,,,
2019/01/25,スウイ―ト カヴアカヴア,6910,,,,
2019/01/26,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/27,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/28,ミーナ津田沼,7604,,,,
2019/01/29,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/31,ＪＲ東日本モバイルＳｕｉｃａ,2000,,,,
2019/01/01,DIGITALOCEAN.COM (DIGITALOCEAN.),562,,,,5.01 USD 112.279 01 02
2019/01/19,ENT-INN-CORP (INTERNET ),1000,,,,1000.00 JPY 1.0000 01 20
2019/01/20,ONEDREAM (0368316633 ),3176,,,,28.47 USD 111.578 01 22
2019/01/31,家賃,61000
2019/01/31,光熱費,10000
インポート
インポート後はこんな感じです。
CSVは外貨建ての決済もありカラムがいくつか増えましたが日本円の決済だけでよいので3カラムのみにします。
ヘッダが3つだけなのはそのためです。
インポートしたら上手いことエラー吐いて不要カラムを除外してくれました。
PS C:\Users\masashi\tools> sqlite3.exe expenses_2019.db
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite>
sqlite> .mode csv
sqlite> .import 201902.csv expences
201902.csv:2: expected 3 columns but found 7 - extras ignored
201902.csv:3: expected 3 columns but found 7 - extras ignored
201902.csv:4: expected 3 columns but found 7 - extras ignored
201902.csv:5: expected 3 columns but found 7 - extras ignored
（略）
sqlite>
テーブル構成はこんな感じ。
sqlite> .schema
CREATE TABLE expenses(
"date" TEXT,
"contents" TEXT,
"payment" TEXT
);
sqlite>
selectしてみます。
sqlite> select * from expenses;
date contents payment
---------- -------------- ----------
2018/12/31 インターネットイニシアティブ 1798
2018/12/31 ヤフージャパン 498
2019/01/01 Ｊ：ＣＯＭ ２サービスパック 5431
2019/01/05 ＪＲ西日本 ５４８９サービス 14240
2019/01/05 ミーナ津田沼 6285
2019/01/06 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/07 イトーヨーカ堂 1894
2019/01/09 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/11 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/11 １２月分 ａｕ電話利用料 3049
2019/01/14 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/15 イトーヨーカ堂 （サインレス 3667
2019/01/17 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/18 イオンリテール 2995
2019/01/20 イトーヨーカ堂 （サインレス 3635
2019/01/21 ＮＥＴＦＬＩＸ．ＣＯＭ 1296
2019/01/22 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/23 ミーナ津田沼 3447
2019/01/24 イトーヨーカ堂 （サインレス 2609
2019/01/25 スウイ―ト カヴアカヴア 6910
2019/01/26 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/27 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/28 ミーナ津田沼 7604
2019/01/29 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/31 ＪＲ東日本モバイルＳｕｉｃａ 2000
2019/01/01 DIGITALOCEAN.C 562
2019/01/19 ENT-INN-CORP ( 1000
2019/01/20 ONEDREAM (0368 3176
2019/01/31 家賃 61000
2019/01/31 光熱費 10000
sqlite>
これを項目ごとに集計すればOKですね。
ジャンル分け
私の決済パターンはざっくり分けてこんな感じです。
- 食費(お酒含む)
- SUICA
- 通信費(ネットサービスもここに入れちゃいます)
- その他(上記以外は娯楽等の雑費とする)
その他が生活費以外に使っているお金というわけですね。
集計はcaseを使います。
caseって正規表現使えないんでしょうか。
(when .. like .. のような構文が動きませんでした)
調べる余地ありですね。
携帯の支払いだけ月が可変になっていそうなのでひとまずサブクエリでLIKEを使ってしのぎます。
また、JCOMもなぜか認識されないのでこちらも同様にサブクエリ使います。
SQLはこんな感じです。
select case contents
when (select contents from expenses where contents like '%ａｕ電話利用料%') then '通信費'
when (select contents from expenses where contents like '%Ｊ：ＣＯＭ%') then '通信費'
when 'インターネットイニシアティブ' then '通信費'
when 'ミーナ津田沼' then '食費'
when 'イトーヨーカ堂 （サインレス' then '食費'
when 'ＪＲ東日本モバイルＳｕｉｃａ' then 'SUICA'
ELSE 'その他' END AS '種類',
sum(payment) AS '合計'
from expenses
group by contents;
ターミナルで見やすくするために
もやっておきます。
結果
種類 合計
---------- ----------
その他 562
その他 1000
その他 3176
その他 2995
その他 1894
その他 9911
通信費 1798
その他 6910
食費 17336
その他 498
その他 10000
その他 61000
通信費 3049
通信費 5431
SUICA 20000
その他 14240
その他 1296
sqlite>
あれ…うまく集計されませんね。
日本語と英語の内容が混雑しているからでしょうか...
列がずれちゃうのは日本語が入るためずれてしますようです。
まあこれは許容範囲ということで。
仕方がないので上記SQLをサブクエリにして出力結果からもう一回selectすることにします。
ついでに今年1月分の内容が欲しいのでwhereで絞ります。
各小計の合計金額も出しておきたいので。UNION ALLで金額だけ計算します。
SQLと結果はこんな感じになります。
sqlite> select 分類 as 決済内容,sum(合計) as 合計 from
...> (select case contents
...> when (select contents from expenses where contents like '%ａｕ電話利用料%') then '通信費'
...> when (select contents from expenses where contents like '%Ｊ：ＣＯＭ%') then '通信費'
...> when 'インターネットイニシアティブ' then '通信費'
...> when 'ＮＥＴＦＬＩＸ．ＣＯＭ' then '通信費'
...> when 'ヤフージャパン' then '通信費'
...> when 'ミーナ津田沼' then '食費'
...> when 'イオンリテール' then '食費'
...> when 'イトーヨーカ堂 （サインレス' then '食費'
...> when 'ＪＲ東日本モバイルＳｕｉｃａ' then 'SUICA'
...> when '家賃' then '住宅費'
...> when '光熱費' then '住宅費'
...> ELSE 'その他' END AS '分類',
...> sum(payment) AS '合計'
...> from expenses
...> where date BETWEEN '2019/01/01' and '2019/01/31'
...> group by contents)
...> group by 分類
...> UNION ALL
...> select '-',sum(payment) as 合計 from expenses
...> where date BETWEEN '2019/01/01' and '2019/01/31';
決済内容 合計
SUICA 20000
その他 37693
住宅費 71000
通信費 9776
食費 20331
- 158800
sqlite>
OKですね!
何とか欲しいものが出来ました!
1月は全部の決済を集計してませんが、今のところ16万ほどつかっているわけですね。
一応給料よりは少なく収まってますw
今後の改良点
とりあえず動きそうなものが作れました。
今回はサンプルとして1ヶ月分のファイルですが、運用していくに辺り以下のスクリプトを作成しようと思います。
- 支払い内容の正規表現
- CSVの整形
- insertコマンドへの整形
- DBへのインポート
- クエリ発行
- 各月の支払い確認
SQLなんで普段書かないのでこの程度の処理も結構時間がかかってしまいました。
全角英語が混じるのもSQLを複雑にした原因だと思います。
というか今回のSQLが奇麗に感じないのでSQLコーディング規約等も勉強しないとですね。
やりたいことたくさん出てきますねえ。
参考サイト
URLをど忘れしてしまったので後で追記します。
追記
いろんなサイト見たのではっきりと覚えてませんが、この辺でcaseの存在を思い出した気がします。
txqz.net
SQLはこのサイトで触りながら覚えました。
JOINとかの使い方もあってRDBMSの良さが理解できたと思います。
(postgresqlのサイトですが、SQLはほとんど同じってことで)
pgexercises.com
