Home → Extension Development →

QIF Export from Moneydance problems

• Edit

cgrinds

12 Apr, 2016 12:29 AM

I wrote an extension to export all accounts to a QIF, like so:

CurrencyType currencyType = bookWrapper.getBook().getCurrencies().getCurrencyByName("US Dollar");
QIFDataWriter qif = new QIFDataWriter(new File("MD_2016.QIF"), currencyType);
try {
  qif.writeAccounts(bookWrapper, statusMon);
} catch(Exception e) {
  e.printStackTrace();
}

That works swimmingly. However, when I import the same QIF into Moneydance on a separate machine, I run into issues.

The two problems are:
1) Some starting balances are exported correctly, other's aren't. Looks like 19 out of 25 accounts are correct.
I checked the QIF and sure enough, no starting balance, even though the account has one in Account > Edit Account
I included the relevant section of the QIF below.

2) More seriously, in some accounts, transfers are doubled.

I know there are other ways to share data between instances of Moneydance, but that's not the problem I'm trying to solve.
I need a QIF that matches what I have in MD, because I have other software that consumes QIFs.

There are no errors in the console during export. Any ideas?

^
!Option:AutoSwitch
!Account
NChecking
D
TBank
^
!Type:Bank
D12/31/1969
CX
N
POpening Balance
M
L[Start Balance]
^

1. 1 Posted by Ben Spencer on 12 Apr, 2016 02:54 PM

   

   One thing to note is that you should make sure that when importing a QIF file from another program you select the "source" option to "from another program".

   Importing QIF files into any personal finance program is always error prone and difficult with respect to transfers. The reason being that the QIF file does not explicitly link the two sides of a transfer, rather it it includes two different unrelated transactions. The money import process attempts to find possible corresponding sides to a transfer from a QIF file but this matching process is fuzzy in nature and error prone.

   I believe that your requirement to have "a QIF that matches what I have in MD" is doomed from the start. QIF files are not a complete representation of what is in Moneydance. QIF files simply do not contain all the information by specification. I do not think using QIF files will meet your goal of sharing data between multiple instances of Moneydance.

   Ben Spencer
   Infinite Kind Support

   • Edit

2. 2 Posted by cgrinds on 12 Apr, 2016 04:32 PM

   

   Hi Ben! Yes, I followed the import instructions here.

   And, yes, I understand that QIFs are single-entry booking instead of double-entry.

   XML is easy enough to parse - is that a less doomed format than QIF?

   I see that it's not possible to Import from XML, but that's OK, my goal isn't to roundtrip, but to use Moneydance as the single source of truth for my financial data, and, at the same time, create other tools to consume Moneydance's data.

   Of the export options, there are: QIF, tab delimited, XML, and MD 2010.
   Which of these is most likely to have the fewest problems, be the best supported, and least likely to change?

   Thanks

   • Edit

3. 3 Posted by Ben Spencer on 12 Apr, 2016 08:44 PM

   

   Of the options you listed the Moneydance XML format is the format that will be the most complete and the easiest for you to manage with standard XML tools. Other personal finance programs may be able to read QIF files directly but there is no official specification for QIF files so I tend to consider using them pretty risky.

   Ben Spencer
   Infinite Kind Support

   • Edit

4. 4 Posted by cgrinds on 12 Apr, 2016 09:33 PM

   

   Thanks, Ben. That makes sense. I'll switch my code over to using the XML.
   Do you know if it's documented anywhere?

   Looks fairly straightforward, but always nice to travel a well-worn path.
   Securities aren't stored quite how I expected.
   I see:

    <DESC>BUY</DESC>
    <PARENTAMT>-0.38</PARENTAMT>
    <SPLITAMT>0.023</SPLITAMT>
    <RAWRATE>6.05263158</RAWRATE>
   

   I expected rawrate to be the share price, but it isn't. In fact, I don't think the share price is stored.
   I assume you calculate the share price yourself with PARENTAMT / SPLITAMT or PARENTAMT * RAWRATE / 100?

   • Edit

5. Support Staff 5 Posted by Sean Reilly on 13 Apr, 2016 05:47 AM

   

   Hello! Unfortunately it's not documented anywhere because it is mainly just a raw representation of the internal data model, but that is documented here: http://infinitekind.com/dev/apidoc/com/infinitekind/moneydance/mode...

   The XML format is from an older version of Moneydance, but still basically maps to the same things. If you are familiar with the Python language you can access the data model directly which might be an easier way to get the data you like in the exact format you prefer.

   As you suggest, the RAWRATE is secondary to the amounts and is only used if one of the amounts is zero or in case a specific rate is needed than can be calculated from the two amounts. However the RAWRATE is based on the smallest units possible in either side of the transaction, which is usually 0.0001 for securities or two decimal places for USD, etc.

   Thanks!

   Sean Reilly
   Developer, The Infinite Kind
   http://infinitekind.com

   • Edit

6. 6 Posted by cgrinds on 13 Apr, 2016 11:58 AM

   

   Thanks Sean. When you say access directly - do you mean from Python running inside the JVM or Python consuming MD's native file format?

   Do you agree with Ben, that of the export options, XML is the most likely to be stable going forward? I don't mind writing the parser, but would rather write it once :)
   I finished the XML reader last night and immediately see advantages over QIF, like memos from securities being exported, oh and double-booked entries actually being together.

   I also looked at the MD format, and it looks equally straightforward. I'd just like to pick the format that ends up being the most stable. Any thoughts?

   Thanks

   • Edit

7. Support Staff 7 Posted by Sean Reilly on 13 Apr, 2016 03:55 PM

   

   Hello,

   By Python I mean that it runs inside the JVM and has direct access to all of the native Moneydance objects. Starting with MD2015 we've built atop a very flexible syncing system in which every object (transaction, account, reminder, etc) is represented as a dictionary (aka Map) with a unique ID. The python code can extract all of that raw information or talk to the API to get the data represented as each of their object types. Our current dev versions of Moneydance 2016 have Python built-in and much better support for running python scripts, including loading them as extensions.

   I do agree with Ben that at the current time the XML format is the best supported and highest resolution format going forward. It is a bit dated however, so I think the Python interface (and objects-as-dictionaries model) will be a bit more long-lived even though it's not built-in to the current public release or even technically a file format.

   Thanks,
   Sean

   • signature.asc 802 Bytes
   • Edit

8. 8 Posted by cgrinds on 13 Apr, 2016 04:17 PM

   

   Thanks Sean. I appreciate the information, and your work on Moneydance.
   I have what I need, and this issue can be closed.

   • Edit

9. 9 Posted by moneydance on 08 May, 2016 11:17 PM

   

   I'd be very grateful to get a copy of this extension. It does not seem to be posted anywhere, and I don't know how to convert the raw text in the discussion box into a working extension.

   • Edit

10. 10 Posted by cgrinds on 09 May, 2016 12:12 PM

    

    Hi llorracc - I created a gist with the plugin. Good luck.

    • Edit

11. System closed this discussion on 02 Oct, 2016 02:33 AM.