I’ve recently started to use Quicken an application to keep track of your spending by allowing you to tag items and categorize them, schedule payments and bills and generally have a better overview of your accounts.
If your American Quicken seems to have agreements with all the major banks to allow you to download and upload files directly to their sites making it easy to stay up to date. Being British this was no help to me and so I was stuck with downloading files and importing them manually into Quicken. This worked just fine with my Natwest account as they offer an OFX file of your statements that Quicken can deal with but unfortunately LloydsTSB don’t! They’ve not kept up with the times and only offer the options of CSV or QIF, QIF being the pre 2001 Quicken file. This left me we a bit of a problem as I was enjoying using Quicken to take control of my finances and wanted to include my second account and so I set out to write a conversion tool to take my CSV file and produce an OFX one.
require_once($_POST['php']); // include the bank file submitted
$bank = new Bank(); // initisialise the bank file
$transactions = $bank->transArray($_POST['csv']); // send file submitted to the bank function for putting into an array.
$institute = $_POST['institute']; // get the institute number being used
$currency = $_POST['currency']; // get the currency being used
$numberoftrans = $_POST['numf']; // get the number of items per transaction for the bank
$accounttype = $_POST['accountt']; // get the account type for the bank
if ($_POST['sortc'] == "first"){ // get the sort code from the array
$sortcode = $transactions[(int)$_POST['sortr']]; // look at the first row of the array
}else{
$sortcode = $transactions[count($transactions)-($numberoftrans - (int)$_POST['sortr'])]; // look at the last row of the array
}
if ($_POST['accountc'] == "first"){ // get the account number from the array
$accountnumber = $transactions[(int)$_POST['accountr']]; // look at the first row of the array
}else{
$accountnumber = $transactions[count($transactions)-($numberoftrans - (int)$_POST['accountr'])]; // look at the last row of the array
}
if ($_POST['startc'] == "first"){ // get the start date from the array
$startdate = $transactions[(int)$_POST['startr']]; // look at the first row of the array
}else{
$startdate = $transactions[count($transactions)-($numberoftrans - (int)$_POST['startr'])]; // look at the last row of the array
}
if ($_POST['endc'] == "first"){ // get the end date from the array
$enddate = $transactions[(int)$_POST['endr']]; // look at the first row of the array
}else{
$enddate = $transactions[count($transactions)-($numberoftrans - (int)$_POST['endr'])]; // look at the last row of the array
}
if ($_POST['finalc'] == "first"){ // get the final balance from the array
$balance = $transactions[(int)$_POST['finalr']]; // look at the first row of the array
}else{
$balance = $transactions[count($transactions)-($numberoftrans - (int)$_POST['finalr'])]; // look at the last row of the array
}
$filename = "./robmcghee" . $sortcode . $accountnumber . ".ofx"; // create a new OFX for the user
$fp = fopen($filename,"w"); // open the file for write access
/*
Below is a bog standard layout for an OFX file that Quicken will except
The data from the csv file we have parsed and the data entered onto the front screen will be entered in
*/
fwrite($fp, "OFXHEADER:100\n"); // header of the file
fwrite($fp, "DATA:OFXSGML\n");
fwrite($fp, "VERSION:102\n");
fwrite($fp, "SECURITY:NONE\n");
fwrite($fp, "ENCODING:USASCII\n");
fwrite($fp, "CHARSET:1252\n");
fwrite($fp, "COMPRESSION:NONE\n");
fwrite($fp, "OLDFILEUID:NONE\n");
fwrite($fp, "NEWFILEUID:NONE\n");
fwrite($fp, "\n"); // bank information
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "<code>0</code>\n"); // error code 0 is all ok
fwrite($fp, "INFO\n");
fwrite($fp, "\n");
fwrite($fp, "" . date(YmdHis) . "\n"); // 20100302093319 YYYYMMDDHHMMSS
fwrite($fp, "ENG\n");
fwrite($fp, "" . $institute . "\n"); // The financial institute number given by quicken. We use Natwests(00508) it's valid
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "1\n");
fwrite($fp, "\n");
fwrite($fp, "<code>0</code>\n"); // error code 0 is all ok
fwrite($fp, "INFO\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "" . $currency . "\n"); // currency
fwrite($fp, "\n");
fwrite($fp, "" . $sortcode . "\n"); // sort code
fwrite($fp, "" . $accountnumber . "\n"); // account number
fwrite($fp, "" . $accounttype . "\n"); // type of account
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "" . $startdate . "\n"); // start date YYYYMMDD
fwrite($fp, "" . $enddate . "\n"); // end date YYYYMMDD
// here is a loop that enters all the transaction from the csv file
$inc = 1;
$i = 0;
while($i < count($transactions)){
fwrite($fp, "\n");
fwrite($fp, "" . $transactions[$i + (int)$_POST['ttr']] . "\n"); // transaction type
fwrite($fp, "" . $transactions[$i + (int)$_POST['tdr']] . "\n"); // transaction date YYYYMMDD
fwrite($fp, "" . $transactions[$i + (int)$_POST['tar']] . "\n"); // amount if credit then - goes in front
if ($i >= $numberoftrans){ // work out if this is another transaction on the same day as the last
if( $transactions[$i + (int)$_POST['tdr']] == $transactions[($i + (int)$_POST['tdr'])-$numberoftrans]){
$inc++;
}else{
$inc = 1;
}
}
fwrite($fp, "" . $transactions[$i + (int)$_POST['tdr']] . substr($inc+10000,1) . "\n"); // date plus incremental number
fwrite($fp, "" . $transactions[$i + (int)$_POST['tnr']] . "\n"); // transaction name
if (!$transactions[$i + (int)$_POST['tmr']] == null){ // if a memo exists
fwrite($fp, "" . $transactions[$i + (int)$_POST['tmr']] . "\n"); // transaction memo
}
fwrite($fp, "\n");
$i = $i + $numberoftrans;
}
fwrite($fp, "\n"); // closing section of an OFX file
fwrite($fp, "\n");
fwrite($fp, "" . $balance . "\n"); // ledger balance - use balance of last transaction
fwrite($fp, "" . $enddate . "\n"); // date - put the last date of the transaction in there
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "" . $balance . "\n"); // amount availabe this takes into account pending payments which this won't deal with
fwrite($fp, "" . $enddate . "\n"); // date - put the last date of the transaction in there
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fwrite($fp, "\n");
fclose($fp);
?>
Above is my main PHP file which takes parameters about the CSV file and writes and OFX file from it.
include('function.php');
class Bank {
//initialise the class
public function __construct() {
}
//RETURNS an array of transacion data
public function transArray($fileName){
$ofxFunction = new ofxFunction(); // initisialise the function file
$i = 0; // count number of items
$items = array();
$file = fopen($fileName,'r');
while(!feof($file)){
$line = fgets($file); // read file in line by line
$fields = $ofxFunction->splitStringtoArray($line, ",");
$j = 0;
while($j < count($fields)){ // count the number of variables
if ($j == 0){
$items[$i] = $ofxFunction->dateReFormat($fields[$j], "/", "DDMMYYYY", "YYYYMMDD"); // insert the formatted date
}elseif ($j == 2) {
$items[$i] = $ofxFunction->stripAllDelim("-", "", $fields[$j]); // insert the formatted sort code
}elseif ($j == 4) { // contains the name and memo field
$namememo = $ofxFunction->splitStringtoArray($fields[$j], "."); // split the field by .
$items[$i] = $ofxFunction->stripAllDelim("\"", "", $namememo[0]); // blank the quote and add name into the array
$i++; // advance i
if (count($namememo)>2){ // if we have more than 2 items then there is a memo
$items[$i] = $ofxFunction->stripAllDelim("\"", "", $namememo[1]); // blank the quote and insert memo into array
} else {
$items[$i] = null; // no memo so insert blank this is key to keep record lengths normal.
}
} elseif ($j == 5) { // check to see if it's a debit or a credit
if (!$fields[$j] == ""){ // if 5 is not empty then its a debit
$val = $ofxFunction->createDebit($fields[$j]); // turn the positive value into a negative
$items[$i] = $ofxFunction->decimalise($val); // insert a formatted value
$j++;
} else { // its a credit
$j++;
$items[$i] = $ofxFunction->decimalise($fields[$j]); //add the item no processing necessary
}
} else {
$items[$i] = $fields[$j]; //add the item no processing necessary
}
$i++; // advance i
$j++; // advance j
}
}
fclose($file);
return $items;
}
}
?>
Above is my Lloyds.php file which deals specifically with LloydsTSB csv file and splits it up into an array for writing to the file.
<?PHP
class ofxFunction {
//initialise the class
public function __construct() {
}
//RETURNS a date in string formatted in any order requested
//note only deals with 4 digit year codes.
//$date = date string we want to reformat
//$deli = the delimitor in the date we want to blank (not needed if none used)
//$now = the format the date is in currently e.g DDMMYYYY
//$after = the format we want the date to be in afterwards e.g YYYYMMDD
public function dateReFormat($date, $deli, $now, $after){
$dte = $date;
if ($deli != null){ // remove the date delim if supplied
$dte = str_replace($deli, "", $date); // replace the delim with blank so e.g 01/01/2010 becomes 01012010
}
$daystart = stripos($now, "D"); // find position of first D in the current date format
$dayend = strripos($now, "D"); // find position of last D in the current date format
$monthstart = stripos($now, "M"); // find position of first M in the current date format
$monthend = strripos($now, "M"); // find position of last M in the current date format
$yearstart = stripos($now, "Y"); // find position of first Y in the current date format
$yearend = strripos($now, "Y"); // find position of last Y in the current date format
$day = substr($dte, $daystart, (($dayend + 1)-$daystart)); // day from the date passed
$month = substr($dte, $monthstart, (($monthend + 1)-$monthstart)); // month from the date passed
$year = substr($dte, $yearstart, (($yearend + 1)-$yearstart)); // year from the date passed
$replace = array("DD" => $day, "MM" => $month, "YYYY" => $year); // replace these with numbers stored
$dydd = strtr(strtoupper($after), $replace); // Put the numbers into the new date format
$dydd = trim($dydd); // get rid of any blank spaces at the start or end of the item
return $dydd; // return formatted date
}
//RETURNS a decimalised value so a . followed by two digits and no , for thousands
//$val = the value that we want to decimalise
public function decimalise($val){
$decval = number_format((float)$val,2,".",""); // variable is type float, 2 decimanl places after a . and no , in thousands
return $decval; // return decimalised value
}
//RETURNS a string with all delimiters removed
//$delim = the character that we want to remove
//$replace = the character we're going to replace the delim with so "" o blank it
//$line = string of text that has the delim in
public function stripAllDelim($delim, $replace ,$line){
$line = str_replace($delim, $replace,$line); // replace any delim with a blank
$line = trim($line); // get rid of any blank spaces at the start or end of the item
return $line; // return formatted string
}
//RETURNS a string with certain delimiters removed
//$delim = the character that we want to remove
//$replace = the character we're going to replace the delim with so "" to blank it
//$line = string of text that has the delim in
//$start = the delim number to start at
//$stop = the delim number to stop at
public function stripCertainDelim($delim, $replace, $line, $start, $stop){
$i = 0; // used to count number of delims
$substart = 0; // remember the position we last checked
while ($i < $stop){
$pos = strpos($line, $delim, $substart); // find delim
if ($pos !== false){ // if we found an instance then carry on and replace it
$substart = $pos + 1; // start next time after the postion we're at now
$i++; // increase the count by 1
if ($i >= $start){
substr_replace($line, $replace , $pos, $pos);// remove the delim
}
}else{
$i = $stop; // get out of the loop
}
}
$line = trim($line); // get rid of any blank spaces at the start or end of the item
return $line; // return formatted string
}
//RETURNS a negative value from a positive one
//$value = item to be converted into negative/positive value
public function createDebit($value){
$debit = (float)$value; // convert value to float
$debit = $debit - (2 * $debit); // value minus 2 times it self gives you the neg/pos of the value
return $debit; // return the debit value
}
//RETURNS array of strings split by the delim passed
//$line = the string to be split into an array
//$delim = the delim that we use to split for CSV use ,
public function splitStringtoArray($line, $delim){
$fields = explode($delim,$line); // split the string using the delim
return $fields; // return array of strings
}
//RETURNS a value with leading zeros
//$num = item to have leading zeros a head of
public function leadingZeros($num){
return substr($num+10000,1);
}
}
?>
The above is a function file that I used to do simple tasks and will allow others to use my files if they wanted.
To create your own OFX file for your bank you’d just need to write your own Lloyds.php or call it whatever and the other files will do the rest.
And if you did not have access to php,or you were code averse, or if you just prefer using a desktop utility for converting your csv file to ofx, try iCreateOFX Basic from
http://icreateofx.co.uk/basic.php
The script above leaves your server open to all sorts of abuse
require_once($_POST[‘php’]); // include the bank file submitted
NEVER do this it is absolutely insane
you are asking the user to name a file on your server and run it, that opens you to a massive amount of potential abuse
If you need (or should?) to do CSV to OFX conversion on desktop, use CSV2OFX at http://www.propersoft.net/csv2ofx
It tries to detect the CSV layout, so your bank is most likely supported.