こんにちは、うっちゃんです。
うっちゃん家ではスマホで家計簿をつけ、月末に収支を計算しています。
今回はPythonを使ってエクセルの家計簿を自動処理するプログラムを使った話をします。
Pythonってなに？という人はこちらのサイトをご覧ください。
（参考：Pythonってなに？）
目次
エクセルの家計簿をプログラミングで自動処理したい！
うっちゃん家は共働きで、「自分で稼いだお金は自由に使うべき」という考えのもと、財布は別々に管理しています。
ただ、生活費は平等に負担したいので、お互いに家計簿をつけて月末に生活費を計算しています。
家計簿にはスマホアプリMoney Managerを使っています。
Money Managerは収入と支出を簡単に記録でき、データをエクセルファイル(拡張子.xls)でエクスポートできます。
エクスポートされた家計簿のデータはこんな感じです。
月末にお互いのエクセルファイルをPCに移して、生活費を計算しています。
家計簿の処理をまとめると下表になります。
家計簿をまとめるのに30分以上かかり、結構な労力がかかります。
また、家計簿はお金の流れの記録であり、家計簿を作ったからと言ってお金が増えるわけではありません。
そこで、できるだけ手間を減らしたいと考え、プログラミング言語Pythonで自動化することにチャレンジしました。
プログラミングと聞くと難しく感じますが、素人にも簡単にできるものでしょうか？
家計簿の自動処理をするプログラムは本1冊とYoutube2本でできた！
PythonはAIや機械学習の分野で有名なプログラミング言語です。
少ないコードで簡潔に書けるために比較的読みやすく、初心者にも学習しやすい特徴を持っています。
プログラミングのやり方は初心者向けの本を1冊買って読み、独学しました。
また、Youtubeで「Python tutorial」と検索して、外国人の解説動画を真似しました。
中でもKeith Galliさんがとってもわかりやすくておすすめです。
正直英語はよくわかりませんが、「コードをこう書いたら」「このような結果になる」の関係を見て勉強しました。
プログラミングの学習は「やりたいこと」を目標に勉強すると、身につけやすいと言われます。
今回の家計簿の自動処理はよい例です。
週末の勉強だけでも、1ヶ月もかからず身につけられるでしょう。
できあがったプログラムはこんな感じで使えます。
この家計簿の自動処理プログラムについて、作り方を解説していきます。
エクセルの家計簿を自動処理するプログラムの作り方
Pythonを使うには事前準備が必要です。
まずはプログラミングを始めるための環境を作りましょう。
プログラミング言語Pythonを使うための準備
まずPythonをインストールします。
詳細なやり方はこちらの動画を参照ください。
仮想環境の構築など、環境整備はこちらの記事がおすすめです。
(参考：Pythonで仮想環境を作る方法)
また、エクセルをプログラムで操作するために必要なライブラリをインストールします。
高度なプログラムはすでに天才たちがライブラリとして作ってくれています。
ライブラリをインストールするだけで自分だけのプログラムが作れます。
今回はpandasという主に表計算やデータ分析に使われるライブラリを使います。
pandasのインストール方法や使い方はこちらの動画をご覧ください。
次にプログラムを書くためのソフトとしてJupyter Notebookをインストールします。
インストール方法はこちらを参照ください。
（参考：Jupyter notebookのインストール方法）
以前の家計簿を自動処理するプログラムの記事ではテキストエディターでプログラムを書いていました。
最近はJupyter Notebookでプログラムを書くことが増えたので、今回はこちらを使用します。
ここまで用意ができたら、プログラムを書いていきます。
エクセルの家計簿に支払い者を入力し、統合ファイルを作成するプログラム
まずは家計簿のエクセルファイルを1つのフォルダーに保存します。
hiro.xlsはうっちゃんの家計簿、misa.xlsは妻の家計簿です。
credit.xlsはクレジットカード払や口座振替など、あらかじめ決まった支出分を記録した家計簿です。
Jupyter Notebookを起動し、プログラムを書いていきます。
#エクセルを操作するためにpandasをインポートする
import pandas as pd
#うっちゃんの家計簿に支払い者としてhiroを入れる
hiro = pd.read_excel('hiro.xls')
hiro['payer'] = 'hiro'
#妻の家計簿に支払い者としてmisaを入れる
misa = pd.read_excel('misa.xls')
misa['payer'] = 'misa'
#クレジットカード払などのデータを読み込む
credit = pd.read_excel('credit.xls')
#3つのエクセルファイルを統合する
df = pd.concat([hiro,misa,credit])
#統合した家計簿をmerge_data.xlsとして保存する
df.to_excel('merge_data.xls',index=False)最初に#をつけた一文はプログラムで処理されないため、コードの説明を書くことができます。
プログラムでエクセルファイルを読み込み、支払い担当者を記載したら統合ファイルを作成しています。
（参考：pandasで表の結合を行う）
統合ファイルができたらmerge_data.xlsとして保存しています。
余談ですが、プログラムを実行すると最近は警告が出るようになりました。
pandasに使われているxlwtがメンテナンス終了になり、今後pandasから除かれる予定だそうです。
openpyxlという別のライブラリを使うように推奨されています。
今後プログラムを書き換える必要が出てきそうです。
さて、これで統合版の家計簿ができました。
次に今月の収支を計算します。
エクセルの家計簿から今月の収支をプログラムで自動計算する
#収入の合計額を計算・表示
get_df = df.loc[df['収入/支出'] == '収入']
get_money = get_df['合計'].sum()
print('収入の合計：')
print(get_money)
#支出の合計額を計算・表示
pay_df = df.loc[df['収入/支出'] == '支出']
pay_money = pay_df['合計'].sum()
print('支出の合計：')
print(pay_money)
#今月の収支を計算
print('今月の収支：')
print(get_money + pay_money)
#収支がプラスなら黒字、マイナスなら赤字と表示
if (get_money + pay_money) >= 0:
print('今月は黒字です')
else:
print('今月は赤字です')
print('-' * 10)収入はプラス、支出はマイナスの値で記録されているため、単純に足し算で収支を計算します。
さらに収支がプラスなら「今月は黒字です」、マイナスなら「今月は赤字です」と表示するようにしています。
次に支出の項目について表示と計算を行います。
エクセルの家計簿から支出の項目をプログラムで自動集計して計算する
#支出の項目を自動集計して計算する
list = ['食費','日用','治療','交通','教育','葬祭','家賃','水光熱','ローン','通信','投資','わり','立て替え']
for i in list:
each_df = df.loc[df['メモ'].str.contains(i)]
print(each_df)
print(i + 'の小計：')
print(each_df['合計'].sum())
print('-' * 10)家計簿では何にいくらお金を使っているのかが重要ですよね。
そこで、食費や日用品などにいくらお金を使っているのか集計して計算します。
ちなみに「わり」は割り勘、「立て替え」は立て替えの金額を意味します。
生活費を均等に負担する上で非常に重要な項目です。
次のコードで計算します。
エクセルの家計簿から割り勘と立て替えをプログラムで自動集計して計算する
#割り勘の合計額を計算
hiro_devide = df.loc[(df['メモ'].str.contains(list[11])) & (df['payer'] == 'hiro')]
hiro1 = hiro_devide['合計'].sum()
misa_devide = df.loc[(df['メモ'].str.contains(list[11])) & (df['payer'] == 'misa')]
misa1 = misa_devide['合計'].sum()
#立て替えの合計額を計算
hiro_back = df.loc[(df['メモ'].str.contains(list[12])) & (df['payer'] == 'hiro')]
hiro2 = hiro_back['合計'].sum()
misa_back = df.loc[(df['メモ'].str.contains(list[12])) & (df['payer'] == 'misa')]
misa2 = misa_back['合計'].sum()
#割り勘と立て替えの合計額からうっちゃんと妻のどちらが多く支払っているか計算
pay1 = (hiro1 + misa1) / 2
pay2 = pay1 - misa1
pay3 = pay2 + misa2 - hiro2
#生活費を均等に払うためにお金を渡す額を表示
if pay3 < 0:
print('misa→hiroに支払い：')
print(0 - pay3)
elif pay3 > 0:
print('hiro→misaに支払い：')
print(pay3)
else:
print('支払いなし')生活費を均等に負担するために割り勘と立て替えの金額を計算します。
list[11]は割り勘の「わり」、list[12]は「立て替え」を意味します。
家計簿のメモに「わり」「立て替え」を入れておかないと計算に反映されません。
最後にどちらからいくらお金を渡すかをプログラムで計算して表示しています。
計算が終わったら結果をコピーしてメールで送って終わりです。
最初は苦労しますが、一度作ってしまえばプログラムを起動するだけなのでとっても楽です。
よかったら、家計簿の計算にプログラムを使ってみませんか？
GitHubから入手いただけます。
もし作って欲しい場合はご連絡いただけたら作成しますので、お気軽にどうぞ。