So, banking data is quite interesting. If one could get a hold of your credit card transactions in an easy way, that would be sweet. But, its not that easy.

My bank, DNBNor, has a feature that you can download either an excel document, or a CSV formatted dump of the transactions against one of your accounts on a monthly basis. The programmers choice would be CSV, but I found the CSV exports to be spotty, so excel it it.

But I don’t want to my analysis in Excel, so Perl to the rescue. The following snippet takes an Excel document, exported from DNBNor, as it’s first argument, and gives you an JSON-representation of the data to STDOUT. Should be quite easy to manipulate these in any language of your choice.

#!/usr/bin/perl

use strict;
use utf8;
use warnings;
use Switch;
use Spreadsheet::ParseExcel;
use JSON::XS;

my $excel = Spreadsheet::ParseExcel->new;

die "Need an xls-file containing DNB Nor transaction list as frowst argument\n" unless @ARGV;

my $doc = $excel->Parse($ARGV[0]) or die('Could not parse excel file\n', $@);

my @transactions = ();

for(my $sheet=0; $sheet < $doc->{SheetCount} ; $sheet++) {
    my $current_sheet = $doc->{Worksheet}[$sheet];
    for(my $row = $current_sheet->{MinRow}; defined $current_sheet->{MaxRow} && $row <= $current_sheet->{MaxRow}; $row++) {
        # Skip header row
        if ($row == $current_sheet->{MinRow}){
            next;
        }
        my %transaction = ();
        for(my $column = $current_sheet->{MinCol}; defined $current_sheet->{MaxCol} && $column <= $current_sheet->{MaxCol} ; $column++) {
            my $cell = $current_sheet->{Cells}[$row][$column];
            if (defined($cell)){
                switch ($column){
                    case 0 { $transaction{'date'} = $cell->Value || undef; }
                    case 1 { $transaction{'description'} = $cell->Value || undef; }
                    case 2 { $transaction{'interest_date'} = $cell->Value || undef; }
                    case 3 {
                        $transaction{'debit'} = $cell->Value || undef;
                        $transaction{'credit'} = '0.00';
                        }
                    case 4 {
                        $transaction{'credit'} = $cell->Value || undef;
                        $transaction{'debit'} = '0.00';
                        }
                }
            }
        }
        push @transactions, \%transaction;
    }
}

my $json = JSON::XS->new->utf8->encode({'transactions' => \@transactions});
print STDOUT $json . "\n";

exit 0;

GIST available at https://gist.github.com/ea0c6ef9e712e2caec88

Requires Spreadsheet::Excel and JSON::XS - both available at CPAN.

Have fun :-)