alliant: Balance is based on the beginning, not the end, of the ofx file

[hlieberman]

I'm not sure if this is a variation anyone has seen before, but seemingly the .qfx files that Alliant sends out has the ledger balance as of the beginning of the transaction list, not at the end of it. For example, in an account with a previous balance of $1000.00, an OFX file containing the set of transactions [+0.33, +555.55, -555.55] has a reported ledger balance of $1000.00, not $1000.33 as should be expected.

I've attached a redacted version of an exemplar QFX file, though it's not particularly useful without knowing that the balance on March 15th was $1000.00 and that there were no subsequent transactions not covered by this file.

2024-03-08.History-040724-014519.qfx.txt

@redstreet, none of the balance_assertion_fudge_date options seem to cover this (admittedly stupid) case. I could solve it by adding a first_transaction option, or I could just patch the Alliant importer to sum the transactions and add it to the ledger balance. Thoughts?

[redstreet]

I've not come across that before, and that's annoying! One thing I'd suggest trying is to examine their qfx file to see if they have a second balance field somewhere showing the ending balance. You can use ofx-summarize --pdb-explore (ships with this repo).

If indeed there is no ending balance, I would simply create a balance assertion as provided by the qfx: on the date and amount that the qfx claims.

Creating a synthetic balance assertion as you suggest (using initial + sum of transactions) IMHO goes a bit against the value of the check that balance assertions create. There is no way to know if there is an error or exclusion in the list of transactions. Using such assertione makes debugging the journal difficult. Thoughts?

[hlieberman]

Unfortunately, there's no second balance -- the .qfx file attached above is a redacted, but actual file from Alliant. The date that it gives is also a lie; it gives you the ending date, but the value is actually the starting value. I'm not sure how this hasn't broken a bunch of stuff for a bunch of people -- maybe Quicken simply ignores the ledger balance completely?

[hlieberman]

I figured out what's going on. It's been staring me in the face for months now, but the answer is so dumb that I couldn't see it.

The entire .qfx file is in reversed order. The transactions are newest-first, and even better, the start date is after the end date. In the example .qfx file I gave above, for example, DTSTART is April 3rd, 2024 and DTEND is March 8th, 2024. Put that in your pipe and smoke it.

I'm just going to override the method and we'll do the balance assertion for the first date. It means that we're always one statement behind, but at least it helps enforce that we got the last import correct.

[redstreet]

Heh, glad you discovered that. I missed it too. Generally, qfx files from most institution have been well formatted and non-quirky. It's csvs that are usually all over the place.

So they have a starting balance. Strange, but as you say, it's still useful for the prior import.