How can I import my non-USD currency accounts into Quicken 2018 for Mac coming from Banktivity 6?

Unknown

I was a Quicken Windows user, then went to Banktivity 6 to get away from a VM, but now that Quicken for Mac has muilticurrency functionality, I wanted to come back.  I have over 10 years of data, that imported fine from Quicken to Banktivity, but now coming back, Banktivity exports all of the files in a given currency as its own. QIF.  And Quicken will only allow to import one QIF.

So how do I get all of my currency files into one Quicken data file?

Stan Moderate

John, do not switch yet. I wanted to switch to QMac from Q Premier 2017 and subscribed only to find it is not fully functional multi currency! Historical transfers are ignored.

Unknown

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

It means that multicurrency transfers (one currency to another) doesn't work in Quicken Mac during the conversion process. 

smayer97

Be aware that QM2018 still does not have TRUE multi-currency support. What it does have are the following features:

• record an account register in the currency of choice
• have account balances in sidebar presented converted into the home currency
• OR have "foreign currency" account not be added as part of the total net worth in the sidebar
• convert transfers between accounts of different currencies as a one time entry. The transfers are not linked together such that any changes to one side of the transaction does not get updated on the other side
• reports do not convert currencies.
• currency conversion does not apply to investments.

Take these limitations into consideration. Meanwhile,  You can add your VOTE to Add true multi-currency conversion support.

First, click on the underlined link above to go there, then click VOTE at the top of THAT page, so your will vote count for THIS feature and increase its visibility to the developers by seeking to have the features you need or desire end up in the latest version.

While you are at it, you may want to add your VOTE to related IDEAS found on the List of Requests for Report Related Features, the  List of Requests Related to Investments, and it will eventually be added to the List of Requests for New Capabilities. Click on each underlined link, then follow the instructions to add your vote to more related ideas. Your VOTES matter!

(If you find this reply helpful, please be sure to click "Like", so others will know, thanks.)

Unknown

Just a general note about QIF exports/imports.  The QIF format has no syntax for different currencies.  It just has amounts.  It would be up to the account to determine what currency any given amount is.

Now in Quicken Mac as far as I know since it really doesn't know anything about currencies, just puts up a symbol, I wouldn't be surprised if you can just change that symbol after the account is created.  And that might work (I don't know since I don't use Mac).

But if this was on the Windows side that does have real currency support you can't change the currency after the account is created.  So one would have to pre-create the accounts that aren't in the right currency before doing the import.

But it gets worse.  Like I said the QIF doesn't have any syntax for currency, but you there is also a very nasty problem.  Importing of transfers by QIF into Quicken Windows is already very error prone.  As in the matching system stinks, but imagine with a transfer from one account to another with different currencies.  Literally nothing has to "line up". The payee, memo, amount all can be different.

From what I have seen playing around with a QIF import into the Quicken Windows Canadian version is that they will in fact ask for the conversion rate for every single transfer between accounts of different currencies.

smayer97

QPW said:

Just a general note about QIF exports/imports.  The QIF format has no syntax for different currencies.  It just has amounts.  It would be up to the account to determine what currency any given amount is.

Now in Quicken Mac as far as I know since it really doesn't know anything about currencies, just puts up a symbol, I wouldn't be surprised if you can just change that symbol after the account is created.  And that might work (I don't know since I don't use Mac).

But if this was on the Windows side that does have real currency support you can't change the currency after the account is created.  So one would have to pre-create the accounts that aren't in the right currency before doing the import.

But it gets worse.  Like I said the QIF doesn't have any syntax for currency, but you there is also a very nasty problem.  Importing of transfers by QIF into Quicken Windows is already very error prone.  As in the matching system stinks, but imagine with a transfer from one account to another with different currencies.  Literally nothing has to "line up". The payee, memo, amount all can be different.

From what I have seen playing around with a QIF import into the Quicken Windows Canadian version is that they will in fact ask for the conversion rate for every single transfer between accounts of different currencies.

Just keep in mind that QW2018 had some "improvements" made to the QIF import. OP is asking about QIF import into QM2018, which is allowed for initial setup from Banktivity (and MoneyDance) and I suspect behaves differently...

Stan Moderate

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

After I converted my data I could see that while some accounts had the correct balance others were seriously 'in the red'!  Checking my data I discovered 1) it was only the accounts that were involved with currency transfers (between US$/£, US$/**** etc).  2) reviewing the detail I discovered that while the 'transfer from' account was deducted, the 'transfer too' account was not credited ie the transfer was ignored.

Were I starting from scratch in QMac I would accept this and be happy to do currency transfers manually, as currently in QP17 Win, when you revisit a previous currency transfer entry it automatically offers you todays exchange rate and there's no way to disregard it.  So, unless you can recall what the transfer amount/rate was you have to go back to your paper statements to correct it!  I hope this makes sense? 

Unknown

QPW said:

Just a general note about QIF exports/imports.  The QIF format has no syntax for different currencies.  It just has amounts.  It would be up to the account to determine what currency any given amount is.

Now in Quicken Mac as far as I know since it really doesn't know anything about currencies, just puts up a symbol, I wouldn't be surprised if you can just change that symbol after the account is created.  And that might work (I don't know since I don't use Mac).

But if this was on the Windows side that does have real currency support you can't change the currency after the account is created.  So one would have to pre-create the accounts that aren't in the right currency before doing the import.

But it gets worse.  Like I said the QIF doesn't have any syntax for currency, but you there is also a very nasty problem.  Importing of transfers by QIF into Quicken Windows is already very error prone.  As in the matching system stinks, but imagine with a transfer from one account to another with different currencies.  Literally nothing has to "line up". The payee, memo, amount all can be different.

From what I have seen playing around with a QIF import into the Quicken Windows Canadian version is that they will in fact ask for the conversion rate for every single transfer between accounts of different currencies.

Just keep in mind that QW2018 had some "improvements" made to the QIF import.

The "improvements" were to take away features.

Note I was just pointing out that fundamentally there are problems with the QIF syntax and having accounts with different currencies.  And as such there will always be a problem with this kind of import.

And BTW the removal of feature in Quicken 2018 for QIF imports makes it more on par with the QIF import in Quicken Mac.  As in since the only purpose Quicken Mac has for importing a QIF file is to create accounts from scratch they have no code for matching existing transactions or for categorizing of those transactions (it is assumed the category is in the QIF data.)

What they removed in Quicken Windows 2018 is exactly those two functions.
Which means that they are purposely breaking the functionality, so that the QIF format won't be good for import transactions downloaded from a financial institution.
Which is of course in line with what they are doing on Quicken Mac.

smayer97

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

Yes, it makes sense what you are saying, and as noted below here:
https://getsatisfaction.com/quickencommunity/topics/how-can-i-import-my-non-usd-currency-accounts-in...

the problem is that Quicken currently does not perform the migration from other versions correctly because it still does not have TRUE multi-currency support. 

Please read that post and add your votes as you see fit.

(If you find this reply helpful, please be sure to click "Like", so others will know, thanks.)

smayer97

QPW said:

Just a general note about QIF exports/imports.  The QIF format has no syntax for different currencies.  It just has amounts.  It would be up to the account to determine what currency any given amount is.

Now in Quicken Mac as far as I know since it really doesn't know anything about currencies, just puts up a symbol, I wouldn't be surprised if you can just change that symbol after the account is created.  And that might work (I don't know since I don't use Mac).

But if this was on the Windows side that does have real currency support you can't change the currency after the account is created.  So one would have to pre-create the accounts that aren't in the right currency before doing the import.

But it gets worse.  Like I said the QIF doesn't have any syntax for currency, but you there is also a very nasty problem.  Importing of transfers by QIF into Quicken Windows is already very error prone.  As in the matching system stinks, but imagine with a transfer from one account to another with different currencies.  Literally nothing has to "line up". The payee, memo, amount all can be different.

From what I have seen playing around with a QIF import into the Quicken Windows Canadian version is that they will in fact ask for the conversion rate for every single transfer between accounts of different currencies.

Quicken has a strange way of considering the removal of features as "Other Enhancements ...Extended QIF import to support all account types." as per the Release Notes.

:-\

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

it sounds like Quicken Mac's QIF import is working like Quicken Windows with the "Special handling for transfers" selection on.  Which for this use case is not going to work right.  Then again neither would work right for the use case where this option was off.

Let me state that the main problem is the concept that transfers have to be linked together.  I know in Quicken Mac because of Quicken Essentials not having this, and then later it was added, Quicken Mac allows both modes.

If we assume that transfers don't have to be linked, then it is possible to use what is in a QIF file to build up everything correctly with no real problems (provided you can change the currency for an account after the fact, and not affect any of the amounts).

Like I said the QIF format doesn't have syntax for currency.  It also doesn't have any syntax that actually connects two transactions.

The only way to connect two transactions is a guess.
As in say you have a transaction for $100 in account A that has a category of [B] in on 12/1/2017 the QIF file.  And you have another transaction in account B for -$100 that has a category of [A] on 12/1/2017.  You can make the guess they are the same transaction, and need to be linked together.  But I could have multiple $100 transactions on 12/1/2017 and as such this really is a guess, not absolute that you would in fact be able to connect the correct transactions together.

Throw in the fact that say account A and B are different currencies, and the transaction would have a different amount on both sides of the transaction, it is now impossible to link the two transactions together.

Of course if you went with the assumption that you didn't need to link transfers then you could just import these two transactions as completely different transactions in their respective accounts.

Note that the concept of linking transactions is actually a modern computer concept for easy of use.  It doesn't exist in the original double book account concept.  With that concept it is the fact that the two transactions will cancel each other out that balances the books.

Now back to the "Special handling for transfers" setting in Quicken Windows and what it means.

In Quicken Windows it is assumed that a transfer means that you connect the transactions between the two accounts.  But as you have seen this is problematic, and frankly Quicken Windows "matching" for this sinks.

So what the "Special handling for transfers" option does is it says "remove one side of the transfer from the import".  The assumption here is that for every transfer there are two transactions in the QIF file.  And that if you put in one of them, and link it to the other account, creating the other transaction/side of the transfer you will get what you want.  This of course assumes that both sides of the transfer have the same amount.

This approach takes a very simply concept of what is needed.  As in it just picks "to" side to remove.

Without this option Quicken Windows' QIF import will leave both sides in, but then you are faced with dealing with those bad matches.

I have struggled for years trying to export my data file as a QIF and import it back into a new data file to recreate it just in case if my data file would have got to a non recoverable corrupted state (hasn't happened, just a safety measure.)

In the end I got it work, but only by changing all the transfers in the QIF file to regular categories, to avoid the problems above.

That is the reason for "ChangeTransfers" documented here:
http://www.quicknperlwiz.com/changetransfers.html

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

Thanks for this info.  I don't really need the transactions to stay linked, as long as they either add or subtract the correct amount in their respective currency.  Quicken has never kept a historical log of currency exchange rates, and therefore never been able to give unrealized or realized currency gains, which is fine too.

What I really want to see is a Net Worth statement or expense categories that combine all of the various currency accounts and shows me a sum total in USD.  However, QM2018 makes you look at each currency separately.  That's useless in my mind.

I also find it odd that they don't allow importing multiple QIF's.  Banktivity allows that.  I'd have to take each separate QIF (one for each currency) and then import it as it's own data file, them export it as QXF, and then reimport it into my desired QIF.

smayer97

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

 I know in Quicken Mac because of Quicken Essentials not having this, and then later it was added, Quicken Mac allows both modes.

If by this you are trying to say that QMac has linked transfers for currency conversions, that is still not so in QM2018. And as per Quicken Marcus, this is a big deal to implement, which suggests to me that it will be a while before this is in place.

(If you find this reply helpful, please be sure to click "Like", so others will know, thanks.)

smayer97

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

I also find it odd that they don't allow importing multiple QIF's.  Banktivity allows that.  I'd have to take each separate QIF (one for each currency) and then import it as it's own data file, them export it as QXF, and then reimport it into my desired QIF.

One work-around, if you have access, is to import the QIFs into QW2018, then convert the file to QM2018. BUT only once QM2018 deals with the migration of conversion transactions/transfers, which it still does not do at the moment.

(If you find this reply helpful, please be sure to click "Like", so others will know, thanks.)

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

I also find it odd that they don't allow importing multiple QIF's.

As I see it there are a few reasons for this.

1. QIF was created to support Quicken support people.  It was never standardized (or "complete" as you can see for the currency and transfer problems).  Intuit started trying to get away from it in Quicken 2005.  And when Quicken Mac was rewritten (Quicken Essentials) it was completely dropped.  It was only brought back for the purpose of converting from other personal finance programs, but has these limitations.  But it was picked up by other personal finance programs and became a sort of standard.
   
   
2. By restricting to this one used case they have avoided lots the issue of matching existing transactions.
3. They really didn't want to have a format that competes with Web Connect/QFX.  Inuit gets paid for that format use.

If by this you are trying to say that QMac has linked transfers for currency conversions, that is still not so in QM2018.

No, I don't mean that.  I'm talking about regular transfers.  In Quicken Windows the assumption is a "transfer" transaction is always linked to another transaction in another register.  Quicken Windows used the category to put in the syntax for a transfer.  And it isn't an accident that it is the syntax you see in a QIF file.
As in if I'm in account A and I want to transfer $100 to account B the category will be [B].  And with it linked like this I can go to either transaction and right click on them and select to jump to the transaction in the other register.

One work-around, if you have access, is to import the QIFs into QW2018,
then convert the file to QM2018. BUT only once QM2018 deals with the
migration of conversion transactions/transfers, which it still does not
do at the moment.

Depending on the complexity of the data file, and having accounts in different currencies would certainly be considered "complex", you might find importing into Quicken Windows just as problematic or worse.

And of course running that will require a Windows machine.  You can get a copy of Quicken Windows 2013 to install from here though:
https://www.quicken.com/support/how-and-when-use-intermediate-version-convert-older-versions-quicken

But I would be much more inclined to believe in this case the best route would be to just change the transfer categories in the QIF file to regular categories and import into Quicken Mac.

If you have a Windows machine you can use ChangeTransfers from my site.
But in fact that program is just a pretty simple compiled Perl program, so I'm going to post the source code here.

Note I have not tested it on a Mac, but I don't believe there is anything in it that shouldn't run on MacOS.

You will need to put this at the top of it (with the correct path to the perl command):
#!/usr/local/bin/perl<br>And make it executable&#46;

Unknown

QPW said:

Just a general note about QIF exports/imports.  The QIF format has no syntax for different currencies.  It just has amounts.  It would be up to the account to determine what currency any given amount is.

Now in Quicken Mac as far as I know since it really doesn't know anything about currencies, just puts up a symbol, I wouldn't be surprised if you can just change that symbol after the account is created.  And that might work (I don't know since I don't use Mac).

But if this was on the Windows side that does have real currency support you can't change the currency after the account is created.  So one would have to pre-create the accounts that aren't in the right currency before doing the import.

But it gets worse.  Like I said the QIF doesn't have any syntax for currency, but you there is also a very nasty problem.  Importing of transfers by QIF into Quicken Windows is already very error prone.  As in the matching system stinks, but imagine with a transfer from one account to another with different currencies.  Literally nothing has to "line up". The payee, memo, amount all can be different.

From what I have seen playing around with a QIF import into the Quicken Windows Canadian version is that they will in fact ask for the conversion rate for every single transfer between accounts of different currencies.

Quicken has a strange way of considering the removal of features as "Other Enhancements ...Extended QIF import to support all account types." as per the Release Notes.

They gave with one hand, and took with the other.

They did enhance the Quicken Windows import.

The Quicken 2017 QIF import dialog:


The Quicken 2018 QIF import dialog:


Starting in Quicken 2005 they blocked the user from selecting the any account that wasn't an asset or liability account type.  This was done to push users to using the QFX format.

The enhancement is they removed this "GUI block".  I say "GUI block" because the workaround was to put the account name and type into the QIF and it would import the transactions into that account instead of the one picked on the GUI.   So in fact this has been used by "SuperUsers" for years and is even in the FAQ list, and was the reason for version of my program.

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

What terrible software this GetSatisfaction is.  I used the "pre" option to post the code which is to tell it is pre formatted and to leave it alone.  But it put in HTML formatting and messed it all up!

I will probably just put in a link to the source code on my website if you want it.

Unknown

John Eisenmann said:

Thanks, Stan.  Could you please explain to me what you mean by "historical transfer are ignored"?

BTW you can probably run ChangeTransfers.exe on MacOS using WINE.