html_to_ofx.rb

require 'nokogiri'
require 'active_support'
require 'builder'
require 'digest'

class Statement
  attr_accessor :rows
  HEADINGS = [:date,:type,:description,:paid_out,:paid_in,:balance]
  
  def initialize(file)
    stmt = Nokogiri::HTML(file)
    table = stmt.css('table[summary="This table contains a statement of your account"]')
    @rows = table.css('tbody tr').map do |row|
      row.css('p').map(&method(:clean_text))
    end
    @account_number = stmt.css('.hsbcAccountNumber').text.strip.gsub(/\D/,'')
  end
  
  def to_ofx(xml)
    ofx_header(xml)
    xml.OFX do
      ofx_preamble(xml)
      xml.BANKMSGSRSV1 do
        xml.STMTTRNRS do
          xml.TRNUID "1"
          xml.STATUS do
            xml.CODE "0"
            xml.SEVERITY "INFO"
          end
          xml.STMTRS do
            xml.CURDEF "GBP"
            xml.BANKACCTFROM do
              xml.BANKID @account_number[0..6]
              xml.ACCTID @account_number
              xml.ACCTTYPE "CHECKING"
            end
            to_txn_list(xml)
            xml.LEDGERBAL do
              xml.BALAMT @balance[:balance]
              xml.DTASOF Date.parse(@balance[:date]).to_time.to_s(:number)
            end
          end
        end
      end
    end
  end
  
  def ofx_header(xml)
    header = %w[OFXHEADER:100 DATA:OFXSGML VERSION:102 SECURITY:NONE ENCODING:USASCII
      CHARSET:1252 COMPRESSION:NONE OLDFILEUID:NONE NEWFILEUID:NONE]
    xml.text!(header.join("\n"))
    xml.text!("\n\n")
  end
  
  def ofx_preamble(xml)
    xml.SIGNONMSGSRSV1 do
      xml.SONRS do
        xml.STATUS do
          xml.CODE "0"
          xml.SEVERITY "INFO"
        end
        xml.DTSERVER Time.now.to_s(:number)
        xml.LANGUAGE "ENG"
        xml.__send__("INTU.BID","01267") # fuck knows what half this shit means
      end
    end
  end

  def to_txn_list(xml)
    xml.BANKTRANLIST do
      @rows.each do |row|
        details = row_hash(row)
        if details[:type].blank?
          @balance = details
        else
          year = details[:date].ends_with?('Jan') ? '2010' : '2009'
          year = '2010' # use above line for last years statements etc.
          date_str = Date.parse("#{details[:date]} #{year}").to_time.to_s(:number)
          fake_id = Digest::MD5.hexdigest(details.values_at(:date,:type,:paid_in,:paid_out).join('')).to_i(16)
          xml.STMTTRN do
            xml.TRNTYPE details[:type]
            xml.DTPOSTED date_str
            xml.TRNAMT details[:paid_in].present? ? details[:paid_in] : "-#{details[:paid_out]}"
            xml.FITID "#{date_str}#{fake_id}"[0...31]
            xml.NAME details[:description]
          end
        end
      end
    end
  end

  def clean_text(node)
    node.text.strip.gsub(/\302\240/,'')
  end
  
  def row_hash(row)
    returning({}) do |hash|
      HEADINGS.each_with_index do |heading,i|
        hash[heading] = row[i]
      end
    end
  end
  
end

stmt = Statement.new(ARGF)
stmt.to_ofx(Builder::XmlMarkup.new(:target=>STDOUT, :indent => 1))