Citizens Bank Web Site Update Broke OFX Import

[Michael Albert]

Hi,

Citizens Bank just switched to a new web site design and now my weekly transaction import into MSM fails. When I find the new location where they put the OFX file and import it I'm told the file is "invalid or contains corrupt data". When I view the file there's nothing obviously wrong: it looks like the same format as the old one.  

Any ideas on how I can get this working again?  

Thanks,
Mike

[First Name Last Name]

Hi Mike,

I've seen such error in the past when there is NO transactions in OFX file.

Other common error is 

* when there is a & in the a comment, not encoded correctly as &

Otherwise, if you send me a sanitized version: obscured account number, amount, and/or an personal info ... I will be happy to take a look

hleofxquotes at gmail dot com

--
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/83065daa-bc82-49d6-bb11-8e697d80488fn%40googlegroups.com.

[Cal Learner]

Try OFX Analyzer. https://microsoftmoneyoffline.wordpress.com/ofx-file-analzer/

The output is a bit arcane, but if it does not speak to you, paste its message into a post. 

For example, here is a parse report that does not have a bug:

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

***OFX 1.02 Add ~10
***OFX 2.0 Add ~2
Running Data Through Parser
No Parse Errors

Reading Data Into Buffer
Tokenizing Data
Initializing Data Structures
Analyzing Data
Verifying Security Types
Verifying Security Uniqueness
Verifying FITID Uniqueness
Verifying Sign Correctness
Verifying Total Calculations
Verifying Other
Done Analyzing File

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

Check that the  output does not contain your account number.

[Michael Albert]

I ran the Analyzer it didn't didn't like the OFX file.  Here's the output: 

*** All line number references do not include header lines ***

***OFX 1.02 Add ~10

***OFX 2.0 Add ~2

Running Data Through Parser

HRESULT       = 0

Error Code    = 0

HRESULT       = -2147450874

Error Code    = 237

Severity      = X

Type          = C

Error Message = XCInvalid Date Data - %s (Data in pszBuffer)

Other Info    = MNYSGMPB: SGML error at (null), line 32 at ">":

          XCInvalid Date Data - \023XSTMTTRN.DTPOSTED (Data in pszBuffer)

Current Buffer = 2

So it looks like it doesn't like a date field at line 32 (not including the header) which probably is probably line 41, which is this:

<DTPOSTED>2023-06-26

When I look at an old file that worked is see lines like this: 

<DTPOSTED>20230531120000

So the different date format seems to be the (or at least a) problem.  Any suggestions how to circumvent it?  I could always write a script to convert the new date format to the old one, but that probably would turn this automatic process into a manual one.  Is there some other way to fix this?  

The first 50 lines of the formatted redacted file in below.  

Thanks,

Mike

------------------------------------------------------------

OFXHEADER:100

DATA:OFXSGML

VERSION:102

SECURITY:NONE

ENCODING:USASCII

CHARSET:1252

COMPRESSION:NONE

OLDFILEUID:NONE

NEWFILEUID:NONE

<OFX>

    <SIGNONMSGSRSV1>

        <SONRS>

            <STATUS>

                <CODE>0

                <SEVERITY>INFO

            </STATUS>

            <DTSERVER>20230626120000

            <LANGUAGE>ENG

        </SONRS>

    </SIGNONMSGSRSV1>

    <BANKMSGSRSV1>

        <STMTTRNRS>

            <TRNUID>1

            <STATUS>

                <CODE>0

                <SEVERITY>INFO

            </STATUS>

            <STMTRS>

                <CURDEF>USD

                <BANKACCTFROM>

                    <BANKID>111111111

                    <ACCTID>1111111111

                    <ACCTTYPE>checking

                </BANKACCTFROM>

                <BANKTRANLIST>

                    <DTSTART>20211226120000

                    <DTEND>20230626120000

                    <STMTTRN>

                        <TRNTYPE>Debit

                        <DTPOSTED>2023-06-26

                        <TRNAMT>44.96

                        <FITID>unknown

                        <NAME>PwP  Smithfield

                        <MEMO>PwP  Smithfield

                    </STMTTRN>

                    <STMTTRN>

                        <TRNTYPE>Credit

                        <DTPOSTED>2023-06-26

                        <TRNAMT>1854.10

[First Name Last Name]

> <DTPOSTED>2023-06-26

Such format is out-of-spec (should be minimally 20230626)

 

One path is to report it back to Citizens Bank citing section "3.2.8.2 Date and Datetime" in the OFX spec

-- spec --

3.2.8.2 Date and Datetime
Elements specified as type date or datetime and generally starting with the letters “DT” accept a fully
formatted date-time-timezone string. For example, “19961005132200.124[-5:EST]” represents October 5,
1996, at 1:22 and 124 milliseconds p.m., in Eastern Standard Time. This is the same as 6:22 p.m.
Greenwich Mean Time (GMT).
Date and datetime also accept values with fields omitted from the right. They assume the following
defaults if a field is missing:
Specified date or datetimeAssumed defaults
YYYYMMDD12:00 AM (the start of the day), GMT
YYYYMMDDHHMMSSGMT
YYYYMMDDHHMMSS.XXXGMT
Note that times zones are specified by an offset and optionally, a time zone name. The offset defines the
time zone. Valid offset values are in the range from –12 to +12 for whole number offsets. Formatting is
+12.00 to -12.00 for fractional offsets, plus sign may be omitted.
Take care when specifying an ending date without a time. For example, if the last transaction returned for
a bank statement download was Jan 5 1996 10:46 am and if the <DTEND> was given as just Jan 6, the
next statement download request would have a <DTSTART> of just Jan 6, causing any transactions posted
OFX 2.3 Specification
10/16/2020
89on Jan 5 after 10:46 am to be missed. If results are available only daily, then just using dates and not times
will work correctly.
Note: Open Financial Exchange does not require servers or clients to use the full precision
specified. However, they are REQUIRED to accept any of these forms without complaint.
Some services extend the general notion of a date by adding special values, such as “TODAY.” These
special values are called “smart dates.” Specific requests indicate when to use these extra values, and list
the element as having a special data type.

--

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/7ba2df2d-1a79-436e-b19f-54266e0624fan%40googlegroups.com.

[Cal Learner]

I agree about the date thing. Nice troubleshooting.

If you used PocketSense, I might add that to a future scrubber for that bank. Otherwise, yes, just write a scrubber program.

[Michael Albert]

So it turns out only DTPOSTED records have the incorrect date format.  The other date records are ok.  Here's an awk line that fixes the record:

$0 = gensub(/<DTPOSTED>([0-9])([0-9])([0-9])([0-9])\-([0-9])([0-9])\-([0-9])([0-9])/,"<DTPOSTED>\\1\\2\\3\\4\\5\\6\\7\\8120000","a")

There may be other format errors lurking there as well.  I haven't checked because I'm looking at another problem: the old web site let me choose to download transactions since my last download, but the new one gives me everything for the year with no choice. 

[Cal Learner]

Giving you everything is no problem if they have done the FITIDs correctly-- they are always the same for the same transaction.

[Michael Albert]

I tried importing the most recent OFX file from Citizens with the DTPOSTED records adjusted.  When I did it correctly ignored all the files that had been previously imported, but it also ignored all the new transactions except the most recent.  When I looked at the FITIDs the reason was obvious: they were all set to "unknown".  When there were multiple occurrences of the same transaction MSM reasonably kept the most recent and discarded the rest.  The FITIDs from the old Citizens web site imports were all unique numbers.

I'm trying to figure out how to make my awk script fix these faulty OFX files for the time being so that I can continue to use MSM.  Maybe construct a fake unique FITID to replace the "unknown" records by hashing the date, name, and amount fields to make a probably-unique number?  If they ever fix these problems I'll have a bunch of transactions to manually delete, but otherwise does that seem like a reasonable approach? 

[First Name Last Name]

> I'm trying to figure out how to make my awk script fix these faulty OFX files for the time being so that I can continue to use MSM.  Maybe construct a fake unique FITID to replace the "unknown" records by hashing the date, name, and amount fields to make a probably-unique number?  If they ever fix these problems I'll have a bunch of transactions to manually delete, but otherwise does that seem like a reasonable approach? 

yes, but do account for collision. For example: Let's say you take subway to work on same day. Going there and going back are likely going to have two entries with same date, name, and amount. So you probably are going to have to keep a map around. You probably are going to need a bit more than awk. Maybe Perl or Python.

To view this discussion on the web visit https://groups.google.com/d/msgid/microsoft-money/bd60b8eb-b893-45fc-a2c0-d4e989f491cdn%40googlegroups.com.

[Michael Albert]

It just occurred to me that the old Citizens web site export apparently generated FITIDs incorrectly too. On the old web site if I accidentally exported a transaction twice MSM would record two identical transactions. Since MSM correctly uses the FITID to identify a transaction, that seems to imply that Citizens was generating a new unique FITID each time it exported a transaction.  

It seems to me my best approach for creating FITIDs is to use a hash made from every field of the transaction.  I understand there's an unlikely but possible chance of collision which would mean I'd loose a transaction, but given the information available when processing the file I don't know what else to do. I could generate a unique FITID using the date and say a serial number (during the day) but then I'd get a different FITID for a transaction each time I exported, and so I'd loose the ability to have MSM ignore duplicate transactions. 

[Cal Learner]

Mike: I have made a FID hash in Python. It is fancy enough to provide for you buying 4 cups of coffee in  different transactions on the same day, and providing unique FITIDs for each.  If you passed your files thru Pocketsense, I could add that function to a scrubber that I make. If not, I could post the code fragment, and you make of it what you will.

In Pocketsense, if you put a QFX or OFX file into the import folder, it processes the file thru a scrubber, and sends the file, now renamed to *.ofx, thru to Money.

What I did not do is to cause it to remove transactions older than a specified date to avoid transactions that have already been processed. I was not working on that code for PocketSense, but rather code that was intended to look at a Fidelity CSV file, and produce a Fidelity OFX. For now, the Fidelity OFX server works fine, so my efforts on that are paused.

[Michael Albert]

Cal,

I too have been busy.  I updated my awk script to make a 10 digit hash which takes as input every record of the transaction.  Any difference in any field of a transaction will produce an (almost guaranteed to be) distinct hash.  Unfortunately the <DTPOSTED> records just have dates with no times so it seems to me there's no way get a different hash for 2 different transactions if all the fields are the same.  How do you get past that problem?  If that's handled in your Python script I'd like to see it.  

I also added date filtering I need to exclude transactions already imported.  I'm about to try an actual import to bring my MSM up to date.  

I'll probably just manually import my corrected OFX files into MSM.  I'm sure this could be automated using using Python but I'm now 22 yrs. retired from software development and haven't kept up with the new development environments.  I can do most of what I want with awk so and even the Windows command processor so I'll stay with that.  

Thanks,
Mike

[Cal Learner]

To get around that, I recorded each FITID I did for a day into a Python "set". If the FITID was already used that day, I incremented a counter that I added to the stuff I hashed. 

[Michael Albert]

Cal, 

I considered something like that and I thought I found a problem.  Say you imported the same transaction twice in a day.  Wouldn't the second transaction get a different FITID and therefore be incorrectly identified as a different transaction?  

By the way it appears that Citizens Bank put their new OFX system online completely prematurely.  In the <TRNAMT> record the value should be negative for a debit but they never generate a negative value.  Every transaction no matter the type is interpreted as a credit by MSM.  I sent them a detailed email describing the problems.  

Thanks for all your help working through these problems. 

Mike

[Cal Learner]

No problem, since I create the "set"  initially empty each time I process the OFX file.

[Cal Learner]

Actually, I create a new set for each day of an OFX file. I presume the transaction days will be in order. So if the date of this transaction is not the same as the date of the previous transaction, then I empty the set.

[Geoff Sullivan]

I downloaded CSV and compared it to the OFX. The CSV definitely has some sort of ID for most transactions but for the number of transactions I have for my accounts it's not worth the trouble to write a script to translate it to OFX. Besides I'm mostly a Linux guy. Citizen's Bank "tech" support says they are working on the problem. WTF did they release all these changes without proper testing? They must have some $$$ deal with Intuit and Quicken that was time sensitive. I'll bet those were fully tested.

Geoff

[Michael Albert]

Hi Geoff,

It's definitely a moving target.  I sent them a detailed email listing the problems I saw on 6/27.  A week later I tried again and found that a lot of the problems had been fixed.  I emailed them again saying what problems remained, noting a fix that was incorrect, and noting a new problem I hadn't noticed before.  Based on what you're saying I expect I'll see more changes when I do my weekly MSM update tomorrow.  

I agree that it's incredibly inconsiderate and unprofessional to introduce the new web site without ensuring that this feature was working correctly.  I worked my entire professional life developing software and have never seen something released that was essentially useless.  It makes me think they really don't care about their customers.  

The one good thing is that they  are fixing things.  Previously I've reported different problems and they've totally ignored them and so I wouldn't have been surprised if they had done that here too. 

Mike

[joe dempsey]

This is how software testing is done these days - release asap, let the customers/users do the debugging and fix whatever bugs they report. Much cheaper than doing full testing before release

[Geoff Sullivan]

FWIW, I am a Linux geek and my wife is the MS Money user. Just for the hell of it I imported a Citizens Bank OFX file into GnuCash and it worked just fine. That was yesterday. Don't know if they had fixed it in the meantime or not. Seems MS Money may be more fussy about OFX specs than GnuCash. GnuCash will also import CSV, QIF, and QFX files too. 

Geoff

[Michael Albert]

That's interesting.  Today I uploaded into MSM and found that Citizens has fixed nothing since last Monday.  There are still 2 errors that I know of.  

First, the FITID field is supposed to contain a unique value that identifies the transaction.  Currently they're all set to "unknown".  For MSM the effect is that it interprets all the transactions as the same one, and so it discards all but the last one.  That I can fix with the awk script.  Apparently GnuCash is ignoring that field, so probably you're ok as long as you don't import the same transaction more than once.  

The other problem is that the Payee and Memo both contain the same value which comes from the MEMO field and so the correct payee information is lost.  You might want to check to see if that happened with your import and you missed it, which I did the first time I looked.  To correct that problem I need to look at my online register and manually enter the Payee for each transaction. 

Mike

[Jeff Feit]

Wondering if anyone has an update or found a workaround. When I called Citizens today they said they are "aware of the problem but have no timing for when it will be fixed". One other thing that changed from before is that the download range of "since last download" is gone. That would be fine if the FITID worked correctly and it could ignore duplicates.

[Michael Albert]

Jeff,

I tried a few days ago and found that the FITID is still broken.  They did fix the Payee and Memo fields so they both have correct values.  Loosing "Since Last Download" is also aggravating.  The Filter lets you select Last 7 Days which mostly works for me but my guess is that's the best we'll get.  As I think I mentioned (or maybe not) I wrote an AWK script that fixes the FITID and some other problems that they've already fixed.  It takes the OFX file and generates a corrected OFX file with valid FITIDs.  It's clumsy to use but it works.  Let me know if you want a copy.

Also it still won't work with Firefox.  Stupid that they can't or can't bother to get Firefox to work.

Mike

[Jeff Feit]

I don't download on a regular schedule so the "since last download" made it easy for me. Before it quit working (and I tried to fix it) I knew nothing about how the OFX files worked. It seems like if the FITID was being used correctly, I could just download well past the last one and let it throw out the duplicates... so hopefully when (if) it gets fixed the FITID is done right.

One thing I found in looking at the OFX files is that they seem to be using invalid <TRNTYPE> entries. I'm seeing things like "ONLINE TRANSFER" and "INTEREST" which from what I can tell aren't valid. This is my reference: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&ved=2ahUKEwiayJ_ouL6BAxUuGVkFHbYkCbAQFnoECA0QAQ&url=https%3A%2F%2Ffinancialdataexchange.org%2Fcommon%2FUploaded%2520files%2FOFX%2520files%2FOFX%2520Banking%2520Specification%2520v2.3.pdf&usg=AOvVaw2Y_y7tIvRigmkwhxO8GO2q&opi=89978449

I don't know what a AWK script is, so I'm guessing it won't do me much good!

I'm using Firefox and don't have any problems. What is it that isn't working?

[Jeff Feit]

Just for laughs I tried a download this morning and it worked! The files now have a FTID in them. I also tried a second download of some transactions that were in the original, and the FTIDs are consistent so Money ignores the duplicates. With this working, not having the "since last download" time option isn't a big deal.

[Cal Learner]

Nice that Citizen got this working!!!

I have been befuddled by some who report having the same FITID for each transaction, and reporting that is not a problem. 

I have a FI which does not keep consistent FITIDs, and special efforts are needed to avoid ambiguity. I sometimes make an intentional 1-day overlap, and then delete the duplicate. This lets me confirm that the bug still exists.

[First Name Last Name]

> ... I have a FI which does not keep consistent FITIDs ...

Not necessarily trying to explain what Cal saw but just to give some data points.

I've seen case like this: in-consistent FITID's, when OFX is generated for

• current transactions
• vs statement transactions

For example, let's say there is a statement date of 10/20/2023. Support there is a transaction dated on 10/15/2023

• On 10/18/2023, I download the "current transaction" OFX which includes transaction dated on 10/15/2023, such transaction will have FITID 1234567890
• On 10/23/2023, I download the "statement transaction" OFX which includes transaction dated on 10/15/2023, such transaction will have FITID 0987654321

Other words, it appears that for some banks, once the statement is closed, they will give the transactions a different FITID completely.

--

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/af0a65ec-9182-42d1-a017-3ba5957d937en%40googlegroups.com.