Finding average balance from HDFC bank statement

Here is a perl script which parses a text delimeted account statement and generates an SQL script that gives average balance maintained for the statement period. If the statement is for a quarter, it gives average quarterly balance (AQB).

#!/usr/bin/perl

# Usage: perl gen_aqb_calc.pl < stmt_file.txt > aqb_calc.sql

$seq = 1;

print "set feedback off;\n\n";

print "create table xcts(sn number, dt date, bal number(20,2));\n\n";

while(<stdin>)

{

 next unless /^\s*\d\d\/\d\d\/\d\d/; # Skip heading and blank lines

 @fields = split(',');

 # Example format of downloaded statement is

 # 20/10/11  ,POS 4214 BHARTI AIRTEL LI  ,20/10/11 , 444.00  , 0.00 ,1011413 ,  23730.59 

 $date = $fields[0];

 $closing_bal = $fields[6];

 $date =~ s/\s//g;

 $closing_bal =~ s/\s//g;

 print "insert into xcts values ($seq, to_date('$date', 'dd/mm/yy'), $closing_bal); \n";

 $seq++;

}

print "\ncommit;\n";

print "

with closing_balances as(select x1.dt, x1.bal

                  from xcts x1,

                       (select max(sn) maxsn, dt from xcts group by dt) x2

                 where x1.sn = x2.maxsn

                 order by x1.dt)

 select round( sum(t1.bal * nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance

  from (select rownum rn, dt, bal

          from closing_balances) t1,

       (select rownum - 1 rn, dt

          from closing_balances) t2

 where t1.rn = t2.rn(+);

";

print "\ndrop table xcts purge;\n";

The perl script generates below SQL script. When we execute it against Oracle database, it gives average balance.

 

set feedback off;

create table xcts(sn number, dt date, bal number(20,2));

insert into xcts values (1, to_date('01/10/11', 'dd/mm/yy'), 45651.08);

-- Some more insert statements here, removed from clarity

commit;

with closing_balances as(select x1.dt, x1.bal

                  from xcts x1,

                       (select max(sn) maxsn, dt from xcts group by dt) x2

                 where x1.sn = x2.maxsn

                 order by x1.dt)

 select round( sum(t1.bal * nvl(t2.dt - t1.dt, 1)) / (max(t1.dt) - min(t1.dt) + 1) , 2 ) average_balance

  from (select rownum rn, dt, bal

          from closing_balances) t1,

       (select rownum - 1 rn, dt

          from closing_balances) t2

 where t1.rn = t2.rn(+);

drop table xcts purge;