利用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.475.17  
 其中:股票145,952,447.475.17  
2固定收益投资--  
 其中:债券--  
 资产支持证券--  
3贵金属投资--  
4金融衍生品投资--  
5买入返售金融资产--  
 其中:买断式回购的买入返售金融资产--  
6银行存款和结算备付金合计1,873,672,031.3866.43  
7其他资产800,725,623.5728.39  
8合计2,820,350,102.42100  
(二)报告期末按行业分类的股票投资组合     
(1)报告期末按行业分类的境内股票投资组合     
代码行业类别公允价值(元)占基金资产净值比例(%)  
A农、林、牧、渔业--  
B采矿业--  
  
C制造业108,721,385.523.86  
D电力、热力、燃气及水生产和供应业--  
E建筑业--  
F批发和零售业--  
G交通运输、仓储和邮政业--  
H住宿和餐饮业--  
I信息传输、软件和信息技术服务业--  
J金融业--  
K房地产业--  
L租赁和商务服务业--  
M科学研究和技术服务业25,902,766.010.92  
N水利、环境和公共设施管理业--  
O居民服务、修理和其他服务业--  
P教育--  
Q卫生和社会工作11,328,295.940.4  
R文化、体育和娱乐业--  
S综合--  
 合计145,952,447.475.18  
      
(三) 报告期末按公允价值占基金资产净值比例大小排序的前十名股票投资明细    
序号股票代码股票名称数量(股)公允价值(元)占基金资产净值比例(%)
1603259药明康德131,05320,204,441.010.72
2300760迈瑞医疗42,20018,760,010.000.67
3600276恒瑞医药161,87317,153,681.810.61
4661长春高新32,10014,740,320.000.52
5300347泰格医药59,22010,301,319.000.37
6300122智飞生物72,40010,288,040.000.36
72007华兰生物123,6236,149,008.020.22
8300601康泰生物38,6676,050,225.490.21
92821凯莱英16,5005,212,350.000.18
10600196复星医药72,4263,622,748.520.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
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页