locked
Problems with Target Red Card QFX RRS feed

  • Question

  • Target recently re-did their entire Red Card web site and in all their wisdom eliminated the ability to download OFX files.  They still permit QFX downloads, but the QFX won't load into money.  The old "corrupt data" message.

    Anyone else use Red Card and willing to help me debug?  I found one problem right a way - space after the header colons.  I know Money doesn't like that.  There are also a number of additional fields compared to other OFX files, but I haven't narrowed down where the issue may lie.

     
    Thursday, September 27, 2018 3:01 AM

All replies

  • You might try the fix-up for Well Fargo described in  https://social.microsoft.com/Forums/mvpforum/en-US/2ded7661-b626-4979-8424-26639e28847a/wells-fargo-download-qfx-file-script-to-fix-up-missing-endofline?forum=money

    The reason I suspect it could work unmodified is that hleofxquotes2 rebuilds the header.

    Thursday, September 27, 2018 11:49 AM
    Moderator
  • Thanks.  It seems like the problem is different.  End of lines are all there.    I managed to discover the old MS OFXAnalyzer and it looks like it might be some bad/inappropriate tags.  Now I just need to the time to figure out exactly which ones.  I guess we should be grateful that 8 years later we still have MS$.
    Friday, September 28, 2018 1:49 AM
  • You could edit the file with Notepad, and change your account number to 1234567. Then paste the contents into a post if it is not too long.

    Friday, September 28, 2018 3:13 AM
    Moderator
  • Was this resolved?  I haven't found a fix yet.

    Andrew

    Tuesday, November 27, 2018 5:15 AM
  • If you post a sample bad file, I will take a look.

    For the sample bad file, mask out any personal info such as account number.

    Wednesday, November 28, 2018 7:02 PM
  • I also have a Target REDcard account.  I just tried a download of a couple of transactions and here is the file as opened in Notepad:

    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><MESSAGE>SUCCESS</MESSAGE></STATUS><DTSERVER>20181128203701.000[0:GMT]</DTSERVER><LANGUAGE>ENG</LANGUAGE><FI><ORG>Target</ORG><FID>3820</FID></FI><INTU.BID>3820</INTU.BID><INTU.USERID>Target</INTU.USERID></SONRS></SIGNONMSGSRSV1><CREDITCARDMSGSRSV1><CCSTMTTRNRS><TRNUID>0</TRNUID><STATUS><CODE>0</CODE><SEVERITY>INFO</SEVERITY></STATUS><CCSTMTRS><CURDEF>USD</CURDEF><CCACCTFROM><ACCTID>9609</ACCTID></CCACCTFROM><BANKTRANLIST><DTSTART>20181127000000.000[0:GMT]</DTSTART><DTEND>20181127000000.000[0:GMT]</DTEND><STMTTRN><TRNTYPE>CREDIT</TRNTYPE><DTPOSTED>20181127000000.000[0:GMT]</DTPOSTED><DTUSER>20181127000000.000[0:GMT]</DTUSER><TRNAMT>66.7700</TRNAMT><FITID>24863158282018112720181128023701</FITID><CORRECTFITID>9609$$20181127$$66.77$2486315828</CORRECTFITID><REFNUM></REFNUM><NAME>TARGET DEBIT CARD ACH PAYMENT</NAME><SIC>0000</SIC></STMTTRN><STMTTRN><TRNTYPE>DEBIT</TRNTYPE><DTPOSTED>20181127000000.000[0:GMT]</DTPOSTED><DTUSER>20181127000000.000[0:GMT]</DTUSER><TRNAMT>-66.7700</TRNAMT><FITID>24863158272018112720181128023701</FITID><CORRECTFITID>9609$76394638331254507356875$20181127$$66.77$2486315827</CORRECTFITID><REFNUM>76394638331254507356875</REFNUM><NAME>                 LITITZ       PA</NAME><SIC>5310</SIC></STMTTRN></BANKTRANLIST><LEDGERBAL><BALAMT>0.00</BALAMT><DTASOF>20181128203701.000[0:GMT]</DTASOF></LEDGERBAL><AVAILBAL><BALAMT>999999.00</BALAMT><DTASOF>20181128203701.000[0:GMT]</DTASOF></AVAILBAL></CCSTMTRS></CCSTMTTRNRS></CREDITCARDMSGSRSV1></OFX>

    I also get an error when importing it.  In contrast, I have other accounts that generate QFX files and they import just fine. 

    I don't need to download REDCard transactions so this isn't an issue for me, but maybe this file will help hleofxquotes2 to sort out what Target is doing wrong.


    Bill Becker

    Wednesday, November 28, 2018 8:57 PM
  • Thanks Bill,

    First look, there were multiple issues with the file in term of OFX spec

    Header: need to remove spaces

    so

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

    Should look like

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


    Then there are some extra tags that are causing Money to choke. If I comment out/remove all block with the following tags, the Money will parse

                <!-- <CORRECTFITID>9609$$20181127$$66.77$2486315828</CORRECTFITID> -->
                <!-- <REFNUM></REFNUM> -->
                <!-- <NAME>TARGET DEBIT CARD ACH PAYMENT</NAME> -->
                <!-- <SIC>0000</SIC> -->

    <CORRECTFITID> is a valid OFX tag ... BUT it must accompanied with a <CORRECTACTION> (which was missing)

    REFNUM is a valid OFX tag but cannot be empty

    SIC (Standard Industrial Code) is valid OFX 2.0 tag but I am not sure for OFX 1.x? 

    So Bill, the following modified copied should be parsable by Money (I re-indent to make it easier to read but the rest of the content should be the same)

    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>
            <MESSAGE>SUCCESS</MESSAGE>
          </STATUS>
          <DTSERVER>20181128203701.000[0:GMT]</DTSERVER>
          <LANGUAGE>ENG</LANGUAGE>
          <FI>
            <ORG>Target</ORG>
            <FID>3820</FID>
          </FI>
          <INTU.BID>3820</INTU.BID>
          <INTU.USERID>Target</INTU.USERID>
        </SONRS>
      </SIGNONMSGSRSV1>
      <CREDITCARDMSGSRSV1>
        <CCSTMTTRNRS>
          <TRNUID>0</TRNUID>
          <STATUS>
            <CODE>0</CODE>
            <SEVERITY>INFO</SEVERITY>
          </STATUS>
          <CCSTMTRS>
            <CURDEF>USD</CURDEF>
            <CCACCTFROM>
              <ACCTID>9609</ACCTID>
            </CCACCTFROM>
            <BANKTRANLIST>
              <DTSTART>20181127000000.000[0:GMT]</DTSTART>
              <DTEND>20181127000000.000[0:GMT]</DTEND>
              <STMTTRN>
                <TRNTYPE>CREDIT</TRNTYPE>
                <DTPOSTED>20181127000000.000[0:GMT]</DTPOSTED>
                <DTUSER>20181127000000.000[0:GMT]</DTUSER>
                <TRNAMT>66.7700</TRNAMT>
                <FITID>24863158282018112720181128023701</FITID>
              </STMTTRN>
              <STMTTRN>
                <TRNTYPE>DEBIT</TRNTYPE>
                <DTPOSTED>20181127000000.000[0:GMT]</DTPOSTED>
                <DTUSER>20181127000000.000[0:GMT]</DTUSER>
                <TRNAMT>-66.7700</TRNAMT>
                <FITID>24863158272018112720181128023701</FITID>
              </STMTTRN>
            </BANKTRANLIST>
            <LEDGERBAL>
              <BALAMT>0.00</BALAMT>
              <DTASOF>20181128203701.000[0:GMT]</DTASOF>
            </LEDGERBAL>
            <AVAILBAL>
              <BALAMT>999999.00</BALAMT>
              <DTASOF>20181128203701.000[0:GMT]</DTASOF>
            </AVAILBAL>
          </CCSTMTRS>
        </CCSTMTTRNRS>
      </CREDITCARDMSGSRSV1>
    </OFX>


    Wednesday, November 28, 2018 11:41 PM
  • Nice analysis!  Clearly Target and/or Quicken haven't considered adherence to the OFX spec as a priority.  Perhaps there is a computer science professor out there who can give out the development of a parsing program that will convert this mess into an OFX-compliant file as a homework assignment?

    Since I use Target rather infrequently, I enter those few transactions manually.  I don't have much incentive to automate it.  See this xkcd for a quantitative analysis of the tradeoffs:

    https://xkcd.com/1205/


    Bill Becker

    Thursday, November 29, 2018 2:36 AM
  • I wrote a little vb script program to clean up Target's exported qfx file so it can be imported into ms money. I'm not a developer - just hacked it together because I don't want to manually enter all of my wife's target purchases! Save this code as c:\temp\fixtarget.vbs then run it from a command prompt with cscript fixtarget.vbs in the c:\temp\ directory with transactions.qfx as the downloaded file. It will produce targetcorrected.qfx which should import into ms money.

    On Error Resume Next
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFileToRead = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\temp\Transactions.QFX",1)
    Set objFileToWrite = CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\temp\targetcorrected.QFX",2,true)
    Dim strLine
    do while not objFileToRead.AtEndOfStream
         strLine = objFileToRead.ReadLine()
    	 
         If InStr(strLine,"<CORRECTFITID>") or InStr(strLine,"REFNUM") or InStr(strLine,"<NAME>") or InStr(strLine,"<SIC>") Then
    		'skip this line
    	 ElseIf InStr(strLine,": ") Then
    		data = Replace(strLine,": ",":", 1,-1,1)
    		objFileToWrite.WriteLine(data)
    	 Else
    		data = strLine
    		objFileToWrite.WriteLine(data)
          End If
    loop
    
    objFileToRead.Close
    Set objFileToRead = Nothing
    objFileToWrite.Close
    Set objFileToWrite = Nothing
    

    Saturday, February 2, 2019 8:52 AM