利用PYTHON复核excel表格数据的勾稽关系

我们在业务中经常填写各种excel表格，但是表格的行数并不是固定的，利用excel自身的VBA检查也不是特别方便。

编写Python脚本来检查Excel数据之间的勾稽关系就快捷迅速很多。比如：上市公告书由业务人员手工编写，并公告全市场，

检查完数据之间的勾稽关系再公告，可以有效避免对外公告出去的数据出错。

七、基金财务状况
（一）基金募集期间费用
本基金募集期间的信息披露费、会计师费、律师费以及其他费用，不从基金财产中列支。
（二）基金上市前重要财务事项
本基金发售后至本报告公告前无重要财务事项发生。
（三）基金资产负债表
资产	本报告期末（2021年？月？？日；单位：人民币元）
资产：
银行存款	373,672,031.38
结算备付金	1,500,000,000.00
存出保证金
交易性金融资产	145,952,447.47
其中:股票投资	145,952,447.47
基金投资
债券投资
资产支持证券投资
贵金属投资
衍生金融资产
买入返售金融资产
应收证券清算款	800,048,122.96
应收利息	510,259.28
应收股利
应收申购款
递延所得税资产
其他资产	167,241.33
资产总计	2,820,350,102.42
负债和所有者权益
负债：
短期借款
交易性金融负债
衍生金融负债
卖出回购金融资产款
应付证券清算款
应付赎回款
应付管理人报酬	69,393.43
应付托管费	23,131.14
应付销售服务费
应付交易费用	14,033.28
应交税费
应付利息
应付利润
递延所得税负债
其他负债	4,176.13
负债合计	110,733.98
所有者权益：
实收基金	2,814,152,837.00
未分配利润	6,086,531.44
所有者权益合计	2,820,239,368.44
负债和所有者权益总计	2,820,350,102.42
八、基金投资组合
截至2021年？月？？日，本基金的投资组合情况如下：
（一） 报告期末基金资产组合情况
序号	项目	金额（元）	占基金总资产的比例（%）
1	权益投资	145,952,447.47	5.17
	其中：股票	145,952,447.47	5.17
2	固定收益投资	-	-
	其中：债券	-	-
	资产支持证券	-	-
3	贵金属投资	-	-
4	金融衍生品投资	-	-
5	买入返售金融资产	-	-
	其中：买断式回购的买入返售金融资产	-	-
6	银行存款和结算备付金合计	1,873,672,031.38	66.43
7	其他资产	800,725,623.57	28.39
8	合计	2,820,350,102.42	100
（二）报告期末按行业分类的股票投资组合
（1）报告期末按行业分类的境内股票投资组合
代码	行业类别	公允价值（元）	占基金资产净值比例（％）
A	农、林、牧、渔业	-	-
B	采矿业	-	-
C	制造业	108,721,385.52	3.86
D	电力、热力、燃气及水生产和供应业	-	-
E	建筑业	-	-
F	批发和零售业	-	-
G	交通运输、仓储和邮政业	-	-
H	住宿和餐饮业	-	-
I	信息传输、软件和信息技术服务业	-	-
J	金融业	-	-
K	房地产业	-	-
L	租赁和商务服务业	-	-
M	科学研究和技术服务业	25,902,766.01	0.92
N	水利、环境和公共设施管理业	-	-
O	居民服务、修理和其他服务业	-	-
P	教育	-	-
Q	卫生和社会工作	11,328,295.94	0.4
R	文化、体育和娱乐业	-	-
S	综合	-	-
	合计	145,952,447.47	5.18
（三） 报告期末按公允价值占基金资产净值比例大小排序的前十名股票投资明细
序号	股票代码	股票名称	数量（股）	公允价值（元）	占基金资产净值比例（％）
1	603259	药明康德	131,053	20,204,441.01	0.72
2	300760	迈瑞医疗	42,200	18,760,010.00	0.67
3	600276	恒瑞医药	161,873	17,153,681.81	0.61
4	661	长春高新	32,100	14,740,320.00	0.52
5	300347	泰格医药	59,220	10,301,319.00	0.37
6	300122	智飞生物	72,400	10,288,040.00	0.36
7	2007	华兰生物	123,623	6,149,008.02	0.22
8	300601	康泰生物	38,667	6,050,225.49	0.21
9	2821	凯莱英	16,500	5,212,350.00	0.18
10	600196	复星医药	72,426	3,622,748.52	0.13
（四） 报告期末按债券品种分类的债券投资组合
本基金于？？年？？月？？日未持有债券。
（五）报告期末按公允价值占基金资产净值比例大小排序的前五名债券投资明细
本基金于？？年？？月？？日未持有债券。
（六）报告期末按公允价值占基金资产净值比例大小排序的前十名资产支持证券投资明细
本基金于？？年？？月？？日未持有资产支持证券。
（七）报告期末按公允价值占基金资产净值比例大小排序的前五名贵金属投资明细
本基金于？？年？？月？？日未持有贵金属。
（八）报告期末按公允价值占基金资产净值比例大小排序的前五名权证投资明细
本基金于？？年？？月？？日未持有权证。
（九）报告期末本基金投资的股指期货交易情况说明
本基金于？？年？？月？？日未投资股指期货。
（十）报告期末本基金投资的国债期货交易情况说明
本基金于？？年？？月？？日未投资国债期货。
（十一） 投资组合报告附注
1.本基金投资的前十名证券的发行主体本期没有出现被监管部门立案调查，或在报告编制日前一年内受到公开谴责、处罚的情形。
2.本基金投资的前十名股票没有超出基金合同规定的备选股票库。
3.其他各项资产构成
序号	名称	金额（元）
1	存出保证金	-
2	应收证券清算款	800,048,122.96
3	应收股利	-
4	应收利息	510,259.28
5	应收申购款	-
6	其他应收款	167,241.33
7	待摊费用	-
8	其他	-
9	合计	800,725,623.57
4.报告期末持有的处于转股期的可转换债券明细
本基金于？？年？？月？？日未持有处于转股期的可转换债券。
5.报告期末前十名股票中存在流通受限情况的说明
本基金于？？年？？月？？日股票投资前十名股票中不存在流通受限情况。
请基金经理确认。

 参考代码：

import os
import re
import pandas as pd
import numpy as np
​
#上市公告书地址
sfilePath = r'F:\tmp\上市公告书\？？？？.xlsx'
​
#读取上市公告书，空字段补填0值
df1=pd.read_excel(sfilePath,header=None)
df2=df1.fillna(0)
​
#找到开始结束行
i=df2[df2[0].str.replace(' ','')==r'资产：'].index
j=df2[df2[0].str.replace(' ','')==r'负债和所有者权益总计'].index
​
#初始化计算指标
isum=0.00
zchj=0.00
fzhj=0.00
ssjj=0.00
wfplr=0.00
syzqyhj=0.00
fzsyzqyhj=0.00
​
while j >= i:
    skey=str(df2.iloc[i][0].values[0])  #取表格名称
    skey=skey.replace(' ','')
    nvalue=float(df2.iloc[i][1])        #取值
    if not (('投资' in skey) | ( skey==r'资产总计') | ( skey==r'负债合计')):
        isum=isum+nvalue  
        #print(skey,nvalue,isum)
    if skey==r'资产总计':               #总计和明细项目核对
        if abs((nvalue-isum))<=0.001:
            print('【相等】资产 = 明细资产总计:',nvalue,isum)
        else:
            print('【不相等】资产 = 明细总计:',nvalue,isum)
        zchj=nvalue 
        isum=0
    if skey==r'负债合计':               #总计和明细项目核对
        if abs((nvalue-isum))<=0.001:
            print('【相等】负债 = 明细负债总计:',nvalue,isum)
        else:
            print('【不相等】负债 = 明细负债总计:',nvalue,isum)
        fzhj=nvalue 
        isum=0 
    if skey==r'实收基金':   
        ssjj=nvalue
    if skey==r'未分配利润':   
        wfplr=nvalue  
    if skey==r'所有者权益合计':   
        syzqyhj=nvalue   
    if skey==r'负债和所有者权益总计':   
        fzsyzqyhj=nvalue      
    i=i+1
​
#勾稽关系核对
if abs(zchj-fzsyzqyhj)<=0.001:
    print('【相等】资产 = 负债和所有者权益总计:',zchj,fzsyzqyhj)
else:
    print('【不相等】资产 = 负债和所有者权益总计:',zchj,fzsyzqyhj)    
​
if abs(fzhj+syzqyhj-fzsyzqyhj)<=0.001:
    print('【相等】负债和所有者权益总计 = 负债 + 所有者权益合计:',fzhj,syzqyhj,fzsyzqyhj)
else:
    print('【不相等】负债和所有者权益总计 = 负债 + 所有者权益合计:',fzhj,syzqyhj,fzsyzqyhj)   
​
if abs(ssjj+wfplr-syzqyhj)<=0.001:
    print('【相等】所有者权益合计 = 实收基金 + 未分配利润:',ssjj,wfplr,syzqyhj)
else:
    print('【不相等】所有者权益合计 = 实收基金 + 未分配利润:',ssjj,wfplr,syzqyhj)
​
#----------------------------------------------------------------------------------------   
​
gptz= df2[df2[0].str.replace(' ','')==r'其中:股票投资'][1].values[0]
gptz
df3=df1.fillna(0)
i=df3[df3[0].str.replace(' ','').str.contains(r'报告期末基金资产组合情况',na=False)].index.values[0]
j=df3[df3[0].str.replace(' ','').str.contains(r'报告期末按行业分类的股票投资组合',na=False)].index.values[0]
​
#print(i,j)
df4=df3.iloc[i:j]   #截取起始、结束行并放入一个新的dataframe
df4
​
isum=0
nvalue=0
bl=0
for idx in df4.index:
    skey=str(df4.loc[idx][1])
    skey=skey.replace(' ','')
    nvalue=df4.loc[idx][2]
    bl=df4.loc[idx][3]
    #print(skey,nvalue,bl)
    if type(nvalue)==float:
        if abs(round((nvalue/zchj),4)*100-bl)<=0.001:
            print('【相等】占基金总资产的比例:',skey,round((nvalue/zchj),4)*100,bl)
        else:
            print('【不相等】占基金总资产的比例:',skey,round((nvalue/zchj),4)*100,bl)
    #print(skey,nvalue,bl)
​
zhgptz=df4[df4[1].str.replace(' ','')==r'其中：股票'][2].values[0]
zhzchj=df4[df4[1].str.replace(' ','')==r'合计'][2].values[0]
    
if abs(zchj-zhzchj)<=0.001:
    print('【相等】资产负债表资产合计 = 投资组合资产合计:',zchj,fzsyzqyhj)
else:
    print('【不相等】资产负债表资产合计 = 投资组合资产合计:',zchj,fzsyzqyhj)    
​
if abs(zhgptz-gptz)<=0.001:
    print('【相等】资产负债表股票投资 = 投资组合股票投资:',zhgptz,gptz)
else:
    print('【不相等】资产负债表股票投资 = 投资组合股票投资:',zhgptz,gptz)   
    
#----------------------------------------------------------------------------------------   
​
df5=df1.fillna(0)
i=df5[df5[0].str.replace(' ','').str.contains(r'报告期末按行业分类的境内股票投资组合',na=False)].index.values[0]
j=df5[df5[0].str.replace(' ','').str.contains(r'报告期末按公允价值占基金资产净值比例大小排序的前十名股票投资明细',na=False)].index.values[0]
df6=df5.iloc[i:j]
for idx in df6.index:
    skey=str(df6.loc[idx][1])
    skey=skey.replace(' ','')
    nvalue=df6.loc[idx][2]
    bl=df6.loc[idx][3]
    if type(nvalue)==float:
        if abs(round(nvalue/(zchj-fzhj),4)*100-bl)<=0.001:
            print('【相等】股票行业比例的比例:',skey,round(nvalue/(zchj-fzhj),4)*100,bl)
        else:
            print('【不相等】股票行业比例的比例:',skey,round(nvalue/(zchj-fzhj),4)*100,bl)
​
hytzjj=df6[df6[1].str.replace(' ','')==r'合计'][2].values[0]
if abs(zhgptz-hytzjj)<=0.001:
    print('【相等】资产负债表股票投资 = 行业分类股票投资:',zhgptz,hytzjj)
else:
    print('【不相等】资产负债表股票投资 = 行业分类股票投资:',zhgptz,hytzjj)   
​
#----------------------------------------------------------------------------------------     
​
df5=df1.fillna(0)
i=df5[df5[0].str.replace(' ','').str.contains(r'报告期末按行业分类的境内股票投资组合',na=False)].index.values[0]
j=df5[df5[0].str.replace(' ','').str.contains(r'报告期末按公允价值占基金资产净值比例大小排序的前十名股票投资明细',na=False)].index.values[0]
df6=df5.iloc[i:j]
for idx in df6.index:
    skey=str(df6.loc[idx][1])
    skey=skey.replace(' ','')
    nvalue=df6.loc[idx][2]
    bl=df6.loc[idx][3]
    if type(nvalue)==float:
        if abs(round(nvalue/(zchj-fzhj),4)*100-bl)<=0.001:
            print('【相等】股票行业比例的比例:',skey,round(nvalue/(zchj-fzhj),4)*100,bl)
        else:
            print('【不相等】股票行业比例的比例:',skey,round(nvalue/(zchj-fzhj),4)*100,bl)
​
hytzjj=df6[df6[1].str.replace(' ','')==r'合计'][2].values[0]
if abs(zhgptz-hytzjj)<=0.001:
    print('【相等】资产负债表股票投资 = 行业分类股票投资:',zhgptz,hytzjj)
else:
    print('【不相等】资产负债表股票投资 = 行业分类股票投资:',zhgptz,hytzjj)   
​
#----------------------------------------------------------------------------------------    
​
df6=df1.fillna(0)
i=df6[df6[0].str.replace(' ','').str.contains(r'其他各项资产构成',na=False)].index.values[0]
j=df6[df6[0].str.replace(' ','').str.contains(r'报告期末持有的处于转股期的可转换债券明细',na=False)].index.values[0]
df7=df6.iloc[i:j]
isum=0.00
for idx in df7.index:
    skey=str(df7.loc[idx][1])
    skey=skey.replace(' ','')
    nvalue=df7.loc[idx][2]
    if type(nvalue)==float:
        if not (skey==r'合计'):
            isum = isum + nvalue
    if skey==r'合计':
        if abs((nvalue-isum))<=0.001:
            print('【相等】其他资产合计 = 明细合计:',nvalue,isum)
        else:
            print('【不相等】其他资产合计 = 明细合计:',nvalue,isum)
​
zhqtzc=df4[df4[1].str.replace(' ','')==r'其他资产'][2].values[0]
            
if abs((zhqtzc-isum))<=0.001:
    print('【相等】其他资产合计 = 基金投资组合其他资产:',nvalue,isum)
else:
    print('【不相等】其他资产合计 = 基金投资组合其他资产:',nvalue,isum)

结果：

【相等】资产 = 明细资产总计: 2820350102.42 2820350102.4200006
【相等】负债 = 明细负债总计: 110733.98 110733.98
【相等】资产 = 负债和所有者权益总计: 2820350102.42 2820350102.42
【相等】负债和所有者权益总计 = 负债 + 所有者权益合计: 110733.98 2820239368.44 2820350102.42
【相等】所有者权益合计 = 实收基金 + 未分配利润: 2814152837.0 6086531.44 2820239368.44
【相等】占基金总资产的比例: 权益投资 5.17 5.17
【相等】占基金总资产的比例: 其中：股票 5.17 5.17
【相等】占基金总资产的比例: 银行存款和结算备付金合计 66.43 66.43
【相等】占基金总资产的比例: 其他资产 28.389999999999997 28.39
【相等】占基金总资产的比例: 合计 100.0 100
【相等】资产负债表资产合计 = 投资组合资产合计: 2820350102.42 2820350102.42
【相等】资产负债表股票投资 = 投资组合股票投资: 145952447.47 145952447.47
【相等】股票行业比例的比例: 制造业 3.8600000000000003 3.86
【相等】股票行业比例的比例: 科学研究和技术服务业 0.9199999999999999 0.92
【相等】股票行业比例的比例: 卫生和社会工作 0.4 0.4
【相等】股票行业比例的比例: 合计 5.18 5.18
【相等】资产负债表股票投资 = 行业分类股票投资: 145952447.47 145952447.47
【相等】股票行业比例的比例: 制造业 3.8600000000000003 3.86
【相等】股票行业比例的比例: 科学研究和技术服务业 0.9199999999999999 0.92
【相等】股票行业比例的比例: 卫生和社会工作 0.4 0.4
【相等】股票行业比例的比例: 合计 5.18 5.18
【相等】资产负债表股票投资 = 行业分类股票投资: 145952447.47 145952447.47
【相等】其他资产合计 = 明细合计: 800725623.57 800725623.57
【相等】其他资产合计 = 基金投资组合其他资产: 800725623.57 800725623.57