Taxes and TXF file import

As April 15th rolls around a young man's fancy turns to ... TAXES ! Not really but it does get everyone's attention, at least in the US. One thing I do, given the rise of internet trading is try my hand at buying and selling of stocks. I usually do not brag but I have made a small fortune on the stock market; unfortunately, I started with a large fortune. However, given how easy it is to trade stocks and to have stocks that give dividends to automatically re-invest when it comes time to fill out the old Schedule D the number of transactions is extraordinarily high. Tax programs like Turbo Tax will let you enter your buys and sells one at a time or import it from its own products or from a .txf (.TXF) file. I was faced with 70+ transactions to input. I did some research and was able to get the format for a .txf file at this link Txf format v 041 . Since I had done no TclOO I decided to develop a TclOO program to convert a Gains and Losses report into a txf file . I could then save alot of work by simply importing it into Turbo Tax. Rather than downloading an excel file then exporting to cvs, I simply highlighted the data in my browser and cut and paste the data into a text file. I used this text file as input. Some columns of data are not needed but I put them in the input file to make the cutting and pasting easier. Some example data is below. Note that you can easily modify the script to take any input format as long as you provide the data needed to build a StockTrans Object.


Column names

Stock Shares date bought price basis Order date sold sell amt total gain/(loss) gainterm

Example Input

  X        1000           02/20/2008 6.7884  6798.39 S 03/26/2008        7.231          7220.9   422.51  Short
  X        200           09/18/2008 5.3083  1071.65 S 09/18/2008        5.8417         1158.34  86.69   Short
  X        10.41807 02/01/2006 11.22          116.89  S 11/03/2008        4.463          46.37          (70.53)  Long
  X        28.67448 08/01/2005 11.95          342.66         S 11/03/2008        4.463          127.62  (215.04) Long

...


Expected Output

V041
AMy Txf package
D04/08/2009
^
TD
N321
C1
L1
P1000 X
D02/20/2008
D03/26/2008
$6798.39
$7220.9
^
TD
N321
C1
L1
P200 X
D09/18/2008
D09/18/2008
$1071.65
$1158.34
^
TD
N323
C1
L1
P10.41807 X
D02/01/2006
D11/03/2008
$116.89
$46.37

....

The Script

The design is simple a StockTrans Class to make StockTrans objects for the TXF class. You may recognize the StockTrans class as the Builder design pattern. To parse input, I read each line and interpret each line as a list parsable by lassign. If you have a cvs file you would change this input parser. The StockTrans object is constructed then added to the TXF object . Once all input is read then the TXF objects toString method is called and written to a file. As TurboTax is a windows program I also set the file to have crlf line ends. I also have several reporting and filtering methods so that I can verify the totals vs the forms sent into the government. In the script, below I output the gain and loss for both long term and short term investments or if a stock symbol was given on the command line the long term and short term gain or loss for that stock.

#!/bin/sh
 # the next line restarts using wish \
 exec /opt/usr/bin/tclsh8.5  "$0" ${1+"[email protected]"}
 
if { [ catch {package require TclOO } err ] != 0 } {
    puts stderr "Unable to find package TclOO ... adjust your auto_path!";
}
oo::class create StockTrans { 
    constructor {stock shares buydate basisamt selldate sellamt gainterm } {
        my variable data 
        #make sure there is data in the critical variables
        array set data { stock "" shares 0 buydate "" 
            basisamt 0.00 selldate "" sellamt 0.00 gainterm "" }
        foreach item {stock shares buydate basisamt selldate sellamt gainterm} {
            set data($item) [set $item]
        }
        switch -glob -- $data(gainterm) {
            l* -
            L* {
                set data(gainterm) long
            }
            s* -
            S* {
                set data(gainterm) short
            }
            default {
                set data(gainterm) ""
            }
        }
    }
    method setValue { args } {
        my variable data
        set opts { -stock -shares -buydate -basisamt -selldate -sellamt -gainterm }
        array set localdata $args
        foreach idx [array names localdata ] {
            if { $idx ni $opts } {
                error "$idx is not a valid option must be one of [join $opts , ]"
            }
            set data([string range $idx 1 end ]) $localdata($idx)
        }
    }
    method calcGain {} {
        my variable data
        return [ expr { $data(sellamt) - $data(basisamt)} ] 
    }
    method get { key } {
        my variable data
        set retval {}
        set opts { -stock -shares -buydate -basisamt -selldate -sellamt -gainterm }
        if { $key ni $opts } {
            error "$key is not a valid option must be one of [join $opts , ]"
        }
        return $data([string range $key 1 end ])
    }
    method getCode { item} { 
        my variable data
        set opts { -stock -shares -buydate -basisamt -selldate -sellamt -gainterm }
        switch -exact -- $item {
            -shares -
            -stock {
                return "P$data(shares) $data(stock)"
            }
            -buydate -
            -selldate {
                return "D$data([string range $item 1 end ])"
            }
            -sellamt -
            -basisamt {
                return "\$$data([string range $item 1 end ])"
            }
            -gainterm {
                switch  -exact -- $data(gainterm) {
                    long {
                        return N323        
                    } 
                    short {
                        return N321
                    }
                    default {
                        return N673
                    }
                }
            }
        }
        return $retval
    }
    #equal filter method where item is one of -stock -shares -buydate -basisamt -selldate -sellamt -gainterm 
    method eq { item key } {
        my variable data
        set item [string range $item 1 end ]
        return [string equal [string tolower $data($item)] [string tolower $key ]]
    }
    #greater than filter method where item is one of -stock -shares -buydate -basisamt -selldate -sellamt -gainterm
    method gt { item key } {
        my variable data
        set item [string range $item 1 end ]
        if {[string is digit $data($item) ] } {
            return [expr ($data($item) > $key) ? 1 : 0 ]
        } else {
            return [expr ([string compare [string tolower $data($item)] [string tolower $key]] == 1 ) ? 1 : 0 ]
        }
    }
    #greater than or equal filter method where item is one of -stock -shares -buydate -basisamt -selldate -sellamt -gainterm
    method ge { item key } {
        my variable data
        set item [string range $item 1 end ]
        if {[string is digit $data($item) ] } {
            return [expr ($data($item) >= $key) ? 1 : 0 ]
        } else {
            return [expr ([string compare [string tolower $data($item) ] [string tolower $key ] ] >= 0 ) ? 1 : 0 ]
        }
    }
    # less than filter method where item is one of -stock -shares -buydate -basisamt -selldate -sellamt -gainterm
    method lt { item key } {
        my variable data
        set item [string range $item 1 end ]
        if {[string is digit $data($item) ] } {
            return [expr ($data($item) < $key) ? 1 : 0 ]
        } else {
            return [expr ([string compare [string tolower $data($item)] [string tolower $key]] == -1 ) ? 1 : 0 ]
        }
    }
    # less than or equal filter method
    method le { item key } {
        my variable data
        set item [string range $item 1 end ]
        if {[string is digit $data($item) ] } {
            return [expr ($data($item) <= $key) ? 1 : 0 ]
        } else {
            return [expr ([string compare [string tolower $data($item)] [string tolower $key]] <= 0 ) ? 1 : 0 ]
        }
    }
    #not equal filter method
    method ne { item key } {
        my variable data
        set item [string range $item 1 end ]
        return ![string equal [string tolower $data($item)] [string tolower $key ]]
    }
    method toString {} {
        set retval "TD\n[[self] getCode -gainterm]\nC1\nL1\n"
        foreach item  { -stock -buydate -selldate -basisamt -sellamt } {
            append retval "[ [self] getCode $item ]\n"
        }
        append retval "^"
        return $retval
    }
    destructor {
    }
}
oo::class create TXF {
    constructor { args } {
        my variable opts
        my variable translist 
        set translist {} 
        array set opts [list \
                            -exportdate    [clock format [clock seconds ] -format "%D" ] \
                            -software      "Tcl Txf package" \
                            -version       "041" \
                           ]
        array set opts $args
        set opts(-version) 041; # only version 041 supported currently
    }
    method addTransaction { transobj } {
        my variable translist
        lappend translist $transobj
    }
    # static method ?
    method  getMatchingTransactions { translist item filterop key } {
        puts "getMatchingTransactions"
            set retval {}
        foreach obj $translist {
            if { [$obj $filterop $item $key ] } {
                lappend retval $obj
            }
        }
        return $retval;
    }
    method getTransactions { gainterm args } {
        my variable translist
        switch -exact -- $gainterm {
            short -
            long {
                set objlist [ my getMatchingTransactions $translist  -gainterm eq $gainterm ]
            }
            all {
                set objlist [ my getAllTransactions ]
            }
            default {
                error "gainterm $gainterm is not valid must be short,long or all"
            }
        }
        puts "For gainterm = $gainterm and args \"$args\" ([llength $args]) the objlist has [llength $objlist ] objects"
        while { [llength $args ] && [ llength $objlist ] } {
            set args [ lassign $args item key]
            puts "geting all objlist that match $item eq $key"
            set objlist [ my getMatchingTransactions $objlist $item eq $key]
        }
        return $objlist
    }
    method getAllTransactions { } {
        my variable translist
        return $translist; # no defensive copy shame!
    }
    method toString {} {
        my variable translist
        my variable opts
        set retval ""
        append retval "V$opts(-version)\nA$opts(-software)\nD$opts(-exportdate)\n^\n"
        foreach obj $translist {
            append retval [$obj toString ]\n
        }
        return $retval;
    }
    method count {} {
        my variable translist
        return [llength $translist ];
    }
    destructor {
        my variable translist
        foreach obj $translist {
            destroy $obj
        }
    }
}
#procs to use the TXF object 
proc printTXF { txfobj fname { eol crlf } } {
    if { [catch {open $fname w 0666 } err ] != 0 } {
            error "Unable to open $fname because $err"
    }
    fconfigure $err -translation $eol
    puts $err [${txfobj} toString] 
    catch { close $err }
}

proc calcSum { txfobj calcitem gainterm args } {
    set objlist [ eval $txfobj getTransactions  $gainterm $args ]
    set sum 0
    foreach obj $objlist {
        set sum [ expr { $sum + [$obj get $calcitem  ] } ]
    }
    return $sum
}


proc calcGainLoss { txfobj gainterm args } { 
    set objlist [ eval $txfobj getTransactions  $gainterm $args ]
    set sum 0
    foreach obj $objlist {
        set sum [ expr { $sum + [$obj calcGain ] } ]
    }
    return $sum
}

######### MAIN ##########
if { $argc !=3 && $argc != 2 } {
puts "Usage: $argv0 inputfile outputfile ?StockSymbol?"
exit 1
}

set txf [ TXF new -software "My Txf package" ]
set fd [open [lindex $argv 0 ] "r" ]
set linecount 0 
while { ![eof $fd ] } {
    gets $fd line
    set excesscol  [lassign $line stock shares buydate Price \
                        basisamt Order selldate sellprice sellamt gainloss gainterm ]
    incr linecount
    if { [llength $excesscol ] || [string length $gainterm ] == 0  } {
        puts stderr "line $linecount is invalid : \"$line\""
        puts stderr "skipping line $linecount"
        continue;
    }
    set obj [StockTrans new $stock \
                 $shares \
                 $buydate \
                 $basisamt \
                 $selldate \
                 $sellamt \
                 $gainterm  ]
    $txf addTransaction $obj
}
# output the TXF file 
printTXF $txf [lindex $argv 1 ]
# report something to user on stdout 
if { $argc == 2 } { 
set stk "All stocks"
set stkcmd ""
} else { 
    set stk [lindex $argv 2 ]
    set stkcmd "-stock $stk"
} 
puts  "Report for $stk : " 
puts  "Long Term Gain/(Loss) Short Term Gain/(Loss) Total Gain/(Loss)"
set lg [eval calcGainLoss $txf  long  $stkcmd ]
set sg [eval calcGainLoss $txf  short $stkcmd ]
set ag [eval calcGainLoss $txf  all   $stkcmd ]
if { $lg < 0 } {
    puts -nonewline [format "(%7.2f)     " $lg ]
} else {
    puts -nonewline [format "%7.2f       " $lg ]
} 
if { $sg < 0 } {
    puts -nonewline [format "(%7.2f)     " $sg ] 
} else {
    puts -nonewline [format "%7.2f       " $sg ]
} 
if { $ag < 0 } {
    puts [format "(%7.2f)     " $ag ]
} else {
    puts [format "%7.2f       " $ag ]
} 

puts "Long Term Sell Amount : [format "%7.2f" [ calcSum $txf -sellamt long  ]]"
puts "Short Term Sell Amount: [format "%7.2f" [ calcSum $txf -sellamt short ]]"
puts "           Sell Amount: [format "%7.2f" [ calcSum $txf -sellamt all   ]]"