From b214a2db5b64b52938aecc60711a0237fc95b575 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Tue, 8 Jan 2013 14:01:58 -0500 Subject: Began work on script to reconcile bank accounts. The goal here is to take as input an account, a monthly balance amount that appears on a bank statement, and the date of that bank statement and output the list of transactions that likely weren't cleared properly as of that date that caused the balance in the accounts to fail to match the balance that appeared on the statement. Note that determining this answer requires solving the known NP-Complete problem called the subset sum problem. There is a known pseudo-polynomial dynamic programming solution to this problem, but it's still exponential in the size of the numbers you have to balance. So, if you have *big* account balances, this will make take quite a while to run. For smaller accounts, the pseudo-polynomial solution might be helpful. (BTW, the wikipedia entry on the subset sum problem isn't, at the time of this commit, particularly good, but it's "good enough" to give you a sense of what the subset sum problem is: http://en.wikipedia.org/wiki/Subset_sum_problem ) I originally wrote the subset sum problem solution implementation here: https://gitorious.org/bkuhn/small-hacks/commit/2dca069d810b61cdfad46e00abcb1a3edaf56d1b The code is just cut and pasted in here with some minor modifications. This rest of this first commit just has that aforementioned paste, plus the beginnings of the CLI and query to run to get the proper entries. --- .../bank-reconcilation.plx | 122 +++++++++++++++++++++ 1 file changed, 122 insertions(+) create mode 100755 contrib/non-profit-audit-reports/bank-reconcilation.plx (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx new file mode 100755 index 00000000..7201ef1f --- /dev/null +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -0,0 +1,122 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +use Math::BigFloat; +use Date::Manip; + +Math::BigFloat->precision(-2); +my $ZERO = Math::BigFloat->new("0.00"); + +my $VERBOSE = 0; +my $DEBUG = 0; + +my $LEDGER_BIN = "/usr/local/bin/ledger"; + +###################################################################### +sub SubSetSumSolver ($$$) { + my($numberList, $totalSought, $extractNumber) = @_; + + my($P, $N) = (0, 0); + foreach my $ii (@{$numberList}) { + if ($ii < $ZERO) { + $N += $ii; + } else { + $P += $ii; + } + } + my $size = scalar(@{$numberList}); + my %Q; + my(@L) = + map { { val => &$extractNumber($_), obj => $_ } } @{$numberList}; + + for (my $ii = 0 ; $ii <= $size ; $ii++ ) { + $Q{$ii}{0}{value} = 1; + $Q{$ii}{0}{list} = []; + } + for (my $jj = $N; $jj <= $P ; $jj++) { + $Q{0}{$jj}{value} = ($L[0]{val} == $jj); + $Q{0}{$jj}{list} = $Q{0}{$jj}{value} ? [ $L[0]{obj} ] : []; + } + for (my $ii = 1; $ii <= $size ; $ii++ ) { + for (my $jj = $N; $jj <= $P ; $jj++) { + if ($Q{$ii-1}{$jj}{value}) { + $Q{$ii}{$jj}{value} = 1; + + $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list}; + push(@{$Q{$ii}{$jj}{list}}, @{$Q{$ii-1}{$jj}{list}}); + + } elsif ($L[$ii]{val} == $jj) { + $Q{$ii}{$jj}{value} = 1; + + $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list}; + push(@{$Q{$ii}{$jj}{list}}, $jj); + } elsif ($Q{$ii-1}{$jj - $L[$ii]{val}}{value}) { + $Q{$ii}{$jj}{value} = 1; + $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list}; + push(@{$Q{$ii}{$jj}{list}}, $L[$ii]{obj}, @{$Q{$ii-1}{$jj - $L[$ii]{val}}{list}}); + } else { + $Q{$ii}{$jj}{value} = 0; + $Q{$ii}{$jj}{list} = []; + } + } + } + foreach (my $ii = 0; $ii <= $size; $ii++) { + foreach (my $jj = $N; $jj <= $P; $jj++) { + print "Q($ii, $jj) == $Q{$ii}{$jj}{value} with List of ", join(", ", @{$Q{$ii}{$jj}{list}}), "\n"; + } + } + return [ $Q{$size}{$totalSought}{value}, \@{$Q{$size}{$totalSought}{list}}]; +} +###################################################################### +sub Commify ($) { + my $text = reverse $_[0]; + $text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g; + return scalar reverse $text; +} +###################################################################### +sub ParseNumber($) { + $_[0] =~ s/,//g; + return Math::BigFloat->new($_[0]); +} +if (@ARGV < 4) { + print STDERR "usage: $0 \n"; + exit 1; +} +###################################################################### +my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV; + +$balanceSought = ParseNumber($balanceSought); + +my $err; +my $startDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err), "%Y/%m/%d"); +die "Date calculation error on $endDate" if ($err); + +my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-b', $startDate, '-e', $endDate, + '-F', '"%(date)","%C","%P","%t"\n', + 'reg', "/$account/"); + + open(FILE, "-|", @fullCommand) + or die "unable to run command ledger command: @fullCommand: $!"; + +my @entries; + +foreach my $line () { + die "Unable to parse output line from: $line" + unless $line =~ /^\s*"([^"]*)","([^"]*)","([^"]*)","([^"]*)"\s*$/; + my($date, $checkNum, $payee, $amount) = ($1, $2, $3, $4); + die "$amount is not a valid amount" + unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/; + $amount = ParseNumber($amount); + + print "$date, $checkNum, $payee, $amount\n"; + push(@entries, { date => $date, checkNum => $checkNum, amount => $amount }); +} + +############################################################################### +# +# Local variables: +# compile-command: "perl -c bank-reconcilation.plx" +# End: -- cgit v1.2.3 From 6962fc4c57c5709cb106bd544df3cdb338c7495a Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Tue, 8 Jan 2013 14:26:04 -0500 Subject: Basic implementation probably correct, but needs much RAM. This is the basic implementation but for large numbers, it needs a *LOT* of RAM. --- .../bank-reconcilation.plx | 45 +++++++++++++++++----- 1 file changed, 36 insertions(+), 9 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 7201ef1f..b09519a3 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -8,8 +8,9 @@ use Date::Manip; Math::BigFloat->precision(-2); my $ZERO = Math::BigFloat->new("0.00"); +my $ONE_HUNDRED = Math::BigFloat->new("100.00"); -my $VERBOSE = 0; +my $VERBOSE = 1; my $DEBUG = 0; my $LEDGER_BIN = "/usr/local/bin/ledger"; @@ -19,18 +20,27 @@ sub SubSetSumSolver ($$$) { my($numberList, $totalSought, $extractNumber) = @_; my($P, $N) = (0, 0); - foreach my $ii (@{$numberList}) { - if ($ii < $ZERO) { - $N += $ii; - } else { - $P += $ii; - } - } my $size = scalar(@{$numberList}); my %Q; my(@L) = map { { val => &$extractNumber($_), obj => $_ } } @{$numberList}; + + if ($VERBOSE) { + } + } + print STDERR " L in this iteration:\n [" if $VERBOSE; + + foreach my $ee (@L) { + if ($ee->{val} < 0) { + $N += $ee->{val} + } else { + $P += $ee->{val}; + } + print STDERR $ee->{val}, ", " if $VERBOSE; + } + print STDERR "]\n P = $P, N = $N\n" if ($VERBOSE); + for (my $ii = 0 ; $ii <= $size ; $ii++ ) { $Q{$ii}{0}{value} = 1; $Q{$ii}{0}{list} = []; @@ -85,6 +95,14 @@ if (@ARGV < 4) { exit 1; } ###################################################################### +sub ConvertTwoDigitPrecisionToInteger ($) { + return sprintf("%d", $_[0] * $ONE_HUNDRED); +} +###################################################################### +sub ConvertTwoDigitPrecisionToIntegerInEntry ($) { + return ConvertTwoDigitPrecisionToInteger($_[0]->{amount}); +} +###################################################################### my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV; $balanceSought = ParseNumber($balanceSought); @@ -111,9 +129,18 @@ foreach my $line () { unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/; $amount = ParseNumber($amount); - print "$date, $checkNum, $payee, $amount\n"; push(@entries, { date => $date, checkNum => $checkNum, amount => $amount }); } +close FILE; +die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0); + +my(@solution) = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), + \&ConvertTwoDigitPrecisionToIntegerInEntry); + +if ($VERBOSE) { + use Data::Dumper; + print Data::Dumper->Dump(\@solution); +} ############################################################################### # -- cgit v1.2.3 From b1b807fcfab49b9682db79b6b2fed000fc230f90 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Tue, 8 Jan 2013 16:37:41 -0500 Subject: Loop through to build smaller sets when testing. Usually, transactions that didn't appear are nearby in date to the statement date. This loop cycles through. Overall, this would take longer to find a solution, but since most solutions are in the early dates "back" from the statement date, this will probably be faster in typical cases. --- .../bank-reconcilation.plx | 81 ++++++++++++++-------- 1 file changed, 52 insertions(+), 29 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index b09519a3..f18f43dc 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -25,11 +25,8 @@ sub SubSetSumSolver ($$$) { my(@L) = map { { val => &$extractNumber($_), obj => $_ } } @{$numberList}; - - if ($VERBOSE) { - } - } - print STDERR " L in this iteration:\n [" if $VERBOSE; + print STDERR " TotalSought:", $totalSought if $VERBOSE; + print STDERR " L in this iteration:\n [" if $VERBOSE; foreach my $ee (@L) { if ($ee->{val} < 0) { @@ -108,40 +105,66 @@ my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV; $balanceSought = ParseNumber($balanceSought); my $err; -my $startDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err), "%Y/%m/%d"); +my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err); + die "Date calculation error on $endDate" if ($err); -my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', - '-b', $startDate, '-e', $endDate, - '-F', '"%(date)","%C","%P","%t"\n', - 'reg', "/$account/"); +my $startDate = ParseDate($endDate); + +my @solution; +while ($startDate ge $earliestStartDate) { + print "START LOOP ITR: $startDate $earliestStartDate\n"; + $startDate = DateCalc(ParseDate($startDate), ParseDateDelta("- 1 day"), \$err); + die "Date calculation error on $endDate" if ($err); + + my $formattedStartDate = UnixDate($startDate, "%Y/%m/%d"); + + print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE; + + my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-b', $formattedStartDate, '-e', $endDate, + '-F', '"%(date)","%C","%P","%t"\n', + 'reg', "/$account/"); open(FILE, "-|", @fullCommand) or die "unable to run command ledger command: @fullCommand: $!"; -my @entries; + my @entries; -foreach my $line () { - die "Unable to parse output line from: $line" - unless $line =~ /^\s*"([^"]*)","([^"]*)","([^"]*)","([^"]*)"\s*$/; - my($date, $checkNum, $payee, $amount) = ($1, $2, $3, $4); - die "$amount is not a valid amount" - unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/; - $amount = ParseNumber($amount); + foreach my $line () { + die "Unable to parse output line from: $line" + unless $line =~ /^\s*"([^"]*)","([^"]*)","([^"]*)","([^"]*)"\s*$/; + my($date, $checkNum, $payee, $amount) = ($1, $2, $3, $4); + die "$amount is not a valid amount" + unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/; + $amount = ParseNumber($amount); - push(@entries, { date => $date, checkNum => $checkNum, amount => $amount }); -} -close FILE; -die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0); - -my(@solution) = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), + push(@entries, { date => $date, checkNum => $checkNum, amount => $amount }); + } + close FILE; + die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0); + + @solution = (); + if (@entries == 1) { + @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries); + } else { + @solution = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), \&ConvertTwoDigitPrecisionToIntegerInEntry); - -if ($VERBOSE) { - use Data::Dumper; - print Data::Dumper->Dump(\@solution); + } + if ($VERBOSE) { + use Data::Dumper; + print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution); + } + print STDERR "Solution Found: Dying" if ($solution[0]) and $VERBOSE; +# last if ($solution[0]); +} +print "DONE LOOP: $startDate $earliestStartDate\n"; +if ($solution[0]) { + print "FINAL SOLUTION: "; + foreach my $ee (@{$solution[1]}) { + print "$ee->date, $ee->payee, $ee->amount\n"; + } } - ############################################################################### # # Local variables: -- cgit v1.2.3 From 18d2867a6315562b4f4588ebf4fc58adf1fb9acf Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Tue, 8 Jan 2013 16:38:24 -0500 Subject: Rename the function to note it's the dynamic programming one. --- contrib/non-profit-audit-reports/bank-reconcilation.plx | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index f18f43dc..669a25b0 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -16,7 +16,7 @@ my $DEBUG = 0; my $LEDGER_BIN = "/usr/local/bin/ledger"; ###################################################################### -sub SubSetSumSolver ($$$) { +sub DynamicProgrammingSubSetSumSolver ($$$) { my($numberList, $totalSought, $extractNumber) = @_; my($P, $N) = (0, 0); @@ -148,7 +148,7 @@ while ($startDate ge $earliestStartDate) { if (@entries == 1) { @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries); } else { - @solution = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), + @solution = DynamicProgrammingSubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), \&ConvertTwoDigitPrecisionToIntegerInEntry); } if ($VERBOSE) { -- cgit v1.2.3 From 0530b729e2b38931f653e226bc3c1cfc47d55d24 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Wed, 9 Jan 2013 15:20:50 -0500 Subject: Default to brute-force subset sum solution. The dynamic programming version of the subset sum problem required far too much RAM for larger bank balances. Meanwhile, the brute-force is not to bad now that the loop tries the closer dates *first*. --- .../bank-reconcilation.plx | 52 ++++++++++++++++++---- 1 file changed, 44 insertions(+), 8 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 669a25b0..18d74067 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -5,6 +5,7 @@ use warnings; use Math::BigFloat; use Date::Manip; +use Data::PowerSet; Math::BigFloat->precision(-2); my $ZERO = Math::BigFloat->new("0.00"); @@ -15,6 +16,30 @@ my $DEBUG = 0; my $LEDGER_BIN = "/usr/local/bin/ledger"; +###################################################################### +sub BruteForceSubSetSumSolver ($$$) { + my($numberList, $totalSought, $extractNumber) = @_; + + my($P, $N) = (0, 0); + my $size = scalar(@{$numberList}); + my %Q; + my(@L) = + map { { val => &$extractNumber($_), obj => $_ } } @{$numberList}; + + my $powerset = Data::PowerSet->new(@L); + + while (my $set = $powerset->next) { + my $total = $ZERO; + foreach my $ee (@{$set}) { + $total += $ee->{val}; + } + if ($totalSought == $total) { + my(@list) = map { $_->{obj} } @{$set}; + return (1, \@list); + } + } + return (0, []); +} ###################################################################### sub DynamicProgrammingSubSetSumSolver ($$$) { my($numberList, $totalSought, $extractNumber) = @_; @@ -88,7 +113,7 @@ sub ParseNumber($) { return Math::BigFloat->new($_[0]); } if (@ARGV < 4) { - print STDERR "usage: $0 \n"; + print STDERR "usage: $0 [-d] \n"; exit 1; } ###################################################################### @@ -100,8 +125,18 @@ sub ConvertTwoDigitPrecisionToIntegerInEntry ($) { return ConvertTwoDigitPrecisionToInteger($_[0]->{amount}); } ###################################################################### +my $firstArg = shift @ARGV; + +my $solver = \&BruteForceSubSetSumSolver; + +if ($firstArg eq '-d') { + $solver = \&DynamicProgrammingSubSetSumSolver; +} else { + unshift(@ARGV, $firstArg); +} my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV; + $balanceSought = ParseNumber($balanceSought); my $err; @@ -139,7 +174,8 @@ while ($startDate ge $earliestStartDate) { unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/; $amount = ParseNumber($amount); - push(@entries, { date => $date, checkNum => $checkNum, amount => $amount }); + push(@entries, { date => $date, checkNum => $checkNum, + payee => $payee, amount => $amount }); } close FILE; die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0); @@ -148,21 +184,21 @@ while ($startDate ge $earliestStartDate) { if (@entries == 1) { @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries); } else { - @solution = DynamicProgrammingSubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought), - \&ConvertTwoDigitPrecisionToIntegerInEntry); + @solution = $solver->(\@entries, + ConvertTwoDigitPrecisionToInteger($balanceSought), + \&ConvertTwoDigitPrecisionToIntegerInEntry); } if ($VERBOSE) { use Data::Dumper; print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution); } - print STDERR "Solution Found: Dying" if ($solution[0]) and $VERBOSE; -# last if ($solution[0]); + last if ($solution[0]); } -print "DONE LOOP: $startDate $earliestStartDate\n"; if ($solution[0]) { print "FINAL SOLUTION: "; foreach my $ee (@{$solution[1]}) { - print "$ee->date, $ee->payee, $ee->amount\n"; + print Data::Dumper->Dump($solution[1]); + print "$ee->{date}, $ee->{payee}, $ee->{amount}\n"; } } ############################################################################### -- cgit v1.2.3 From 94094ce3650e26e584b68f3c0d94d593732dc3bc Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Wed, 9 Jan 2013 16:44:54 -0500 Subject: Finish reporting details for STDOUT; change command line arg to bank balance. Report in CSV now goes to STDOUT. The command line argument that was the difference to seek is now the bank balance. --- .../bank-reconcilation.plx | 60 ++++++++++++++-------- 1 file changed, 40 insertions(+), 20 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 18d74067..ada923f3 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -112,10 +112,6 @@ sub ParseNumber($) { $_[0] =~ s/,//g; return Math::BigFloat->new($_[0]); } -if (@ARGV < 4) { - print STDERR "usage: $0 [-d] \n"; - exit 1; -} ###################################################################### sub ConvertTwoDigitPrecisionToInteger ($) { return sprintf("%d", $_[0] * $ONE_HUNDRED); @@ -129,17 +125,43 @@ my $firstArg = shift @ARGV; my $solver = \&BruteForceSubSetSumSolver; +if (@ARGV < 5) { + print STDERR "usage: $0 [-d] <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n"; + exit 1; +} if ($firstArg eq '-d') { $solver = \&DynamicProgrammingSubSetSumSolver; } else { unshift(@ARGV, $firstArg); } -my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV; +my($title, $account, $endDate, $bankBalance, @mainLedgerOptions) = @ARGV; + +$bankBalance = ParseNumber($bankBalance); +my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-e', $endDate, '-F', '%t\n', 'bal', "/$account/"); -$balanceSought = ParseNumber($balanceSought); +open(FILE, "-|", @fullCommand) or die "unable to run command ledger command: @fullCommand: $!"; + +my $total; +foreach my $line (<FILE>) { + chomp $line; + die "Unable to parse output line from: \"$line\"" + unless $line =~ /^\s*\$\s*([\-\d\.\,]+)\s*$/ and not defined $total; + $total = $1; + $total = ParseNumber($total); +} +close FILE; +if (not defined $total or $? != 0) { + die "unable to run ledger @fullCommand: $!"; +} +my $differenceSought = $total - $bankBalance; my $err; +my $formattedEndDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 day"), \$err), + "%Y-%m-%d"); +die "Date calculation error on $endDate" if ($err); + my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err); die "Date calculation error on $endDate" if ($err); @@ -148,11 +170,11 @@ my $startDate = ParseDate($endDate); my @solution; while ($startDate ge $earliestStartDate) { - print "START LOOP ITR: $startDate $earliestStartDate\n"; + print STDERR "START LOOP ITR: $startDate $earliestStartDate\n" if ($VERBOSE); $startDate = DateCalc(ParseDate($startDate), ParseDateDelta("- 1 day"), \$err); die "Date calculation error on $endDate" if ($err); - my $formattedStartDate = UnixDate($startDate, "%Y/%m/%d"); + my $formattedStartDate = UnixDate($startDate, "%Y-%m-%d"); print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE; @@ -180,26 +202,24 @@ while ($startDate ge $earliestStartDate) { close FILE; die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0); - @solution = (); - if (@entries == 1) { - @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries); - } else { - @solution = $solver->(\@entries, - ConvertTwoDigitPrecisionToInteger($balanceSought), - \&ConvertTwoDigitPrecisionToIntegerInEntry); - } + @solution = $solver->(\@entries, + ConvertTwoDigitPrecisionToInteger($differenceSought), + \&ConvertTwoDigitPrecisionToIntegerInEntry); if ($VERBOSE) { use Data::Dumper; - print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution); + print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n", + Data::Dumper->Dump(\@solution); } last if ($solution[0]); } if ($solution[0]) { - print "FINAL SOLUTION: "; + print "\"title:$formattedEndDate: $title\"\n\"BANK RECONCILATION: $account\",\"ENDING\",\"$formattedEndDate\"\n"; + print "\n\n\"DATE\",\"CHECK NUM\",\"PAYEE\",\"AMOUNT\"\n\n"; + print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"$bankBalance\"\n\n"; foreach my $ee (@{$solution[1]}) { - print Data::Dumper->Dump($solution[1]); - print "$ee->{date}, $ee->{payee}, $ee->{amount}\n"; + print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"$ee->{amount}\"\n"; } + print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"$total\"\n\n"; } ############################################################################### # -- cgit v1.2.3 From 8ebb54638ca8a57b15126cb11fd6329faf639be5 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" <bkuhn@ebb.org> Date: Thu, 10 Jan 2013 11:25:19 -0500 Subject: Start search from date: easy way to resume searches. Instead of always starting a search from the end date, allow a CLI option that is the data to use for the start of searching (back from the end date). This is useful when resuming a search (since they take a long time). --- contrib/non-profit-audit-reports/bank-reconcilation.plx | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index ada923f3..701f053e 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -125,8 +125,8 @@ my $firstArg = shift @ARGV; my $solver = \&BruteForceSubSetSumSolver; -if (@ARGV < 5) { - print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n"; +if (@ARGV < 6) { + print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n"; exit 1; } if ($firstArg eq '-d') { @@ -134,7 +134,7 @@ if ($firstArg eq '-d') { } else { unshift(@ARGV, $firstArg); } -my($title, $account, $endDate, $bankBalance, @mainLedgerOptions) = @ARGV; +my($title, $account, $endDate, $startSearchFromDate, $bankBalance, @mainLedgerOptions) = @ARGV; $bankBalance = ParseNumber($bankBalance); @@ -166,7 +166,7 @@ my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month" die "Date calculation error on $endDate" if ($err); -my $startDate = ParseDate($endDate); +my $startDate = ParseDate($startSearchFromDate); my @solution; while ($startDate ge $earliestStartDate) { @@ -179,7 +179,7 @@ while ($startDate ge $earliestStartDate) { print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE; my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', - '-b', $formattedStartDate, '-e', $endDate, + '-b', $formattedStartDate, '-e', $startSearchFromDate, '-F', '"%(date)","%C","%P","%t"\n', 'reg', "/$account/"); -- cgit v1.2.3 From 11639785bba1b97600e9b06a85be248e8d2d7688 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" <bkuhn@ebb.org> Date: Sun, 27 Jan 2013 20:24:07 -0500 Subject: Improve spreadsheet and debugging output. --- contrib/non-profit-audit-reports/bank-reconcilation.plx | 16 ++++++++++------ 1 file changed, 10 insertions(+), 6 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 701f053e..5b8d3d6f 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -206,20 +206,24 @@ while ($startDate ge $earliestStartDate) { ConvertTwoDigitPrecisionToInteger($differenceSought), \&ConvertTwoDigitPrecisionToIntegerInEntry); if ($VERBOSE) { - use Data::Dumper; - print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n", - Data::Dumper->Dump(\@solution); + if ($solution[0]) { + use Data::Dumper; + print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n", + Data::Dumper->Dump(\@solution); + } else { + print STDERR "No Solution Found. :(\n"; + } } last if ($solution[0]); } if ($solution[0]) { print "\"title:$formattedEndDate: $title\"\n\"BANK RECONCILATION: $account\",\"ENDING\",\"$formattedEndDate\"\n"; print "\n\n\"DATE\",\"CHECK NUM\",\"PAYEE\",\"AMOUNT\"\n\n"; - print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"$bankBalance\"\n\n"; + print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"\$$bankBalance\"\n\n"; foreach my $ee (@{$solution[1]}) { - print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"$ee->{amount}\"\n"; + print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"\$$ee->{amount}\"\n"; } - print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"$total\"\n\n"; + print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"\$$total\"\n\n"; } ############################################################################### # -- cgit v1.2.3 From afe912f163da2259f29bfd3ac3f5bfbc50190156 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" <bkuhn@ebb.org> Date: Sun, 27 Jan 2013 20:24:34 -0500 Subject: Searching is better when you can set the begin date, end date and then go back from begin date. --- contrib/non-profit-audit-reports/bank-reconcilation.plx | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 5b8d3d6f..2a3d0d38 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -125,8 +125,8 @@ my $firstArg = shift @ARGV; my $solver = \&BruteForceSubSetSumSolver; -if (@ARGV < 6) { - print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n"; +if (@ARGV < 7) { + print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <END_SEARCH_TO_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n"; exit 1; } if ($firstArg eq '-d') { @@ -134,7 +134,7 @@ if ($firstArg eq '-d') { } else { unshift(@ARGV, $firstArg); } -my($title, $account, $endDate, $startSearchFromDate, $bankBalance, @mainLedgerOptions) = @ARGV; +my($title, $account, $endDate, $startSearchFromDate, $endSearchToDate, $bankBalance, @mainLedgerOptions) = @ARGV; $bankBalance = ParseNumber($bankBalance); @@ -170,16 +170,16 @@ my $startDate = ParseDate($startSearchFromDate); my @solution; while ($startDate ge $earliestStartDate) { - print STDERR "START LOOP ITR: $startDate $earliestStartDate\n" if ($VERBOSE); $startDate = DateCalc(ParseDate($startDate), ParseDateDelta("- 1 day"), \$err); die "Date calculation error on $endDate" if ($err); my $formattedStartDate = UnixDate($startDate, "%Y-%m-%d"); - print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE; + print STDERR "Testing $formattedStartDate through $endSearchToDate for a total of ", Commify($differenceSought), ": \n" + if $VERBOSE; my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', - '-b', $formattedStartDate, '-e', $startSearchFromDate, + '-b', $formattedStartDate, '-e', $endSearchToDate, '-F', '"%(date)","%C","%P","%t"\n', 'reg', "/$account/"); -- cgit v1.2.3 From e87b6abb7f1c7bfd7a626ba5d27dc03fd48f701b Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" <bkuhn@ebb.org> Date: Sat, 16 Feb 2013 21:28:27 -0500 Subject: Sort solution by date in output. --- contrib/non-profit-audit-reports/bank-reconcilation.plx | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'contrib/non-profit-audit-reports/bank-reconcilation.plx') diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx index 2a3d0d38..7a8da911 100755 --- a/contrib/non-profit-audit-reports/bank-reconcilation.plx +++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx @@ -220,7 +220,7 @@ if ($solution[0]) { print "\"title:$formattedEndDate: $title\"\n\"BANK RECONCILATION: $account\",\"ENDING\",\"$formattedEndDate\"\n"; print "\n\n\"DATE\",\"CHECK NUM\",\"PAYEE\",\"AMOUNT\"\n\n"; print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"\$$bankBalance\"\n\n"; - foreach my $ee (@{$solution[1]}) { + foreach my $ee (sort { $a->{date} cmp $b->{date} } @{$solution[1]}) { print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"\$$ee->{amount}\"\n"; } print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"\$$total\"\n\n"; -- cgit v1.2.3