none
OFX vs QFX

    Întrebare

  • One of my banks (credit card) just stopped having QIF files available for download.  QFX (and also the quickbooks file type) is now the only option.
     
    QFX is a proprietary superset of OFX.
     
    For my file, all I needed to do after downloading it was:
     
    Open file in notepad.
    Remove all Ampersands (&) using the Find/Replace tool
    Re-save it as an .ofx  file.
     
    Then  it worked fine.
     
     

    Ron
    7 ianuarie 2011 14:08

Toate mesajele

  • At least in later versions, Money will accept QFX files if you do File->Import->DownloadStatement. It will ignore the extra elements such as <INTU.BID> and <INTU.USERID>. If you want Money associated with the QFX extension, take a look at the second post on http://social.microsoft.com/Forums/nb-NO/money/thread/220ed237-fa35-4476-bd9b-31851fd77474

    Regarding the '&' removal, I did not think there was any difference between OFX and QIF in that regard. Each requires that an '&" in a Payee, Comment, etc be replaced with the following 5-character string: '&amp;' (case-sensitive). So AT&T becomes AT&amp;T. Some other characters must be replaced with similar escape sequences.... most notably the '<' and '>' that are not the delimiters on SGML or XML tags. So what I am suspecting is that maybe the QFX file you are dealing with could have been a problem even if fed to Quicken.

    I edited this post because &amp; should be lower case, and I had it wrong.

     

    7 ianuarie 2011 16:12
  • On Fri, 7 Jan 2011 16:12:46 +0000, Cal Learner wrote:
     
    >
    >
    >At least in later versions, Money will accept QFX files if you do File->Import->DownloadStatement. It will ignore the extra elements such as <INTU.BID> and <INTU.USERID>. If you want Money associated with the QFX extension, take a look at the second post on http://social.microsoft.com/Forums/nb-NO/money/thread/220ed237-fa35-4476-bd9b-31851fd77474
    >
    >Regarding the '&' removal, I did not think there was any difference between OFX and QIF in that regard. Each requires that an '&" in a Payee, Comment, etc be replaced with the following 5-character string: '&AMP;' (not case-sensitive). So AT&T becomes AT&AMP;T. Some other characters must be replaced with similar escape sequences.... most notably the '<' and '>' that are not the delimiters on SGML or XML tags. So what I am suspecting is that maybe the QFX file you are dealing with could have been a problem even if fed to Quicken.
    >
     
    That could be but they've had the qfx files up for quite a while.  I would have guessed they would have addressed that problem long ago.
     
    Also, I tried the substitution of &AMP; for & and Money would still not do the import.  It was only when I deleted the & that Money would do the import.
     

    Ron
    7 ianuarie 2011 17:32
  • On Fri, 7 Jan 2011 16:12:46 +0000, Cal Learner wrote:
    Also, I tried the substitution of &AMP; for & and Money would still not do the import.  It was only when I deleted the & that Money would do the import.
     

    Assuming it was not some identifiable data, was the tag and the data within the tag?

    For example, this is from an OFX 1.x file:

    <SECNAME>ALEXANDER &amp; BALDWIN INC<TICKER>ALEX

    In OFX 1.x file, not all items have closing tags (with the backlash).

    7 ianuarie 2011 17:45
  • On Fri, 7 Jan 2011 17:45:24 +0000, Cal Learner wrote:
     
    >On Fri, 7 Jan 2011 16:12:46 +0000, Cal Learner wrote: Also, I tried the substitution of &AMP; for & and Money would still not do the import.  It was only when I deleted the & that Money would do the import.  
    >
    >Assuming it was not some identifiable data, was the tag and the data within the tag?
    >
    >For example, this is from an OFX 1.x file:
    >
    ><SECNAME>ALEXANDER &amp; BALDWIN INC<TICKER>ALEX
    >
    >In OFX 1.x file, not all items have closing tags (with the backlash).
     
    Here is the sequence from the file that contained the ampersand.  This is a credit card account:
     
    ========================
    <STMTTRN>
    <TRNTYPE>DEBIT
    <DTPOSTED>20101207
    <TRNAMT>-00000000008.99
    <FITID>244450003405511158361771001
    <NAME>BARNES & NOBLE #2052
    </STMTTRN>
    ==========================
     

    Ron
    7 ianuarie 2011 18:42
  • Weird. I would expect

    <NAME>BARNES & NOBLE #2052

     to fail under Money and Quicken, and I would expect 

     <NAME>BARNES &AMP; NOBLE #2052

     to succeed under both.

    7 ianuarie 2011 18:50
  • On Fri, 7 Jan 2011 18:50:09 +0000, Cal Learner wrote:
     
    >
    >
    >Weird. I would expect
    >
    ><NAME>BARNES & NOBLE #2052
    >
    > to fail under Money and Quicken, and I would expect 
    >
    > <NAME>BARNES &AMP; NOBLE #2052
    >
    > to succeed under both.
     
    I have no idea what would happen with Quicken, but I went back to the original
     
    Did a Find "&"  Replace with &AMP;
     
    This was the result:
     
    =================================
    <STMTTRN>
    <TRNTYPE>DEBIT
    <DTPOSTED>20101207
    <TRNAMT>-00000000008.99
    <FITID>244450003405511158361771001
    <NAME>BARNES &AMP; NOBLE #2052
    </STMTTRN>
    ==============================
     
    And when I tried to import it into Money, it gave me an error message about the file being bad.
     
    Previously, I had replace & with nothing, and it worked OK.
     

    Ron
    7 ianuarie 2011 19:24
  • I think I was wrong about the &amp; not being case sensitive. I tried your section in a test file. It did not register, but there was no error indicated. This was in Money Plus Deluxe Sunset. I tried using lower case, and it worked fine.

    =================================
    <STMTTRN>
    <TRNTYPE>DEBIT
    <DTPOSTED>20101207
    <TRNAMT>-00000000008.99
    <FITID>244450003405511158361771001x
    <NAME>BARNES &amp; NOBLE #2052
    </STMTTRN>
    ==============================
    Try that one. Note that I changed the FITID so that Money would not think it had already seen that transaction. I also changed the <TRNUID>earlier in the file for the same reason.
     
    As I was looking into QFX/OFX files fetched by PocketSense, I noted a funny substitution. Here is a line:
    <NAME>BATH &amp;amp; BODY WORKS
    Clearly the code made a substitution to avoid a problem, but it also did the substitution when it was not a problem.
     
     
    7 ianuarie 2011 20:30
  • On Fri, 7 Jan 2011 20:30:08 +0000, Cal Learner wrote:
     
    >
    >
    >I think I was wrong about the &amp; not being case sensitive. I tried your section in a test file. It did not register, but there was no error indicated. This was in Money Plus Deluxe Sunset. I tried using lower case, and it worked fine. ================================= <STMTTRN> <TRNTYPE>DEBIT <DTPOSTED>20101207 <TRNAMT>-00000000008.99 <FITID>244450003405511158361771001x <NAME>BARNES &amp; NOBLE #2052 </STMTTRN> ============================== Try that one. Note that I changed the FITID so that Money would not think it had already seen that transaction. I also changed the <TRNUID>earlier in the file for the same reason.   As I was looking into QFX/OFX files fetched by PocketSense, I noted a funny substitution. Here is a line: <NAME>BATH &amp;amp; BODY WORKS Clearly the code made a substitution to avoid a problem, but it also did the substitution when it was not a problem.    
     
     
    Yup, case sensitivity was the issue.  I changed to lower case, and it works here, too.
     

    Ron
    8 ianuarie 2011 00:25