Perl CWMoney DB hack

I usually use CWMoney to keep track of where I spend my money and to help to stay on budget. However for the last year I have been rather lazy and haven’t been entering my financial data. I keep putting it off because it is tedious.

I had previously asked the developers to add a import feature so I could add *.csv files to the app but nothing happened. I decided to give it a go in Perl.

I used SQLite Browser to help me visualize changes and understand the structure of the database.

The app turns out to be quite simple. The transactions are stored in the “rec_table” and the categories of transactions are stored in the “kind” and “kinds” table.

Screenshot from 2018-10-11 04-57-59.png

I first used SQLite Browser to craft a SQL query to add an entry and checked that it was picked up by the program. After crashing it a few times I figured out which fields are necessary. I then used DBI::SQLite to drive the database.

Using small iterations I was able to get to a point where %80 of my transaction were filtered and entered into the relevant categories. These transactions come from multiple banks with different account types.

I was thinking about adding a dynamic row picker to make the program compatible with any CSV file type, but for now it does it’s job. I couldn’t imagine how many hours it would take to enter those transactions by hand.

Here is the code:

use strict;
use warnings;
use 5.26.01;
use Switch;
use DateTime;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=$ARGV[0]","","");
&main;
sub main {
open(my $FH, "<", $ARGV[1]) or die "Please specifiy db!";
while (<$FH>) {
my @string = split (/,/, $_);
my ($money, $date, $kind, $kinds, $account, $desc, $created, $type, $txtype);
say scalar @string;
# type 1
if (scalar @string > 6) {
$money = $string[1];
$date = convertdate($string[0]);
$account = 1;
$desc = $string[5];
$txtype = $string[4] . $string[5];
$created = time();
#type 2
} elsif (scalar @string == 4) {
$money = $string[1];
$date = convertdate($string[0]);
$account = 1;
$desc = $string[2];
$txtype = $desc;
$created = time();
}
## If a CREDIT (not internal transaction)
if ($txtype =~ m/((Credit|DEPOSIT|INTEREST PAID|REFUND|Refund|MISCELLANEOUS CREDIT|TRANSFER CREDIT|INTER-BANK CREDIT|SALARY))/
&& ($txtype !~ m/Linked/)) {
$type = 2;
$money =~ s/[^0-9.]//g;
$kind = 1;
$kinds = 1;
switch ($txtype) {
case /SALARY/ { $kinds = 1 }
}
insert($money, $date, $kind, $kinds, $account, $desc, $created, $type);
say "Credit $money, $date, $kind, $kinds, $account, $desc, $created, $type";
## If a DEBIT (not an internal transaction)
} elsif ( ($money =~ /-/ ) && ($txtype !~ m/(Linked|OTHER ACCOUNT|rpy)/) ) {
$type = 1;
$money =~ s/[^0-9.]//g;
$kind = 10;
$kinds = 39;
switch ($txtype) {
case /(WOOLWORTHS|COLES|IGA)/ { $kind = 1; $kinds = 3; }
case /(KFC|MCDONALD)/ { $kind = 1; $kinds = 5; }
case /(PAYPAL)/ { $kind = 7; $kinds = 7; $kinds = 42; }
case /(DIGITALOCEAN|AMAYSIM)/ { $kind = 4; $kinds = 15; }
case /(ALDI)/ { $kind = 4; $kinds = 14; }
case /(ALLIANZ)/ { $kind = 9; $kinds = 34 }
case /(LOAN REPAYMENT)/ { $kind = 9; $kinds = 34 }
case /(WW PETROL)/ { $kind = 3; $kinds = 11 }
}
insert($money, $date, $kind, $kinds, $account, $desc, $created, $type);
say "Debit $money, $date, $kind, $kinds, $account, $desc, $created, $type";
} else {
say "Aborting line: $_";
}
}
}
sub insert {
#my ($money, $date, $kind, $kinds, $account, $desc, $created, $type) = @_;
my $insert = $dbh->prepare('INSERT INTO rec_table (
i_money, i_date, i_kind, i_kinds, i_account, i_remark,
i_item, i_create, i_type, i_photo, i_invoice, i_rev1, i_gps
) values ( ?, ?, ?, ?, ?, ?, 1, ?, ?, "", ",", "0", "0,0")');
$insert->bind_param(1, $_[0]);
$insert->bind_param(2, $_[1]);
$insert->bind_param(3, $_[2]);
$insert->bind_param(4, $_[3]);
$insert->bind_param(5, $_[4]);
$insert->bind_param(6, $_[5]);
$insert->bind_param(7, $_[6]);
$insert->bind_param(8, $_[7]);
$insert->execute();
}
# convert date to epoch time
sub convertdate {
my $date = shift;
my (@dt, $et);
if ($date =~ m/\//) {
@dt = split (/\//, $date);
$et = DateTime->new(
year => $dt[2],
month => $dt[1],
day => $dt[0]);
} else {
@dt = split (/ /, $date);
my %months = ( "Jan" => "01",
"Feb" => "02",
"Mar" => "03",
"Apr" => "04",
"May" => "05",
"Jun" => "06",
"Jul" => "07",
"Aug" => "08",
"Sep" => "09",
"Oct" => "10",
"Nov" => "11",
"Dec" => "12" );
$et = DateTime->new(
year => "20$dt[2]",
month => $months{$dt[1]},
day => $dt[0]);
}
my $epoch = $et->epoch;
return $epoch;
}
sub usage {
say "Usage: mod.pl db.dbi transaction.csv";
}
view raw mod_cwmoney.pl hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s