Importing bank transactions

Like many, I’ve often downloaded financial transactions from my bank’s web site.  The options for my bank (in Canada), is fairly typical, and limited to “Microsoft Money”, “Intuit Quicken”, “Intuit QuickBooks”, “Simply Accounting”, “Spreadsheet (CSV)”.

Now, I don’t use any of the above accounting programs.  I want the data to play around with, and use how I want.  Originally, I used it for an Excel spreadsheet, and now in a MySQL database.  In the past, I took the “Spreadsheet (CSV)” option, which seems like the obvious choice if you’re not using the recognized accounting software.  But, the CSV option is surprisingly limited.  Most importantly, it does NOT include a field to identify the bank account.  So, if you look at a file, you can’t tell what account it’s from.  That makes it pretty useless.  As for the other formats, I simply took for granted that they were proprietary, given how they’re named.

But, nope.  They’re actually a public open file format, OFX, that’s easy for anybody with the most junior programming/scripting skills to read.  Simply download and open the file in an ASCII editor, and you can see it’s easy to figure out.

You can see an example at http://www.ofx.net/OFXExamplesPage/OFXExamples.aspx.  The same site also describes the format.

I happened to use PHP to parse it, but any other scripting language would work just as easy.  I wrote a function to import it into a MySQL database.  I run it with a web browser, accessing localhost.  That process is somewhat personalized to my needs.  So, I’ve copied a more generic version, which just outputs an HTML table of the data (see below).  You can copy/paste the table in a browser to whatever program you want, or change the code to do something else.  As you can see, this is a very quick and dirty version, and NOT suitable for a production environment.

Now, if I wanted this to be used in a web app that others had access to, I’d write something more sophisticated (and I may in future).  One approach might be to convert the file to XML, and use the appropriate functions in PHP to read it.  I would need to handle a lot of details, that I obviously don’t worry about here.

As for the code.  If you have a problem with “mb_substr”, just use “substr”.  “FITID” is the field that uniquely identifies one transaction for a given account.  I use “htmlspecialchars_decode” because I found the “OFX” file actually uses “&”‘s in it (for the Bank Description field, which is called “NAME” in the file), which need to be converted to “&”.  I’m not sure if this is a standard (to use HTML special characters), so I wrote the code to work regardless.

  function ProcessFile($file_path)
  {
    if (file_exists($file_path))
    {
      $file_contents = file_get_contents($file_path);
      $file_array = explode(“rn”,$file_contents);
      $len = count($file_array);
      
      $BankID = “”;
      $BankAcctID = “”;
      $ItemCount = 0;

      $DatePosted = “”;
      $BankDescription = “”;
      $TransactAmount = 0;
      $FITID = “”;
      $TransactType = “”;
      $ChequeNum = 0;
      
      
      echo “<table>”;
      echo “<tr><th>Bank ID</th><th>Acccount ID</th><th>Date</th><th>Description</th><th>Amount</th><th>FITID</th><th>Type</th><th>Ch#</th></tr>”;
      for ($i=0; $i<$len; $i++)
      {
        $line = $file_array[$i];
        if (mb_substr($line,0,10)==”<DTPOSTED>”)
        {
          $DatePosted=mb_substr($line,10,4) . ‘-‘ . mb_substr($line,14,2) . ‘-‘ . mb_substr($line,16,2);
        }
        elseif (mb_substr($line,0,8)==”<BANKID>”)
        {
          $BankID = mb_substr($line,8);
        }
        elseif (mb_substr($line,0,8)==”<ACCTID>”)
        {
          $BankAcctID = mb_substr($line,8);
        }
        elseif (mb_substr($line,0,6)==”<NAME>”)
        {
          $BankDescription = htmlspecialchars_decode(mb_substr($line,6),ENT_QUOTES);
        }
        elseif (mb_substr($line,0,8)==”<TRNAMT>”)
        {
          $TransactAmount = mb_substr($line,8);
        }
        elseif (mb_substr($line,0,7)==”<FITID>”)
        {
          $FITID = mb_substr($line,7);
        }
        elseif (mb_substr($line,0,9)==”<TRNTYPE>”)
        {
          $TransactType = mb_substr($line,9);
        }
        elseif (mb_substr($line,0,10)==”<CHECKNUM>”)
        {
          $ChequeNum = intval(mb_substr($line,10));
        }
        elseif (mb_substr($line,0,9)==”<STMTTRN>”)
        {
          $DatePosted = “”;
          $BankDescription = “”;
          $TransactAmount = 0;
          $FITID = “”;
          $TransactType = “”;
          $ChequeNum = 0;
        }
        elseif (mb_substr($line,0,10)==”</STMTTRN>”)
        {
          $ItemCount++;
          $BankDescriptionHTML = htmlspecialchars($BankDescription,ENT_QUOTES);
          $ChequeNumDisplay = $ChequeNum==0?””:$ChequeNum;
          
          echo “<tr>”;
          echo “<td>$BankID</td>”;
          echo “<td style=’text-align:right;’>$BankAcctID</td>”;
          echo “<td>$DatePosted</td>”;
          echo “<td>$BankDescription</td>”;
          echo “<td style=’text-align:right;’>$TransactAmount</td>”;
          echo “<td>$FITID</td>”;
          echo “<td>$TransactType</td>”;
          echo “<td>$ChequeNumDisplay</td>”;
          echo “</tr>n”;
        }
      }           
      echo “</table>n”;
    }
  }

Leave a Reply

Your email address will not be published. Required fields are marked *