OFX statement import (version 5.0)

Hi Guys!,
I’m trying to import an OFX file (created using this tool CSV2OFX for a CSV source downloaded from my bank) but unfortunately I’m getting an exception since it appears that the balance_date is a required field and it cann’t be recreated from the data that comes in the OFX file (it’s my understanding that the (LEDGERBAL) tag is the one that indeed has that info… I’ve verified and CSV2OFX is not inserting that tag despite the fact that my CSV file has the balance attribute for each record)…

My question is: Has somebody been successful importing statements from a csv source (through OFX) into Tryton? if yes, what conversion tool (CSV->OFX) have you used?

Thanks in advance!,

This is the traceback of the exception:

  Traceback (most recent call last):
  File "/trytond/wsgi.py", line 73, in dispatch_request
    return endpoint(request, **request.view_args)
  File "/trytond/protocols/dispatcher.py", line 46, in rpc
    request, database_name, *request.rpc_params)
  File "/trytond/wsgi.py", line 44, in auth_required
    return wrapped(*args, **kwargs)
  File "/trytond/protocols/wrappers.py", line 122, in wrapper
    return func(request, pool, *args, **kwargs)
  File "/trytond/protocols/dispatcher.py", line 176, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/trytond/wizard/wizard.py", line 292, in execute
    return wizard._execute(state_name)
  File "/trytond/wizard/wizard.py", line 318, in _execute
    do_result = do(action)
  File "/trytond/modules/account_statement/statement.py", line 1053, in do_import_
    statements = list(getattr(self, 'parse_%s' % self.start.file_format)())
  File "/trytond/modules/account_statement_ofx/account.py", line 41, in parse_ofx
    statement = self.ofx_statement(ofx, account)
  File "/trytond/modules/account_statement_ofx/account.py", line 70, in ofx_statement
    statement.date = ofx_account.statement.balance_date.date()
AttributeError: 'Statement' object has no attribute 'balance_date'

It seems that the balance date (DTASOF) is not a required field in OFX (see chapter 3.1.4).
So for me it is a bug in account_statement_ofx which should have a fallback if the date is missing (probably use today).

I filled Issue 9777: balance date is optional on OFX - Tryton issue tracker

Thank you Cédric!..
I’ve tried with this change in account.py (in account_statement_ofx module):

    if hasattr(ofx_account.statement, 'balance_date'):
        statement.date = ofx_account.statement.balance_date.date()
    else:
        statement.date = Date.today()

However I got another exception:
> Traceback (most recent call last):

  File "/trytond/wsgi.py", line 73, in dispatch_request
    return endpoint(request, **request.view_args)
  File "/trytond/protocols/dispatcher.py", line 46, in rpc
    request, database_name, *request.rpc_params)
  File "/trytond/wsgi.py", line 44, in auth_required
    return wrapped(*args, **kwargs)
  File "/trytond/protocols/wrappers.py", line 122, in wrapper
    return func(request, pool, *args, **kwargs)
  File "/trytond/protocols/dispatcher.py", line 176, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/trytond/wizard/wizard.py", line 292, in execute
    return wizard._execute(state_name)
  File "/trytond/wizard/wizard.py", line 318, in _execute
    do_result = do(action)
  File "/trytond/modules/account_statement/statement.py", line 1053, in do_import_
    statements = list(getattr(self, 'parse_%s' % self.start.file_format)())
  File "/trytond/modules/account_statement_ofx/account.py", line 41, in parse_ofx
    statement = self.ofx_statement(ofx, account)
  File "/trytond/modules/account_statement_ofx/account.py", line 78, in ofx_statement
    statement.start_balance = ofx_account.statement.balance - total_amount
AttributeError: 'Statement' object has no attribute 'balance'

That being said… I’m not completely convinced that the missing tag is DTASOF, I thought it was LEDGERBAL (I did a little debug of the parserBalance method in ofxparse.py where I assume the statetement balance and balance_date are set… but I recognize that I’m full novice in OFX and Python too :wink: ) that will explain both exceptions… Anyway I am little bit lost, because I was not able to find any reference to LEDGERBAL in the OFX spec that you sent… so I’m just wondering where the issue might be…

  >   def parseBalance(cls, statement, stmt_ofx, bal_tag_name, bal_attr,
>                      bal_date_attr, bal_type_string):
>         bal_tag = stmt_ofx.find(bal_tag_name)
>         if hasattr(bal_tag, "contents"):
>             balamt_tag = bal_tag.find('balamt')
>             dtasof_tag = bal_tag.find('dtasof')
>             if hasattr(balamt_tag, "contents"):
>                 try:
>                     setattr(statement, bal_attr, cls.toDecimal(balamt_tag))
>                 except (IndexError, decimal.InvalidOperation):
>                     statement.warnings.append(
>                         six.u("%s balance amount was empty for \
>                             %s") % (bal_type_string, stmt_ofx))
>                     if cls.fail_fast:
>                         raise OfxParserException("Empty %s balance\
>                             " % bal_type_string)
>             if hasattr(dtasof_tag, "contents"):
>                 try:
>                     setattr(statement, bal_date_attr, cls.parseOfxDateTime(
>                         dtasof_tag.contents[0].strip()))

The value of the balance is a mandatory field so your OFX is not valid.

1 Like

Thank you Cedric!..
I made some changes to CSV2OFX in order to include the balance records, I will be submitting to the project for the benefit of others in the community…

Meanwhile, for those looking for a way to transform the csv statement into ofx format, I found an online tool that might be ok (probably some preparation of the csv could be required i.e. remove commas, etc…) https://csvconverter.biz