Python automation teaches you how to handle bills

The first public account of this article: CoXie talks about office automation,
Committed to creating an official account for other people's homes

Original address: [ Please click here to hyperlink ]

Preface

Many people don't know what to do or don't know where their monthly money goes. Therefore, this time I will bring you how to use Python handle your own WeChat bill (Alipay bill) , the dry goods are full.
Here is an example of 200 pieces of data in April, the data is for reference only

How to export my WeChat bill?

Baidu's tutorial is no longer applicable, please see below for specific tutorials

• 1. Find WeChat Pay
• 2. Click on my bill
• 3 Billing details
• 4. Frequently asked questions in the upper right corner
• 5. Download bill at the top
• 6. Click for personal reconciliation

• 7. Enter your email address

  The bill will be automatically sent to the mailbox later

How do I proceed to the next step after I get the bill?

First of all, no matter what code you write, you must know what the third-party library . The following third-party libraries will be used this time, please import them in advance.

• pandas
• openpyxl
• time

CMD import method:

• pip install pandas
• pip install openpyxl
• pip install time

Pycharm import method:

Process xlsx files

First of all, we must read the file first, here we use load_workbook (file name +.xlsx) to read the target Excel

Next, get the current working table, don’t care about the name of the table here, because use

wb = load_workbook(file_name + '.xlsx')
ws = wb.active

Has been automatically obtained.

删除无关数据
ws.delete_rows(1,16) #删除第 1 行起的 16 行(含起始)
ws.delete_cols(9,2) #删除第 9 列起的 2 列(含起始)

In order to demonstrate the code effect obviously, I have saved the data processed above as a xlsx file.
The file name is: file_name + current time +.xlsx
The rest is to process the data you want to obtain. Of course, the processed data must be processed xlsx file processed above.

Secondary processing xlsx

data1=test[-test['交易类型'].str.contains('微信红包')] #然后再删除列里有微信红包的数据
data1=data1[-data1['交易类型'].str.contains('转入零钱通')] #然后再删除列里转入零钱通里面的

Finally, save a copy of the data processed twice, the save format is still file_name + current time +.xlsx

Afterword

This sharing is over here, is not easy to create, please do not copy, please inform the blogger or indicate the source of reprint
Finally, the complete code is here

import pandas as pd
import openpyxl
from pandas import Series,DataFrame
from openpyxl import *
import time, datetime

file_name = '文件名'
now = time.strftime("%Y%m%d%H%M%S", time.localtime())
wb = load_workbook(file_name + '.xlsx')
ws = wb.active
ws.delete_rows(1,16) #删除第 1 行起的 16 行(含起始)
ws.delete_cols(9,2) #删除第 9 列起的 2 列(含起始)
wb.save(file_name + ' - ' + now + ' - 1 - 已删除无效行和列.xlsx')

test = pd.read_excel(file_name + ' - ' + now + ' - 1 - 已删除无效行和列.xlsx')
data1=test[-test['交易类型'].str.contains('微信红包')] #然后再删除列里有微信红包的数据
data1=data1[-data1['交易类型'].str.contains('转入零钱通')] #然后再删除列里转入零钱通里面的
data1.to_excel(file_name + ' - ' + now + ' - 2 - 红包和零钱通.xlsx', index=False) #将处理后的结果写入新表

print("亲，已全部删除无效数据")

Easter eggs

, there are small partners asking: Is there any good 160c0ad51f2225 completion project ? I believe this is what many friends want. Don’t like it yet?