paloma blog

NWエンジニアやってます。主に自宅環境のお遊びを書きます。Pythonもちょっと。タイトルは好きなカクテルから。

月次の支払い集計ツールを作る 2019前半を集計する編

集計のフォーマットが出来たので半年分のデータを取り込みます。

いきなり環境移植します

SQLiteも入力リダイレクトからSQL発行が出来ますが、powershellだとこれが出来ません。

なので同マシンのWSLにDB環境を移動します。

PS C:\Users\masashi> wsl cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"

インポート用ファイルのpermissionも変更しておきます。
WindowsとWSL間はこの編連携できないのかな?
ファイルコピーしたらUbuntukから見えなくなったりいろいろチューニングしました。

masashi@DESKTOP-N095TND:~/expenses$ ll
total 4
drwxrwxrwx 1 masashi masashi 4096 Jul 31 21:48 ./
drwxr-xr-x 1 masashi masashi 4096 Jul 31 21:49 ../
---------- 1 masashi masashi 1769 Jul 29 22:19 201902.csv
-rw-r--r-- 1 masashi masashi    0 Jul 31 21:48 Credit_expenses_2019.db
masashi@DESKTOP-N095TND:~/expenses$
masashi@DESKTOP-N095TND:~/expenses$ sudo chmod 644 201902.csv
masashi@DESKTOP-N095TND:~/expenses$

前回と同コマンドでファイルインポート。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import 201902.csv expenses
201902.csv:2: expected 3 columns but found 7 - extras ignored
201902.csv:3: expected 3 columns but found 7 - extras ignored
(略)
sqlite>
sqlite>
sqlite> select * from expenses;
2018/12/31,"インターネットイニシアティブ",1798
2018/12/31,"ヤフージャパン",498
2019/01/01,"Ｊ：ＣＯＭ　２サービスパック利用料",5431
2019/01/05,"ＪＲ西日本　５４８９サービス",14240
(略)
2019/01/31,"家賃",61000
2019/01/31,"光熱費",10000
sqlite>

ちゃんと動きますね。

CSV整形スクリプト…は間に合わなかった

整形の前に文字コードを変換(SJIS → UFT-8)しないといけないのですが、これは手動でやっちゃいます。

生CSVは必要なデータが足りないのでちょっと整形します。

  • ヘッダ、フッタを削除

    • ヘッダ1行とフッタ3行が邪魔なので消しちゃいます。
      6ファイルなのでこれも手動でやります。
      BOMもついていたので削除しました。

  • 固定費の挿入

    • 最終行に各月の末尾に家賃、光熱費を挿入します。
      これはshellですぐできたのでコマンドで入れます。

指定月の末尾を取るコマンドです。
下記の例だと4月の末尾が返ってきます。

masashi@DESKTOP-N095TND:~/expenses$ date +"%Y%m%d" -d"`date +"%Y0401"` 1day ago + 1 month"
20190430

なので2月から6月までの固定費を各月に挿入していきます。

masashi@DESKTOP-N095TND:~/expenses$ echo $(date +"%Y/%m/%d" -d"`date +"%Y0201"` 1day ago + 1 month"),家賃,61000 >> 201903.csv
masashi@DESKTOP-N095TND:~/expenses$ echo $(date +"%Y/%m/%d" -d"`date +"%Y0201"` 1day ago + 1 month"),光熱費,10000 >> 201903.csv

これを201907.csvまで繰り返します。

insert用SQL作成スクリプト

DBのテーブルは作成済みなのでCSVのデータを挿入できるようにします。

CSVの中身を読んでinsert分に変更するだけ。
shellでも作れそうですがここは書き慣れてるpythonで作ります。
degug用にprint仕込んだままにしときます。

masashi@DESKTOP-N095TND:~/expenses$ cat insertsql.py
import csv

def SQLConversion(csvfile):
    with open(csvfile, 'r') as f:
      reader = csv.reader(f)
      for i in reader:
        print('INSERT INTO expenses VALUES(\'{}\',\'{}\',\'{}\');'.format(i[0],i[1],i[2]))
        with open('.'.join([csvfile,'sql']), 'a') as wsql:
            wsql.write('INSERT INTO expenses VALUES(\'{}\',\'{}\',\'{}\');\n'.format(i[0],i[1],i[2]))

SQLConversion('201903.csv')
SQLConversion('201904.csv')
SQLConversion('201905.csv')
SQLConversion('201906.csv')
SQLConversion('201907.csv')

ファイルもちゃんとできました。

masashi@DESKTOP-N095TND:~/expenses$ cat 201903.csv.sql
INSERT INTO expenses VALUES('<feff>2019/01/31','ヤフージャパン','498');
INSERT INTO expenses VALUES('2019/01/31','インターネットイニシアティブ','1794');
INSERT INTO expenses VALUES('2019/02/01','イオンリテール','2332');
INSERT INTO expenses VALUES('2019/02/01','Ｊ：ＣＯＭ　２サービスパック利用料','5431');
INSERT INTO expenses VALUES('2019/02/02','つり具ランド　館山総合館','1930');
INSERT INTO expenses VALUES('2019/02/03','ＪＲ東日本モバイルＳｕｉｃａ','2000');
(略)

これで半年分作れましたね。

masashi@DESKTOP-N095TND:~/expenses$ ls -l *.sql
-rw-rw-rw- 1 masashi masashi 2866 Jul 31 22:25 201903.csv.sql
-rw-rw-rw- 1 masashi masashi 3287 Jul 31 22:25 201904.csv.sql
-rw-rw-rw- 1 masashi masashi 3226 Jul 31 22:25 201905.csv.sql
-rw-rw-rw- 1 masashi masashi 3000 Jul 31 22:25 201906.csv.sql
-rw-rw-rw- 1 masashi masashi 2985 Jul 31 22:25 201907.csv.sql

それでは集計したいのでデータを投入します。
6末日まで取りたいのですが、8月分がまだ締まってないので今回は諦めます。
(25日でいったん決済が締まるので)

SQL実行

それでは残りの月をSQLファイルからインポートします。
試しに2月分だけ支払いデータを入れてみます。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < 201903.csv.sql

OK!

sqlite> select * from expenses where date like '2019/02%';
2019/02/01|イオンリテール|2332
2019/02/01|Ｊ：ＣＯＭ　２サービスパック利用料|5431
2019/02/02|つり具ランド　館山総合館|1930
2019/02/03|ＪＲ東日本モバイルＳｕｉｃａ|2000
2019/02/05|ＪＲ東日本モバイルＳｕｉｃａ|2000
2019/02/06|ＪＲ東日本モバイルＳｕｉｃａ|2000
2019/02/07|ＪＲ東日本モバイルＳｕｉｃａ|21340
2019/02/07|イトーヨーカ堂　（サインレス）|2323
(略)

残りも入れちゃいます。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < 201904.csv.sql
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < 201905.csv.sql
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < 201906.csv.sql
masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < 201907.csv.sql

集計

いよいよメインイベントの集計タイムです。

前回VPS(Degital Ocean)の固定費を忘れていたのでSQLファイルに追加。

syukei01.sql

.headers on
.mode column

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 'DIGITALOCEAN.COM (DIGITALOCEAN.)' 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';

1月分はこんな感じで出力されます。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < syukei01.sql
決済内容        合計
----------  ----------
SUICA       20000
その他         37131
住宅費         71000
通信費         12630
食費          20331
-           161092

いいですね～

集計用のSQLはwhereで期間を指定するのでここも手動で直して各月分作ります。

各月の確認は自分で見るとして今回は半年分の結果のみ紹介します。
上記のSQLファイルの日付を2019/01/01～2019/06/30に変更するだけです。
一応別ファイルで作ります。

実行結果

どきどき…

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db < syukei2019\ first\ half.sql
決済内容        合計
----------  ----------
SUICA       185000
その他         211674
住宅費         426000
通信費         58775
食費          70291
-           951740

半年間の出費合計 95万1740円!!!
約5割は居住費ですが、こんなに使ったかなあ…

詳細は内容を見ていかないと何とも言えませんが、
おそらくお酒を減らしていく必要がありますね。

まとめ

というわけで今回やりたかった集計はできました!
テーブル1つだけの簡単な構成ですが、今回の内容を一気にしようとすると意外と大変でした。
自分の月次、半年分のだいたいの生活費も判明したということで、もう少し節約生活ができるよう精進したいと思います。

今月中に本記事を書きたかったので集計ツールというにはほど遠く、
ほとんど手動でやってしまいましたが今回の手順は全てスクリプトで動くようにしたいですね。
月一でポチっとするとその月の結果が出せるようになるのが理想です。

あとは数値の出力だけじゃなく可視化にもチャレンジしたいです。
平均や特定の月がなぜか出費が増えてる、とか。
(年末というオチでしょうが)

DBのバックアップも取るようにしないとな。

参考サイト