paloma blog

NWエンジニアやってます。技術の備忘など。Pythonもちょっと。タイトルは好きなカクテルから。

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

昨年のエントリ

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機なので今回はpowershellsqlite叩きます。

処理方式の整理

たいそうな処理はしませんが一応。

  1. CSVファイルを整形
  2. 支払いの分類分けをする
  3. 分類ごとに集計する
  4. 月次になるよう集計期間を設定する
  5. 期間内の小計、合計を出す

これだけ。

DB作成、インポート

CSVファイルをsqliteにインポートします。
そのままでは使いづらいのでインポート前に少し加工してます。

  • SJIS -> UTF-8へ変更
  • ヘッダの追加
  • 現金払いの固定費(家賃、光熱費)の追加
    • 家賃と光熱費はカード払いしてませんので月末に挿入します。
      光熱費は8000円~9000円くらいの可変ですが10000円固定にしましょう。
      余ったら好きに使っていいことにします。
  • 必要行以外の削除
    • 末行に合計金額の行が記載されているので、集計を行うのに不要なフィールドは削除しておきます。
    • 職場の定期代も削除してあります。

整形後のCSVはこんな感じ。
全角英数字が使われているところが日本のシステムっぽいですw

生活圏がモロバレになってしまいますが、まあいいでしょう。
(宣伝ですが、"スウイ―ト カヴアカヴア"というバーは雰囲気も良くておすすめです。私もちょくちょく通ってます)

私のクレジットは25日でいったん締まりますが、今回はサンプル的に1ファイルでやります。
1月分の支払いなので2月の支払いファイルになります。

date,contents,payment
2018/12/31,インターネットイニシアティブ,1798,,,,
2018/12/31,ヤフージャパン,498,,,,
2019/01/01,J:COM 2サービスパック利用料,5431,,,,
2019/01/05,JR西日本 5489サービス,14240,,,,
2019/01/05,ミーナ津田沼,6285,,,,
2019/01/06,JR東日本モバイルSuica,2000,,,,
2019/01/07,イトーヨーカ堂,1894,,,,
2019/01/09,JR東日本モバイルSuica,2000,,,,
2019/01/11,JR東日本モバイルSuica,2000,,,,
2019/01/11,12月分 au電話利用料,3049,,,,
2019/01/14,JR東日本モバイルSuica,2000,,,,
2019/01/15,イトーヨーカ堂 (サインレス),3667,,,,
2019/01/17,JR東日本モバイルSuica,2000,,,,
2019/01/18,イオンリテール,2995,,,,
2019/01/20,イトーヨーカ堂 (サインレス),3635,,,,
2019/01/21,NETFLIX.COM,1296,,,,
2019/01/22,JR東日本モバイルSuica,2000,,,,
2019/01/23,ミーナ津田沼,3447,,,,
2019/01/24,イトーヨーカ堂 (サインレス),2609,,,,
2019/01/25,スウイ―ト カヴアカヴア,6910,,,,
2019/01/26,JR東日本モバイルSuica,2000,,,,
2019/01/27,JR東日本モバイルSuica,2000,,,,
2019/01/28,ミーナ津田沼,7604,,,,
2019/01/29,JR東日本モバイルSuica,2000,,,,
2019/01/31,JR東日本モバイルSuica,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  J:COM 2サービスパック  5431
2019/01/05  JR西日本 5489サービス  14240
2019/01/05  ミーナ津田沼          6285
2019/01/06  JR東日本モバイルSuica  2000
2019/01/07  イトーヨーカ堂         1894
2019/01/09  JR東日本モバイルSuica  2000
2019/01/11  JR東日本モバイルSuica  2000
2019/01/11  12月分 au電話利用料    3049
2019/01/14  JR東日本モバイルSuica  2000
2019/01/15  イトーヨーカ堂 (サインレス  3667
2019/01/17  JR東日本モバイルSuica  2000
2019/01/18  イオンリテール         2995
2019/01/20  イトーヨーカ堂 (サインレス  3635
2019/01/21  NETFLIX.COM     1296
2019/01/22  JR東日本モバイルSuica  2000
2019/01/23  ミーナ津田沼          3447
2019/01/24  イトーヨーカ堂 (サインレス  2609
2019/01/25  スウイ―ト カヴアカヴア    6910
2019/01/26  JR東日本モバイルSuica  2000
2019/01/27  JR東日本モバイルSuica  2000
2019/01/28  ミーナ津田沼          7604
2019/01/29  JR東日本モバイルSuica  2000
2019/01/31  JR東日本モバイルSuica  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
    • 主にコンビニ(酒、おつまみ、煙草等)
      • 定期以外の電車賃も含みます
  • 通信費(ネットサービスもここに入れちゃいます)
  • その他(上記以外は娯楽等の雑費とする)
    • 飲み屋
    • 服など

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

SQLで集計します

集計はcaseを使います。
caseって正規表現使えないんでしょうか。
(when .. like .. のような構文が動きませんでした)
調べる余地ありですね。

携帯の支払いだけ月が可変になっていそうなのでひとまずサブクエリでLIKEを使ってしのぎます。
また、JCOMもなぜか認識されないのでこちらも同様にサブクエリ使います。

SQLはこんな感じです。

select case contents
   when (select contents from expenses where contents like '%au電話利用料%') then '通信費' 
   when (select contents from expenses where contents like '%J:COM%') then '通信費' 
   when 'インターネットイニシアティブ' then '通信費' 
   when 'ミーナ津田沼' then '食費' 
   when 'イトーヨーカ堂 (サインレス' then '食費' 
   when 'JR東日本モバイルSuica' 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 '%au電話利用料%') then '通信費'
   ...>    when (select contents from expenses where contents like '%J:COM%') then '通信費'
   ...>    when 'インターネットイニシアティブ' then '通信費'
   ...>    when 'NETFLIX.COM' then '通信費'
   ...>    when 'ヤフージャパン' then '通信費'
   ...>    when 'ミーナ津田沼' then '食費'
   ...>    when 'イオンリテール' then '食費'
   ...>    when 'イトーヨーカ堂 (サインレス' then '食費'
   ...>    when 'JR東日本モバイルSuica' 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