E*Trade Portfolio Import solution: csv->qif

[Keith Iosso]

Derek Atkins <warlord <at> MIT.EDU> writes:
> 
> >    print "O$commission\n";
> 
> the commmission should be omitted if it's 0.  So I would change
> this this to:
> 
>      print "O$commission\n" if ($commission != 0);
> 
snip
> 
> Thanks for the submission.  Hopefully people will find it useful.
> 
> -derek

Thanks, Derek.  I agree.  A night's sleep provided more ideas to "improve"
the script also.  The revised script follows:

#!/usr/bin/perl
#
# Convert one specific format of investment CSV file into QIF. 
# Specifically, this works with one CSV file generated by E*Trade.  This 
# comes from Accounts->Records->Trade Confirms->Download as of April 8, 2004.
#
# Input:
#"Account Number","Trade Date",Symbol,Cusip,Action,Quantity,Price,Amount,"Net
Amount"
#1234-5678,02-02-2004,RHAT,1-11-1,Buy,100.00000,17.58000,1758.00000,1767.99000
#...

# Output:
#!Type:Invst (one per file)
#D02/02/2004 (all the rest are per transaction)
#NBuy
#YRHAT
#I17.5800
#Q100.00000
#O9.99000
#T1767.99000
#^
#
# invocation: csv2qif.perl <inputfilename.csv >outputfilename.qif
#
# tested on Fedora core 1 with gnucash 1.8.8
#

$lastfldndx = 8;
print "!Type:Invst\n";

$line = <>;	# lose first line after error checks, assuming a comment
$lineno = 1;
@field = split(/,/, $line);		# break up line on commas
warn "line $lineno: unexpected # of fields: $#field" 
   if ( $#field != $lastfldndx ); 
warn "unexpected first header field: $field[0]" 
   if ( $field[0] ne '"Account Number"' );

while (<>) {
   $lineno++;
   @field = split(/,/);		# break up line on commas
   if ( $#field != $lastfldndx ) {
      warn "line $lineno: unexpected # of fields"; 
   } else {
      $field[1] =~ s|-|/|g;	# probably unneeded, replace dashes in date
      print "D$field[1]\n";	# date
      print "N$field[4]\n";	# action
      print "Y$field[2]\n";	# security
      print "M$field[2]\n";	# memo
      print "I$field[6]\n";	# price
      print "Q$field[5]\n";	# quantity of shares
      $commission = sprintf("%.2f", abs($field[8] - $field[7]));
      print "O$commission\n" if ($commission != 0);	# commission, if not 0
      print "T$field[8]";	# total, \n is apparently included in file
      print "^\n";		# end of entry
   }
}