家計簿を自動で処理！プログラミング言語Pythonのコードを公開

import pandas as pd
import numpy as np
from pathlib import Path
import glob
from openpyxl import load_workbook, Workbook
from openpyxl.styles.alignment import Alignment
import datetime
from dateutil.relativedelta import relativedelta

date = datetime.date.today() - datetime.timedelta(days=15)
date_list = str(date).split('-')
mergefile = str(date_list[0]+date_list[1])
mergefile

#read hiro's data:change file name.
hiro = pd.read_excel('hiro.xls')
hiro['payer'] = 'hiro'

#read misa's data:change file name.
misa = pd.read_excel('misa.xls')
misa['payer'] = 'misa'

#read credit's data:change file name.
credit = pd.read_excel('credit.xls')
credit['日付'] = date

#Merge hiro ,misa and credit into a single file
df = pd.concat([hiro,misa,credit])
df = df.reset_index()

#save the single file
df.to_excel('merge_data{0}.xlsx'.format(mergefile),index=False)

#How much did we pay and get money?
get_df = df.loc[df['収入/支出'] == '収入']
get_money = get_df['合計'].sum()

pay_df = df.loc[df['収入/支出'] == '支出']
pay_money = pay_df['合計'].sum()

print('収入の合計：'+ str(get_money))
print('支出の合計：' + str(pay_money))
print('今月の収支：' + str(get_money + pay_money))

if (get_money + pay_money) >= 0:
    kekka = '今月は黒字です'
else:
    kekka ='今月は赤字です'

print(kekka)
print('-' * 10)

#pay equally
hiro_devide = df.loc[(df['メモ'].str.contains('わり')) & (df['payer'] == 'hiro')]
hiro1 = hiro_devide['合計'].sum()
misa_devide = df.loc[(df['メモ'].str.contains('わり')) & (df['payer'] == 'misa')]
misa1 = misa_devide['合計'].sum()

#pay back
hiro_back = df.loc[(df['メモ'].str.contains('立て替え')) & (df['payer'] == 'hiro')]
hiro2 = hiro_back['合計'].sum()
misa_back = df.loc[(df['メモ'].str.contains('立て替え')) & (df['payer'] == 'misa')]
misa2 = misa_back['合計'].sum()

#payment in house
pay1 = (hiro1 + misa1) / 2 
pay2 = pay1 - misa1
pay3 = pay2 + misa2 - hiro2

if pay3 < 0:
    kekka2 = 'misa→hiroに支払い：'+str(0 - pay3)
elif pay3 > 0:
    kekka2 = 'hiro→misaに支払い：'+str(pay3)
else:
    kekka2 = '支払いなし'

print(kekka2)
print('-' * 10)

#show each paytment
list = ['食費','日用','治療','交通','教育','葬祭','家賃','水光熱','ローン','通信','投資','わり','立て替え']
each_data = []

for i in list:
    each_df = df.loc[df['メモ'].str.contains(i)]
    each_data.append(each_df)
    each_data.append('\n' + i + 'の小計：' + str(each_df['合計'].sum()))
    each_data.append('-' * 10)

kekka3 = ''
for j in each_data:
    kekka3 += str(j) + '\n'

import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
import base64
from email.mime.text import MIMEText
from apiclient import errors

# 1. Gmail APIのスコープを設定
SCOPES = ['https://www.googleapis.com/auth/gmail.send']

# 2. メール本文の作成
def create_message(sender, to, subject, message_text):
    message = MIMEText(message_text)
    message['to'] = to
    message['from'] = sender
    message['subject'] = subject
    encode_message = base64.urlsafe_b64encode(message.as_bytes())
    return {'raw': encode_message.decode()}

# 3. メール送信の実行
def send_message(service, user_id, message):
    try:
        message = (service.users().messages().send(userId=user_id, body=message)
                   .execute())
        print('Message Id: %s' % message['id'])
        return message
    except errors.HttpError as error:
        print('An error occurred: %s' % error)

# 4. メインとなる処理
def main(i,mess,sbj):
    # 5. アクセストークンの取得
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                    'credentials.json', SCOPES)
            creds = flow.run_local_server()
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    service = build('gmail', 'v1', credentials=creds)
    
       # 6. メール本文の作成
    sender = '****@gmail.com'
    to = i
    subject = sbj
    message_text = mess
    message = create_message(sender, to, subject, message_text)
    # 7. Gmail APIを呼び出してメール送信
    send_message(service, 'me', message)

# 8. プログラム実行！
if __name__ == '__main__':
    sbj = '''
    {0}年{1}月の家計簿
    '''.format(date_list[0], #年
               date_list[1] #月
              )
    
    mess = '''
    {0}年{1}月の家計簿\n
    収入：{2}\n
    支出：{3}\n
    今月の収支：{4}\n
    [5]\n
    ----------
    夫婦勘定：{6}\n
    ----------
    各項目\n
    {7}
    
    '''.format(date_list[0], #年
               date_list[1], #月
              str(get_money), #収入
              str(pay_money), #支出
               str(get_money + pay_money), #今月の収支
              kekka, #赤字か黒字
               kekka2, #割り勘
               kekka3 #各項目の家計簿
              )
    to_list = ['****@gmail.com','****@gmail.com']
    for i in to_list:
        main(i,mess,sbj)