青田县审计局
 
  欢迎您访问青田县审计局网站!  今天是:
站内检索:
大数据时代的审计分析——LD农商银行现金流贡献度专项审计

大数据是当下很是高大上的话题,很多机构、很多人都在说大数据。相当多的人认为大数据其实就是指数据的规模大,数据量的增长快,分析数据会更容易。这只是描述了大数据这一表面现象,大数据归根到底是数据分析的一种技术,能从各种各样类型的数据中,快速获得有价值信息的能力,就是大数据技术。什么是大数据?度娘给出的定义是:大数据(large data)是巨量数据集合——指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合,是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化能力的海量、高增长率和多样化的信息资产。

在维克托·迈尔-舍恩伯格及肯尼斯·库克耶编写的《大数据时代》中大数据指不用随机分析法(抽样调查)这样捷径,而采用所有数据进行分析处理。IBM提出大数据的5V特点:Volume(大量)、Velocity(高速)、Variety(多样)、Value(低价值密度)、Veracity(真实性)。

随着信息技术的不断发展,特别是银行业大数据业务的推广,给审计分析带来了不可多得的机遇。审计分析获取基础数据更为便利,审计结果中的数据将更为精准,大数据给了用数据说话的审计更强大的数字支撑。

20165NSYH采集存量贷款客户的相关数据,利用SQLSERVER数据库对所有审计范围内的数据进行分析处理取得汇总统计信息和各项明细数据,对NSYH辖内营业网点贷款客户现金流贡献度的执行情况进行了分析。

一、确定审计需要的数据      

根据相关制度中现金流贡献度相关规定,确定审计范围为20164月底有余额的贷款,重点是现金流贡献度{现金流贡献度是指借款客户在农商行贷款日起向前追溯一个贷款期限的日均存款余额与本期贷款最高余额(含本笔贷款余额)之比}低于5%的贷款。确定需要的数据为:普通贷款分户文件(贷款明细)、对公客户拓展信息表(对公贷款法定代表人信息)、对私客户家庭成员信息、存款信息主档、存款积数表。

二、数据采集导入

1.普通贷款分户文件、对公客户拓展信息表、对私客户家庭成员信息三表从辅助审计系统后台数据库导出(后台为DB2数据库,导出DELCSV格式,导出时要注意数据格式。可通过Aqua Data Studio等工具导出)。

存款信息主档、存款积数表从薪酬系统后台数据库导出(后台为SQLSERVER数据库)。

2.通过SQLSERVER数据库(SQLSERVER要选装企业版否则不能处理超过2G的数据)的导入工具将取得的数据导入SQLSERVER数据库,导入时SQLSERVER默认的字段长度为50,要注意调整对应字段的长度。

3.检查导入数据的正确性。导入的数据量是否与取得的数据源相同。比如,日期型的数据是否导入为日期型,数值型的数据导入后是否为数值,类型或数据格式不对可能过修改数据结构或者UPDATE语句修正。

三、数据分析思路及SQL语句实现

 ()根据审计要求从以下4块内容进行分析

1.2015年发放一年期贷款,201614月重新发放贡献度不足5%贷款额未降低。

2.2015110月新增贷款发放后半年的贡献度。

3.201414月发放2年期贷款2016年重新发放上一贷款年度的贡献度。

4.20144月至12月发放的存量贷款自发放日到2016531日的贡献度。

5.201441日至2016531日发放的存量贷款中不同贡献度产生的不良贷款比重。

 ()数据处理的关键点是依据各表的数据结构和现金流贡献度的定义,确定现金流贡献度的算法。存款积数表的结构如下:

年度

账号

A0101

A0102

……

A1231

201X

XX

1

2

 

365

多年数据在同一表中以年底区分,存款积数存于A0101A1231各字段,A0101A1231为逐日累加值

单个存款账户日均=积数/天数=(A2-A1)/D2-D1),以贷款分户表与存款信息表关联, 客户贷款的发放日期和到期日期对应其存款积数的起始日期和结束日期,再从存款积数表中取得各存款账户的积数。SQL语句实现方法如下:

--取贷款发放时间

select min(staidate) as 贷款发放日期,stabcsid,stanflnm  into #贷款 from 贷款发放明细2016  group by stabcsid,stanflnm

select stabcsid,stanflnm,贷款发放日期,SUBSTRING(贷款发放日期,1,4)-1 as tranyear,('A'+SUBSTRING(贷款发放日期,6,2)+SUBSTRING(贷款发放日期,9,2)) as 日均起始日期 into 贷款发放日期 from #贷款

select tranyear,savno,custno,custname,'1111111111111111111111' as aa,a1231 into 存款日均2015 from 相关存款账户积数 where tranyear=2015

select a.*,贷款发放日期,b.tranyear as 贷款前一年,日均起始日期 into #ttmp from 存款日均2015 as a join 贷款发放日期 as b on custno=stabcsid

 

DECLARE E1cursor cursor     

FOR SELECT savno,日均起始日期,tranyear FROM #ttmp

OPEN E1cursor               

declare @savno  nvarchar(50),@日均起始日期 nvarchar(50) ,@tranyear  nvarchar(50) FETCH NEXT from E1cursor  into @savno,@日均起始日期,@tranyear   

WHILE @@FETCH_STATUS = 0   

BEGIN

declare @sql varchar(4000);

set @sql =' select '+@日均起始日期+' from 相关存款账户积数 where savno= '+@savno+'  and tranyear= '+@tranyear  ;

set @sql=' update #ttmp set aa= ('+@sql +') where savno= '+@savno+'  and tranyear= '+@tranyear ;

exec(@sql)

FETCH NEXT from E1cursor  into @savno,@日均起始日期,@tranyear

END

CLOSE E1cursor             

DEALLOCATE E1cursor  

select a.*,begdate,canceldate into 相关存款账户积数2015 from #ttmp as a  join sas$savbaseinfo as b on a.savno=b.savno

where aa='1111111111111111111111'

--2015年日均

select tranyear,savno,custno,custname,aa,a1231,贷款发放日期,日均起始, (ISNULL(a1231,0)-ISNULL(aa,0)) as 上年积数 ,(datediff(d,日均起始,'2015-12-31')) as 上年天数 into 日均表 from 相关存款账户积数2015

select a.*,b.aa as 本年积数 ,(datediff(d,'2016-01-01',b.贷款发放日期)) as 本年天数 into #temp from 日均表 as a

left join 相关存款账户积数2016 as b

on a.savno=b.savno

四、汇总分析

(一)20164月末存量贷款现金流贡献度分析

截至20164月末,NSYH贷款户数N户、贷款余额N万元,前一年(20155月至20164月)存款日均N万元,现金流贡献度为8.25%,低于20142月底现金流贡献度(8.34%);各支行现金流贡献度从高到低依次为:Y支行14.57%C支行13.08%L支行11.68%G支行10.89%N支行9.39%B支行8.31%S支行7.31%Z支行7.16%Q支行6.98%、营业部6.53%H支行5.78%F支行4.32%W支行3.81%

截至20164月末,NSYH贷款余额10万元以下的贷款N万元,前一年存款日均N万元,现金流贡献度为23.63%;贷款余额10万元至30万元(含)的贷款N万元,前一年存款日均N万元,现金流贡献度为10.91%;贷款余额30万元至50万元(含)的贷款N万元,前一年存款日均N万元,现金流贡献度为7.33%;贷款余额50万元以上的贷款N万元,前一年存款日均N万元,现金流贡献度为3.9%。各支行情况如下:

支行

贷款余额10

万元以下

贷款余额10万元

30万元(含)

贷款余额30万元

50万元(含)

贷款余额50万元以上

现金流
贡献度

现金流
贡献度

现金流
贡献度

现金流
贡献度

H支行

19.92%

7.11%

9.45%

3.29%

B支行

23.29%

9.53%

5.39%

2.40%

G支行

28.85%

13.20%

6.41%

4.46%

L支行

26.89%

14.99%

8.63%

5.06%

C支行

23.20%

12.17%

16.13%

5.54%

N支行

25.24%

10.41%

7.39%

3.82%

S支行

18.86%

9.58%

10.67%

3.09%

W支行

16.74%

5.96%

5.46%

2.30%

Y支行

22.74%

14.68%

9.76%

8.19%

Q支行

21.61%

8.92%

6.77%

3.39%

营业部

32.86%

14.31%

6.41%

4.38%

F支行

13.74%

6.38%

5.92%

2.49%

Z支行

32.11%

6.44%

6.91%

6.72%

总计

23.63%

10.91%

7.33%

3.90%

(三)201441日至20165月末存量贷款现金流贡献度分析

截至20165月末,NSYH201441日后发放的存量贷款合同笔数共计N笔,合同金额N万元,贷款发放日至20165月末存款日均余额N万元,现金流贡献度低于5%11954笔,占比59.6%。各支行现金流贡献度低于5%的占比从高到低依次为:F支行81.65%Z支行77.59%W支行74.93%,营业部74.45%H支行72.12%Q支行65.08%S支行62.81%N支行57.89%B支行56.82%G支行53.50%C支行52.45%L支行49.90%Y支行45.02%

1.从不良贷款占比来看,NSYH201441日至2016531日发放的存量贷款中不良贷款笔数331笔,余额17064万元,其中现金流贡献度低于5%317笔,占比95.77%

2.从担保方式来看,NSYH201441日至2016531日发放的存量贷款现金流贡献度低于5%的贷款11954笔,金额411075万元,其中担保方式为抵质押的2290笔,金额179423万元,占比43.65%;担保方式为保证的5076笔,金额151627万元,占比36.89%

()共核对数据中现金流贡献度低于5%且到期后未按规定压缩或用信超过半年的贷款有2077笔,其中借款人未婚、离婚、丧偶的794笔,占比38.23%

在大数据时代,充分利用数据仓库、联机分析、数据挖掘等技术、把不同系统中的海量数据彼此关系并进行深度挖掘分析,可以对相关制度实施的效果进行评估,从而得出客观的审计结论。(何国芳)

 

文章来源:    责任编辑:审计局  
【推荐】 【打印】 【关闭】  
青田县审计局
[加入收藏] [设为首页] [联系我们] [网站声明]
版权所有:青田县审计局 地址:青田县鹤城中路8号 电话:0578-6824153
备案序号:浙ICP备08001658号 [管理入口]