PythonでSquareの売上データ・入金データを弥生会計用に変換する

SquareのPOSレジのデータをPythonを使って弥生会計用のデータに変換してみました。

売上データのデータ変換

日々の売上データを作成するためのものです。現金売上と、クレジット決済・QRコード決済の売上（売掛金を計上）のデータを作成します。

基本的には、

  1. 元となるCSVファイルを入力用のDataFrameに読み込みする
  2. 入力用のDataFrameのデータを編集・集計する
  3. データ出力用の別のDataFrameを作成し、データ入力用のDataFrameのデータを嵌め込む
  4. データ出力用のDataFrameからCSVファイルを出力する

という手順で行っています。

import os
import csv
import pandas as pd
from decimal import Decimal, ROUND_HALF_UP

# 四捨五入
def r_round(x):
    return int(Decimal(str(x)).quantize(Decimal('0'),rounding=ROUND_HALF_UP))

df = pd.read_csv(r'お取引-2024-02-01-2024-04-15.csv',encoding = 'utf-16'
,dtype = 'object',sep='\t',usecols=[0,13,15,49])

# 「,」と「¥」を削除
df.replace({',': '','¥': ''},inplace = True,regex=True)

df['日付'] = df['日付'].str[0:4] + '/' + df['日付'].str[5:7]
 + '/' + df['日付'].str[8:10]

# 文字列を数値に変換
df_temp = df.astype({'カード': 'int', '現金': 'int', 'PayPay': 'int'})

# 集計すると'日付'がindexになるためresetする
grouped = df_temp.groupby('日付').sum().reset_index()

df_cash = pd.DataFrame(index = grouped.index,columns=['A','B','C','D','E','F'
,'G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y'])

df_cash['A'] = 2000
df_cash['B'] = ''
df_cash['C'] = ''
df_cash['D'] = grouped['日付']
df_cash['E'] = '現金'
df_cash['F'] = ''
df_cash['G'] = ''
df_cash['H'] = '対象外'
df_cash['I'] = grouped['現金'] 
df_cash['J'] = 0
df_cash['K'] = '売上高'
df_cash['L'] = ''
df_cash['M'] = ''
df_cash['N'] = '課税売上内10%'
df_cash['O'] = df_cash['I'] 
df_cash['P'] = df_cash['I'] - (df_cash['I']/1.1).map(r_round)
df_cash['Q'] = '本日売上高'
df_cash['R'] = ''
df_cash['S'] = ''
df_cash['T'] = 0
df_cash['U'] = ''
df_cash['V'] = ''
df_cash['W'] = 0
df_cash['X'] = 0
df_cash['Y'] = 'no'

df_cash[df_cash['I'] != 0].to_csv('py_square_uriage.csv',encoding = 'cp932'
,header = False,index = False)

df_credit = pd.DataFrame(index = grouped.index,columns=['A','B','C','D','E','F'
,'G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y'])

df_credit['A'] = 2000
df_credit['B'] = ''
df_credit['C'] = ''
df_credit['D'] = grouped['日付']
df_credit['E'] = '売掛金'
df_credit['F'] = 'Square'
df_credit['G'] = ''
df_credit['H'] = '対象外'
df_credit['I'] = grouped['カード'] + grouped['PayPay'] 
df_credit['J'] = 0
df_credit['K'] = '売上高'
df_credit['L'] = ''
df_credit['M'] = ''
df_credit['N'] = '課税売上内10%'
df_credit['O'] = df_credit['I'] 
df_credit['P'] = df_credit['I'] - (df_credit['I']/1.1).map(r_round)
df_credit['Q'] = '本日売上高'
df_credit['R'] = ''
df_credit['S'] = ''
df_credit['T'] = 0
df_credit['U'] = ''
df_credit['V'] = ''
df_credit['W'] = 0
df_credit['X'] = 0
df_credit['Y'] = 'no'

df_credit[df_credit['I'] != 0].to_csv('py_square_uriage.csv',mode = 'a'
,encoding = 'cp932',header = False,index = False)

次の入金データについてもそうですが、金額のデータはコンマ（,）や円マーク（¥）が付いた文字列となっていますのでそれらを削除しています。

元のデータは決済ごとのデータになっているため、日付で集計すると同一のレコードに現金売上とクレジット・QRコード売上の両方の金額が存在する場合があります。それで、現金売上のDataFrameとクレジット・QRコード売上のDataFrameをすべての日付について別々に作成して、それぞれ売上金額が0円のものを除外してCSVファイルに出力するようにしています。

入金データのデータ変換

Squareの決済端末でクレジットカードやQRコードを使用して決済した売上金額が預金口座に振り込まれる際の、売上金額から控除されるSquareの手数料を集計するためのものです。

プログラムは以下のようなものです。

import os
import csv
import pandas as pd

#日付の文字列を日付形式で読み込む
df = pd.read_csv(r'振込-詳細-2024-02-01-2024-04-14.csv'
,encoding = 'utf-16',dtype = 'object',sep='\t',usecols=[0,6]
,parse_dates=[0],date_format='%Y-%m-%d')

df.replace({',': '','¥': ''},inplace = True,regex=True)

#日付を1日ずらす
df['入金日'] = df['入金日'] + pd.Timedelta(days=1)

#日付形式を文字列に変換
df['入金日'] = df['入金日'].dt.strftime('%Y/%m/%d')

# 文字列を数値に変換して負の値を正の値にする
df['手数料'] = df['手数料'].astype(int) * -1

# 集計すると'入金日'がindexになるためresetする
grouped = df.groupby('入金日')['手数料'].sum().reset_index()

df_square = pd.DataFrame(index = grouped.index,columns=['A','B','C','D','E','F'
,'G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y'])

df_square['A'] = 2000
df_square['B'] = ''
df_square['C'] = ''
df_square['D'] = grouped['入金日']
df_square['E'] = '支払手数料'
df_square['F'] = ''
df_square['G'] = ''
df_square['H'] = '非課仕入'
df_square['I'] = grouped['手数料'] 
df_square['J'] = 0
df_square['K'] = '事業主貸'
df_square['L'] = ''
df_square['M'] = ''
df_square['N'] = '対象外'
df_square['O'] = df_square['I']
df_square['P'] = 0
df_square['Q'] = '手数料　　 スクエア（カ'
df_square['R'] = ''
df_square['S'] = ''
df_square['T'] = 0
df_square['U'] = ''
df_square['V'] = ''
df_square['W'] = 0
df_square['X'] = 0
df_square['Y'] = 'no'

df_square.to_csv('py_square_nyukin.csv',encoding = 'cp932',header = False
,index = False)こ

日付の計算を行っています。データ上の入金日と実際に銀行の口座に振り込まれた日が一日ずれていたためです。

そのため、CSVファイルをDataFrameに読み込みする際に日付のデータが文字列の型式ではなく日付の型式になるように読み込みして、日付を一日分足し、その後で弥生会計用の日付の文字列に変換しています。

入金データは入金日ごとに集計されておらず、決済ごとのデータの集まりであるため、groupbyを使って入金日ごとに集計しています。

タイトルとURLをコピーしました