弥生給与の出力データをPythonで仕訳データにする

Pythonなどのプログラミング言語を使って仕訳データを作るメリットは、入力する項目の多い給与などの仕訳で威力を発揮します。こちらの会社では約40人分の給与があるのですが、部門計算と支給残高の間違いを確認するために、個別に未払金の補助残高を設けています。（会社の経理のレベルが上がってきたら、合計記帳で十分になると思います。）ですので、入力の量がものすごく多いのです。
PythonでCSVデータを作るのは、非常に簡単です。一見難しそうに見えるかもしれませんが、3つか4つの基本的なパターンを組み合わせているだけですので、一度わかってしまうととてもシンプルなのです。
今回やったのは、こんな感じです。たったこれだけです。ちなみに加工前のデータは弥生給与を使っています。
1) エクセルや他に必要なモジュールをインポートする。
import openpyxl
import datetime
2) 仕訳のクラスJournalを定義する。
class Journal:
def __init__(self):# コンストラクタ
self.type = 2000
self.Date = “”
self.DrAcc = “”
self.DrSubAcc = “”
self.DrCostCenter = “”
self.DrCTaxCode = “”
self.Amount = 100
self.DrCTaxAmount = 0
self.CrAcc = “”
self.CrSubAcc = “”
self.CrCostCenter = “”
self.CrCTaxCode = “”
self.CrCTaxAmount = 0
self.Description = “”
2) エクセルのファイルを開く。読込対象と書き込み対象のシートを選択する。
wb = openpyxl.load_workbook(“X:/Accounting/ClientNameABC/Working/SummarySalary201904.xlsx”)
sheet_salaries = wb[‘salaries’]
sheet_result_salaries = wb[‘result_salaries’]
3) 個別の仕訳を作成する処理（ここでは総額の仕訳だけですが、この記事の最後のところでコード全体がわかるように表示しています）。
def renderJournalGrossSalar(flag, aDate, name, cost_center_forYayoi, gross_salary):
journal = Journal()
journal.type = flag
journal.Date = aDate
drAcc = “複合”
if cost_center_forYayoi == “A”:
drAcc = “職員給料-A”
elif cost_center_forYayoi == “B”:
drAcc = “職員給料-B”
elif cost_center_forYayoi == “C”:
drAcc = “職員給料-C”
journal.DrAcc = drAcc
journal.DrSubAcc = name
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外仕入”
journal.Amount = gross_salary
journal.DrCTaxAmount = 0
journal.CrAcc = “複合”
journal.CrSubAcc = “”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “月次給与_” + name + “_” + cost_center
return journal
4) データが有るか無いかを1列ごとに判定して、同じ処理を繰り返す。読み取ってJournalクラスのインスタンスにしたものを配列journalsに加えていく。
journals = [] #配列の作成
for columnCount in range(3, 42): #列の数は1を足したり引いたりせず、そのまま使ってよい。
if sheet_salaries.cell(column=columnCount, row=13).value is not None:
#総額の仕訳、交通費の仕訳、源泉所得税、住民税、社会保険料、雇用保険、処遇改善手当、ネットの支給額の仕訳、
　　 gross_salary = sheet_salaries.cell(column=columnCount, row=34).value
commutation = sheet_salaries.cell(column=columnCount, row=29).value
subsidy_shogu_kaizen = sheet_salaries.cell(column=columnCount, row=17).value
subsidy_A = sheet_salaries.cell(column=columnCount, row=18).value
subsidy_B = sheet_salaries.cell(column=columnCount, row=19).value
withholding_social_ins = sheet_salaries.cell(column=columnCount, row=47).value + sheet_salaries.cell(column=columnCount, row=48).value + sheet_salaries.cell(column=columnCount, row=49).value
withhodling_unemployment = sheet_salaries.cell(column=columnCount, row=51).value
witholding_income_tax = sheet_salaries.cell(column=columnCount, row=52).value
withholding_resdient_tax = sheet_salaries.cell(column=columnCount, row=53).value
withholding_other = sheet_salaries.cell(column=columnCount, row=46).value
withholding_meals = sheet_salaries.cell(column=columnCount, row=56).value
netPayable = sheet_salaries.cell(column=columnCount, row=65).value
journal_grossSalary = renderJournalGrossSalar(2110, aDate, name, cost_center_forYayoi, gross_salary)
journals.append(journal_grossSalary)
journal_commutation = renderJournalCommutation(2100, aDate, name, cost_center_forYayoi, commutation)
journals.append(journal_commutation)
if withholding_social_ins != 0:
journal_socInc = renderJournalWithholdingSoc(2100, aDate, name, cost_center_forYayoi, withholding_social_ins)
journals.append(journal_socInc)
if withhodling_unemployment != 0:
journal_unemployment = renderJournalWithholdingUnemployment(2100, aDate, name, cost_center_forYayoi, withhodling_unemployment)
journals.append(journal_unemployment)
if witholding_income_tax != 0:
journal_whTax = renderJournalWithholdingIncomeTax(2100, aDate, name, cost_center_forYayoi, witholding_income_tax)
journals.append(journal_whTax)
if withholding_resdient_tax != 0:
journal_whResidentTax = renderJournalWithholdingResidentTax(2100, aDate, name, cost_center_forYayoi, withholding_resdient_tax)
journals.append(journal_whResidentTax)
if withholding_meals != 0:
journal_whMeals = renderJournalWithholdingMeal(2100, aDate, name, cost_center_forYayoi, withholding_meals)
journals.append(journal_whMeals)
if subsidy_shogu_kaizen != 0:
journal_shogu_kaizen = renderJournalShoguKaizen2(2100, aDate, name, cost_center_forYayoi, subsidy_shogu_kaizen)
journals.append(journal_shogu_kaizen)
if subsidy_A != 0:
journal_subsidy_A = renderJournal_SubsidyA(2100, aDate, name, cost_center_forYayoi, subsidy_A)
journals.append(journal_subsidy_A)
if subsidy_B != 0:
journal_subsidy_B = renderJournal_SubsidyB(2100, aDate, name, cost_center_forYayoi, subsidy_B)
journals.append(journal_subsidy_B)
journal_netAP = renderJournalNetAP(2101, aDate, name, cost_center_forYayoi, netPayable)
journals.append(journal_netAP)
print(str(columnCount) + “行目 = ” + name)
cnt = 1
5) エクセルに仕訳データを書き込む
def renderJournalCSV(journal, sheet, rowNum):
sheet[“A” + str(rowNum)] = journal.type
sheet[“D” + str(rowNum)] = journal.Date #.strftime(“%Y/%m/%d”)
sheet[“E” + str(rowNum)] = journal.DrAcc
sheet[“F” + str(rowNum)] = journal.DrSubAcc
sheet[“G” + str(rowNum)] = journal.DrCostCenter
sheet[“H” + str(rowNum)] = journal.DrCTaxCode
sheet[“I” + str(rowNum)] = journal.Amount
sheet[“J” + str(rowNum)] = journal.DrCTaxAmount
sheet[“K” + str(rowNum)] = journal.CrAcc
sheet[“L” + str(rowNum)] = journal.CrSubAcc
sheet[“M” + str(rowNum)] = journal.CrCostCenter
sheet[“N” + str(rowNum)] = journal.DrCTaxCode
sheet[“O” + str(rowNum)] = journal.Amount
sheet[“P” + str(rowNum)] = 0
sheet[“Q” + str(rowNum)] = journal.Description
sheet[“T” + str(rowNum)] = 3
sheet[“Y” + str(rowNum)] = “no”
6) ファイルに名前を付けて保存する
datetime_now = datetime.datetime.now()
yyyy = str(datetime_now.year)
mm = str(datetime_now.month)
dd = str(datetime_now.day)
hour = str(datetime_now.hour)
minutes = str(datetime_now.minute)
second = str(datetime_now.second)
str_datetime_now = str(datetime_now)
wb.save(“C:/Users/Ichiro Kawai/desktop/summary_result_” + yyyy + mm + dd + hour + minutes + second + “.xlsx”)
最後にコードの全体分を貼っておきます。使われる方は、コピペして適当にアレンジして使ってください。弥生給与の設定項目によって出力される行数が、異なってくると思います。
import openpyxl
import datetime
def getColum(columnNum):
if columnNum == 1:
return “A”
elif columnNum == 2:
return “B”
def renderJournalCSV(journal, sheet, rowNum):
sheet[“A” + str(rowNum)] = journal.type
sheet[“D” + str(rowNum)] = journal.Date #.strftime(“%Y/%m/%d”)
sheet[“E” + str(rowNum)] = journal.DrAcc
sheet[“F” + str(rowNum)] = journal.DrSubAcc
sheet[“G” + str(rowNum)] = journal.DrCostCenter
sheet[“H” + str(rowNum)] = journal.DrCTaxCode
sheet[“I” + str(rowNum)] = journal.Amount
sheet[“J” + str(rowNum)] = journal.DrCTaxAmount
sheet[“K” + str(rowNum)] = journal.CrAcc
sheet[“L” + str(rowNum)] = journal.CrSubAcc
sheet[“M” + str(rowNum)] = journal.CrCostCenter
sheet[“N” + str(rowNum)] = journal.DrCTaxCode
sheet[“O” + str(rowNum)] = journal.Amount
sheet[“P” + str(rowNum)] = 0
sheet[“Q” + str(rowNum)] = journal.Description
sheet[“T” + str(rowNum)] = 3
sheet[“Y” + str(rowNum)] = “no”
def renderJournalGrossSalar(flag, aDate, name, cost_center_forYayoi, gross_salary):
journal = Journal()
journal.type = flag
journal.Date = aDate
drAcc = “複合”
if cost_center_forYayoi == “A”:
drAcc = “職員給料-A”
elif cost_center_forYayoi == “イ1”:
drAcc = “職員給料-イ1”
elif cost_center_forYayoi == “イ2”:
drAcc = “職員給料-イ2”
elif cost_center_forYayoi == “B”:
drAcc = “職員給料-B”
elif cost_center_forYayoi == “本社”:
drAcc = “役員報酬”
journal.DrAcc = drAcc
journal.DrSubAcc = name
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外仕入”
journal.Amount = gross_salary
journal.DrCTaxAmount = 0
journal.CrAcc = “複合”
journal.CrSubAcc = “”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “月次給与_” + name + “_” + cost_center
return journal
def renderJournalCommutation(flag, aDate, name, cost_center_forYayoi, commutation):
journal = Journal()
journal.type = flag
journal.Date = aDate
drAcc = “複合”
if cost_center_forYayoi == “A”:
drAcc = “職員給料-A”
elif cost_center_forYayoi == “イ1”:
drAcc = “職員給料-イ1”
elif cost_center_forYayoi == “イ2”:
drAcc = “職員給料-イ2”
elif cost_center_forYayoi == “B”:
drAcc = “職員給料-B”
elif cost_center_forYayoi == “本社”:
drAcc = “職員給料-本社”
journal.DrAcc = drAcc
journal.DrSubAcc = “通勤費”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “課税対象仕入8”
journal.Amount = commutation
journal.DrCTaxAmount = 0
journal.CrAcc = “複合”
journal.CrSubAcc = “”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “月次通勤費_” + name + “_” + cost_center
return journal
def renderJournalWithholdingSoc(flag, aDate, name, cost_center_forYayoi, withholding_social_ins):
journalSocIns = Journal()
journalSocIns.type = flag
journalSocIns.Date = aDate
journalSocIns.DrAcc = “複合”
journalSocIns.DrSubAcc = “”
journalSocIns.DrCostCenter = cost_center_forYayoi
journalSocIns.DrCTaxCode = “対象外”
journalSocIns.Amount = withholding_social_ins
journalSocIns.DrCTaxAmount = 0
journalSocIns.CrAcc = “預り金”
journalSocIns.CrSubAcc = “社会保険”
journalSocIns.CrCostCenter = “” #cost_center_forYayoi
journalSocIns.CrCTaxCode = “対象外”
journalSocIns.CrCTaxAmount = 0
journalSocIns.Description = “社会保険料預り金_” + name + “_” + cost_center_forYayoi
return journalSocIns
def renderJournalWithholdingUnemployment(flag, aDate, name, cost_center_forYayoi, withhodling_unemployment):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “複合”
journal.DrSubAcc = “”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = withhodling_unemployment
journal.DrCTaxAmount = 0
journal.CrAcc = “預り金”
journal.CrSubAcc = “労働保険”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “労働保険料預り金_” + name + “_” + cost_center_forYayoi
return journal
def renderJournalWithholdingIncomeTax(flag, aDate, name, cost_center_forYayoi, withhodling_income_tax):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “複合”
journal.DrSubAcc = “”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = withhodling_income_tax
journal.DrCTaxAmount = 0
journal.CrAcc = “預り金”
journal.CrSubAcc = “源泉税-給与”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “源泉税-給与_” + name + “_” + cost_center_forYayoi
return journal
def renderJournalWithholdingResidentTax(flag, aDate, name, cost_center_forYayoi, withholding_resdient_tax):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “複合”
journal.DrSubAcc = “”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = withholding_resdient_tax
journal.DrCTaxAmount = 0
journal.CrAcc = “預り金”
journal.CrSubAcc = “住民税”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “住民税_” + name + “_” + cost_center_forYayoi
return journal
def renderJournalWithholdingMeal(flag, aDate, name, cost_center_forYayoi, withholding_meals):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “複合”
journal.DrSubAcc = “”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = withholding_meals
journal.DrCTaxAmount = 0
journal.CrAcc = “預り金”
journal.CrSubAcc = “給食代”
journal.CrCostCenter = “” #cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “給食代_” + name + “_” + cost_center_forYayoi
return journal
def renderJournalNetAP(flag, aDate, name, cost_center_forYayoi, netPayable):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “複合”
journal.DrSubAcc = “”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = netPayable
journal.DrCTaxAmount = 0
journal.CrAcc = “未払金”
journal.CrSubAcc = “給与-” + name
journal.CrCostCenter = cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “未払給与_” + name + “_” + cost_center_forYayoi
return journal
def renderJournalShoguKaizen2(flag, aDate, name, cost_center_forYayoi, subsidy2):
journal = Journal()
drSubAcc = “”
crAcc = “仮払金”
crSubAcc = “”
if cost_center_forYayoi == “A”:
drSubAcc = “処遇改善2(Y)”
crAcc = “職員給料-A-処遇”
crSubAcc = “処遇改善2”
elif cost_center_forYayoi == “イ1”:
drSubAcc = “処遇改善2(Y)”
crAcc = “職員給料-イ1-処遇”
crSubAcc = “処遇改善2”
elif cost_center_forYayoi == “イ2”:
drSubAcc = “処遇改善2(Y)”
crAcc = “職員給料-イ2処遇”
crSubAcc = “処遇改善2”
elif cost_center_forYayoi == “B”:
drSubAcc = “処遇改善2(K)”
crAcc = “職員給料-北-処遇・補助金”
crSubAcc = “処遇改善2”
journal.type = flag
journal.Date = aDate
journal.DrAcc = “預り金”
journal.DrSubAcc = drSubAcc
journal.DrCostCenter = “”# cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = subsidy2
journal.DrCTaxAmount = 0
journal.CrAcc = crAcc
journal.CrSubAcc = crSubAcc
journal.CrCostCenter = cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “処遇改善_” + name + “_” + cost_center_forYayoi
return journal
def renderJournal_SubsidyA(flag, aDate, name, cost_center_forYayoi, subsidy_A):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “預り金”
journal.DrSubAcc = “A手当(Y)”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = subsidy_A
journal.DrCTaxAmount = 0
journal.CrAcc = “職員給料-A-処遇”
journal.CrSubAcc = “A手当”
journal.CrCostCenter = cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “A手当_” + name + “_” + cost_center_forYayoi
return journal
def renderJournal_SubsidyB(flag, aDate, name, cost_center_forYayoi, subsidy_B):
journal = Journal()
journal.type = flag
journal.Date = aDate
journal.DrAcc = “預り金”
journal.DrSubAcc = “B手当(K)”
journal.DrCostCenter = cost_center_forYayoi
journal.DrCTaxCode = “対象外”
journal.Amount = subsidy_B
journal.DrCTaxAmount = 0
journal.CrAcc = “職員給料-北-処遇・補助金”
journal.CrSubAcc = “B手当”
journal.CrCostCenter = cost_center_forYayoi
journal.CrCTaxCode = “対象外”
journal.CrCTaxAmount = 0
journal.Description = “B手当_” + name + “_” + cost_center_forYayoi
return journal
class Journal:
def __init__(self):# コンストラクタ
self.type = 2000
self.Date = “”
self.DrAcc = “”
self.DrSubAcc = “”
self.DrCostCenter = “”
self.DrCTaxCode = “”
self.Amount = 100
self.DrCTaxAmount = 0
self.CrAcc = “”
self.CrSubAcc = “”
self.CrCostCenter = “”
self.CrCTaxCode = “”
self.CrCTaxAmount = 0
self.Description = “”
# def create_salary_journal(name, aDate, gross_salary ):
wb = openpyxl.load_workbook(“X:/Accounting/ClientABC/3_決算関係\R02年03月期/Salary201904.xlsx”)
sheet_salaries = wb[‘salaries’]
sheet_result_salaries = wb[‘result_salaries’]
journals = []
print(“sheet loaded..”)
for columnCount in range(3, 42): #列の数は1を足したり引いたりせず、そのまま使ってよい。
if sheet_salaries.cell(column=columnCount, row=13).value is not None:
#aDate = sheet_salaries.cell(column=columnCount, row=77).value
aDate =”2019/4/30″
name = sheet_salaries.cell(column=columnCount, row=8).value
cost_center = sheet_salaries.cell(column=columnCount, row=7).value[0]
print(aDate)
print(cost_center)
cost_center_forYayoi = “”
if cost_center == “1”:
cost_center_forYayoi = “A”
elif cost_center == “2”:
cost_center_forYayoi = “I1”
elif cost_center == “3”:
cost_center_forYayoi = “I12”
elif cost_center == “4”:
cost_center_forYayoi = “B”
elif cost_center == “5”:
cost_center_forYayoi = “I2”
elif cost_center == “0”:
cost_center_forYayoi = “HQ”
gross_salary = sheet_salaries.cell(column=columnCount, row=34).value
commutation = sheet_salaries.cell(column=columnCount, row=29).value
subsidy_shogu_kaizen = sheet_salaries.cell(column=columnCount, row=17).value
subsidy_A = sheet_salaries.cell(column=columnCount, row=18).value
subsidy_B = sheet_salaries.cell(column=columnCount, row=19).value
withholding_social_ins = sheet_salaries.cell(column=columnCount, row=47).value + sheet_salaries.cell(column=columnCount, row=48).value + sheet_salaries.cell(column=columnCount, row=49).value
withhodling_unemployment = sheet_salaries.cell(column=columnCount, row=51).value
witholding_income_tax = sheet_salaries.cell(column=columnCount, row=52).value
withholding_resdient_tax = sheet_salaries.cell(column=columnCount, row=53).value
withholding_other = sheet_salaries.cell(column=columnCount, row=46).value
withholding_meals = sheet_salaries.cell(column=columnCount, row=56).value
netPayable = sheet_salaries.cell(column=columnCount, row=65).value
journal_grossSalary = renderJournalGrossSalar(2110, aDate, name, cost_center_forYayoi, gross_salary)
journals.append(journal_grossSalary)
journal_commutation = renderJournalCommutation(2100, aDate, name, cost_center_forYayoi, commutation)
journals.append(journal_commutation)
if withholding_social_ins != 0:
journal_socInc = renderJournalWithholdingSoc(2100, aDate, name, cost_center_forYayoi, withholding_social_ins)
journals.append(journal_socInc)
if withhodling_unemployment != 0:
journal_unemployment = renderJournalWithholdingUnemployment(2100, aDate, name, cost_center_forYayoi, withhodling_unemployment)
journals.append(journal_unemployment)
if witholding_income_tax != 0:
journal_whTax = renderJournalWithholdingIncomeTax(2100, aDate, name, cost_center_forYayoi, witholding_income_tax)
journals.append(journal_whTax)
if withholding_resdient_tax != 0:
journal_whResidentTax = renderJournalWithholdingResidentTax(2100, aDate, name, cost_center_forYayoi, withholding_resdient_tax)
journals.append(journal_whResidentTax)
if withholding_meals != 0:
journal_whMeals = renderJournalWithholdingMeal(2100, aDate, name, cost_center_forYayoi, withholding_meals)
journals.append(journal_whMeals)
if subsidy_shogu_kaizen != 0:
journal_shogu_kaizen = renderJournalShoguKaizen2(2100, aDate, name, cost_center_forYayoi, subsidy_shogu_kaizen)
journals.append(journal_shogu_kaizen)
if subsidy_A != 0:
journal_subsidy_A = renderJournal_SubsidyA(2100, aDate, name, cost_center_forYayoi, subsidy_A)
journals.append(journal_subsidy_A)
if subsidy_B != 0:
journal_subsidy_B = renderJournal_SubsidyB(2100, aDate, name, cost_center_forYayoi, subsidy_B)
journals.append(journal_subsidy_B)
journal_netAP = renderJournalNetAP(2101, aDate, name, cost_center_forYayoi, netPayable)
journals.append(journal_netAP)
print(str(columnCount) + “行目 = ” + name)
cnt = 1
for journal in journals:
#print(journal)
#print(journal.Date)
renderJournalCSV(journal, sheet_result_salaries, cnt)
cnt += 1
datetime_now = datetime.datetime.now()
yyyy = str(datetime_now.year)
mm = str(datetime_now.month)
dd = str(datetime_now.day)
hour = str(datetime_now.hour)
minutes = str(datetime_now.minute)
second = str(datetime_now.second)
str_datetime_now = str(datetime_now)
wb.save(“C:/Users/Ichiro Kawai/desktop/summary_result_” + yyyy + mm + dd + hour + minutes + second + “.xlsx”)
print(“work completed_” + str(cnt) + ” lines” )