キャッシュフロー表を作ってみる
資金移動の可視化を行いたい
PDFからターゲットのデータを引き抜くことができるようになりましたので、全体把握のために月次の資金移動表を作ってみました。
キャッシュフロー表の説明
財務諸表からデータを転記する。一部のデータは手入力する。
JDLの会計プログラムで生成した月毎の試算表をPDF化し、PDFファイルからデータを読み込みます。1ヶ月単位でのファイルになります。指定のフォルダに入れた複数のファイルが対象です。一つのファイルでも構いません。年月日を読み込んで指定のセルにデータを書き込む方式です。
エクセルのテンプレートに月次の試算表からデータを転記する場合、データを千円単位で切り捨て、指定のセルに書き込みます。《背面が白の部分》
財務諸表のデータからキャッシュフロー表は生成されるようにしています。エクセル表で0が表示されているセルは計算式が入っています。
財務諸表の《背面がピンク》のセル：営業外収益は日常的にデータが存在しないことと、データの位置が可変に動くので面倒なこともあって手入力にしました。
キャッシュフロー表の《背面がピンク》のセル：固定費と変動費の推測をしたいため、給与、家賃など固定費項目は手入力とし、変動費は一般管理費から差し引いて計算しています。
キャッシュフロー表の月末残高（計算）は現預金の想定残高です。
未来にに関しては、売上（予想）のセルに予測数値を入れ、想定粗利で計算しようと考えています。現状まだできていません。
期が変わる時は、別のシートに足して行くつもりです。
A4両面印刷を行うために行のグループ化をしています。
感想
【複式簿記】の偉大さに改めて感心しました。今まで手抜きをしていたことを実感しています。
キャッシュフローの増減の可視化が簡単に理解可能で、社員の複式簿記に関する理解が進むのかもと感じました。
月次の試算表からエクセルのキャッシュフロー表に転記するプログラム
電子化でPDFが飛び交う時代になりました。「生成AIを利用すれば非定型データを扱えるようになる」と言われています。
試算表などのPDFは「半定型データ」だと考えました。データを二次元リスト化してターゲットデータを取得することは可能です。以前伝票からデータを引き抜くために、座標からデータを取得する方法も試してみましたが、とても面倒で利用できる方式では無いとの結論に至りました。
銀行からメールで届く「入出金」のPDFも同じ方式で利用していますが、現状では問題は発生していません。色々の場面で使えそうです。
import fitz
# pip install PyMuPDF で fitz が使えます。
import os
import re
# PDF からTEXTを抽出
def extract_text_from_password_protected_pdf(pdf_path, password):
extracted_text = ""
# PDFファイルを開く
pdf_document = fitz.open(pdf_path)
# パスワードが必要な場合
if password != "":
pdf_document.authenticate(password)
# 全ページのテキストを抽出
for page_num in range(pdf_document.page_count):
page = pdf_document.load_page(page_num)
page_text = page.get_text()
extracted_text += page.get_text()
# PDFを閉じる
pdf_document.close()
return extracted_text
# 文字列の中から年、月を取得し月が一文字の場合には先頭に0を足す。
def format_date(date_str):
# 正規表現で「年」「月」をキャプチャ
pattern = r'(\d+年)(\d+)月'
match = re.search(pattern, date_str)
if match:
year_part = match.group(1)
month = int(match.group(2)) # 月を整数に変換
# 月を2桁の文字列にフォーマット 月別にソートするため
formatted_month = f'{month:02d}月'
# フォーマットされた日付を返す
return f'{year_part}{formatted_month}'
return date_str
#pdf_1D_list = []
pdf_1D_txt = ''
pdf_txt_list = [] # 取得の生データを見るため
folder_path = '試算表PDF' # ここのフォルダーに月次の試算表を入れます
# フォルダ内のすべてのファイルを取得します
# 重複するデータがあっても良いように行う。
file_list = os.listdir(folder_path)
# import os の機能を利用して file_listを作ります。
#print(file_list)
file_list = [filename for filename in file_list if not (filename.startswith('.') or '~$' in filename)]
file_list = [filename for filename in file_list if (filename.endswith('.xlsx') or filename.endswith('.csv') or filename.endswith('.pdf'))]
# 隠しファイルなどを取り除く
file_list.sort(key=lambda x: x[0])
#
sorted_list = sorted(file_list, key=lambda x: (x.split('.')[0][-18:] if x.endswith('.pdf') else x))
#print(sorted_list)
siwake_list = []
kisyu_list = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
for file_name in sorted_list:
print('処理ファイル ',file_name)
pdf_path = './' + folder_path + '/' + file_name
password = '' # ここのパスワードを入れます
pdf_1D_txt = ''
#pdf_1D_list = []
# print(pdf_path)
# PDF から文字を取得する関数の呼び出し
extracted_text = extract_text_from_password_protected_pdf(pdf_path, password)
# データの正規化
extracted_text = extracted_text.replace(',','')
extracted_text = extracted_text.replace(' ','')
extracted_text = extracted_text.replace('\u3000','')
# ファイルが複数あった場合の対処策 全てのデータを extracted_text にすることに対応
pdf_1D_txt += extracted_text
# 文字列を\nで分割し、空白文字を削除して要素が一つの一次元リストを作成
pdf_1D_list = [line for line in pdf_1D_txt.split('\n') if line.strip()]
pdf_2D_list = []
#
pdf_2D_list_copy = []
# 対象データを、マッチすることが簡単にできることを目的に二次元リスト化をしています。
# # 一次元リストを要素ごとに分割して二次元リストに展開
ii = ""
for i in pdf_1D_list: # 要素ごとに切り出す。
# 数字か文字かを判断
if i.isdigit(): # 数字の場合
one_line = [None]
one_line[0] = str(ii)
pdf_2D_list.append(one_line)
one_line = [None]
one_line[0] = str(i) # 数字を入れる
pdf_2D_list.append(one_line)
ii = ""
continue
# 文字が一文字ずつ分割されるので、文字の場合は連結する。
else: # 文字の場合
#ii = ""
if '.' in i:
i = ''
ii += str(i)
# 関連する対象データを取得するため、同じリスト「pdf_2D_list_copy」を作りindexを基準に同じ二次元リストの値を取得
# 例えば'振込' が出てきたら関連のデータをコピーしたListのpdf_2D_list_copy から値を取得する。
pdf_2D_list_copy = pdf_2D_list
# pdf_2D_list を表示して対象となるデータは何番目に有るかを判断ため
# ここが肝になります。
for row in pdf_2D_list_copy:
print(row)
######################
print('+++++++++++')
bs_pl_list = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
#########################################################
# 期首 10月の場合に期首残高を入れるデータを作成する
#########################################################
kisyu_mm = '10'
text = str(pdf_2D_list[0]) # 先頭の要素を取得
# ['合計残高試算表（貸借対照表）1頁税抜
# 株式会社＊＊＊＊＊＊令和5年10月31日現在勘定科目期首残高借方貸方当月残高構成比現金']
print('+++', text)
# 正規表現パターン
#pattern = r'(\d+年\d+月\d+日)' # 日まで取得する場合
pattern = r'(\d+年\d+月)'
match = re.search(pattern, text)
date = match.group(1)
date_pl = format_date(date)
print(f"抽出された日付: {date_pl}")
# 先頭に集計知る年月を入れる
bs_pl_list[0] = date_pl
date_mm = date_pl[-3:-1] # 後ろから月の数字を取得
print('月 ', date_mm)
mm_first_zaiko = 0 # 月初在庫の初期化
for index, row in enumerate(pdf_2D_list): # index を取得するため
# [:-3] は 千円単位にするため
if row[0] == '〔現金預金〕':
bs_pl_list[1] = int(pdf_2D_list_copy[index + 7][0][:-3]) # 現預金 [７つ下の要素がターゲット]
#print(date_mm, kisyu_mm)
if date_mm == kisyu_mm: # 期首残 10月の場合だけ取得
kisyu_list[1] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 現預金 期首
if row[0] == '資動売掛金':
bs_pl_list[2] = int(pdf_2D_list_copy[index + 7][0][:-3]) # 売掛金残高
bs_pl_list[3] = int(pdf_2D_list_copy[index + 5][0][:-3]) # 当月入金
if date_mm == kisyu_mm:
kisyu_list[2] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 売掛金 期首
# 前渡金の場合 ターゲットデータが変動するため 次の項目'短期貸付金'を見て判断する
if row[0] == '前渡金':
if date_mm == kisyu_mm: #
kisyu_list[4] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 前渡金 期首
kamoku_n_4 = pdf_2D_list_copy[index + 4][0]
kamoku_n_6 = pdf_2D_list_copy[index + 6][0]
kamoku_n_8 = pdf_2D_list_copy[index + 8][0]
print('+ + * ','-4-',kamoku_n_4,'-6-',kamoku_n_6,'-8-',kamoku_n_8)
if '短期貸付金' == kamoku_n_6:
bs_pl_list[4] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 前渡金
continue
if '短期貸付金' == kamoku_n_8:
maeuke_pus = int(pdf_2D_list_copy[index + 3][0][:-3]) - int(pdf_2D_list_copy[index + 5][0][:-3])
if maeuke_pus >= 0:
bs_pl_list[4] = maeuke_pus
continue
if '短期貸付金' == kamoku_n_4:
bs_pl_list[4] = 0
continue
if row[0] == '買掛金':
bs_pl_list[5] = int(pdf_2D_list_copy[index + 7][0][:-3]) # 買掛金残高
bs_pl_list[6] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 当月支払
if date_mm == kisyu_mm:
kisyu_list[5] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 買掛金 期首
if row[0] == '負流短期借入金':
bs_pl_list[9] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 借入金
if date_mm == kisyu_mm:
kisyu_list[9] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 負流短期 期首
# 文字を連結して取得するため月により異なる場合があったため in を使用しました。
# 正しくデータを取得できました。 すべてのif文は in を使用した方が良いかもしれません。
if '未払費用' in str(row[0]): # == で判断できなかったため in としました。
mibarai_1 = int(pdf_2D_list_copy[index + 1][0][:-3]) # 月初残
mibarai_30 = int(pdf_2D_list_copy[index + 7][0][:-3]) # 月末残
bs_pl_list[7] = mibarai_30 - mibarai_1 # 増加額 減額
print('未払費用', bs_pl_list[10])
if date_mm == kisyu_mm:
kisyu_list[7] = mibarai_1
if '預り金' in str(row[0]):
azukarikin_1 = int(pdf_2D_list_copy[index + 1][0][:-3]) # 初残
azukarikin_30 = int(pdf_2D_list_copy[index + 7][0][:-3]) # 初残
bs_pl_list[8] = azukarikin_30 - azukarikin_1 # 預り金
print('預り金', bs_pl_list[11])
if date_mm == kisyu_mm:
kisyu_list[8] = azukarikin_1
if row[0] == '営（純売上高）':
bs_pl_list[11] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 売上高
if '期末棚卸高' in str(row[0]):
mm_end_zaiko = int(pdf_2D_list_copy[index + 3][0][:-3])
mm_first_zaiko = int(pdf_2D_list_copy[index + 1][0][:-3])
bs_pl_list[14] = mm_end_zaiko # 棚卸高
if date_mm == kisyu_mm:
kisyu_list[14] = int(pdf_2D_list_copy[index + 1][0][:-3]) # 期末棚卸高 期首
if '（売上原価）' in str(row[0]):
mm_genka = int(pdf_2D_list_copy[index + 3][0][:-3])
bs_pl_list[15] = mm_genka # 売上原価
# 当月仕入金額は試算表からは取れないので、計算しました。
if row[0] == '〔売上総損益金額〕':
bs_pl_list[16] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 売上総損益金額
if row[0] == '〔販売費及び一般管理費〕':
bs_pl_list[17] = int(pdf_2D_list_copy[index + 3][0][:-3]) # 一般管理費
if row[0] == '雑収入':
if pdf_2D_list_copy[index + 2][0] != '':
bs_pl_list[19] = int(pdf_2D_list_copy[index + 2][0][:-3])
#continue
# 当月仕入金額は試算表からは取れないので、計算しました。
bs_pl_list[13] = bs_pl_list[15] + bs_pl_list[14] - mm_first_zaiko # 当月仕入
print(mm_first_zaiko, bs_pl_list[14], bs_pl_list[16], '月仕入', bs_pl_list[15])
siwake_list.append(bs_pl_list)
siwake_list.sort(key=lambda x: x[0])
for row in siwake_list:
print(row)
print('期首 ', kisyu_list)
#########################################################
# ここから エクセルに書き込み
#########################################################
import openpyxl
folder_path = '集計キャッシュフロー' # このフォルダーにエクセルのキャッシュフロ表を入れます
file_list = os.listdir(folder_path)
# import os の機能を利用して file_listを作ります。
#print(file_list)
file_list = [filename for filename in file_list if not (filename.startswith('.') or '~$' in filename)]
file_list = [filename for filename in file_list if filename.endswith('.xlsx')]
# 隠しファイルなどを取り除く
filename = file_list[0]
file_path = './' + folder_path + '/' + filename
print(file_path)
# 月が記載される列を取得するため辞書にする。
mm_column = {"10": 6, "11": 7, "12": 8, "01": 9, "02": 10, "03": 11, "04": 12,
"05": 13, "06": 14, "07": 15, "08": 16, "09": 17}
#mm = "06"
#mm_col = mm_column[mm]
#print(mm_col)
# 選択された項目だけをエクセルに展開
workbook = openpyxl.load_workbook(file_path)
sheet_name = '９期'
worksheet = workbook[sheet_name]
# 期首残高を列 ５に記入
worksheet.cell(3, 5).value = int(kisyu_list[1]) # 現預金
worksheet.cell(4, 5).value = int(kisyu_list[2]) # 売掛金（残高）
worksheet.cell(6, 5).value = int(kisyu_list[4]) # 前渡金（仕入）
worksheet.cell(7, 5).value = int(kisyu_list[5]) # 買掛金（残高）
worksheet.cell(9, 5).value = int(kisyu_list[7]) # 未払費用（残）
worksheet.cell(10, 5).value = int(kisyu_list[8]) # 預り金（残）
worksheet.cell(11, 5).value = int(kisyu_list[9]) # 借入金
worksheet.cell(16, 5).value = int(kisyu_list[14]) # 月末在庫
# 月次残高を指定のセルに記入
for row in siwake_list:
yy_mm = str(row[0])
mm = yy_mm[-3:-1] # 月を取得
mm_col = mm_column[mm] # 辞書から月に対応した列を取得します
#print(yy_mm, mm, mm_col)
worksheet.cell(2, column=mm_col).value = 'R' + str(row[0]) # 月 令和の R を加える
worksheet.cell(3, column=mm_col).value = int(row[1]) # 現預金
worksheet.cell(4, column=mm_col).value = int(row[2]) # 売掛金（残高）
worksheet.cell(5, column=mm_col).value = int(row[3]) # 回収：売掛金
worksheet.cell(6, column=mm_col).value = int(row[4]) # 前渡金（仕入）
worksheet.cell(7, column=mm_col).value = int(row[5]) # 買掛金（残高）
worksheet.cell(8, column=mm_col).value = int(row[6]) # 支払：買掛金
worksheet.cell(9, column=mm_col).value = int(row[7]) # 未払費用
worksheet.cell(10, column=mm_col).value = int(row[8]) # 預り金
worksheet.cell(11, column=mm_col).value = int(row[9]) # 借入金
worksheet.cell(13, column=mm_col).value = int(row[11]) # 売上
worksheet.cell(15, column=mm_col).value = int(row[13]) # 当月仕入
worksheet.cell(16, column=mm_col).value = int(row[14]) # 月末在庫
worksheet.cell(17, column=mm_col).value = int(row[15]) # 売上原価
#worksheet.cell(18, column=mm_col).value = int(row[16]) # 営業利益 計算式を入れ確認
worksheet.cell(19, column=mm_col).value = int(row[17]) # 一般管理費計
workbook.save(file_path)
workbook.close()肝はPDFから取得したデータの整形です。当初抽出した文字データを見た時どうしようかと混迷しました。「売 掛 金」は一文字ずつ表示されます。PDFは見栄えを考えスペースが多用されることが原因かと思います。データの正規化を如何に上手くするかが重要です。ここで使った方法は、一つの方式だと理解してください。
# データの正規化
extracted_text = extracted_text.replace(',','')で数値のカンマを削除しておいて
ii = ""
for i in pdf_1D_list: # 要素ごとに切り出す。
# 数字か文字かを判断
if i.isdigit(): # 数字の場合
one_line = [None]
one_line[0] = str(ii)
pdf_2D_list.append(one_line)
one_line = [None]
one_line[0] = str(i) # 数字を入れる
pdf_2D_list.append(one_line)
ii = ""
continue
# 文字が一文字ずつ分割されるので、文字の場合は連結する。
else: # 文字の場合
#ii = ""
if '.' in i:
i = ''
ii += str(i)で文字列と数値列を分離します。
数値が出てきたら、連結した文字列を二次元リストに加え、その後数値を二次元リストに加えています。
if文の条件分離はターゲット文字があるか無いか in で判断した方が良いかもしれません。動いているのでエラーが起こった場合だけ in に直しました。
この記事が気に入ったらサポートをしてみませんか？