本帖最后由 平Fan_d世界 于 2023-11-3 19:48 编辑
用了beancount来记账后，发现每月的账单手动记是一件极其麻烦的事情。
然后再github搜索一通后，有double-entry-generator（https://github.com/deb-sig/double-entry-generator）能转换支付宝/微信的账单，但是没有自己用的建行和招商。
所有自己写了一个py：
- 建行能导出的账单是excel格式的直接用；
- 招商导出的是pdf，幸好还能转成txt，然后自己整理成csv
- 后续可能会加上深农商和农行，也可能不会，这两张卡用的不多。
代码如下：
[Python] 纯文本查看 复制代码
import os
import re
import pandas as pd
import numpy as np
dir = os.path.dirname(os.path.abspath(__file__))
#input_file = "xxxxxx.csv"
input_file = input("输入账单路径：")
bank = input("输入账单归属银行[CCB、CMB、SRCB、ABC]：") #暂时只有CCB和CMB
output_file = dir + "\\" + bank + ".bean"
config_file = dir + "\\config\\" + bank + ".conf" #当前config文件夹下的CCB.conf和CMB.conf。
#CCB账单excel的整理
def CCB_data(_file_):
data = pd.read_excel(_file_)
data.columns = ["No","text0","CCY","Yuan","time","cost","bal","text1","text2"]
data = data.iloc[2:]
#data["time"] = data["time"].str[0:4] + "-" + data["time"].str[4:6] + "-" + data["time"].str[6:8]
data["time"] = pd.to_datetime(data["time"]).astype(str)
data.loc[:,"account"] = data["text0"] + ":" + data["text2"]
data["CCY"] = data["CCY"].replace("人民币元","CNY")
data = data[["time","cost","CCY","account","text0","text1","text2"]]
#pd.DataFrame(data).to_csv(output_file+".csv",encoding='utf-8-sig') #可以导出csv
return data
#CMB账单csv的整理
def CMB_data(_file_):
data = pd.read_csv(_file_)
data.columns = ["time","CCY","cost","bal","text0","text2"]
data["time"] = pd.to_datetime(data["time"]).astype(str)
data["cost"] = data["cost"].astype(str)
data.loc[:,"text1"] = None
data.loc[:,"account"] = data["text0"] + ":" + data["text2"]
data = data[["time","cost","CCY","account","text0","text1","text2"]]
#pd.DataFrame(data).to_csv(output_file+".csv",encoding='utf-8-sig') #可以导出csv
return data
#关键字查找替换
def replace(_data_,_config_):
conf = pd.read_csv(_config_)
for conf in conf.itertuples():
_data_ = _data_.str.replace(".*"+conf[1]+".*",conf[2],regex = True)
return _data_
#输出beancount格式的字段
def to_bean(_data_):
global bank
bean = _data_["time"] + ' * \"' +_data_["text0"] + '\"\n\t'
if _data_["text1"].notnull().any():
bean = bean + 'notes: \"' + _data_["text1"] + '\"\n\t'
bean = bean + 'id: \"' + _data_["text2"] + '\"\n\t'
bean = bean + "Assets:Bank:" + bank + " " + _data_["cost"] + " " + _data_["CCY"] + "\n\t"
bean = bean + _data_["account"] + "\n"
return bean
#整理账单格式
if bank == "CCB":
DATA = CCB_data(input_file)
if bank == "CMB":
DATA = CMB_data(input_file)
#按conf文件查找替换关键字
DATA["account"]= replace(DATA["account"],config_file)
#print(DATA)
#整理为beancount格式的字段
bean = to_bean(DATA).to_frame()
#print(bean)
#输出文件CCB.bean或者CMB.beantext = ""
for bean in bean.itertuples():
text = text + str(bean[1]) + "\n"
file = open(output_file,'w')
file.write(text)
file.close()
config文件，格式：【关键字,替换后】，例如：
[Python] 纯文本查看 复制代码 Text,Replace_text
饿了么,Expenses:Food:Others
效果如下：
| 
发表于 2023-11-3 18:59
|
发表于 2023-11-3 19:33
