OFX import, everything is a deposit.
Nicky Bulthuis
via web
Hello All,
I'm trying to import an ofx file from my bank. The importing doesn't give any errors, but every transaction is a 'deposit'. Below are two transactions, one debit and one credit. Both end up as a deposit.
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20100128
<DTUSER>20100128
<TRNAMT>1000.00
<FITID>20100128-40712341
<NAME>HR N BULTHUIS
<MEMO>VASTE LASTEN
TRANSACTIEDATUM* 28-01-2010
</STMTTRN>
<STMTTRN>
<TRNTYPE>CREDIT
<DTPOSTED>20100128
<DTUSER>20100128
<TRNAMT>-49.72
<FITID>20100128-40364425
<NAME>SPORTCENTRUM
<MEMO>PERIODE 01-02-2010 / 28-02-2010
</STMTTRN>
Probably the formatting used in my ofx file doesn't match what MoneyWell expects. Also only the first line of the 'MEMO' field is imported, any other lines are skipped.
Any idea what is wrong?
Comments are currently closed for this discussion. You can start a new one.
Support Staff 2 Posted by Kevin Hoctor on 09 Mar, 2010 07:55 PM
There are definitely some anomalies in that OFX file. The DEBIT amount is set to a positive and the CREDIT is a negative. I'll have to look at our import rules to see how it handles this poorly built information.
Also, the MEMO information shouldn't include line breaks but we should probably handle it anyway. I'll review this as well.
Is there a QIF format you can use that might be structured better?
3 Posted by Nicky Bulthuis on 10 Mar, 2010 06:50 AM
Sadly, there is no QIF format i can use. The options that i have are the OFX, a CSV format or Exact format (i'm not sure if this Application is known outside The Netherlands).
I've tried importing the CSV formatted one, which works like a charm. But the formatting of Payee, Memo and other text-based fields is horrible compared to the OFX one.
What i did so far is do a FIND/REPLACE on the OFX file for the DEBIT/CREDIT (i've switched them around) and the amounts turn up properly now. I've also looked at the OFX standard and my OFX file is wrong, which gives me some info on telling my bank about fixing it. I'll send an email to my bank, asking why they have decided to switch them around and pray i get a reply.
If you can change your import rules (of course without breaking it for other people) to support my 'weird' OFX file, it would be great.
Thanks sofar.
Support Staff 4 Posted by Ash on 10 Mar, 2010 05:40 PM
Kevin can probably work something out here. Can you tell me what Institution is issuing these OFX files? If you could give us a sanitized version of such a file, that could help as well.
~ash
5 Posted by Nicky Bulthuis on 10 Mar, 2010 09:33 PM
Hello Ash and Kevin,
The institution in question is the SNS Bank from the Netherlands. I've attached a file containing all header info and 2 debit and 2 credit transactions. Let me know if you need any more info.
I hope this helps.
Nicky
Support Staff 6 Posted by Kevin Hoctor on 11 Mar, 2010 02:15 AM
Nicky: Thank you for this OFX file. It is pretty broken and would be hard to add a rule to handle it. The type and the amount are conflicting. If SNS Bank can fix this and the multi-line memos, that would be the best scenario.
7 Posted by Nicky Bulthuis on 11 Mar, 2010 06:03 AM
My thoughts exactly. I'm hoping they will change it, but since it's a large company it will probably take a looooong time or they don't care.
Just for my sake, you know a lot more about OFX than me. The 'things' that need to be changed in my OFX are:
Are there any other lines that are wrong?
My intention is, since i already have to import manually, to write myself a preprocessor (in sed, or python, etc) that converts my 'bad' ofx to a 'good' one.
Thanks for all the quick replies.
Support Staff 8 Posted by Kevin Hoctor on 12 Mar, 2010 05:16 PM
Nicky: That looks correct to me. I'm still looking to see if I can tweak the MoneyWell code safely without breaking other correct imports.
9 Posted by Nicky Bulthuis on 12 Mar, 2010 07:47 PM
Well.. since i guess getting access to the source code is not an option -.-
Keep in mind that my knowledge of OFX is not that great. As i see it, having an amount that is either positive or negative and having DEBIT and CREDIT is the same. So perhaps it's possible do a preference choice. First look at the amount, then verify it with the DEBIT/CREDIT. If there is a mix up. Ask the user which to prefer, the amount of the DEBIT/CREDIT.
-or-
I would NOT change the importing rules at all. I would keep strictly to the OFX standard, and instead add an option to allow a preprocessor to process the file before the actual import. It's kind of what i do now, i wrote a python script that takes my file, does the CREDIT/DEBIT replacement and makes the MEMO one line. It would be nice to do this 'automatically'. This option allows a lot of flexibility, but also a lot more threads on the forum from people on how to preprocess their specific file, especially the ones without any programming experience.
Just my 2 cents.
Stil no reply from my bank though, i doubt i even get one, but i can hope.
Support Staff 10 Posted by Ash on 13 Mar, 2010 01:38 AM
That's certainly something to think about, but a bit advanced for some (most) of our users.
~ash
Ash resolved this discussion on 13 Mar, 2010 01:38 AM.
Nicky Bulthuis re-opened this discussion on 13 Mar, 2010 03:41 PM
11 Posted by Nicky Bulthuis on 13 Mar, 2010 03:41 PM
It's not just for other people. It also gives yourself a nice way to implement small variations on your own import rules, while still keeping the OFX parser intact.
Support Staff 12 Posted by Kevin Hoctor on 08 Jul, 2010 08:35 PM
Nicky: With the upcoming MoneyWell 1.6 release, you'll be able to tell MoneyWell to ignore the transaction type in the OFX file and to set it via the sign on the amount. After you get the new release (currently in beta), open the Terminal and paste the following command:
defaults write com.nothirst.moneywell OfxAdjustTypeBySign -bool YES
Kevin Hoctor resolved this discussion on 08 Jul, 2010 08:35 PM.