Convert extracts of accounts exported from Credit Suisse online banking (formerly DirectNet) in CSV format to a CSV format suitable for importing in YNAB.

directnet2ynab.pl

#!/usr/bin/env perl

use strict;
use warnings;

use lib qw{ ./Text-CSV/lib };

use Getopt::Long;
use Pod::Usage;
use Text::CSV;
use File::Basename;
use Path::Class::File;

binmode(STDOUT, ":encoding(UTF-8)");

my $help  = 0;
my $man   = 0;
my $in    = undef;
my $out   = undef;
my $credit_card;

GetOptions(
	'in=s'        => \$in,
	'out=s'       => \$out,
	'creditcard'  => \$credit_card,
	'help|?'      => \$help,
	'man'         => \$man
) or pod2usage(2);

pod2usage(2) if $help;
pod2usage(-exitstatus => 0, -verbose => 2) if $man;

unless (defined($in)) {
	pod2usage(1);
}

if (defined($out)) {
	if (-d $out) {
		my $basename = basename($in,'.csv');
		my $file = Path::Class::File->new($out, $basename . '_converted.csv');
		$out = $file;
	}
} else {
	my $basename = basename($in,'.csv');
	$out = $basename . '_converted.csv';
}

# online banking format for account:
# Buchungsdatum,Text,Belastung,Gutschrift,Valutadatum,Saldo

# online banking format for credit card:
# Transaktionsdatum,Beschreibung,Währung,Betrag,Belastung (CHF),Gutschrift (CHF),Buchungsdatum

# YNAB format:
# Date,Payee,Category,Memo,Outflow,Inflow

sub  trim { my $s = shift; if (defined $s) { $s =~ s/^\s+|\s+$//g }; return $s };

my @ynab_header = qw(Date Payee Category Memo Outflow Inflow);
my @imported_rows;
my $csv = Text::CSV->new ( { binary => 1 } )
	or die "Cannot use CSV: " . Text::CSV->error_diag();

open my $fh, "<:encoding(UTF-8)", $in or die "$in: $!";

# the first line is a header line
$csv->getline($fh);


if ($credit_card) {
# import extract of credit card

	my ($prefix, $credit_card, $card_holder);
	while ( my $row = $csv->getline( $fh ) ) {
	# get the line indicating the credit card
		next unless $row->[0] =~ m/^Kreditkarte/;
		($prefix, $credit_card, $card_holder) = split(/\s\s/, $row->[0]);
		last;
	}

	while ( my $row = $csv->getline( $fh ) ) {
		next unless $row->[0] =~ m/^\d{2}\.\d{2}\.\d{4}/;
		my @new_row;
		push @new_row, $row->[0];                         # Date
		push @new_row, $row->[1];                         # Payee
		push @new_row, '';                                # Category
		push @new_row, $credit_card;                      # Memo
		push @new_row, $row->[4] eq '-' ? '' : $row->[4]; # Outflow
		push @new_row, $row->[5] eq '-' ? '' : $row->[5]; # Inflow
		push @imported_rows, \@new_row;
	}
}
else {
# import extract of account
	while ( my $row = $csv->getline( $fh ) ) {
		next unless $row->[0] =~ m/^\d{2}\.\d{2}\.\d{4}/;

		my @new_row;
		my @description = split(",", $row->[1]);
		my ($memo, $payee);

		# Example: Debitkarte Zahlung Verkaufspunkt CHF ,12345678-9 Restaurant Sportplat ,Restaurant Sportplat,vom 05.06.21 um 19:05,Karten-Nr. 12345678-9
		if ($row->[1] =~ m/^Debitkarte Zahlung Verkaufspunkt CHF \,\d{8}\-\d\s.*\,(.*)\,(vom \d{2}\.\d{2}\.\d{2} um \d{2}\:\d{2})/) {
			($memo, $payee) = ($description[0] . $2 . ' Uhr', $1);
		# Example: Debitkarte Zahlung Verkaufspunkt CHF ,10.07.2021 17:16 McDonald's Restaura ,Karten-Nr. 1234 56XX XXXX 7890
		} elsif ($row->[1] =~ m/^Debitkarte Zahlung Verkaufspunkt CHF \,(\d{2}\.\d{2}\.\d{4})\s(\d{2}\:\d{2})\s(.*)\s\,/) {
			($memo, $payee) = ($description[0] . 'vom ' . $1 . ' um ' . $2 . ' Uhr', $3);
		# Example: Debit Zahlung Ausland ,19.07.2021 14:00 CARNISSERIA ARNALL ,Karten-Nr. 1234 56XX XXXX 7890,EUR 7.52 Kurs 1.104293,fixiert am 20.07.21
		} elsif ($row->[1] =~ m/^Debit Zahlung Ausland \,(\d{2}\.\d{2}\.\d{4})\s(\d{2}\:\d{2})\s(.*)\s\,/) {
			($memo, $payee) = ($description[0] . 'vom ' . $1 . ' um ' . $2 . ' Uhr', $3);
		# Example: Debit Zahlung Ausland ,12345678-9 EUR 79.69 SUPER ELS MASOS ,SUPER ELS MASOS,vom 12.07.21 um 12:21,Karten-Nr. 12345678-9,EUR 79.69 Kurs 1.101393,fixiert am 13.07.21,zzgl. Kommission CHF 1.50
		} elsif ($row->[1] =~ m/^Debit Zahlung Ausland \,\d{8}\-\d\s.*\,(.*)\,(vom \d{2}\.\d{2}\.\d{2} um \d{2}\:\d{2})/) {
			($memo, $payee) = ($description[0] . $2 . ' Uhr', $1);
		# Example: Debitkarte Bargeldbezug CHF ,08.07.2021 15:59 ZKB ZH HB Sihlquai ,Karten-Nr. 1234 56XX XXXX 7890
		} elsif ($row->[1] =~ m/^Debitkarte Bargeldbezug CHF \,(\d{2}\.\d{2}\.\d{4})\s(\d{2}\:\d{2})\s(.*)\s\,/) {
			($memo, $payee) = ($description[0] . 'vom ' . $1 . ' um ' . $2 . ' Uhr', $3);
		# Example: Debitkarte Bargeldbezug CHF ,12345678-9 CS Aarau City 6 ,CS Aarau City 6,vom 10.04.21 um 14:10,Karten-Nr. 12345678-9
		} elsif ($row->[1] =~ m/^Debitkarte Bargeldbezug CHF \,\d{8}\-\d\s(.*)\s\,/) {
			($memo, $payee) = ($description[0], $1);
		# Example: TWINT Zahlung ,Coop-1895 Unterentfelden 5035 Untere ,vom 10.04.21 um 15:40,Karten-Nr.,Coop-1895 Unterentfelden,5035 Unterentfelden,IBAN CH0123456789123456789
		} elsif ($row->[1] =~ m/TWINT Zahlung \,(.*)\s,(vom \d{2}\.\d{2}\.\d{2} um \d{2}\:\d{2})/) {
			($memo, $payee) = ($description[0] . $2 . ' Uhr', $1);
		# Exmaple: TWINT Gutschrift ,Max, Muster +417951234567
		} elsif ($row->[1] =~ m/TWINT Gutschrift \,(.*)$/) {
			($memo, $payee) = ($description[0], $1);
		} else {
			($memo, $payee) =  ($description[0], $description[1]);
		}

		push @new_row, trim ( $row->[0] ); # Date
		push @new_row, trim ( $payee );    # Payee
		push @new_row, "";                 # Category
		push @new_row, trim ( $memo );     # Memo
		push @new_row, trim ( $row->[2] ); # Outflow
		push @new_row, trim ( $row->[3] ); # Inflow
		push @imported_rows, \@new_row;
	}
}

$csv->eof or $csv->error_diag();
close $fh;

open $fh, ">:encoding(UTF-8)", $out or die "$out: $!";
$csv->print ($fh, \@ynab_header);
print $fh "\n";
foreach (@imported_rows) {
 $csv->print ($fh, $_);
 print $fh "\n";
}
close $fh or die "$out: $!";

__END__

=pod

=head1 NAME

directnet2ynab.pl - convert Credit Suisse extracts of accounts for import into YNAB

=head1 SYNOPSIS

    directnet2ynab.pl -in=/path/to/export.csv -out=/path/to/converted.csv [-creditcard] [-help] [-man]

     Options:
       -in              path to CSV being converted
       -out             output file
       -creditcard
       -help            brief help message
       -man             full documentation

=head1 OPTIONS

=over 8

=head2 B<-in>

Path to the extract of account exported from L<DirectNet|http://directnet.com> in CSV format,
 the one you want to convert for importing in L<YNAB|https://www.youneedabudget.com/>. This option is
B<required>.

=head2 B<-out>

A path and file name to which the converted output should be saved. Defaults to B<out.csv>.

=head2 B<-creditcard>

Use this flag if you want to convert an extract of account in CSV format from a credit card.

=head2 B<-help>

Print a brief help message and exits.

=head2 B<-man>

Prints the manual page and exits.

=back

=head1 DESCRIPTION

This script lets you convert extracts of accounts exported in CSV format from
L<DirectNet|http://directnet.com> to a CSV format suitable for import in
L<YNAB|https://www.youneedabudget.com/>.

=head1 AUTHOR

Matías E. Fernández

=head1 COPYRIGHT AND LICENSE

This software is copyright (c) 2013 by Matías E. Fernández.

This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.

=cut