locked
Problem with TransAmerica QFX download RRS feed

  • Question

  • I am trying to download to Money investment data from a single investment in an IRA (not a 401K) account with Transamerica which only has a Quicken (QFX) download.  When I try to import the file  (even with file extension changed to OFX) I get an error message that this is not an MSMoney file.  So I tried running it through the OFXAnalyzer, but I got the following message:

    ERROR -- File not found, File empty, Out of Memory, or Unknown Parse Failure

    So for some reason, the file doesn't conform to OFX standards in a way that defeats the Analyzer.  I am including the text of the downloaded file (with some numbers replaced by pound signs [#]) in case an expert has the time to take a quick look to see if there is an easy fix to make it work.  Thanks in advance.

    OFXHEADER: 100
    DATA: OFXSGML
    VERSION: 102
    SECURITY: NONE
    ENCODING: USASCII
    CHARSET: 1252
    COMPRESSION: NONE
    OLDFILEUID: NONE
    NEWFILEUID: NONE

    <OFX>
    <SIGNONMSGSRSV1>
    <SONRS>
    <STATUS>
    <CODE>0</CODE>
    <SEVERITY>INFO</SEVERITY>
    </STATUS>
    <DTSERVER>20170802</DTSERVER>
    <LANGUAGE>ENG</LANGUAGE>
    <FI>
    <ORG>Transamerica Retirement Solution</ORG>
    <FID>101</FID>
    </FI>
    <INTU.BID>10766</INTU.BID>
    </SONRS>
    </SIGNONMSGSRSV1>

    <INVSTMTMSGSRSV1>
    <INVSTMTTRNRS>
    <TRNUID>0</TRNUID>
    <STATUS>
    <CODE>0</CODE>
    <SEVERITY>INFO</SEVERITY>
    </STATUS>
    <INVSTMTRS>
    <DTASOF>20170801</DTASOF>
    <CURDEF>USD</CURDEF>
    <INVACCTFROM>
    <BROKERID>divinvest.com</BROKERID>
    <ACCTID>xxxxxxxx  xxxxx/xxxx</ACCTID>
    </INVACCTFROM>
    <INVTRANLIST>
    <DTSTART>20170101</DTSTART>
    <DTEND>20170801</DTEND>
    <BUYOTHER>
    <INVBUY>
    <INVTRAN>
    <FITID>2017-03-29111020170329522V555666538302698440024597443026984400</FITID>
    <DTTRADE>20170329</DTTRADE>
    <DTSETTLE>20170329</DTSETTLE>
    </INVTRAN>
    <SECID>
    <UNIQUEID>56665383026984400</UNIQUEID>
    <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>35.726168</UNITS>
    <UNITPRICE>26.380000</UNITPRICE>
    <TOTAL>942.46</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
    </INVBUY>
    </BUYOTHER>
    <BUYOTHER>
    <INVBUY>
    <INVTRAN>
    <FITID>2017-06-29111020170629522HRV5666538302698440024597443026984400</FITID>
    <DTTRADE>20170629</DTTRADE>
    <DTSETTLE>20170629</DTSETTLE>
    </INVTRAN>
    <SECID>
    <UNIQUEID>56665383026984400</UNIQUEID>
    <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <UNITS>41.684230</UNITS>
    <UNITPRICE>26.810000</UNITPRICE>
    <TOTAL>1117.55</TOTAL>
    <SUBACCTSEC>CASH</SUBACCTSEC>
    <SUBACCTFUND>CASH</SUBACCTFUND>
    </INVBUY>
    </BUYOTHER>
    </INVTRANLIST>
    <INVPOSLIST>
    <POSOTHER>
    <INVPOS>
    <SECID>
    <UNIQUEID>56665383026984400</UNIQUEID>
    <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <HELDINACCT>CASH</HELDINACCT>
    <POSTYPE>LONG</POSTYPE>
    <UNITS>
                        ####.######
                </UNITS>
    <UNITPRICE>
                        27.260000
                </UNITPRICE>
    <MKTVAL>########</MKTVAL>
    <DTPRICEASOF>20170801</DTPRICEASOF>
    </INVPOS>
    </POSOTHER>
    </INVPOSLIST>
    <INV401K>
    <EMPLOYERNAME>Transamerica Traditional IRA</EMPLOYERNAME>
    <PLANID>79762133026984400</PLANID>
    </INV401K>
    <INV401KBAL>
    <TOTAL>#####.##</TOTAL>
    </INV401KBAL>
    </INVSTMTRS>
    </INVSTMTTRNRS>
    </INVSTMTMSGSRSV1>
    <SECLISTMSGSRSV1>
    <SECLIST>
    <MFINFO>
    <SECINFO>
    <SECID>
    <UNIQUEID>56665383026984400</UNIQUEID>
    <UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
    </SECID>
    <SECNAME>Transamerica Multi-Mgd Balanced A</SECNAME>
    <FIID>N315</FIID>
    </SECINFO>
    </MFINFO>
    </SECLIST>
    </SECLISTMSGSRSV1>
    </OFX>


    Thursday, August 3, 2017 4:09 AM

Answers

  • 1. The error indicating "File not found..." is caused by a space after the colon on each of the header lines. There should be no spaces.

    2. After that is fixed, the parser will warn that the <UNITS> for the <INVPOS> element is invalid, again due to spaces. There should be no spaces after the ">" character, nor after the end of the value. 

    <UNITS>
                        ####.######
                </UNITS>

    should be <UNITS>####.######</UNITS>

    Fix all instances where the extra spaces and carriage returns exist.

    P.S. There are other errors too:

    1. The BUY transactions must have the total value as a negative, money is coming out of your account.

    2. The <INV401K> and <INV401BAL> aggregates are invalid for OFX 1.02 (Version 102). You would have to change this to an OFX 2.0 formatted file. Essentially, you would have to change the header lines.

    The OFX 2.0 version of the header is:

    <?xml version="1.0" encoding="US-ASCII" standalone="no"?>
    <?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>

    Looks like they have totally messed up the OFX parsing on their side.


    Bob - MSN Mobile


    • Edited by BobSmiley Thursday, August 3, 2017 4:42 PM
    • Marked as answer by bobjbkln Friday, August 4, 2017 3:51 AM
    Thursday, August 3, 2017 3:40 PM

All replies

  • 1. The error indicating "File not found..." is caused by a space after the colon on each of the header lines. There should be no spaces.

    2. After that is fixed, the parser will warn that the <UNITS> for the <INVPOS> element is invalid, again due to spaces. There should be no spaces after the ">" character, nor after the end of the value. 

    <UNITS>
                        ####.######
                </UNITS>

    should be <UNITS>####.######</UNITS>

    Fix all instances where the extra spaces and carriage returns exist.

    P.S. There are other errors too:

    1. The BUY transactions must have the total value as a negative, money is coming out of your account.

    2. The <INV401K> and <INV401BAL> aggregates are invalid for OFX 1.02 (Version 102). You would have to change this to an OFX 2.0 formatted file. Essentially, you would have to change the header lines.

    The OFX 2.0 version of the header is:

    <?xml version="1.0" encoding="US-ASCII" standalone="no"?>
    <?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>

    Looks like they have totally messed up the OFX parsing on their side.


    Bob - MSN Mobile


    • Edited by BobSmiley Thursday, August 3, 2017 4:42 PM
    • Marked as answer by bobjbkln Friday, August 4, 2017 3:51 AM
    Thursday, August 3, 2017 3:40 PM
  • Thanks so much, Bob.  I'll give it a whirl over the weekend.  I'm curious, do you happen to know whether these errors would also prevent it being imported into Quicken?

    BobJ

    Thursday, August 3, 2017 5:39 PM
  • I made the suggested changes and ran the file through the OFXAnalyzer.  It worked perfectly (it parsed with no errors).  I'll try it on the Money file this weekend.  Thanks again BobSmiley.

    • Edited by bobjbkln Friday, August 4, 2017 3:54 AM
    Friday, August 4, 2017 3:54 AM