月次の支払い集計ツールを作る SQL準備編

昨年のエントリ

pythonでクレジット支払い集計ツールを作りたい - paloma blog

でクレジット払いの集計をやったのですが、今年も半分終わってしまったので改めて前半の集計をしたいと思います。

私はほとんどクレジット・SUICA払いのためこのような集計で簡単家計簿をつけようと思ったのですが、
やりかけで止まってしまっていたのをふと思い出したので、本自宅PJを進めます。
(いまだに家計簿は苦手でつけてません)

やること

月次で支出を計算する
- 支払いの分類分けをして固定費と変動費を明確にする
- カード会社の月次csvファイルを利用する

とりあえず月にいくら支出があるのか、半年でいくら使ったのかを計算します。
支出を分類分けして減らせそうなところをあぶりだす目的でもあります。

カード会社のサイトからCSVで月次の支払い明細をダウンロードできるため、これを利用して集計します。

CSVなんでpandasとかでちょちょっとできそうな気もしますが、今回は久しぶりにSQLを触りたいと思います。
前回もpython経由でわざわざSQL叩いてたのでそれなら直接でいいですよね。

環境

サブ機 Windows10でやります。

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月の支払いファイルになります。

201902.csv

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
- 主にコンビニ(酒、おつまみ、煙草等)
  - 定期以外の電車賃も含みます
通信費(ネットサービスもここに入れちゃいます)
- 携帯キャリア
- ネットプロバイダ
- MVNO
- ヤフオク
- NETFLIX
その他(上記以外は娯楽等の雑費とする)
- 本
- 飲み屋
- 服など

その他が生活費以外に使っているお金というわけですね。

SQLで集計します

集計は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;

ターミナルで見やすくするために

.headers on
.mode column

もやっておきます。

結果

種類          合計
----------  ----------
その他         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>