Quicken Canada 2016 not calculating USD to CAD correctly

Unknown
Unknown Member
edited October 2018 in Investing (Windows)
I am having an issue with the conversion of USD to CAD on my reports.  I have setup an account with the currency = USD.  When I enter the transactions (in USD) I am asked for a conversion rate which is always shown as how many USD for CAD (0.7402).  When I run a report the calcuation is not correct.

So for an example:

I enter $100 USD in the account with a rate of 0.7402
This should be 100/0.7402 = 135.10 on my report (which is reporting in Canadian dollars)
My report is actually reflecting 100*0.7402 = 74.02

I have tried to correct this by inverting the rate on the transaction to 1.3510 - this doesn't seem to have any affect.

Now the other issue is that the report is showing converted at today's rate, rather than using the rate entered on the transaction.  This is also making my reports incorrect.

Any suggestions would be helpful!

Comments

  • Unknown
    Unknown Member
    edited October 2018
    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.
  • Unknown
    Unknown Member
    edited October 2018
  • smayer97
    smayer97 SuperUser, Mac Beta, Canada Beta ✭✭✭✭✭
    edited March 2017
    QPW said:

    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.

    [the] fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".
    @QPW To implement this correctly, I do not think it is necessary to use the day exchange rate if a conversion rate is available at the transaction level. The program simply needs to use a "day conversion rate" when one is NOT available at the transaction level. 

    But I agree, as it stands, NO Quicken product supports true multi-currency conversion. 
    Have Questions? Help Guide for Quicken for Mac
    FAQs: Quicken Mac Quicken Windows Quicken Mobile

    Add your VOTE to Quicken for Mac Product Ideas

    Object to Quicken's business model, using up 25% of your screen? Add your vote here:
    Quicken should eliminate the LARGE Ad space when a subscription expires

    (Canadian user since '92, STILL using QM2007)
  • smayer97
    smayer97 SuperUser, Mac Beta, Canada Beta ✭✭✭✭✭
    edited August 2018
    @Nicole as pointed out, true multi-currency conversion support is NOT implemented in any Quicken product at this time. 

    BUT as for record-keeping at the transaction level, the correct conversion rate is 1.3510 (as per your example) since 1 USD is worth MORE than 1 CAD. 

    (If you find this reply helpful, please be sure to click "Like", so others will know, thanks.)
    Have Questions? Help Guide for Quicken for Mac
    FAQs: Quicken Mac Quicken Windows Quicken Mobile

    Add your VOTE to Quicken for Mac Product Ideas

    Object to Quicken's business model, using up 25% of your screen? Add your vote here:
    Quicken should eliminate the LARGE Ad space when a subscription expires

    (Canadian user since '92, STILL using QM2007)
  • Unknown
    Unknown Member
    edited March 2017
    QPW said:

    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.

    "@QPW To implement this correctly, I do not think it is necessary to use
    the day exchange rate if a conversion rate is available at the
    transaction level."

    But it isn't available.

    Let me give you an example.  Lets say the home currency is "CAD" and you have an account in CAD.  What is the exchange rate on each transaction?

    There is none.  But why would I need one?
    Simple I ask for the report in USD.  And in fact I can ask for the report in any of the supported currencies.  So even if I have a transfer from USD account to a CAD account and have that exchange rate recorded.  If I now ask for the report in UK pounds that information it basically useless.

    What's more even though when you do a transfer between say a USD account and a CAD account and the user puts in the exchange rate, I contend that information isn't the correct information to use on a net worth report.

    If I buy 100 shares of IBM in the morning for $55, but at the end of the day they are worth $60 a share, a net worth report for the day should show that security being worth $600, not $550.

    The same is true for any currency exchange.  The moment you made the transfer/exchange it is history, and only matters in how much "shares" you have in that new currency.
  • smayer97
    smayer97 SuperUser, Mac Beta, Canada Beta ✭✭✭✭✭
    edited March 2017
    QPW said:

    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.

    Yes, you are correct. I did not mean to do a deep dive. But I believe these kinds of issues can be resolved and programmed to handle them. For your example, a Net Worth report only does make sense using the latest available exchange rate. User can be prompted to allow Quicken to get latest info or use what is already stored.

    As for share balances, again user can be prompted to get latest info or use what is already loaded.

    The context and caveat for Quicken is that it is not a trading software, so it could be made to only use the the latest daily exchange or use entered exchange rates. And when data is missing to prompt user to allow Quicken to get necessary info...Of course, there would a delay to get that data if there is a lot of it to get...but that could be a warning displayed to the user.

    Bottom line, I believe this is doable, albeit with some caveats and simplifications needed. Not to say there would not be some challenges.
    Have Questions? Help Guide for Quicken for Mac
    FAQs: Quicken Mac Quicken Windows Quicken Mobile

    Add your VOTE to Quicken for Mac Product Ideas

    Object to Quicken's business model, using up 25% of your screen? Add your vote here:
    Quicken should eliminate the LARGE Ad space when a subscription expires

    (Canadian user since '92, STILL using QM2007)
  • smayer97
    smayer97 SuperUser, Mac Beta, Canada Beta ✭✭✭✭✭
    edited March 2017
    QPW said:

    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.

    BTW, I know that other PFM software have some kind of multi-currency capabilities. I wonder how they have tackled these issues, e.g. MS Money (Win), iBank/Banktivity (Mac), MoneyDance (Mac) ? from light perusal it seems users of those capabilities have been satisfied, but I could be wrong.
    Have Questions? Help Guide for Quicken for Mac
    FAQs: Quicken Mac Quicken Windows Quicken Mobile

    Add your VOTE to Quicken for Mac Product Ideas

    Object to Quicken's business model, using up 25% of your screen? Add your vote here:
    Quicken should eliminate the LARGE Ad space when a subscription expires

    (Canadian user since '92, STILL using QM2007)
  • Unknown
    Unknown Member
    edited March 2017
    QPW said:

    Well you aren't going to like the answer.

    The answer is that Quicken always uses the latest currency exchange rates in reports that have mixed currencies.

    This actually gets pretty complicated.  There is in fact no way even if currency exchange rates are fully supported to "properly" use the number you put in, because of fact that even though that transaction might have had that exchange rate, the report is for at min for a day.  So it would vary all during the day and they would have to just pick the "day exchange rate".

    But in Quicken it is worse than that in that it doesn't even use the daily exchange rate even if it is available.

    At one time the users in the US thought this was only a US version problem because in the US version Quicken doesn't even keep a history of the exchange rates, so the only one it has this latest one, and it uses that for everything.

    The Canadian version of Quicken does record the history of the exchange rates, but as I found out when I did a beta of Quicken 2017 (I'm in the US and I was just interested in this kind of thing), it doesn't use it.

    "a Net Worth report only does make sense using the latest available exchange rate."

    It should use the latest exchange rate for the period it is calculated, with mostly likely them min period being a day. Like you said this isn't currency exchange software.

    If I ask for a net worth graph with one bar per day for the last 30 days in USD, and I have both USD and CAD accounts.  The software needs to only to total the USD accounts, but for the CAD accounts it needs total them and then multiply that amount by the conversion rate for that day.  And the two amounts at added for the total for the day.  It would then plot that one bar for that day, and then repeat for the rest of the 29 days.  For a period other than a day you would just use the rate on the last day in each period.

    So in fact Quicken Canadian with a daily exchange rate, does have all the information it needs to do the graph right.  They just haven't done it.

    Note my referring to "shares" is just to point out currencies exchanges work the same as you exchanging cash for shares of a security.
This discussion has been closed.