🎯如何制作个人财务报表

type
status
date
slug
summary
tags
category
icon
password
📈
在日常生活中,我们往往会使用各种平台进行财务活动,比如使用支付宝转账,使用微信小程序买奶茶,发个红包,在线支付水电费。这里我们的收入和消费散落在各个地方。我希望将他们统一收集、汇总,做个人的财务分析报表。
除了AWS的部分,以下操作均在我的ubuntu虚拟机内进行

1. 技术栈

  1. 数据开发:Python、DBT
  1. 数据探查:AWS Athena
  1. 数据存储:AWS S3
  1. 元数据管理:AWS Glue
  1. BI报表:Apache-Superset

2. 数据收集

微信

步骤:微信钱包 → 账单 → 常见问题 → 下载账单 → 用于个人对账 → 选择类型和时间 → 填入邮箱 → 在邮箱下载(需要看下微信提供的密码进行解压)

支付宝

步骤:我的 → 账单 → 开具交易流水证明 → 用于个人对账 → 申请 → 选择类型和时间 → 填入邮箱 → 在邮箱下载(需要看下支付宝提供的密码进行解压)

3. 数据清洗

  • 无论是支付宝还是微信,得到的数据都是如下这种形式(以微信为例)
notion image
 
  • 我只需要下半部分的csv格式的数据,上半部分得去掉(注意:微信的编码格式是utf-8,而支付宝的是GBK)
 
  • Athena是不支持中文字段的,这里我需要将表头替换为英文
 
  • Parquet 格式是一种专为大数据处理和分析设计的列式存储格式,我很喜欢使用。我将csv转换为pandas dataframe,再转换为Parquet后,上传到s3数据桶。当然,你需要注册AWS并配置相关权限
 
  • 同样的,对于支付宝的账单数据,重复上述操作

4. 建表

这一步创建两张表,一张支付宝的账单明细数据,一张微信的账单明细数据
  • 进入AWS Glue的catalog,点击Add database,创建一个数据库financial_management
 
notion image
 
  • 进入athena编辑器,选择financial_management数据库,点击S3存储桶数据,用于读取S3的数据建表
notion image
 
  • 为表起一个名字,选择数据的S3 URI路径,数据格式选择parquet
notion image
 
  • 添加列名:将第3步中的表头填进这里
notion image
 
  • 点击创建表,表就创建出来了。微信、支付宝都需要这样操作
 

5. 数据汇总

我需要将微信和支付宝的账单明细合并为一张表,方便后期分析
下面是汇总的逻辑,这是DBT的sql文件,是一种jinja sql,可以动态的根据设置的jinja模板渲染出最终的sql

6. 主键唯一性和非空检验

最终的汇总表,它的唯一键是transaction_order_number ,需要有一个测试逻辑,保证它的唯一和非空,可以在dbt项目中设置yml文件进行定义
 
运行dbt build命令执行汇总和检验的代码
notion image

7. Superset

7.1 pip安装

  • 设置环境变量
 

7.2 启动并连接athena

  • 启动superset,成功后vs-code会提示你打开浏览器
  • 登录后,点击settings → Database Connection → +Database
  • 选择Amazon Athena
  • 编辑SQLAlchemy URI
notion image
  • 测试连接

7.3 制作图表和仪表板

创建数据集

  • 进入sql lab,编辑sql,查询出汇总表的数据
notion image
 
  • 点击SAVE内的save as dataset,保存查询结果为数据集,命名为“历史流水记录”
  • 点击Datasets,选择刚才创建出来的数据集,点击后进入编辑chart的页面
notion image
 

图表案例

现在我想制作这样一张报表:使用柱状图展示支付宝平台每月各个消费类型的开支
  • 在metrics内编辑sql,这里计算的是充值缴费的支出
其他消费类型同理,都需要在metrics里配置
notion image
 
  • dimension选择月,这里要对交易的时间戳进行格式转换,变为月,在Sort by一栏中也需要配置上这个sql,这样可以使得月份有序排列
notion image
  • 在自定义一栏中,勾选stacked bars,这样不同的消费类型支出数据会在同一个bar里分割显示
notion image
  • 最终效果,可见2024-01和2024-05的开支巨大,原因是交房租。。 2024-09的文化休闲占比很大,原因是买了几款3A游戏,都是好几百块
notion image
  • 其他图表的制作方法参见这里
  • 将所有制作好的chart,放在一个DashBoard仪表板里
notion image
 
notion image
notion image
notion image

8. 下一步

  1. 现在手动运行代码的次数太多,效率较低。计划将代码容器化部署在AWS上,并配置Lambda实现自动化处理流程
  1. 汇总银行的收入数据
  1. 学习和制作更丰富的图表
vs-code如何连接谷歌虚拟机黑客与画家
Loading...
Catalog