======
#ofx2csv.tcl
# -*- tcl -*-
# The next line is executed by /bin/sh, but not tcl \
exec wish "$0" ${1+"$@"}
# This program will process a OFX 1.x file that can typically be
# obtained from a bank or credit card.
# The object of the exercise is to produce a CSV type file from
# the transactions contained in the OFX file.
# I have modeled the output to be something like I get from
# processing a QIF file from Quicken. ie I have fields
# Date Amount Tranid Source Memo Status Category
# The first five fields can be generally be extracted from the
# OFX file. Note that the Memo field might be empty or contain
# overfill from the source field or some actual info.
# I always use an "X" for the status to indicate a cleared
# transaction.
# The bank/credit card cannot assign a category so the user
# has to supply this field. Note that the code always uses
# "UNKNOWN" as the initial contents of this field.
# I use the "Category" field as a foreign key when I insert the
# CSV field into my Postgresql Database so it is important that
# the value actually matches an element in the appropriate table.
# To prevent mistakes I have placed all of the choices for the
# category field into a listbox.
# The basic strategy is that the user will double click one of the UNKNOWN
# fields to select it and then double click the appropriate category
# to use from the listbox of choices. This will cause the UNKNOWN
# value to be replaced with the users choice of category.
# After editing the CSV file the user can then save the file and
# later import the CSV file into their favorite database.
# Enjoy!
# Author: Jerry LeVan
# Env: Recent TCL/TK
# Date: Feb 18, 2006
# Version: 0.7
# Version: 0.8 Feb 2, 2007
# This is free ware use as you like (but keep my name...)
# Possible transaction tags
# Value = Enum [CREDIT | DEBIT | INT | DIV | FEE | SRVCHG | DEP
#| ATM | POS | XFER | CHECK | PAYMENT | CASH | DIRECTDEP | DIRECTDEBIT | REPEATPMT | OTHER]
package require Tk
package require Tktable
set version 0.8
set sep |
# items cannot contain white space, hence the quotes
set menuitems {
Auto
Auto:Fuel
Auto:Service
"Bank Charges"
Charity
"Check Deposit"
"[Checking Bank One]"
Clothing
Computer
"Credit Card"
"Customer Deposit"
Dining
Education
Entertainment
Gifts
Groceries
"Home Repair"
"Home Repair:Yard Work"
Household
Insurance:Auto
Insurance:Home
"Interest Earned"
"Interest Paid"
[MasterCard-Marijo]
Medical
Miscellaneous
"Mortgage Int"
Pension
Recreation
Salary
"[Savings at Bank One]"
Taxes
Taxes:Federal
Taxes:Other
Taxes:Property
Taxes:State
Telephone
Travel
Utilities
"Utilities:Cable TV"
"Utilities:Gas & Electric"
Utilities:Water
"[Visa - Jerry]"
}
# Change values to be more in line with Quicken
proc cleanUp { key value } {
set thevalue $value
if { $key eq "DTPOSTED" } {
# fiddle with the date
set myDate [string range $value 0 7]
set thevalue ""
append thevalue [string range $myDate 0 3] - [string range $myDate 4 5] - [string range $myDate 6 7]
}
if {$key eq "TRNTYPE" && $value eq "CREDIT" } {set thevalue "DEP"}
if {$key eq "TRNTYPE" && $value eq "DEBIT" } { set thevalue "WITHD"}
if {$key eq "TRNAMT" } { set thevalue [format "%.2f" $value]}
return $thevalue
}
# Generate and process one complete transaction
proc processOne { trans row} {
global output sep table_data
array set items {
date "" amount "" tranid "" source "" memo "NONE" status "X" category "UNKNOWN"
}
set items(row) [expr {$row + 1 }]
foreach obj $trans {
#puts $obj
regexp {.*<(.*)>(.*)\}*} $obj all key value
set value [string trim $value]
set value [cleanUp $key $value]
switch -exact -- $key {
TRNTYPE { set items(tranid) $value }
DTPOSTED { set items(date) $value }
TRNAMT { set items(amount) $value }
NAME { set items(source) $value }
MEMO { set items(memo) $value }
CHECKNUM { set items(tranid) $value}
}
}
set col -1
foreach var {row date amount tranid source memo status category} {
set table_data($row,$col) $items($var)
incr col
}
}
# Split the list of tranactions and process each one
proc analyzeOneThing { transaction row } {
# split on new line
set tranlist [split $transaction \n ]
# pitch clutter of first tag, last tag
set tranlist [lrange $tranlist 1 end-1]
foreach item [list $tranlist] {
processOne $item $row
}
}
# Get the OFX file to process.
proc openFile {} {
set fileName [tk_getOpenFile -parent .frm.t]
if { $fileName != "" } {
set f [open $fileName r]
set theFile [ read $f ]
close $f
return [list $fileName $theFile]
}
return ""
}
# Save the contents of the Text Widget to a file.
proc saveFile {} {
global table_data sep
set fname [tk_getSaveFile -initialdir "~" \
-defaultextension ".csv"]
if { $fname != "" } {
if { [ catch {
set f [open $fname "w"]
set rows [.frm.t cget -rows]
set cols [.frm.t cget -cols]
#puts "$rows $cols"
incr rows -2
incr cols -2
for {set j 0} {$j<=$rows} {incr j} {
set line ""
for { set i 0 } {$i <=$cols } { incr i } {
if { $i==$cols } {
append line $table_data($j,$i)
} else {
append line $table_data($j,$i) $sep
}
}
puts $f $line
}
close $f
tk_messageBox -message "Write Completed Ok." -icon info
} result] } {
tk_messageBox -message "File Save of $fname failed. Reason : $result" -type ok
}
}
}
# Get the file to process, extract the transactions and process each one...
proc processFile {} {
global table_data
set tmp [openFile]
if { $tmp == "" } { exit }
set f [lindex $tmp 0]
set theFile [lindex $tmp 1]
focus -force .frm.t
# Empty the text widget
#.frm.t delete 1.0 end
#
# Break out the list of transactions...
#
set items [regexp -all -inline {.*?} $theFile]
if { $items == "" } {
tk_messageBox -message "No Transactions Found in $f" -type ok -icon info
}
set rowCnt [llength $items]
.frm.t configure -rows [incr rowCnt]
array set table_data { -1 date -1 amount -1 tranid -1 source -1 memo -1 status -1 category }
set row 0
foreach chunk $items {
analyzeOneThing $chunk $row
incr row
}
}
proc make_table { parent } {
global table_data
global menuitems
global colHeaders
table $parent.t \
-variable table_data \
-yscrollcommand "$parent.sy set" \
-xscrollcommand "$parent.sx set" \
-titlerows 1 \
-titlecols 1 \
-roworigin -1 \
-colorigin -1 \
-colstretchmode last \
-invertselected 0 \
-selectmode browse \
-selecttype cell \
-padx 4 \
-coltagcommand colproc \
-autoclear 1 \
-cols 8 -rows 10
$parent.t config -bg white
scrollbar $parent.sx -orient horizontal -command [list $parent.t xview]
scrollbar $parent.sy -command " $parent.t yview"
set colnum 0
foreach header $colHeaders {
set table_data(-1,$colnum) $header
incr colnum
}
$parent.t tag config title -bg lightblue
$parent.t tag config title -fg black
$parent.t tag configure sel -bg blue
$parent.t tag configure sel -fg red
$parent.t tag config anchorcenter -anchor c
$parent.t tag config anchorwest -anchor w
$parent.t tag config anchoreast -anchor e
$parent.t tag col anchoreast -1
$parent.t tag row anchorcenter -1
$parent.t width -1 5
# build a popup menu...
menu $parent.t.menu -tearoff 0
set cnt 0
foreach thing $menuitems {
incr cnt
if { $cnt == 20 } {
set brk 1 ; set cnt 0
} else { set brk 0 }
$parent.t.menu add command -columnbreak $brk -label "$thing" \
-command [list setItem $thing ]
}
return $parent.t
}
proc setItem newString {
global lastx lasty
.frm.t set [.frm.t index @$lastx,$lasty] $newString
}
proc colproc {col} {
if {$col ==-1 || $col ==1 } {
return anchoreast
} else { return anchorwest }
}
# Build the text widget for displaying the results as a CSV file
proc BuildGui {} {
global menuitems categoryCol lastx lasty version
# Create a "menu bar"
menu .menubar
# make it the menu for the application window
. config -menu .menubar
# create the File menu
menu .menubar.file -tearoff 0
# add this menu to the menubar
.menubar add cascade -label "File" -menu .menubar.file
# add the menu items with actions
# add a scripts menu
menu .menubar.replace -tearoff 0
.menubar add cascade -label "Category Choices" -underline 0 -menu .menubar.replace
# build a table object and position it in the frame
set f [ frame .frm ]
set theTable [make_table $f]
pack $f -fill both -expand yes
pack .frm.sx -side bottom -fill x
pack .frm.sy -side right -fill y
pack $theTable -side top -fill both -expand yes
# populate the menus
.menubar.replace add command -label "Replace With..." -command showReplaceInfo
.menubar.file add command -label "Open OFX File..." -command processFile
.menubar.file add command -label "Save As..." -command saveFile
.menubar.file add command -label "Quit" -command exit
#Support the MouseWheel
bind .frm.t { .frm.t yview scroll -5 units }
bind .frm.t { .frm.t yview scroll +5 units }
bind .frm.t {
if { %D < 0} {
.frm.t yview scroll +5 units
} else {
.frm.t yview scroll -5 units
}
}
# bind the popup menu
bind .frm.t {
if { [ .frm.t index @%x,%y col ] != $categoryCol } return
set lastx %x
set lasty %y
tk_popup .frm.t.menu %X %Y
#puts "x=%x, y=%y\nX=%X, Y=%Y\n cell: [.frm.t get @%x,%y]\n @%x,%y\n"
#puts "row: [.frm.t index @%x,%y]\n"
}
wm title . "Ofx2Csv $version"
}
# Action routine to replace field in Text Widjet with selected
# field in the list box.
proc changeItem {theTable } {
global table_data
# Get the selected item
set tabIndex [$theTable curselection]
if { $tabIndex == ""} {
tk_messageBox -title OFX2CSV \
-message "No Replacement Selected." \
-type ok \
-icon error
return
}
set theItem [$theTable get $tabIndex]
set theCell [.frm.t tag cell sel ]
if {$theCell ne ""} {
set table_data($theCell) $theItem
focus -force .frm.t
} else {
tk_messageBox -title OFX2CSV \
-message "Nothing Selected in CSV File" \
-type ok -icon error
}
}
# Build and display the list box which contains all of
# the foreign keys.
# Not really needed anymore...
proc showReplaceInfo {} {
global menuitems
destroy .showList
toplevel .showList
listbox .showList.choices -width 30 -height 10 \
-borderwidth 3 \
-exportselection 0 \
-relief groove \
-yscrollcommand {.showList.sy set }
scrollbar .showList.sy -command [list .showList.choices yview]
frame .showList.bframe
pack .showList.bframe -side bottom
button .showList.bframe.showtab -text "Edit Item" \
-command [list changeItem .showList.choices ]
button .showList.bframe.exit -text "Exit Edit Selection" -command {destroy .showList }
pack .showList.bframe.showtab -side left
pack .showList.bframe.exit -side left
pack .showList.choices -side left -expand true -fill both -padx 4 -pady 4
pack .showList.sy -side right -fill y -padx 4
bind .showList.choices [list changeItem .showList.choices ]
# load the listbox
foreach item $menuitems {
.showList.choices insert end $item
}
}
#### Start Here ####
# table headers
set colHeaders { date amount tranid source memo status category }
# This is the column we have to be careful about
# and which the popup menu operates.
set categoryCol 6
# lastx and lasty will be the coordinates of the button click
# that summons the popup menu.
set lastx 0
set lasty 0
# Set up the interface
BuildGui
======
<>Category Business