Help with importing a downoad of my Capital One CD

[bobj bkln]

I downloading ofx and qfx files  from Capital One for years.  Savings, Credit cards and cds and importing them  (as ofx) into Money Sunset for years without any problem.  Recently I rolled over a CD and the new one throws an error when I try to import its qfx download.  All other accounts still work fine as did the previous CD.

Here's an example of my latest download (dollar amounts and acct numbers hidden)

xml version="1.0" encoding="utf-8" ?>
<?OFX OFXHEADER="200" VERSION="202" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE" ?>
<OFX>
    <SIGNONMSGSRSV1>
        <SONRS>
            <STATUS>
                <CODE>0</CODE>
                <SEVERITY>INFO</SEVERITY>
            </STATUS>
            <DTSERVER>20240917134623.834[-4:EDT]</DTSERVER>
            <LANGUAGE>ENG</LANGUAGE>
            <FI>
                <ORG>Capital One Bank</ORG>
                <FID>1001</FID>
            </FI>
            <INTU.BID>1236</INTU.BID>
        </SONRS>
    </SIGNONMSGSRSV1>
    <BANKMSGSRSV1>
        <STMTTRNRS>
            <TRNUID>0</TRNUID>
            <STATUS>
                <CODE>0</CODE>
                <SEVERITY>INFO</SEVERITY>
            </STATUS>
            <STMTRS>
                <CURDEF>USD</CURDEF>
                <BANKACCTFROM>
                    <BANKID>nnnnnnnnn</BANKID>
                    <ACCTID>nnnn</ACCTID>
                    <ACCTTYPE>CD</ACCTTYPE>
                </BANKACCTFROM>
                <BANKTRANLIST>
                    <DTSTART>20240817200000.000[-4:EDT]</DTSTART>
                    <DTEND>20240916200000.000[-4:EDT]</DTEND>
                    <STMTTRN>
                        <TRNTYPE>CREDIT</TRNTYPE>
                        <DTPOSTED>20240913000000.000[-4:EDT]</DTPOSTED>
                        <TRNAMT>$$.¢¢</TRNAMT>
                        <FITID>2024091350</FITID>
                        <MEMO>Monthly Interest Paid</MEMO>
                    </STMTTRN>
                </BANKTRANLIST>
                <LEDGERBAL>
                    <BALAMT>$$$$.¢¢</BALAMT>
                    <DTASOF>20240917134623.834[-4:EDT]</DTASOF>
                </LEDGERBAL>
            </STMTRS>
        </STMTTRNRS>
    </BANKMSGSRSV1>
</OFX>

I ran it through the OFX analyzer and got this  result:

***

HRESULT       = -2147450874
Error Code    = 243
Severity      = X
Type          = C
Error Message = XCInvalid Data at "%s". Enumeration value not allowed. (Data in pszBuffer)
Other Info    = MNYSGMPB: SGML error at (null), line 29 at ">":
          XCInvalid Data at "\030NBANKACCTFROM.ACCTTYPE". Enumeration value not allowed. (Data in pszBuffer)

Current Buffer = C

***

Can someone take a quick look to see what the problem is? If it requires a  lot of work, there's no need to bother.  It's only one entry/month that I have to do manually.

[First Name Last Name]

Bob, I think the issue line

                    <ACCTTYPE>CD</ACCTTYPE>

Though CD is valid type per OFX spec. It looks like money does not like it. As a quick test, I changed the text CD to CHECKING and the analyzer passed.

--
You received this message because you are subscribed to the Google Groups "Microsoft Money" group.
To unsubscribe from this group and stop receiving emails from it, send an email to microsoft-mon...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/6a69f887-a11b-4f5d-8eab-55e19a3e776en%40googlegroups.com.

[First Name Last Name]

Actually, I am taking it back. According to the OFX 2.0.3 spec, CD is NOT a valid type. It was added later. So

Capital One is at fault here.

* Declare using version VERSION="202"

* but use an invalid ACCTTYPE value of CD

[Cal Learner]

Sounds like a good job for a simple scrubber... change any "<ACCTTYPE>CD"  to "<ACCTTYPE>CHECKING"

It could be more or less robust, allowing for whitespace variations, or not.

[bobj bkln]

Thanks Cal and hleofx.  Worked perfectly when I changed CD to Checking.  

Surprisingly though, Money still would not accept it when I changed CD  to Savings (another approved type that seemed to make more sense programmatically)

[Cal Learner]

Bob: Would you like me to make a Python program that has this usage in a command line:

python fix_capital1.py whatever.ofx

Whatever.ofx gets read in , and the <ACCTTYPE>CD is changed to <ACCTTYPE>CHECKING. The output is written to ModOut.ofx or whatever hard-coded filename you wanted. The input filename could be something.qfx just as readily. I don't plan to check the contents, and would just make the substitution. If the expected string was not in the file, then the file would be passed thru unchanged.

Where I wrote python in the command line example, you would use whatever your python installation requires. Capital1.py is the proposed name for the python file.

You could invoke it via a batch file. I expect the batch file could be run without explicitly starting a command window.

I could write it for Python 2.7.x easily, because that is what PocketSense and F2O use, but I could try it for Python 3.x, which I think only differs for the print in this case.

[bobj bkln]

«Bob: Would you like me to make a Python program that has this usage in a command line: »

Only if it is not much work for you, as manually changing CD to Checking  once a month is not a real struggle for me. 

The version of Python I have is 3.12

--

Peace,

BobJ

[Cal Learner]

hardly tested.  No indents needed.

############begin CHASE_CD_FIX_FIX.PY########
#modifies OFX or QFX file with  to change any <ACCTTYPE>CD  to <ACCTTYPE>CHECKING
#invoke with filename of the source as the parameter.

#Example: python fix_capital1.py whatever.ofx
#2024-9-19 Cal Learner...

import sys, re

ofxfile1= open(sys.argv[1],'r') # open for read in current directory
    # Input file read in as parameter. Output file fixed in this case.
ofxfile2= open("ModOut.ofx",'w') # open for write in current directory

ofx=ofxfile1.read()

# make the change
ofx_mod=re.sub("<ACCTTYPE>CD","<ACCTTYPE>CHECKING",ofx,1,flags=re.DOTALL) #the main change.

ofxfile2.write(ofx_mod)

ofxfile1.close()
ofxfile2.close()
print ("Modified version of ", sys.argv[1],' written to "ModOut.ofx" file.')

############end CHASE_CD_FIX_FIX.PY########

[bobj bkln]

Thanks. Cal.  But what are the next steps?

Do I  copy everything past your "hardly tested" line and paste it into Notepad and save it with a .py extension?  Do I need to change all your ofxes to qfxes?

Lastly do I run the file by right clicking on the cap1.qfx  file and choose Open With?

[Cal Learner]

Yes, copy copy everything past your "hardly tested" line and paste it into Notepad and save it with a .py extension?  Since everything past a # is a comment, those lines need not be copied, but won't hurt.

You do not need to change ofx to qfx in the *.py file. The ofx strings in the file are variables, and the variable named ofx is a string of bytes/characters.

Regarding " Lastly do I run the file by right clicking on the cap1.qfx  file and choose Open With?", I have never used that method for python. I am largely cmd window and batch file oriented. With open With, that sounds interesting. Somebody who is more up on this could probably tell you for sure....

[Cal Learner]

It would be easy to change the python code to make  the input file open to always use a fixed filename and path, rather than having the *.qfx file specified as a parameter.

[bobj bkln]

Cal,

I tried the .py file you wrote and it works fine with this month's downloaded file from CapOne.  I named the python file Cap1_CD_Fix.py and saved it in my Downloads folder.  To run it, I created a shortcut to the file and put it in the "C:\Users\<username>\AppData\Roaming\Microsoft\Windows\SendTo" folder.  When I download the qfx file from Cap One, I just go to the Downloads folder, right click on the .qfx file and chose Sendto > Cap1_CD_Fix.py  and the fixed file appears as the newest file in the Downloads folder. Double clicking on it imports it into Money.

Thanks again 

--

Peace,

BobJ

[Cal Learner]

BobJ: Glad it is works for you. In the  F2O code I have a batch file fset.bat that figures out the latest version of a couple files and assigns the paths to environment variables. Here is the important part, which you could adapt to your batch file...

=================================================
for /f "delims=" %%x in ('dir /od /a-d /b %USERPROFILE%\Downloads\Accounts_History*.csv') do set hist=%%x

echo %hist%
for /f "delims=" %%x in ('dir /od /a-d /b %USERPROFILE%\Downloads\Portfolio_Positions*.csv') do set positions=%%x
echo %positions%

====================

Also note that instead of writing "C:\Users\<username>\AppData\Roaming\Microsoft\Windows\SendTo" folder, you could write "%USERPROFILE%\AppData\Roaming\Microsoft\Windows\SendTo" folder. In other words, you can actually use %USERPROFILE% in your batch file.   In a command window, to see what %USERPROFILE% represents in a batch file for the current user try this:

echo %USERPROFILE%

[bobj bkln]

Thanks, but I am not using a batch file.  I just right click on the  downloaded qfx file and choose Sendto > Cap1_CD_Fix.py.