基于Python的两项直达延期政策审计算法研究

掌孝政

[摘要]两项直达延期政策审计需将审计期间商业银行全部的贷款台账、商业银行上报人民银行领取补贴的台账进行比对分析，由于审计数据量非常庞大、审计模型非常复杂，传统的手工抽样方式难以实现审计全覆盖，存在审计风险。引入Python可以更好地解决两项直达延期政策审计的问题。本文针对两项直达延期政策审计的算法进行了深入研究。延期政策包括展期、无还本续贷、借新还旧、三个工作日还旧借新等多种模型，在编写代码的时候要综合考虑每种模型的特点。

[关键词]两项直达货币政策工具   Python

了加大金融對实体经济的支持、帮助小微企

业渡过难关，2020年6月1日，中国人民银行推出了两项直达货币政策。如何监督商业银行在两项直达延期政策中的执行情况成为新的审计难点。本文以人民银行对***商业银行两项直达政策审计项目的实例，从数据准备、代码解析、输出结果分析三个方面详细阐述延期政策的审计方法。人民银行审计部门可以通过数据比对分析监督商业银行在两项直达政策中的执行情况，商业银行也可以通过代码筛选出贷款台账中符合延期政策的贷款，具有较高的实用价值。

一、数据准备

（一）被审计商业银行的贷款台账

两项直达延期政策要求贷款的到期日期要大于等于2020年6月1日，而一般的银行贷款年限为1—2年。所以，在审计中需要调取商业银行贷款发放日期为2018年6月1日至审计日的所有贷款台账（以下简称大台账）。调取大台账的要素主要包括个人（企业）的“证件号码”“客户名称”“贷款金额”“贷款发放日期”“贷款到期日期”“贷款结清日期”“利率”“自助循环贷款标识”“贷款产品名称”“贷款用途”“逾期贷款标识”“展期贷款标识”等。大台账的格式如表1所示。

（二）商业银行上报人民银行的台账

商业银行上报人民银行的台账（以下简称小台账）是商业银行领取延期政策奖励的主要依据，其格式如表2所示。

二、代码解析

（一）数据预处理

引入需要使用的Python库函数，代码如下：

import os

import pandas as pd

import numpy as np

import datetime

from datetime import datetime

from chinese_calendar import is_holiday

1.大台账数据预处理。首先，导入大台账，以变量df_nsls_yq进行存储。代码如下：

df_nsls_yq = pd.read_excel（r'201901-202106台账.xlsx'）

其次，通过strptime（）函数将“贷款发放日期”“贷款到期日期”“贷款结清日期”转换成可以计算的日期格式。这里要用到Python的datetime函数，代码如下：

list1 = []

for i in df_nsls_yq['贷款发放日期']：

a = datetime.strptime（str（i），'%Y%m%d'）

list1.append（a）

df_nsls_yq['贷款发放日期'] = list1

list1 = []

for i in df_nsls_yq['贷款到期日期']：

a = datetime.strptime（str（i），'%Y%m%d'）

list1.append（a）

df_nsls_yq['贷款到期日期'] = list1

其中%Y%m%d是大台账中日期（如20190806）的原始格式（见表1），如果日期格式是2019-08-06，则需要改为%Y-%m-%d，格式要保持一致。这里的Y代表年，m代表月，d代表日。

而“贷款结清日期”由于存在未结清贷款的数据是空白的情况，将需要空白的地方进行填充，以方便后续进行计算。代码如下：

dt2030 = datetime（2030， 1， 1， 0， 0）

list1 = []

for i in df_nsls_yq['贷款结清日期']：

if len（str（i）） < 5：

a = dt2030

list1.append（a）

else：

b = datetime.strptime（str（i），'%Y%m%d'）

list1.append（b）

df_nsls_yq['贷款结清日期'] = list1

最后，检查一下“贷款金额”是否为浮点型（float）数据，如果是字符型（str）数据则需要转换为浮点型数据。经过处理后的大台账格式如表3所示。

2.小台账的数据预处理。因为商业银行一般是按月进行统计，而小台账的数据格式是一致的，这里可以通过代码将所有的小台账数据表格进行合并，代码如下所示：

path = r'D：＼小台账'

files = os.listdir（path）

list1 = []

for i in files：

df1 = pd.read_excel（i）

list1.append（df1）

df_yq = pd.concat（list1）

将所有的小台账数据存放于D盘“小台账”这个文件夹中，方便进行提取合并。

最后检查一下“延期本金”是否为浮点型（float）数据，如果是字符型（str）数据则需要转换为浮点型数据。

（二）数据比对及存储

数据预处理完成以后，就可以进行比对了。这里以许*的贷款台账为例，分为5个步骤进行详细说明。

1.通过小台账中的“客户名称”，查找大台账中对应名称的所有贷款台账，代码如下：

dt202006 = datetime（2020， 6， 1， 0， 0）

list_name = list（df_yq['企业名称']）

list_name = list（set（list_name））

for k in list_name：

df1 = df_nsls_yq[df_nsls_yq['客户名称'] == k]

当客户名称为许*时，变量df1的数据如表4所示。

通过台账中的“证件号码”可以看出，“客户名称”为“许*”共有四个重名，证件号码的尾号分别为14、31、32、36。

2.计算许*贷款台账中符合展期条件的贷款金额，代码如下：

df33 = df1[df1['是否展期'] == '展期']

df44 = df33[df33['自助'] ！= '自助放款']

df55 = df44[df44['贷款到期日期'] >= dt202006]

m_zq = sum（df55['贷款金额']）

通过表4的台账可以看出，许*没有展期贷款，所以变量m_zq等于初始值0。

3.根据表4中的“证件号码”对许*贷款台账进行分类提取，代碼如下：

df1 = df1[（df1['是否展期'] == '展期'） | （df1['贷款到期日期'] >= dt202006）]

df2 = df_yq[df_yq['企业名称'] == k]

a = sum（df2['延期本金']）*10000

if len（df1） == 0：

listn.append（df2）

else：

listzj = list（df1['证件号码']）

listzj = list（set（listzj））

for x in listzj：

dfx = df1[df1['证件号码'] == x]

dfx = dfx.reset_index（drop = True）

4.分别计算每个证件号码对应的贷款台账中符合借新还旧、三个工作日还旧借新条件的金额，并将金额进行相加，代码如下：

for i in range（0，len（dfx））：

s = dfx['贷款发放日期'][i]

z = dfx['自助'][i]

for j in range（0，len（dfx））：

e = dfx['贷款结清日期'][j]

e1 = e + datetime.timedelta（days=1）

e2 = e + datetime.timedelta（days=2）

e3 = e + datetime.timedelta（days=3）

e_0 = False

e_1 = False

e_2 = False

e_3 = False

if e ！= dt2030：

e_0 = is_holiday（e）

e_1 = is_holiday（e1）

e_2 = is_holiday（e2）

e_3 = is_holiday（e3）

w = dfx['贷款结清日期'][j].weekday（）

c = （s -e）.days

if i ！= j：

if e_0 == True or e_1 == True or e_2 == True or e_3 == True：

if e_0 == True：

h = 1

bool_h =1

while bool_h：

bool_h = is_holiday（e + datetime.timedelta（days=h））

h = h+1

h = h - 1

else：

if e_1 == True：

h = 1

bool_h =1

while bool_h：

bool_h = is_holiday（e1 + datetime.timedelta（days=h））

h = h+1

else：

if e_2 == True：

h = 1

bool_h =1

while bool_h：

bool_h = is_holiday（e2 + datetime.timedelta（days=h））

h = h+1

else：

if e_3 == True：

h = 1

bool_h =1

while bool_h：

bool_h = is_holiday（e3 + datetime.timedelta（days=h））

h = h+1

if c <= （3+h） and c >= 0 and z ！= '自助放款'：

list1.append（i）

list1.append（j）

list2.append（e）

list3.append（i）

list4.append（j）

else：

if w == 6 or w == 0 or w == 1：

if c <= 3 and c >= 0 and z ！= '自助放款'：

list1.append（i）

list1.append（j）

list2.append（e）

list3.append（i）

list4.append（j）

elif w == 2 or w == 3 or w == 4：

if c <= 5 and c >= 0 and z ！= '自助放款'：

list1.append（i）

list1.append（j）

list2.append（e）

list3.append（i）

list4.append（j）

elif w == 5：

if c <= 4 and c >= 0 and z ！= '自助放款'：

list1.append（i）

list1.append（j）

list2.append（e）

list3.append（i）

list4.append（j）

same = [x for x in list3 if x in list4]

same = list（set（same））

if len（same） > 0：

dfs = dfx.loc[same]

same_m = sum（dfs['贷款金额']）

list1 = list（set（list1））

df11 = dfx.loc[list1]

if len（df11） > 0：

df11 = df11.reset_index（drop = True）

for i in range（0，len（df11））：

if df11['贷款结清日期'][i] in list2：

list11.append（df11['贷款金额'][i]）

else：

list22.append（df11['贷款金额'][i]）

m1 = sum（list11）

m2 = sum（list22）+same_m

mm = min（m1，m2）

m = m + mm

m = m + m_zq

这里的核心代码主要是三个工作日还旧借新，在计算三个工作日时，要考虑到星期六、星期日和公共节假日等情况。首先，通过chinese_calendar库中的is_holiday（）函数，判断贷款结清日期与贷款发放日期之间是否存在公共节假日，若存在，则需要在“贷款发放日期”减去“贷款结清日期”差值的基础上剔除公共节假日的天数;其次，通过weekday（）函数判断贷款结清日期与贷款发放日期之间是否存在星期六、星期日，若存在，则需要在“贷款发放日期”减去“贷款结清日期”差值的基础上剔除星期六、星期日的天数。

5.将变量m的值与表2上报人民银行许*的总金额96万元进行比对，查看金额是否一致。若上报人民银行的金额小于或等于符合延期政策的金额，则提取出对应“客户名称”的大台账;若上报人民银行的金额大于符合延期政策的金额，则提取出对应“客户名称”的小台账，代码如下：

if m >= a：

listy.append（df11）

listy.append（df33）

else：

listn.append（df2）

if len （listy） > 0：

dfy = pd.concat（listy）

dfy.to_excel（'比中台账.xls'）

if len（listn） > 0：

dfn = pd.concat（listn）

dfn.to_excel（'未比中台账.xls'）

证件号码的尾号为14、31的两个贷款台账符合延期政策，经过程序计算，变量m的值为960，000。与表2上报人民银行许*的总金额96万元进行比对，发现完全一致。存储为大台账格式，如表5所示。

三、输出结果分析

通過上述代码对***商业银行的37万余条大台账数据、4000余条小台账数据进行比对筛选，最终导出比中台账与未比中台账，并对输出的结果进行分析。

（一）比中台账分析

比中台账为大台账格式，通过Excel的筛选功能查看比中的台账是否存在逾期（不良）贷款、消费类贷款。

根据两项直达政策的规定，逾期（不良）贷款、消费类贷款不能领取补贴。通过筛选，发现所有比中的台账不存在逾期贷款、消费类贷款。

（二）未比中台账分析

未比中台账为小台账格式，将未比中台账中的客户名称逐一代入大台账，分析未比中原因。

共有两个客户的台账未比中，分别是董**，王**。结合董**与王**的大小台账数据进行分析，未比中的原因如下。

1.多报金额。从董**的大台账可以看出，董**符合延期政策的贷款金额为308万元（如表6所示），而上报人民银行的台账为520万元，多报了212万元（如表7所示）。

2.结清与发放日期不符合三个工作日换旧借新。从王**的大台账可以看出（如表8所示），王**的贷款结清日期与贷款发放日期不符合三个工作日换旧借新，没有符合延期政策的贷款，而上报人民银行的台账为60万元，多报了60万元（如表9所示）。

四、总结

两项直达延期政策审计涉及的审计数据量庞大、审计模型复杂，以文中***商业银行的审计项目为例，传统的审计方法只能通过人工方式对小台账进行逐一核实。经过测算，核实一条台账的平均时间大约为2分钟，假设审计人员每天工作8小时，需要16天的时间才能完成。在实际的审计项目中，往往只能通过抽查的方式进行审计。而通过Python进行处理，可以实现审计的全覆盖，1个小时以内即可完成核实工作，极大地缩减了审计时间，提升了审计结果的准确性。

两项直达延期政策的原始数据是商业银行的贷款台账，其数据比较规整、格式基本一致，非常适合运用Python进行数据分析。在本次审计分析完成后，可以将代码进行保存，在下一次两项直达延期政策审计中稍加修改就可以直接使用，有着极高的可延续性和可重复性。

（作者单位：中国人民银行连云港市中心支行，邮政编码：222000，电子邮箱：271769150@qq.com）