OFX example

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

OFX example

Wojciech Piekutowski
Hi,

I'm working on a script that will convert my monthly bank statement
data to OFX format. I have some questions about this. What version of
OFX is supported? Are there any example OFX files available that will
work well with gnucash? This would greatly help me and possibly other
clients of this bank as I'm planning to release this script under MIT
license.

I have some questions too.

<BANKACCTFROM>
          <BANKID>1234 1234</BANKID>
          <ACCTID>5678 5678 5678 5678</ACCTID>

Should I split BANKID and ACCTID like this or is better to stick the
whole account number into ACCTID and leave BANKID? Or maybe use the
bank name as BANKID?

When TRNTYPE is POS, ATM or FEE should I include BANKACCTTO part?

Is FITID used at all by gnucash, for example to match internal
transactions between two client's accounts?

Any help would be highly appreciated.

Greetings,
Wojciech

BTW My current output looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE"
NEWFILEUID="NONE"?>
<OFX>
  <BANKMSGSRSV1>
    <STMTTRNRS>
      <STMTRS>
        <CURDEF>PLN</CURDEF>
        <BANKACCTFROM>
          <BANKID>1234 1234</BANKID>
          <ACCTID>5678 5678 5678 5678</ACCTID>
          <ACCTTYPE>SAVINGS</ACCTTYPE>
        </BANKACCTFROM>
        <BANKTRANLIST>
          <DTSTART>20100501[+1:CET]</DTSTART>
          <DTEND>20100531[+1:CET]</DTEND>
<!-- debit card payment -->
<STMTTRN>
  <TRNTYPE>POS</TRNTYPE>
  <DTPOSTED>20100503[+1:CET]</DTPOSTED>
  <DTUSER>20100426[+1:CET]</DTUSER>
  <TRNAMT>-23.11</TRNAMT>
  <FITID>588fdb8b38401023215007e017fc93ae</FITID>
  <NAME></NAME>
  <MEMO>description</MEMO>
  <BANKACCTTO>
    <BANKID></BANKID>
    <ACCTID></ACCTID>
    <ACCTTYPE>SAVINGS</ACCTTYPE>
  </BANKACCTTO>
</STMTTRN>
<!-- regular transfer -->
<STMTTRN>
  <TRNTYPE>CREDIT</TRNTYPE>
  <DTPOSTED>20100506[+1:CET]</DTPOSTED>
  <DTUSER>20100506[+1:CET]</DTUSER>
  <TRNAMT>1519.97</TRNAMT>
  <FITID>c99bf204ac1167ff12fe1caa861539e5</FITID>
  <NAME>JAN KOWALSKI</NAME>
  <MEMO>PRZELEW</MEMO>
  <BANKACCTTO>
    <BANKID></BANKID>
    <ACCTID>12 3456 3456 3456 3456 3456</ACCTID>
    <ACCTTYPE>SAVINGS</ACCTTYPE>
  </BANKACCTTO>
</STMTTRN>
<!-- regular transfer -->
<STMTTRN>
  <TRNTYPE>DEBIT</TRNTYPE>
  <DTPOSTED>20100506[+1:CET]</DTPOSTED>
  <DTUSER>20100506[+1:CET]</DTUSER>
  <TRNAMT>-1500.0</TRNAMT>
  <FITID>07bce7a5cd4d10e79b333a802fa39ae5</FITID>
  <NAME>JAN KOWALSKI</NAME>
  <MEMO>1875336</MEMO>
  <BANKACCTTO>
    <BANKID>GNB II O./Lublin</BANKID>
    <ACCTID>43 1234 1234 1234 1234 1234 1234</ACCTID>
    <ACCTTYPE>SAVINGS</ACCTTYPE>
  </BANKACCTTO>
</STMTTRN>
<!-- ATM transaction -->
<STMTTRN>
  <TRNTYPE>ATM</TRNTYPE>
  <DTPOSTED>20100514[+1:CET]</DTPOSTED>
  <DTUSER>20100513[+1:CET]</DTUSER>
  <TRNAMT>-200.0</TRNAMT>
  <FITID>ad01139e4079be43e7f2bf9745bec65d</FITID>
  <NAME></NAME>
  <MEMO>WBK S.A.</MEMO>
  <BANKACCTTO>
    <BANKID></BANKID>
    <ACCTID></ACCTID>
    <ACCTTYPE>SAVINGS</ACCTTYPE>
  </BANKACCTTO>
</STMTTRN>
<!-- ATM fee -->
<STMTTRN>
  <TRNTYPE>FEE</TRNTYPE>
  <DTPOSTED>20100526[+1:CET]</DTPOSTED>
  <DTUSER>20100526[+1:CET]</DTUSER>
  <TRNAMT>-5.0</TRNAMT>
  <FITID>34c97d25118cfe684bea6c7765ee2d23</FITID>
  <NAME></NAME>
  <MEMO>BGZ SA</MEMO>
  <BANKACCTTO>
    <BANKID></BANKID>
    <ACCTID></ACCTID>
    <ACCTTYPE>SAVINGS</ACCTTYPE>
  </BANKACCTTO>
</STMTTRN>
        </BANKTRANLIST>
      </STMTRS>
    </STMTTRNRS>
  </BANKMSGSRSV1>
</OFX>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

David Reiser

On Aug 12, 2010, at 10:45 AM, Wojciech Piekutowski wrote:

> Hi,
>
> I'm working on a script that will convert my monthly bank statement
> data to OFX format. I have some questions about this. What version of
> OFX is supported? Are there any example OFX files available that will
> work well with gnucash? This would greatly help me and possibly other
> clients of this bank as I'm planning to release this script under MIT
> license.
>
> I have some questions too.
>
> <BANKACCTFROM>
>     <BANKID>1234 1234</BANKID>
>     <ACCTID>5678 5678 5678 5678</ACCTID>
>
> Should I split BANKID and ACCTID like this or is better to stick the
> whole account number into ACCTID and leave BANKID? Or maybe use the
> bank name as BANKID?

<BANKID> is normally the Routing/Transit Number. As long as it is unique among possible files you'll be importing, that's sufficient. I would leave BANKID and ACCTID separate.

>
> When TRNTYPE is POS, ATM or FEE should I include BANKACCTTO part?

I don't know this one.
>
> Is FITID used at all by gnucash, for example to match internal
> transactions between two client's accounts?

FITID is used internally to match duplicates. For a valid ofx file FITID _must_ be unique forever within a given account. Gnucash stores the FITID in the data schema and compares incoming transaction FITIDs to existing FITIDs in that account. This duplicate tracking makes ofx imports a lot more convenient if your bank always sends you 30 days of history when you download, but you actually download once a week. The FITIDs make it trivial for gnucash to automatically reject duplicate transactions.

The "always unique" and "always the same for a specific transaction" makes it hard to generate FITIDs for transactions from an arbitrary bank download that doesn't contain some kind of unique identifier.

>
> Any help would be highly appreciated.
>
> Greetings,
> Wojciech
>
> BTW My current output looks like this:
>
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <?OFX OFXHEADER="200" VERSION="211" SECURITY="NONE" OLDFILEUID="NONE"
> NEWFILEUID="NONE"?>
> <OFX>
> <BANKMSGSRSV1>
> <STMTTRNRS>
> <STMTRS>
>   <CURDEF>PLN</CURDEF>
>   <BANKACCTFROM>
>     <BANKID>1234 1234</BANKID>
>     <ACCTID>5678 5678 5678 5678</ACCTID>
>     <ACCTTYPE>SAVINGS</ACCTTYPE>
>   </BANKACCTFROM>
>   <BANKTRANLIST>
>     <DTSTART>20100501[+1:CET]</DTSTART>
>     <DTEND>20100531[+1:CET]</DTEND>
> <!-- debit card payment -->
> <STMTTRN>
> <TRNTYPE>POS</TRNTYPE>
> <DTPOSTED>20100503[+1:CET]</DTPOSTED>
> <DTUSER>20100426[+1:CET]</DTUSER>
> <TRNAMT>-23.11</TRNAMT>
> <FITID>588fdb8b38401023215007e017fc93ae</FITID>
> <NAME></NAME>
> <MEMO>description</MEMO>
> <BANKACCTTO>
> <BANKID></BANKID>
> <ACCTID></ACCTID>
> <ACCTTYPE>SAVINGS</ACCTTYPE>
> </BANKACCTTO>
> </STMTTRN>
> <!-- regular transfer -->
> <STMTTRN>
> <TRNTYPE>CREDIT</TRNTYPE>
> <DTPOSTED>20100506[+1:CET]</DTPOSTED>
> <DTUSER>20100506[+1:CET]</DTUSER>
> <TRNAMT>1519.97</TRNAMT>
> <FITID>c99bf204ac1167ff12fe1caa861539e5</FITID>
> <NAME>JAN KOWALSKI</NAME>
> <MEMO>PRZELEW</MEMO>
> <BANKACCTTO>
> <BANKID></BANKID>
> <ACCTID>12 3456 3456 3456 3456 3456</ACCTID>
> <ACCTTYPE>SAVINGS</ACCTTYPE>
> </BANKACCTTO>
> </STMTTRN>
> <!-- regular transfer -->
> <STMTTRN>
> <TRNTYPE>DEBIT</TRNTYPE>
> <DTPOSTED>20100506[+1:CET]</DTPOSTED>
> <DTUSER>20100506[+1:CET]</DTUSER>
> <TRNAMT>-1500.0</TRNAMT>
> <FITID>07bce7a5cd4d10e79b333a802fa39ae5</FITID>
> <NAME>JAN KOWALSKI</NAME>
> <MEMO>1875336</MEMO>
> <BANKACCTTO>
> <BANKID>GNB II O./Lublin</BANKID>
> <ACCTID>43 1234 1234 1234 1234 1234 1234</ACCTID>
> <ACCTTYPE>SAVINGS</ACCTTYPE>
> </BANKACCTTO>
> </STMTTRN>
> <!-- ATM transaction -->
> <STMTTRN>
> <TRNTYPE>ATM</TRNTYPE>
> <DTPOSTED>20100514[+1:CET]</DTPOSTED>
> <DTUSER>20100513[+1:CET]</DTUSER>
> <TRNAMT>-200.0</TRNAMT>
> <FITID>ad01139e4079be43e7f2bf9745bec65d</FITID>
> <NAME></NAME>
> <MEMO>WBK S.A.</MEMO>
> <BANKACCTTO>
> <BANKID></BANKID>
> <ACCTID></ACCTID>
> <ACCTTYPE>SAVINGS</ACCTTYPE>
> </BANKACCTTO>
> </STMTTRN>
> <!-- ATM fee -->
> <STMTTRN>
> <TRNTYPE>FEE</TRNTYPE>
> <DTPOSTED>20100526[+1:CET]</DTPOSTED>
> <DTUSER>20100526[+1:CET]</DTUSER>
> <TRNAMT>-5.0</TRNAMT>
> <FITID>34c97d25118cfe684bea6c7765ee2d23</FITID>
> <NAME></NAME>
> <MEMO>BGZ SA</MEMO>
> <BANKACCTTO>
> <BANKID></BANKID>
> <ACCTID></ACCTID>
> <ACCTTYPE>SAVINGS</ACCTTYPE>
> </BANKACCTTO>
> </STMTTRN>
>   </BANKTRANLIST>
> </STMTRS>
> </STMTTRNRS>
> </BANKMSGSRSV1>
> </OFX>
>

Dave
--
David Reiser
[hidden email]




_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

Derek Atkins
In reply to this post by Wojciech Piekutowski
Wojciech Piekutowski <[hidden email]> writes:

> Hi,
>
> I'm working on a script that will convert my monthly bank statement
> data to OFX format. I have some questions about this. What version of
> OFX is supported? Are there any example OFX files available that will
> work well with gnucash? This would greatly help me and possibly other
> clients of this bank as I'm planning to release this script under MIT
> license.

Is there any reason you're using OFX instead of QIF for this?  I think
the QIF format would be much easier to generate and doesn't have all the
gotcha's that OFX has.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

Wojciech Piekutowski
In reply to this post by David Reiser
On 12 August 2010 22:43, David Reiser <[hidden email]> wrote:
> On Aug 12, 2010, at 10:45 AM, Wojciech Piekutowski wrote:
>> Should I split BANKID and ACCTID like this or is better to stick the
>> whole account number into ACCTID and leave BANKID? Or maybe use the
>> bank name as BANKID?
>
> <BANKID> is normally the Routing/Transit Number. As long as it is unique among possible files you'll be importing, that's sufficient. I would leave BANKID and ACCTID separate.
>

Thanks, I'll try your way.

>> Is FITID used at all by gnucash, for example to match internal
>> transactions between two client's accounts?
>
> FITID is used internally to match duplicates. For a valid ofx file FITID _must_ be unique forever within a given account. Gnucash stores the FITID in the data schema and compares incoming transaction FITIDs to existing FITIDs in that account. This duplicate tracking makes ofx imports a lot more convenient if your bank always sends you 30 days of history when you download, but you actually download once a week. The FITIDs make it trivial for gnucash to automatically reject duplicate transactions.
>
> The "always unique" and "always the same for a specific transaction" makes it hard to generate FITIDs for transactions from an arbitrary bank download that doesn't contain some kind of unique identifier.

Let's say I have 2 accounts in this bank and I've transfered money
from the first to the second. Does GNUCash uses FITID to detect
internal money transfer?
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

Wojciech Piekutowski
In reply to this post by Derek Atkins
On 13 August 2010 15:39, Derek Atkins <[hidden email]> wrote:

> Wojciech Piekutowski <[hidden email]> writes:
>> I'm working on a script that will convert my monthly bank statement
>> data to OFX format. I have some questions about this. What version of
>> OFX is supported? Are there any example OFX files available that will
>> work well with gnucash? This would greatly help me and possibly other
>> clients of this bank as I'm planning to release this script under MIT
>> license.
>
> Is there any reason you're using OFX instead of QIF for this?  I think
> the QIF format would be much easier to generate and doesn't have all the
> gotcha's that OFX has.

I thought that OFX is the preferred format and it has much better
import support. I don't find OFX hard to generate, only the specs and
how GNUCash handles various fields are unclear. I think QIF can't
accomodate all the data I get from the bank, for example destination
account. I hope that by using OFX GNUCash will properly handle
transactions between my accounts in other banks.
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

Derek Atkins
Wojciech Piekutowski <[hidden email]> writes:

> On 13 August 2010 15:39, Derek Atkins <[hidden email]> wrote:
>> Wojciech Piekutowski <[hidden email]> writes:
>>> I'm working on a script that will convert my monthly bank statement
>>> data to OFX format. I have some questions about this. What version of
>>> OFX is supported? Are there any example OFX files available that will
>>> work well with gnucash? This would greatly help me and possibly other
>>> clients of this bank as I'm planning to release this script under MIT
>>> license.
>>
>> Is there any reason you're using OFX instead of QIF for this?  I think
>> the QIF format would be much easier to generate and doesn't have all the
>> gotcha's that OFX has.
>
> I thought that OFX is the preferred format and it has much better
> import support. I don't find OFX hard to generate, only the specs and
> how GNUCash handles various fields are unclear. I think QIF can't
> accomodate all the data I get from the bank, for example destination
> account. I hope that by using OFX GNUCash will properly handle
> transactions between my accounts in other banks.

QIF can indeed handle destination account.  If it's an Income/Expense
account you use a QIF Category:

LQIFCategory

If it's an Asset/Liability destination account then you use a QIF
Account:

L[QIFAccount]

During the import process you map QIF Categories and QIF Accounts to GNC
Accounts.

Happy to Help!

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

David Reiser
In reply to this post by Wojciech Piekutowski

On Aug 14, 2010, at 7:16 AM, Wojciech Piekutowski wrote:

> On 12 August 2010 22:43, David Reiser <[hidden email]> wrote:
>> On Aug 12, 2010, at 10:45 AM, Wojciech Piekutowski wrote:
>>> Should I split BANKID and ACCTID like this or is better to stick the
>>> whole account number into ACCTID and leave BANKID? Or maybe use the
>>> bank name as BANKID?
>>
>> <BANKID> is normally the Routing/Transit Number. As long as it is unique among possible files you'll be importing, that's sufficient. I would leave BANKID and ACCTID separate.
>>
>
> Thanks, I'll try your way.
>
>>> Is FITID used at all by gnucash, for example to match internal
>>> transactions between two client's accounts?
>>
>> FITID is used internally to match duplicates. For a valid ofx file FITID _must_ be unique forever within a given account. Gnucash stores the FITID in the data schema and compares incoming transaction FITIDs to existing FITIDs in that account. This duplicate tracking makes ofx imports a lot more convenient if your bank always sends you 30 days of history when you download, but you actually download once a week. The FITIDs make it trivial for gnucash to automatically reject duplicate transactions.
>>
>> The "always unique" and "always the same for a specific transaction" makes it hard to generate FITIDs for transactions from an arbitrary bank download that doesn't contain some kind of unique identifier.
>
> Let's say I have 2 accounts in this bank and I've transfered money
> from the first to the second. Does GNUCash uses FITID to detect
> internal money transfer?

I don't think so -- you'll have the transaction in both ofx files (ofx is one account per file only) and the FITIDs do not have to match. Usually they wouldn't match. I think when you import each file, gnucash is only trying to match the split associated with the account being imported, so you'd get a match in each account based on date and amount. After importing both ofx files, both sides of the transfer operation would be cleared. I suspect that since FITIDs only have to be unique within accounts, that a matching FITID in a different account is not considered as a possible transfer transaction.

If you have lots of transfers of similar amounts, you will probably run into transaction matching problems.

Dave
--
David Reiser
[hidden email]




_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: OFX example

Derek Atkins
David Reiser <[hidden email]> writes:

>> Let's say I have 2 accounts in this bank and I've transfered money
>> from the first to the second. Does GNUCash uses FITID to detect
>> internal money transfer?
>
> I don't think so -- you'll have the transaction in both ofx files (ofx
> is one account per file only) and the FITIDs do not have to
> match. Usually they wouldn't match. I think when you import each file,
> gnucash is only trying to match the split associated with the account
> being imported, so you'd get a match in each account based on date and
> amount. After importing both ofx files, both sides of the transfer
> operation would be cleared. I suspect that since FITIDs only have to
> be unique within accounts, that a matching FITID in a different
> account is not considered as a possible transfer transaction.
>
> If you have lots of transfers of similar amounts, you will probably
> run into transaction matching problems.

This is one place where QIF is definitely better.  If you have QIF
Account A and QIF Account B, the importer will notice an A->B ($xx.yy)
and B<-A (-$xx.yy) and declare them duplicates.

With OFX you need to manually match A->B/B<-A dups during the import of
the second set.

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.