大家好,我是刀哥。
如果你是一家集團公司的财務人員,合并報表由你來做,或者你是這家集團公司外聘的獨立審計師,需要你定期編制合并底稿、出具審計報告,而集團公司有多家分子公司,可能十幾家,甚至幾十家上百家。
在做合并報表時,需要将每家分子公司原始财務報表(或稱未審報表)數據過入到合并底稿表格中,然後再編制調整分錄,此時如果手工一家一家數據搬運,無數次Ctrl c和Ctrl v,會覺得特别無聊,也非常累人,估計連續搬運還不到5家,手就開始抽筋了。
加上如果每月都要做的話,那光就重複搬運未審數這一項任務,就足夠費時夠折磨人的,所以這次刀哥就拿自己工作中的實例,用代碼來解決這個問題。
1.
目标任務
刀哥要将2020年8月16家分子公司的财務報表(資産負債表和利潤表)數據,過入到2020年8月的合并底稿表格中。
▲ 各家原始财務報表及待填入的合并底稿表格,已作脫敏處理
2.
解決過程
①獲取每家公司原始财務報表的文件路徑并保存
如上圖,刀哥已将這16家分子公司的财務報表都放在了同一文件夾下,文件夾名稱為“8月報表”。如果還有更多的分子公司,請全部放在同一文件夾下,盡管放!使勁放!
獲取每家财務報表文件路徑的代碼如下。
1#把所有原始财務報表放在同一文件夾下,将其文件路徑提取出來并保存
2defget_file(folder_path):#獲取同一文件夾下所有财務報表各自的文件路徑
3dir_file=os.listdir(folder_path)
4print(type(dir_file))
5forpathindir_file:
6ifpath[-4:]=='xlsx'orpath[-3:]=='xls':
7whole_path=r'd:\F:學習\python\讀寫财務報表\8月報表\{}'.format(path)
8dir_xls.append(whole_path)
9returndir_xls
10
11dir_xls=[]
12folder_path=r'd:\F:學習\python\讀寫财務報表\8月報表'#此為文件夾路徑
13dir_xls=get_file(folder_path)
14print(dir_xls)
②打開已加密的合并底稿表格。
1#打開要寫入的合并底稿表格,因為是加密文檔,用密碼打開
2xlApp=win32.Dispatch('Excel.Application')
3write_filepath=r'd:\F:學習\python\讀寫财務報表\某超NB集團公司 Consolidation-2020-IFRS.xlsx'#要寫入的合并底稿表格的文件路徑
4password='Hope for a peace world'#打開密碼
5xlApp.Visible=True
6xlwb=xlApp.Workbooks.Open(write_filepath,False,True,None,Password=password)
③讀取單家财務報表數并寫入合并底稿
我用單元格内容定位進行讀取,用單元格地址定位進行寫入。
比如,讀取資産負債表時,取“項目”列中“貨币資金”對應的“期末餘額”數,取到之後寫入合并底稿對應表格的“E14”單元格中,其他各報表項目均以此類推,代碼如下。
1defread_and_write_datas(read_file_path,target_sheet):#讀取财務報表數據,并寫入合并底稿表格
2data=xlrd.open_workbook(read_file_path)
3names=data.sheet_names()
4#print(names)
5foriinnames:
6if'資産負債表'ini:
7sheetName1=i
8elif'利潤表'ini:
9sheetName2=i
10else:
11continue
12data1=pd.read_excel(read_file_path,sheet_name=sheetName1,header=3)
13assets=data1.iloc[:,:4]
14liabilities_and_equity=data1.iloc[:,4:8]
15data2=pd.read_excel(read_file_path,sheet_name=sheetName2,header=3)
16
17#---------------------------
18#資産類項目
19Cash_and_cash_equivalents=assets['期末餘額'][assets['項目']==' 貨币資金']#貨币資金
20Fair_value_financial_assets_through_PL=assets['期末餘額'][assets['項目']==' 交易性金融資産']#交易性金融資産
21Bills_receivable=assets['期末餘額'][assets['項目']==' 應收票據']#應收票據
22Trade_receviables_others=assets['期末餘額'][assets['項目']==' 應收賬款']#應收賬款
23Prepayment_to_suppliers=assets['期末餘額'][assets['項目']==' 預付款項']#預付款項
24Other_receivables=assets['期末餘額'][assets['項目']==' 其他應收款']#其他應收款
25Inventory=assets['期末餘額'][assets['項目']==' 存貨']#存貨
26Deferred_expenses=assets['期末餘額'][assets['項目']==' 長期待攤費用']#待攤費用
27Property_and_equipment=assets['期末餘額'][assets['項目']==' 固定資産淨額']#固定資産
28Construction_in_progress=assets['期末餘額'][assets['項目']==' 在建工程']#在建工程
29Intangible_assets=assets['期末餘額'][assets['項目']==' 無形資産']#無形資産
30
31#---------------------------
32#負債類和所有者權益類項目,都要加負号
33Bank_loan=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 短期借款']#短期借款
34Notes_payable=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 應付票據']#應付票據
35Accounts_payable=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 應付賬款']#應付賬款
36Advance_from_customers=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 預收款項']#預收款項
37Salaries_and_welfare=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 應付職工薪酬']#應付職工薪酬
38Income_tax=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 應交稅費']#應交稅費
39Interest_payable_current=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 應付利息']#應付利息
40Other_payables=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 其他應付款']#其他應付款
41Long_term_loan=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 長期借款']#長期借款
42Long_term_payables=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 長期應付款']#長期應付款
43Reserves=-liabilities_and_equity['期末餘額.1'][liabilities_and_equity['項目.1']==' 專項儲備']#專項儲備
44
45#---------------------------
46#損益類項目,收入類加負号,成本費用類不加負号
47turnover=-data2['本年累計'][data2['項目']=='其中:主營業務收入']#主營業務收入
48Cost_of_Sales=data2['本年累計'][data2['項目']=='其中:主營業務成本']#主營業務成本
49business_tax_and_surcharges=data2['本年累計'][data2['項目']=='營業稅金及附加']#營業稅金及附加
50Selling_Expenses=data2['本年累計'][data2['項目']=='銷售費用']#銷售費用
51G_A=data2['本年累計'][data2['項目']=='管理費用']#管理費用
52Finance_Cost=data2['本年累計'][data2['項目']=='财務費用']#财務費用
53Interest_Income=data2['本年累計'][data2['項目']=='利息收入']#利息收入
54Other_expense=data2['本年累計'][data2['項目']=='減:營業外支出']#營業外支出
55Other_Income=-data2['本年累計'][data2['項目']=='加:營業外收入']#營業外收入
56Realized_Gain_or_Loss_from_Financial_Assets=-data2['本年累計'][data2['項目']=='投資收益(損失以“-”号填列)']#投資收益
57Income_Taxes=data2['本年累計'][data2['項目']=='減:所得稅費用']#所得稅費用
58
59#----------------
60#寫入已打開的合并底稿表格
61ws=xlwb.Worksheets(target_sheet) #實現表格一一對應關系
62ws.Range('E14').value=float(Cash_and_cash_equivalents)
63ws.Range('E16').value=float(Fair_value_financial_assets_through_PL)
64ws.Range('E17').value=float(Trade_receviables_others)
65ws.Range('E19').value=float(Bills_receivable)
66ws.Range('E20').value=float(Other_receivables)
67ws.Range('E22').value=float(Inventory)
68ws.Range('E23').value=float(Prepayment_to_suppliers)
69ws.Range('E24').value=float(Deferred_expenses)
70ws.Range('E40').value=float(Property_and_equipment)
71ws.Range('E44').value=float(Construction_in_progress)
72ws.Range('E52').value=float(Intangible_assets)
73ws.Range('E67').value=float(Bank_loan)
74ws.Range('E68').value=float(Notes_payable)
75ws.Range('E69').value=float(Accounts_payable)
76ws.Range('E70').value=float(Advance_from_customers)
77ws.Range('E71').value=float(Salaries_and_welfare)
78ws.Range('E73').value=float(Income_tax)
79ws.Range('E77').value=float(Interest_payable_current)
80ws.Range('E74').value=float(Other_payables)
81ws.Range('E90').value=float(Long_term_loan)
82ws.Range('E92').value=float(Long_term_payables)
83ws.Range('E101').value=float(Reserves)
84ws.Range('E131').value=float(turnover)
85ws.Range('E133').value=float(Cost_of_Sales) float(business_tax_and_surcharges)
86ws.Range('E139').value=float(Selling_Expenses)
87ws.Range('E141').value=float(G_A)
88ws.Range('E143').value=float(Finance_Cost)-float(Interest_Income)
89ws.Range('E145').value=float(Interest_Income)
90ws.Range('E147').value=float(Other_expense) float(Other_Income)
91ws.Range('E157').value=float(Realized_Gain_or_Loss_from_Financial_Assets)
92ws.Range('E164').value=float(Income_Taxes)
④建立合并底稿與各單家的一一對應關系,實現多報表批量讀寫
因為要将各單家公司原始财務報表數據過入到合并底稿中對應公司名的表格中去,故需建立一一對應關系,防止數據跑錯表格。
且與①中保持一緻,有多少家就放多少家,盡管放!使勁放!代碼如下。
1#建立匹配對應關系,即每一張未審報表數據讀取出來後,要寫入到合并底稿中的哪個表格中去,指明去處,别跑錯地方了
2match_dict={
3'P公司':'CX',
4'A公司':'BF',
5'B公司':'FY',
6'C公司':'DY',
7'D公司':'BP',
8'E公司':'DT',
9'F公司':'JL',
10'G公司':'LJ',
11'H公司':'PX',
12'I公司':'PXF',
13'J公司':'MA',
14'K公司':'XS',
15'L公司':'XP',
16'M公司':'XY',
17'N公司':'GC',
18'O公司':'YF'
19}
20
21#讀寫過程
22start_time=time.time()
23print('刀哥,程序開始讀寫工作!')
24foriinrange(len(dir_xls)):
25print('正在讀寫第{}張表:{}'.format(i 1,dir_xls[i]))
26read_file_path=dir_xls[i]
27forkey,valueinmatch_dict.items():
28ifkeyinread_file_path:
29target_sheet=value
30read_and_write_datas(read_file_path,target_sheet)
31print('第{}張表讀寫完畢!'.format(i 1))
32end_time=time.time()
33total_time=end_time-start_time
34print('所有财務報表全部讀寫完畢!共用時{:.1f}秒'.format(total_time))
3.
目标實現
經過上述一番折騰,運行代碼終端輸出如下結果。
1刀哥,程序開始讀寫工作!
2正在讀寫第1張表:d:\F:學習\python\讀寫财務報表\8月報表\财務報表(2020-08)-P公司.xlsx
3第1張表讀寫完畢!
4正在讀寫第2張表:d:\F:學習\python\讀寫财務報表\8月報表\财務報表(2020-08)-A公司.xlsx
5第2張表讀寫完畢!
6......
7正在讀寫第15張表:d:\F:學習\python\讀寫财務報表\8月報表\财務報表(2020年-08)-N公司.xlsx
8第15張表讀寫完畢!
9正在讀寫第16張表:d:\F:學習\python\讀寫财務報表\8月報表\财務報表(2020年-08)-O公司.xlsx
10第16張表讀寫完畢!
11所有财務報表全部讀寫完畢!共用時78.4秒
于是,經過1分多鐘的等待,16張報表數據全部過入到對應的合并底稿表格中,并已保存好乖乖躺在了我的電腦裡,至此,打完收工!
最後還要說一點的是,通過代碼實現對表格格式的統一性要求比較高,需要各公司使用統一格式的報表模闆,合并底稿中對應各家的表格也要統一,這樣才能準确的讀取數據,并準确的寫入到指定的地方去。否則,程序容易報錯,讀寫結果也容易出現錯位串行。
每家公司報表難免會有自己的特殊性,難免用代碼寫入後還有報表不平的情況,遇到這樣的情況,手動打開表格檢查一下修改一下就好,這也比完全靠人工搬運,效率也要高出幾十、幾百倍,并且單家報表數量越多,則越能體現出它的優勢。
刀哥介紹:一個會寫代碼的注冊會計師,看用代碼能把财會玩出什麼新花樣!
,更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!