paloma blog

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

trelloでタスク管理をする(自宅の)

仕事の他、プライベートで自分のやりたいことはたくさんあるけどいまいち進捗がよくない...
この状況によく陥ります。
なあなあの状態にしないようタスク管理ツールで見える化したいと思います。

メモアプリは普段Google Keepを使っているのですが、
期限を書く所がないのでやりたいことだけ溜まっていってしまいます。

かといってRedmineなんかを使うほどでもありません。
ここはタスクボード的なもので簡単に状況が分かればいいですよね。

職場ではkanboardというツールを使って自分のタスク管理をしています。

でもいつもSI業界の閉じられた環境にいるので、
自宅環境周りではWebアプリとして公開されているメジャーなツールを使っていきたいと思います。

というのでtrelloな訳です。

登録

URL

https://trello.com/

Googleアカウントでログイン可能です。
特に言うことなし。

用語

trelloでは主に以下の用語があります。

  • ボード
    • ボードごとにタスクを管理できる
    • 所謂プロジェクトの単位
  • リスト
    • カードの状態を表す列
  • カード
    • タスク

リスト構成

タスクの状態を表す列ですね。
あくまでプライベートのタスクなので、進捗というよりはやったかどうかだけ見えればよいです。

ボードは1つでいいので作業系と買い物系のタスクを管理します。

  • 今月中
  • 4半期中
  • 完了
  • 買い物系
    • これは欲しいものをメモっておきます

くらいで一旦作ります。

カード

現時点でやりたいことはこれです。

作業系
  • ブログ執筆(週1回なので4個)
    • 月4回目標でこのブログ更新したいです
    • これは月ごとにリセットします
  • 中古車検索サイト作る
    • これを完成させる
    • 年末くらい目標で
  • Home Labを作る
  • 録画サーバを作る
    • 先代が壊れてから作ってないのでそろそろ作ります
買い物系
  • 自宅サーバ購入
  • 録画用サーバ購入
  • キッチンテーブル
    • キャンプ用

カードに色付けられないのが残念ですがこれくらいシンプルでもいいですね。

というわけでtrelloに起こしたものはこんな感じです。

f:id:paloma69:20190831175800p:plain

タスクが無いことは本来いいことなのですが、少なくて寂しいですねw
まだ全部書ききれてないので少ないですがこれから追記していきます。

使い方が慣れてきたら運用の見直しもしつつほかの機能も探そうと思います。

映画視聴ロガーを作る ツイッターに投稿する編

前回の続きで取得した情報をツイートします。

Twitter APIの取得

投稿のためにTwitter APIを取得しました。
申請が面倒くさかったですが、すんなりGETできました。

下記サイトの手順のままです。

https://www.torikun.com/entry/twitter-developer-api/

私は申請の説明は映画情報投稿アプリを作りたい旨の説明文を書きました。
8/20に申請したのですが、その日に登録のメールが来ました。

投稿用スクリプト

tweetAPIのナレッジはたくさんあります。
私は下記サイトを参考(というか丸々借用)しました。

tweepyの参考サイト
qiita.com

※tweepyとは別にtweetpyというライブラリもあるようです。
ナレッジが多い前者で作っています。

keyは別ファイルに外出ししておきます。

  • configure.py
(tweetpy) masashi@PC-ubuntu:~/tweetpy$ cat configure.py
CK = '取得したキー'
CS = '取得したトークン'
AT = '取得したキー'
AS = '取得したトークン'
  • posttw.py

自分のアカウントにtweetするだけです。

(tweetpy) masashi@PC-ubuntu:~/tweetpy$ cat posttw.py
import tweepy
import sys
import configure

Consumer_Key=configure.CK
Consumer_Secret=configure.CS
Access_Token=configure.AT
Access_Secret=configure.AS

auth = tweepy.OAuthHandler(Consumer_Key, Consumer_Secret)
auth.set_access_token(Access_Token, Access_Secret)
api = tweepy.API(auth)

# ツイート
api.update_status(sys.argv[1])

テストツイートしてます。

(tweetpy) masashi@PC-ubuntu:~/tweetpy$ python posttw.py 'APIからツイート!'

OKですね。

シェルと合体

シェルスクリプトでの結果をpythonに渡します。

  • twmovieinfo.sh
(tweetpy) masashi@PC-ubuntu:~/tweetpy$ cat twmovieinfo.sh
#!/bin/bash

# タイトルにスペースを含む場合がある
movieinfo=$(movies -d $@)

if [ $? == 1 ] ; then
    echo "タイトルが見つかりません。終了します。"
    exit 1
fi

DATE=$(date +%F)
INFO=$(echo "$movieinfo" | cut -d ' ' -f 2- | grep -Pe '^Y|^G|^Di|^Ac|^Pr')
# sedで行末スペース削除
TITLE=$(echo "$movieinfo" | grep Title | awk '{c="";for(i=3;i<=NF;i++) c=c $i" " ;print c}'| sed -e 's/ $//')

TWEET="""\
${DATE}に「${TITLE}」を視聴しました。

その他Infomation
---
${INFO}
---
"""

python posttw.py "${TWEET}"

映画情報を打ってみます。
(これも好きな映画)
ちなみに本件用にvenvで仮想環境構築してます。

(tweetpy) masashi@PC-ubuntu:~/tweetpy$ bash twmovieinfo.sh pulp fiction

OK!

まとめ 

難しいことはしていませんが、API部分は意外とサクッと作れてしまいました。
今回のツールはtweetするだけですが、APIを使っての操作ということでようやく今時っぽい(?)ツールが作れました。
元々記録するだけの予定でしたが今年は何本見たのか、よく見るジャンルは、監督は…などの集計もできそうですね。

というわけで今日から運用したいと思います。

映画視聴ロガーを作る 情報取得編

記録するというのは大切な事だと思います。
ある日振り替える機会があったときに過去の自分の活動が見えるからです。
このブログもその1つです。

また、私は映画が好きで週一本のペースで見てるのですが、
これも同じでローカルに映画視聴メモなんてのも書いてます。

しかしこれが以外と大変で簡単な感想を書くのも一苦労です。
(批評サイトとか作ってる人は尊敬します)

視聴記録と言ってもそれ用のアプリは山ほどありますし、
感想を書くとか大したもんじゃなくていいので
「いつ何を見たのか」を簡単に残せる仕組みを作りたいと思います。

せっかくなんでローカルに残すのではなくオンラインに残せるようにします。
上げ先はよく使うtwitterにします。

やること

  1. 映画の概要を取得する
  2. 内容を整形する
  3. twitterに投稿する

簡単にこれだけ。

なにで作るか

これを実現する私のスキルはshellかpythonしかまともな選択肢はありません。

twitter回りはpythonでやるとして映画情報をどう取るかですね。

そこでshellのmoviesコマンドです。
わざわざ作り込まなくてもいいのであるものは利用しましょう。(スピード大事!)

moviesコマンドは以前書いたBash-snippetsに入ってます。
paloma69.hatenablog.com

shell scriptベースで取得ツールと投稿ツールを叩くという内容にします。

環境

サブ機のUbuntuで動かすことにします。

masashi@PC-ubuntu:~$ lsb_release -a ; uname -a ; bash --version
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 18.04.3 LTS
Release:        18.04
Codename:       bionic
Linux PC-ubuntu 4.15.0-58-generic #64-Ubuntu SMP Tue Aug 6 11:12:41 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
GNU bash, バージョン 4.4.20(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2016 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL バージョン 3 またはそれ以降 <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

moviesで映画情報を取る

今回は映画情報を取得するまでにします。

moviesの出力はこんな感じです。
(-dで詳細まで出しています)

masashi@PC-ubuntu:~$ movies -d the circle

==================================================
| Title: The Circle
| Year: 2017
| Runtime: 110 min
| IMDB: 5.3/10
| Tomato: 15%
| Metascore: 43/100
| Rated: PG-13
| Genre: Drama, Sci-Fi, Thriller
| Director: James Ponsoldt
| Actors: Emma Watson, Ellar Coltrane, Glenne Headly, Bill Paxton
| Plot: A woman lands a dream job at a powerful tech company called the Circle, only to uncover an agenda that will affect the lives of all of humanity.
| Box Office: $20,476,300
| Production: EuropaCorp / STXfilms
| Awards: 1 win & 1 nomination.
==================================================

とれる情報は英語ですがまあいいでしょう。

ちなみにThe Circleはお盆休みに見た映画です。
(エマ・ワトソンが出てるので見たかったんです。)

moviesコマンドだけで欲しい方法は取れるのですがtwitterの文字数もあるので少し削ります。
これだけ記録するように取りましょう。

  • 視聴日付
  • タイトル
  • 公開年
  • 監督
  • 役者
  • 配給会社

ツイートイメージはこんな感じです。
いつ、何の映画を見たという情報だけです。

yyyy/mm/ddに「○○」を視聴しました。

その他情報
---
Year: xxxx
Genre: xxx
Director: xxx
Actors: xxx
Production: xxx
---

実装

shell scriptを書きます。

簡単なメモ
  • 映画情報取得に失敗したら処理を止める
  • 見たらすぐコマンドを打つことを想定しているのでdateは特にいじらない
  • moviesコマンドの出力は枠が付いてくるので、cutで項目名以降を抽出する
  • タイトルはタイトルのみ抜き出したいのでawkで特定の列以降を出力する
twmovie.sh
#!/bin/bash

# タイトルにスペースを含む場合がある
movieinfo=$(movies -d $@)

if [ $? == 1 ] ; then
    echo "タイトルが見つかりません。終了します。"
    exit 1
fi

DATE=$(date +%F)
INFO=$(echo "$movieinfo" | cut -d ' ' -f 2- | grep -Pe '^Y|^G|^Di|^Ac|^Pr')
# sedで行末スペース削除
TITLE=$(echo "$movieinfo" | grep Title | awk '{c="";for(i=3;i<=NF;i++) c=c $i" " ;print c}'| sed -e 's/ $//')


echo """\
${DATE}に「${TITLE}」を視聴しました。

その他Infomation
---
${INFO}
---
"""

動かしてみる

視聴日にこのスクリプトを叩いて記録です。
今見終わったという体で別の映画で試しましょう。
(これも割と好きなswordfish)

masashi@PC-ubuntu:~$ bash twmovie.sh swordfish
2019-08-21に「Swordfish」を視聴しました。

その他Infomation
---
Year: 2001
Genre: Action, Crime, Thriller
Director: Dominic Sena
Actors: John Travolta, Hugh Jackman, Halle Berry, Don Cheadle
Production: Warner Bros. Pictures
---

OKですね!
想定通りの出力にできました。
あとはこれをtwitterに投稿できるようにすれば一旦完成です。

今回はここまでにします。
次回は投稿部分を作ります。

参考サイト

月次の支払い集計ツールを作る 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,"J:COM 2サービスパック利用料",5431
2019/01/05,"JR西日本 5489サービス",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','J:COM 2サービスパック利用料','5431');
INSERT INTO expenses VALUES('2019/02/02','つり具ランド 館山総合館','1930');
INSERT INTO expenses VALUES('2019/02/03','JR東日本モバイルSuica','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|J:COM 2サービスパック利用料|5431
2019/02/02|つり具ランド 館山総合館|1930
2019/02/03|JR東日本モバイルSuica|2000
2019/02/05|JR東日本モバイルSuica|2000
2019/02/06|JR東日本モバイルSuica|2000
2019/02/07|JR東日本モバイルSuica|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 '%au電話利用料%') then '通信費'
    when (select contents from expenses where contents like '%J:COM%') then '通信費'
    when 'インターネットイニシアティブ' then '通信費'
    when 'NETFLIX.COM' then '通信費'
    when 'ヤフージャパン' then '通信費'
    when 'DIGITALOCEAN.COM (DIGITALOCEAN.)' 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';

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のバックアップも取るようにしないとな。

参考サイト

月次の支払い集計ツールを作る 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

MinecraftにComputerCraftを入れた

やっていたゲームが一段落したので欲しかったMinecraftを購入しました。

ComputerCraftを入れたかったのでJAVA Editionです。
久しぶりにSteam外でゲーム買いましたw

以前読んだ「Automate the Boring Stuff with Python」の著者が「CODING WITH MINECRAFT」という本を出していて
ちょっと読んでみたら面白そうだったので真似してやってみることにしました。

https://turtleappstore.com/book/

概要としてはMinecraftのModを使って単調作業(採掘など)はプログラミングで解決しようというものです。
Minecraftの楽しみが減る気もしますが、私もITで働いている身として自動化作業も勉強したいと思い購入に踏み切りました。

また、購入の理由はもう1つあって最近自宅でプログラミングしていないので何かやるきっかけが欲しかったからです。

とは言えMod入れる前にまずは遊んでみます。

ノーマルで遊んでみる

Youtubeでマイクラ動画はたまに見たりしていたので概要はわかります。

今時点(2019/07/20)の最新版は1.14です。

スポーン地点が悪く島の孤島でした。
数時間遊んで周りの島も探検してみましたが完全に孤立していて大陸がみつかりません。
また、羊も見つからなく夜を超すことができないのでこのワールドは諦めてやり直しですね。

f:id:paloma69:20190720093835p:plain

デフォルトではパッドに対応していないのでキーボード操作に苦労しました。

ComputerCraft導入

購入の目的はComputerCraftなので本編はいったんおいておきます。

導入はこちらを参考にしました。

studio.beatnix.co.jp

本ではATLancherというツール経由で入れていたんですが、それだとModがうまく入らずに起動しませんでした。
バージョンの関係もあると思いますが、本での手順は諦めて上記を参考に導入しました。

ComputerCraftの安定版は1.8.9なんですね。
最新版は1.14なのでちょい古ですね。

とにかく導入できました。

ComputerCraftのルール決め

とりあえず2つだけ守ります。

  1. クリエイティブモードOK
    プログラミングで作業自動化が目的なのでクリエイティブモードは有効にしておきます。
    サバイバルだけでやると本題に進まなそうなので。

  2. 簡単な自動化を動かすまでやる
    これやらないと買った意味がないですからね。
    とりあえずの目標は鉱石堀りや簡単な拠点作成を目指します。

Mining turtle作成

Mining turtleというのが作業を自動化してくれる物ですが、レッドストーンが必要なので作成までハードルが高いです。
なので最初はクリエイティブモードでサクッとやっちゃいます。

すぐ配置できちゃいます。
クリエイティブモードは便利ですね。
斧のタイプしか知らないのですが、剣とかのは攻撃用とかになるんですかね。

f:id:paloma69:20190720094058p:plain

この子がマイニングタートルです。

f:id:paloma69:20190720094136p:plain

肝心のプログラムはまだ動かせてないんですが、おいおい進めていきます。

プログラムはluaで書いていくのでこっちの学習も一緒にやっていきます。
飽きたり詰まったりしたら普通のモードで気分転換がてら遊べるのですごくお得な買い物をしました(^^)v

今月GTA Onlineのアップデートもあるのに全然プレイできませんねw

SRXのVLANタグ化と諸々

Juniper社のSRXをタグ化する作業があって、諸々調べたのでメモります。

マニュアルを確認したらタグ化について似たような設定がいくつか出てきました。

  • vlan-tagging
    • シングルタグの送受信ができる
    • 結局タグ化はこの設定でOKでした

クラスタ組んでいる状態でreth2のIFにVLAN10のタグをつけたい場合は以下でできました。
IPアドレスはunit 0で採番済みでタグ化だけしたいという状況です。
接続先はCiscoのL2でswitchport mode trunkにしています。 JUNOSは15.1X49 です。

set interfaces reth2 vlan-tagging
set interfaces reth2 unit 0 vlan-id 10

あとはcommitでOK。

その他

  • flexible-vlan-tagging
    • シングルタグとデュアルタグの送受信ができる
  • stacked-tag-vlan
    • デュアルタグの送受信ができる

シングルタグ、デュアルタグとは

シングル、デュアルはJuniper社の言い方のようでタグの重ね掛けのことのようです。
調べたらVLANタグを2つ付けられるようですね。(QinQ:後述)
知らなかったなー。


VLANについて諸々

VLANとは

  • Vitual LAN

いろんなVLAN

色々種類がありますのでさらっと書いておきます。

ポートVLAN
  • ciscoで言うアクセスVLAN設定
  • 機器内で同VLANのポートへ転送する
  • 他の機器への転送時VLANタグはつかない
(ダイナミックVLAN)
  • MACアドレスを見て自動でVLANが付与される
  • オフィス用途でデータセンタ内の設備では使用しないと思うので割愛
タグVLAN
  • 1つのポートに複数のVLANトラフィックを流せる
  • 802.1qタグが付与される
  • タグが付与されないネイティブVLANの設定が行える(デフォルトはVLAN 1)
  • TPIDは0x8100
  • VLAN IDのヘッダは12bit長なので値は4096まで
  • CiscoではISLという独自のタグフォーマットがある(使ったこと無いです)
プライベートVLAN
VLANトンネリング (QinQ)
  • 2種類あるようです

    • VLANスタック
      • 802.1qのVLANタグをスタックし、スタック後のVLAN IDで転送を行う
        • 上位のVLANタグヘッダには802.1adのタグが付与される
        • 802.1adタグはEthernetヘッダ側にカプセル化される
      • プロバイダ網等で使用される
      • TPIDは0x88a8
      • VLAN IDの値はdot1qと同じ
    • VLAN置換
      • 機器間でVLAN IDが置換される

まとめ

QinQで802.1q + 802.1qの場合と802.1ad + 802.1qのパターンが出てきてどちらが正しいんですかね。
CiscoとかApresiaのサイトでは802.1adの文字は出てきませんでした。
2005年にRFCに策定されたようなので比較的新しいからですかね。
私は802.1ad + 802.1qのパターンで認識しようと思います。

VLAN透過はたまに使ったりするのですが、VLANトンネリングは初めて知りました。
ちょっと前にVMware NSXを勉強する機会があったのでVxLAN周りはいろいろ調べたのですが、
VLANトンネリングでもちょっとしたL2延伸的なことはできそうですね。

細かい検証もできず絵も無いまとめただけの記事になってしまいましたが、
それでもなんとなくすっきりできました。

参考サイト

Enabling VLAN Tagging - TechLibrary - Juniper Networks

What Is a Virtual LAN (VLAN) and What Can It Do?

第4編 レイヤー2

IEEE 802.1ad - Wikipedia