Home → Switching to Moneydance →

Bug report: Importing QIF with short sales (with example QIF)

• Edit

Scott R.

20 Nov, 2016 11:31 PM

I am trying out your product as an alternative to Quicken. I would love to become a paying customer, however I'm currently not able to import my Quicken data successfully.

Scenario: Import a QIF file with opening short sell transactions with an action type of "ShtSell", and corresponding closing transactions with action type "CvrShrt". (See attachment for example QIF to reproduce)

Expected behavior: Short sell transactions are imported with an action type of "Short", and closing short sell transactions are imported with an action type of "Cover", and both have the correct security, number of shares, and price

Actual behavior: Both opening and closing transactions are imported with an action type of "Xfr", with no security set, with the price set to the number of shares, and nothing for the number of shares.

It almost looks like it's defaulting to a banking transaction type because it doesn't know what to do. After some research and playing around with the QIF file a bit I had some success globally replacing the "ShtSell" transaction types with "CGShortX". That imports the short sells as "DivXfr", and it picks up the security, which is better, but the rest of the info is still incorrect.

I'm willing to help get this resolved, up to and including fixing the bug myself (I have a software background) as I would REALLY like to be able to eventually dump Quicken. Thanks.

1. 1 Posted by Scott R. on 27 Nov, 2016 05:32 PM

   

   See also my (so far unsuccessful) attempt to remedy this through scripting: http://help.infinitekind.com/discussions/moneydance-development/1720-setting-investment-transaction-type-to-investtxntypeshort-results-in-investtxntypecover

   • Edit

2. 2 Posted by J Brown on 14 Dec, 2016 11:34 PM

   

   I've had the same trouble with Moneydance 2017 and importing many years of Quicken 2014 Home and Business data. The main area of trouble is with the Investment accounts. I do a ton of short trading. I went through manually and repaired thousands of short/cover entries. In addition to ShtSell/CvrShrt being not recognized, you will see that Price and Amount are ignored from the qif file and quantity and security are lost but commission carries over but is ignored in the new amount.

   Manual fixing was a pain. I have multiple brokerage accounts to go through (thousands of records). I'm trying an alternative -- edited the qif file and changed all NShtSell to NSell and NCvrShrt to NBuy. While this loses the designation as Short, Moneydance doesn't seem to have trouble selling what you don't have before the buy. It doesn't match with your broker's record, but the import is successful and the total is correct, security is registered, and price, quantity, and commissions are still good.

   I'm still reconciling one of my accounts back to Quicken using the above method. This account only has about 400 short/covers so I may edit each of those to match Quicken now that all the rest of the data is captured (a lot less typing than before).

   One other problem I've seen is with one of my symbols "T". Instead of naming all my securities by their name and symbol, I use just the symbol in Quicken. This carried over just fine but I've noticed that every imported buy/sell/short/cover of T carries with it a $0 amount. The qif file has the correct values so it is something with the matching process or symbol look up during the import that is being broken because I have a single letter security name. Other single security letters (like X) haven't had the problem (but I'm still reconciling).

   I did a download from Interactive Brokers (IB) in a qfx format and imported it into Moneydance (the past few days of trading). I had a mix of sells to offset a previous buy, selling options owned, and shorting options. The import asked me to match up securities found in the download with positions in my moneydance broker account and for those unknown (many of the new options I was buying or shorting/writing). Moneydance created records for them but all options were recorded in the register as 1 vs 100 so the price was off. So, if you trade options, you'll have to adjust the quantity (like you did in Quicken) to get price back in line. I looked inside the qfx file and the quantity specified was 1 vs 100 so moneydance didn't do anything other than take the value. Would be nice if it recognized it as an option (data exists in the file to do that) and make adjustments or set up the security with some more data. And don't expect any magic with the newly added securities - they defaulted to stock. The short sale I did, was recorded as sell in moneydance while Quicken recognized it as a short sale.

   I think you'll also be frustrated coming from Quicken with the method for adding new securities if you manually enter the transactions vs download. First, if the security doesn't exist (within the account), you'll need to navigate away from the register to the "Securities Detail" and add the security and then return. The good news is that you can pick up where you left off. The second frustration will be with the autocomplete of the security (for instance T), you will think that it will start to autofill beginning with T, but that's not the case, as you type, a lot of words have T in them so you likely begin with the A list and have to scroll way down to find T. The third frustration will be once you select a security, the quantity autofills with what was entered last vs how many you have.

   There is also the automation of the downloads. Looking at quicken, they do a ton of downloads via Express Web Connect. As Moneydance has pointed out, Intuit holds on to your credentials and every evening retrieves the data from your financial institutions and houses it. I double checked this with quicken because I couldn't believe this was true -- it is. After thinking about that, I decided that it isn't so bad logging on myself and cutting Intuit out of the picture.

   But so far, even with all the above, I'm looking to make the transition from Quicken to Moneydance. I have more to work through and learning on how things are done in Moneydance. And I haven't gotten to looking at the scheduled/memorized transactions to see how that stacks up.

   • Edit

3. 3 Posted by J Brown on 17 Dec, 2016 10:27 PM

   

   A few other lessons learned in the migration:
   1) MiscExpX and MiscIncX need cleanup. They tend to result in double transactions and one needs to be deleted.
   2) Memorized/Scheduled transactions don't migrate so you need to set those up again.
   3) Categories are a bit hit and miss. They are there (and sometimes additional ones created). The classification of Expense vs Income needs some cleanup. Unfortunately, you can't just change the type from Expense to Income - you have to create new and then Batch change the account, delete the old and rename the new (if you want the same name).
   4) Somehow I have a couple of hierarchies in the categories where an Income category is found under an expense category and vise versa. Some of the hierarchies didn't carry over (children made parents) but this you can edit and change pretty easy.
   5) If, like me you migrated from MS Money to Quicken when Microsoft discontinued Money and Intuit created that conversion program, you likely have a Cash account for some of your brokerage accounts. These can be cleaned up, but takes some effort since there are a lot of BuyXfr/SellXfr transactions and Batch doesn't seem to allow you to change the action in bulk. I did find though that if you change a BuyXfr to just a Buy, Moneydance cleans up the transfer.
   6) ShrsIn/ShrsOut aren't understood so you need to adjust each of transactions to point at the right new brokerage account. And then combine it with #5 above and it will clean up the transactions. Moneydance creates a new Expense category with the name of the Security being transferred. If you don't want clutter, once you've cleaned up the transactions, you'll find that each of these categories has no transactions in it so you can then delete them.
   7) For the Reminders (Scheduled Transactions), if you used the "estimate the amount due" where it averages the last 3 transactions... there is no equivalent.

   On the good side:
   1) I like the ability to have multiple account windows open at the same time. This has made it easier to figure out what's gone on and how to fix things.
   2) The backup after every close is nice and the file validation with each startup is another nice feature - no more file repair needed.
   3) Transaction entry is pretty quick and the autofill is better than Quicken was in my opinion. I have over a half a million records that were transferred so I don't see this as a small setup. Quicken was getting slow when I'd manually enter transactions in Investment accounts.
   4) The method of changing ShtSell/CvrShrt for the import worked well overall. I imported all accounts and then for those Investment/brokerage accounts that I did short trading in, I did individual exports from Quicken, modified the file to change ShtSell to Sell and CvrShrt to Buy and deleted the imported transactions from the Moneydance account before importing to it. This way, all accounts were set up and only minor changes were needed.

   I have all my accounts now reconciled with Quicken. Now I'm going through and cleaning up all the categories and shifting transactions to use the right version of the categories when multiple were created or the hierarchy or type got screwed up. I'll post again if there are any more tips or tricks I come across to make it easier than using the Batch feature or manually adding a new category/account and transferring records to it.

   • Edit

4. 4 Posted by Scott R. on 18 Dec, 2016 01:12 AM

   

   Jeff,

   Thanks for all the info and tips! I am actually probably 70% of the way there to having a script which will correctly import the ShtSell and CvrShrt transactions from a QIF. I was able to successfully insert a Short transaction with all of the correct info (# shares, security, etc.) via a script, and I've expanded it to import from QIF records. The next steps are to:

   1.) Take another export of my QIF and make a copy
   2.) Write another script to delete the short transactions from my full QIF export from Quicken
   3.) Import the stripped QIF file (everything minus the short transactions) using MoneyDance's import function
   4.) Use my script to import the short transactions from the original QIF (ignoring all the others)
   5.) Submit the ShtSell and CvrShrt import handling code as a bug fix to MoneyDance

   With the holidays approaching I probably won't have it done until January, but when it is done I'll post an update. If I lose my ambition for the script I might either try your trick of setting the transactions to "Sell" and "Buy", or just archive a year-end copy from Quicken, then import the reduced QIF. I'm starting to question the need to keep all of the old securities and transactions around anyways. Speaking of which, I wish MoneyDance had a "hide securities" option similar to Quicken. I saw another thread of someone asking for that very feature but it seems no luck thus far. Anyways, more to come on the script...

   • Edit

5. System closed this discussion on 19 Mar, 2017 01:20 AM.