Djangoで家計簿の自動仕訳
- estoy
- 2020年12月3日
以前、Chart.jsにて家計簿をグラフ化する記事を書きましたが、今回は、Chart.jsに表示するためのデータを生成する部分、主にDjango側で行っているファイルデータの自動仕訳部分について、書いていきたいと思います。
クレジットをよく利用する人は、クレジットで払った内容を好きなようにグルーピングしてグラフを見れるようになりますので、参考になれば幸いです。
1. 概要
このアプリを作るにあたって、個人的に一番最初に考えていたことが、どうやったらこのアプリを継続して利用できるかということです。
一回の買い物（レシート毎）に仕訳・手入力すれば、一番細かい単位で管理できるとは思いましたが、面倒くさがりな自分は絶対に続けられない自信がありました。
そこで、極力入力する手間をかけずに自分の支出を管理するために、利用するクレジットカードで生成される明細ファイルをアップロードすると自動で仕訳して画面表示するような仕組みでアプリを作ってみました。
実際の例として、次のようなサンプルファイルをアップロードすると、、、
| 支払日 | 支払内容 | 金額 |
| 2020/03/26 | デイリーヤマザキ／ｉＤ | 162 |
| 2020/03/26 | ローソン／ｉＤ | 410 |
| 2020/03/27 | ファミリーマート／ｉＤ | 488 |
| 2020/03/30 | デイリーヤマザキ／ｉＤ | 388 |
・自動仕訳処理結果
画像の赤枠部分のように、ファイルの支払内容部分に応じて自動に仕訳され、グラフでグルーピングして表現できるようなアプリになっています。
2. データモデル(model.py)
このアプリで作成したテーブルは2つ。「仕訳をするためのマスタ」と「実際の支払データ」であり、以下に記載しているような構成でテーブルを作成しています。
ファイル名：model.py
from django.db import models
from datetime import datetime
#仕訳マスタ
class PaymentMst(models.Model):
pay_id = models.CharField(primary_key=True,max_length=2)
pay_name = models.CharField(max_length=60)
regex = models.CharField(max_length=90,null=True,blank=True)
priority = models.IntegerField()
color = models.CharField(max_length=25,null=True,blank=True)
update_tm = models.DateTimeField(null=True,blank=True,default=datetime.now)
class Meta:
db_table = 'payment_mst'
#支払データ
class Payment(models.Model):
seq = models.AutoField(primary_key=True)
pay_month = models.CharField(max_length=6)
pay_id = models.CharField(default='99',max_length=2)
pay_day = models.DateField()
price = models.IntegerField(default=0)
tekiyo = models.CharField(null=True,max_length=90)
update_tm = models.DateTimeField(null=True,blank=True,default=datetime.now)
class Meta:
db_table = 'payment'
仕訳マスタ(PaymentMst)は「pay_id」を主キーとし、画面に表示する際の名称の「pay_name」、支払内容を仕訳するための正規表現を登録する「regex」、仕訳を適用する優先順位を決める「priority」、画面表示の際の「color」といったレイアウトになっています。
一方で、支払データ（Payment）の方は、シーケンスの「seq」を主キーとして、支払を行った月を示す「pay_month」、仕訳マスタによって仕訳された「pay_id」、支払日を示す「pay_day」、金額を示す「price」、クレジットの明細に記載された支払内容を示す「tekiyo」というレイアウトになっています。
両者の関係としてはpay_idが結合キーとなりますが、支払データの「tekiyo」を基に、仕訳マスタの「regex」が一致する場合、「pay_id」に分類していくような処理を行います。（詳細は後述）
3. データ処理部分(view.py)
次に実際にファイルをアップロードして、仕訳処理を行うview.pyの部分になります。
view.pyに記載している処理は、以下の通り。
ファイル名：viewl.py
from django.shortcuts import render,redirect
from django.views.generic import TemplateView
from django.views.generic import FormView
from django.views.generic import ListView
from django.http import HttpResponse
from .models import Payment,PaymentMst
from datetime import datetime, timedelta,date
import socket
from .forms import UploadFileForm
from django.db import connection, transaction
#月別支払合計データ表示ビュー
class PaymentData(FormView):
template_name = 'home_app/payment_upload.html'
form_class = UploadFileForm
#月別支払データ表示処理
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
form = self.get_form()
#Set Form
context['form'] = form
#Get Payment Data
json = GetPaymentData()
context["pay_data"] = json
return context
#支払データ仕訳処理
def form_valid(self, form):
upfile = self.request.FILES['file']
file_path = 'media/temp/' + upfile.name
month = upfile.name.split('.')[0]
with open(file_path , 'wb+') as fs:
for chunk in upfile.chunks():
fs.write(chunk)
with open(file_path,encoding='shift-jis', mode='r') as fp:
fp.readline()
with transaction.atomic():
Payment.objects.filter(pay_month=month).delete()
for line in fp.readlines():
datas = line.strip().split(',')
if len(datas[0])==10:
value = datas[5] if len(datas[2]) == 0 else datas[2]
p = Payment(pay_month=month,pay_day=datas[0].replace('/','-'),tekiyo=datas[1],price=value)
p.save()
msts = PaymentMst.objects.raw('SELECT * FROM payment_mst WHERE regex IS NOT NULL ORDER BY priority')
cursor = connection.cursor()
sql = "UPDATE payment T01 SET pay_id = %s, update_tm = now() WHERE T01.pay_id = '99' AND T01.pay_month= %s AND T01.tekiyo regexp %s"
for mst in msts:
regex = mst.regex
id = mst.pay_id
cursor.execute(sql,[id,month,regex])
return redirect('home_app:payment_data')
#月別支払明細データ表示ビュー
class PaymentDetail(ListView):
template_name = 'home_app/payment_detail.html'
def get_queryset(self):
try:
yyyymm=self.kwargs['yyyymm']
param = yyyymm[:4] + '-' + yyyymm[4:] + '-01'
qs = Payment.objects.raw("SELECT * FROM payment WHERE pay_day >= %s AND pay_day <= LAST_DAY(%s) ORDER BY pay_id, pay_day",[param,param])
except:
qs = Payment.objects.all()
finally:
return qs
def get_context_data(self,**kwargs):
context = super().get_context_data(**kwargs)
try:
yyyymm = self.kwargs['yyyymm']
except:
yyyymm = None
json = GetPaymentData(yyyymm)
context["pay"] = json
return context
#内部処理（データ取得処理）
def GetPaymentData(yyyymm=None):
mst = PaymentMst.objects.all().order_by("priority","pay_id")
with connection.cursor() as cur:
sql = []
param = ""
if yyyymm is not None:
param = yyyymm[:4] + '-' + yyyymm[4:] + '-01'
sql.append( " SELECT")
sql.append( " DATE_FORMAT(T01.pay_day,'%%Y%%m') AS PAY_MONTH")
for p in mst:
sql.append(" , SUM( CASE WHEN T01.pay_id =")
sql.append(p.pay_id )
sql.append(" THEN T01.price ELSE 0 END ) AS PAY_")
sql.append(p.pay_id)
sql.append(" FROM ")
sql.append(" payment T01")
sql.append(" WHERE ")
sql.append(" pay_day >= %s AND pay_day <= LAST_DAY(%s) ")
sql.append(" GROUP BY ")
sql.append(" DATE_FORMAT(T01.pay_day,'%%Y%%m')")
sql.append(" ORDER BY ")
sql.append(" DATE_FORMAT(T01.pay_day,'%%Y%%m')")
cur.execute("".join(sql),[param,param])
else:
sql.append( " SELECT")
sql.append( " DATE_FORMAT(T01.pay_day,'%Y%m') AS PAY_MONTH")
for p in mst:
sql.append(" , SUM( CASE WHEN T01.pay_id =")
sql.append(p.pay_id )
sql.append(" THEN T01.price ELSE 0 END ) AS PAY_")
sql.append(p.pay_id)
sql.append(" FROM ")
sql.append(" payment T01")
sql.append(" GROUP BY ")
sql.append(" DATE_FORMAT(T01.pay_day,'%Y%m')")
sql.append(" ORDER BY ")
sql.append(" DATE_FORMAT(T01.pay_day,'%Y%m')")
cur.execute("".join(sql))
json = {}
json["pay"]=[]
for p in (mst):
color = p.color if p.color is not None else ''
temp = { "id":p.pay_id, "name":p.pay_name, "values":[], "color":color}
json["pay"].append(temp)
for row in cur.fetchall():
json.setdefault("pay_month",[]).append(row[0])
for i, p in enumerate(mst):
json["pay"][i]["values"].append(int(row[i+1]))
return json
DjangoのビューはClassビューを使っており、月毎の合計を示すPamentDataと月別の明細を示すPaymentDetailの二つのClassビューを作成しています。
メインの仕訳処理は、PamentDataの「form_vaild」部分で、処理内容を簡単に説明すると、次のようになります。
クライアントから送られてきたファイルを/media/tempフォルダ（mediaのURLはsetting.pyにて指定）に格納し、ファイルを一行ずつ読み取り、modelで作成したPaymentテーブルに対して、一旦、「pay_id = '99'」として登録。ファイルのデータの登録が完了したら、今度はPaymentMstのデータを優先度順（priorityの昇順）に、一時登録したpay_idを正規表現が一致するものに更新するということを行っています。
また、その他「Chart.jsで家計簿のグラフ化」の記事に記載した画面でグラフを表示するChart.jsに渡すための処理は「GetPaymentData関数」にて行っています。
支払を行った月毎にデータを集計し、横持でpay_id毎の金額を取得するような動的SQLを組んでデータを取得し、Chart.jsに表示するために、次のようなJSONデータを作成する処理となっています。
Chart.js側の処理については、下記の関連する記事にも記載していますが、以前投稿した「Chart.jsで家計簿をグラフ化」にて処理を記載していますので、そちらを見て頂ければと思います。
Chart.js出力データ（サンプル）
{
'pay_month': ['201811', '201812', '201901', '201902', '201903'....], //Paymentに登録した今までの年月
"pay":[
{
'id': '1', // PaymentMstに設定したpay_id
'color': 'rgb(100,200,100)', // PaymentMstに設定したcolor
'values': [7123, 9368, 2834, 2470, 4663...], //pay_idの月毎の合計金額
'name': '食費（コンビニ）' // PaymentMstに設定したname
}, ...
]
}
※その他補足.
- DBはMySqlを利用しています。そのため、SQLはMySqlに準拠したものとなっています。
- 仕訳を行ってデータを登録する「form_vaild」の処理において、 「 if len(datas[0])==10:」という条件判定がありますが、これはINSERT対象の明細データか否かを判定するための条件になっています。利用しているクレジット会社の明細ファイルの1列目が日付書式「yyyy/mm/dd」の時、明細データがファイルに記載されているので、それを識別するためにこの条件を追記しています。本ソースを参考される方は、ファイルレイアウトに応じて変更した方が良い部分になります。
- あくまで個人のアプリなので、try-except等の例外処理を省くなど、色々端折って実装しています。ところどころ詰めが甘い点はご容赦下さい。。。
4. 利用イメージ
実際、現在アプリを利用していますが、仕訳マスタの設定例が次のような感じです。
私の場合、コンビニを利用する大半が食事のため、regexに記載しているように「セブン-イレブン」、「ファミリーマート」、「ローソン」、「デイリーヤマザキ」の名前が、支払内容に記載されていた場合、「pay_id = 1」の「コンビニ（食費）」と判別するような設定となっています。
上記のように、マスタの設定が完了すると、概要に記載したような形で支払内容の仕訳ができるようになり、最終的には支払の種別（pay_id）毎の月別の推移が見れるようになります。
説明が長くなってしまいましたが、上記のような考えで家計簿の自動仕訳のアプリを作ってみましたので、もし、家計簿のアプリを作ってみたいという方の参考になれば幸いです。
また、グラフを生成するChart.js側の処理については、下記の「Chart.jsで家計簿のグラフ化」の記事でロジックを紹介していますので、興味がある人はそちらも見て頂ければと思います。