Using multiple exchange rates for a single currency

Unknown

I use multiple currencies, and make multiple purchases each year, all at a different exchange rate.

I need to be able to track and compare, year over year, the true cost, in Can $, for a given product.

I use Quicken for Windows, Home and Office, 2011, have 8 or 10,000 transactions over the years to compare annual true cost

Quicken seems only to allow one rate for a currency and this "adjusts" previous rates to the new rate.

I could, in theory, export to Excel, and then do my comparisons in Excel, but this is rather awkward given hundreds of transactions per year.

Any other suggestions?

From my research, even the most current version of Quicken only allows a single rate per currency.

My currencies include C$, US$, Euro, Pound, Swiss Franc, Australian and NZ dollars

q_lurker

Are you using the US or Canadian version of Quicken?  It used to be that the Canadian version kept some semblance of historical exchange.  The US version has never kept historical exchange rates.  At least that has been my understanding from posts to this site and it predecessors.

Unknown

Hi q.lurker. Thanks for the question.  Means someone is reading this.  I am using the Canadian version, 2011 Release 1.  And everywhere I look shows only a single rate of exchange for any particular currency. As soon as I post a new rate, it impacts all previous transactions at their own unique rates.  Thanks again if you have any other suggestions.

Unknown

I just tested this on a Canadian version of Quicken.  Even though it does maintain a historical exchange rate list.  It doesn't seem to apply it to the reports.  For instance given a USD and a CAD account both with just one online balance in them.  And a few manually entered historical exchange rates, the values of both accounts don't vary over the one period of time that I was testing.

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

Thanks for the suggestions, and it helps in a general sense, but not specific to individual transactions.

Unfortunately Quicken calculates the cost of a currency based on one rate applying to all transactions.

And the historical rate table does help in that it remembers a single rate of a given day, but I have not seen a way to calculate an annual comparison of cost of goods from within Quicken.

So my work around, for now, is to use the historical rate table and to use the rate changes to plug against an extracted Excel file in raw currency amounts.

For example I may have 100 Euro transactions in a year (actually 3,000) and I can gather the data in Quicken under Euro currency accounts, then when I need to do a Can $ charge out, I extract all the Euro account numbers to Excel, refer to the historical exchange rate and apply that to each transaction in Excel.

Very cludgie, but produces an accurate charge rate. and can be duplicated on a year over year basis for comparison purposes. And since I have perhaps 20 foreign currency transactions on any given day, I have to somehow remember the rate for each of those transactions.  BLAAAAHHHH.....

I guess I am expecting too much from an off the shelf financial tracking program.

 

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

"And the historical rate table does help in that it remembers a single rate of a given day"

How does it help?

Now I get that it wouldn't be 100% accurate if say during the day your transactions are actually at different exchange rates.  But I would expect the daily exchange rate to at least be "in the ball park".

So what I would expect is that if say I was running a net worth report in CAD and I had a USD account. That Quicken would apply the daily exchange rate to the USD account and I would see it going up and down in CAD value even if the actual number of dollars never changes.

I'm not seeing that in the net worth report (or any other report).
So I fail to see how the recording of the exchange rate is helping in anything.

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

First, thanks for the feedback and questions, they help me think through what I am trying to accomplish.

I may be unnecessarily complicating my life, but I need to be able to compare the cost of various widgets bought over multiple years, with multiple currencies and at multiple item prices.

I see that I can extract the transactions to excel, then I can look at the historical rate table and plug those rates against the transactions for a given day, which will tell me the Can price that I actually paid, not what I would have paid if the exchange rate wasn't fluctuating.

A simple example would be

Widget A, cost on Jan 4, 2013, 5.00 GBP, cost in Can $8.40
Same widget, same cost, July 1, 2013, cost in Can $9.25
Same widget, different cost, Dec 22, 2013, cost in Can 7.78
Similar changes for 2014, 2015.......

Now I I want to look back to see if it is a good time to stock up on Widget A, I need to be able to look back at all individual transactions and see what it cost in Can $, as a minimum, maximum and average cost.

Quicken doesn't seem to cut it so I have to extract a report to Excel, save it, and refer to it to build these comparisons.

Take that widget number and multiply by several hundred, take currency changes and multiply by several thousand variables and you can see that I am challenged to come up with anything but an approximation.

Approximations don't cut it in knowing the true cost of goods sold.

If I was keeping paper records, I would have a ledger for widget A, cost, currency, conversion and Canadian cost so I could manually scan for minimum, maximum, rough average and make an intelligent buying decision.

I currently keep comments stating how many widgets, price, conversion rate etc but it is cumbersome to use that comment field to create reports

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

Actually I understand what you are saying about your problem, and I have known that it is always been a problem in Quicken.

For Quicken to do really do multiple currencies correctly it would have to treat every amount like it does for securities.  As in not only do you have the daily record of prices, with every transaction you record the price of that security at that time.

Now as programmer I know why they don't do this.  First off when started they wouldn't have planned for something like this and it would have been best if they wanted to do this to do from the ground up.  But maybe even more importantly if you look at what happens in the investment accounts when the transaction count goes up, the performance goes down.  This is at least partly because of the extra information that they have save and compute.

So to do this all the way through Quicken would be costly in both storage space and performance.  And it would make Quicken a whole lot more complex, and that means more bugs.

So with that said, that is why you are currently left with something that doesn't really meet your needs.

BUT that wasn't what my last question was about.

I was wonder if you had any insight on the benefit of saving the exchange rates as far as anything you have seen in Quicken.

Saving daily exchange rates in itself isn't really that important.  I can get that kind of information from off a website.

It is only when that information is applied to something in Quicken that it has any benefit.  As far as I can see Quicken doesn't use the daily exchange history for anything.

The US version keeps just the current exchange rates so it isn't any surprise that if someone that has accounts in multiple currencies and pulls up a net worth graph over time, that the historical net worth values are going to be wrong.  It will be right for only today's value, not for any values in the past.

The US customers when hearing that the Canadian version is storing the daily exchange rates expected that rate to be applied to the amounts in the reports so that at least they represent what that amount would have been at the close of that day.

I now have access to a Canadian version of Quicken, and I can't find a single place where is actually uses any of that historical exchange rate data.

Can you?

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

I don't see any subsequent use to the day rate in my Canadian version and surmise that I would use it if I set up quicken to automatically collect the day rate on several currencies each day.  Effectively it would just save me the effort of looking it up later and trying to think if it was an end of day or intra day rate.  

And I ran some grander scale tests tonight and came to the conclusion that it really doesn't make that much of a difference.  On days where I have multiple purchases I can blend the rate based on total cost Canadian divided by total cost currency x and override the automatic download of the day rate.

I can extract the information, cross reference the day rates, and come within about 20 cents on 100k worth of numbers so the margin of error is minuscule.

So, outside of an interesting mind exercise I can move on to my next insignificant task.

Thanks again for sparking the conversation.

Unknown

Thanks QuicknPerlWiz

Your comment about the historical exchange rate list is helpful and I will go back and test based on this table.  

Will get back to this forum following testing

Thank you for the information.
That is basically that conclusion I came to about the storing of the exchange rates in the Canadian version.  Currently it has no value over say looking it up on the web for that day.

In truth as you have found out the idea of a "precise" currency calculation is an extremely difficult thing to do, and in fact my example of comparing it to a security isn't even accurate.

What's more also as you have found it is pretty close to a useless notion.

First the difficulty.  If I buy a security I get something like 45 shares of IBM at 43.55 per share.  Notice at this point I need two numbers to represent the transaction.
If I was exchanging cash I would have something similar.  1 USD to .86 CAD.
(Note I'm not even sure the Quicken would have access to the exact exchange information for all transactions).

So that part is like I was like my comparison to securities.
But Quicken allows people to do something in a report that makes this into an extremely difficult if not impossible task.  You can ask for the report in any given currency.  The only way to do that would be if you knew the exact currency exchange for all currencies involved for at the time of all your transactions.  That would basically be impossible.

What's more it doesn't really make sense.  Let's take your widget example.
I can state that on a precise time on July 7 IBM sold for 43.55.  So I have its "cost" at that moment, but as a practical sense, I can never buy at that price again.

So if I'm selling widgets A and B in different currencies, then the relationship of costs is always changing.  It might be interesting that B cost for 123.34 CAD a year ago and at the time that was sold for say 105.33 UK pounds.  I would of course be interested in this relationship because it is cost to profit.  But I could never count on that exact relationship every happening again.

Unknown

I've been using Quicken 2006 for 20 years but only recently started using the investment performance reports. I also found the problem that the Quicken internal rate of return reports for international securities I bought takes the latest exchange rate in that list of historical exchange rates when calculating the IRR NOT the rate that's loaded when you input the security purchase into the investment register! So the IRR calculations are all wrong on the Investment performance report, so are the portfolio value and cost reports!!

I only found this out as I started using sharesight.com/nz website, inputted all my share transactions and got big variances with the returns there against my quicken reports. Thank goodness I found this out! I'm glad I'm migrating to some other system as I do a few international transactions and quicken is not really equipped to deal with this properly if it's not taking the exchange rate from the day a security was bought or sold but rather it's using the latest exchange rate you've inputted (even to calculate the cost of the securities bought).

Unknown

I've been using Quicken 2006 for 20 years but only recently started using the investment performance reports. I also found the problem that the Quicken internal rate of return reports for international securities I bought takes the latest exchange rate in that list of historical exchange rates when calculating the IRR NOT the rate that's loaded when you input the security purchase into the investment register! So the IRR calculations are all wrong on the Investment performance report, so are the portfolio value and cost reports!!

I only found this out as I started using sharesight.com/nz website, inputted all my share transactions and got big variances with the returns there against my quicken reports. Thank goodness I found this out! I'm glad I'm migrating to some other system as I do a few international transactions and quicken is not really equipped to deal with this properly if it's not taking the exchange rate from the day a security was bought or sold but rather it's using the latest exchange rate you've inputted (even to calculate the cost of the securities bought).

I never tested investment accounts for this kind of thing, but given what they are doing in the regular reports it makes sense that they would also be not doing the investment continuations correct either.

Thanks for the feedback.

Unknown

Yes @QPW agreed! It didn't really come to light for me until I started generating the cost vs today's value investment reports. I only used quicken to work out my net worth on a given day and not really compare it to earlier dates as I was doing that via a spreadsheet. I can understand why they removed a lot of those investment reports in quicken versions after 2007 and don't even offer them now in the quicken for mac 2017. I'm exploring the sharesight + xero cashbook combination which even though it's cloudbased and more expensive, hopefully the reports are accurate and give me the info I need.

Unknown

You still need the historical exchange rates if you want to work out your net worth in quicken and have assets in other currencies. Every time you input a new historical exchange rate it adjusts your net worth figure. I'm not sure why it can't take the historical rates you input when you input an international security transaction for the cost basis of investment performance reports. As you say it must be too complicated. I doubt many people who use quicken actually use the investment features (or used them if they were using pre Quicken 2007).

smayer97

I've been using Quicken 2006 for 20 years but only recently started using the investment performance reports. I also found the problem that the Quicken internal rate of return reports for international securities I bought takes the latest exchange rate in that list of historical exchange rates when calculating the IRR NOT the rate that's loaded when you input the security purchase into the investment register! So the IRR calculations are all wrong on the Investment performance report, so are the portfolio value and cost reports!!

I only found this out as I started using sharesight.com/nz website, inputted all my share transactions and got big variances with the returns there against my quicken reports. Thank goodness I found this out! I'm glad I'm migrating to some other system as I do a few international transactions and quicken is not really equipped to deal with this properly if it's not taking the exchange rate from the day a security was bought or sold but rather it's using the latest exchange rate you've inputted (even to calculate the cost of the securities bought).

See...it is doable...it simply requires the will to implement this in Quicken.

Unknown

I've been using Quicken 2006 for 20 years but only recently started using the investment performance reports. I also found the problem that the Quicken internal rate of return reports for international securities I bought takes the latest exchange rate in that list of historical exchange rates when calculating the IRR NOT the rate that's loaded when you input the security purchase into the investment register! So the IRR calculations are all wrong on the Investment performance report, so are the portfolio value and cost reports!!

I only found this out as I started using sharesight.com/nz website, inputted all my share transactions and got big variances with the returns there against my quicken reports. Thank goodness I found this out! I'm glad I'm migrating to some other system as I do a few international transactions and quicken is not really equipped to deal with this properly if it's not taking the exchange rate from the day a security was bought or sold but rather it's using the latest exchange rate you've inputted (even to calculate the cost of the securities bought).

I never said it wasn't doable.  It is even doable to have currency support where a given account might have transactions in any given currency (which is much more complicated, and has a lot more overhead).

But that "simply" is actually the "hard" part.  As Quicken Inc has to decide that it is a "priority" and to do it.

smayer97

I've been using Quicken 2006 for 20 years but only recently started using the investment performance reports. I also found the problem that the Quicken internal rate of return reports for international securities I bought takes the latest exchange rate in that list of historical exchange rates when calculating the IRR NOT the rate that's loaded when you input the security purchase into the investment register! So the IRR calculations are all wrong on the Investment performance report, so are the portfolio value and cost reports!!

I only found this out as I started using sharesight.com/nz website, inputted all my share transactions and got big variances with the returns there against my quicken reports. Thank goodness I found this out! I'm glad I'm migrating to some other system as I do a few international transactions and quicken is not really equipped to deal with this properly if it's not taking the exchange rate from the day a security was bought or sold but rather it's using the latest exchange rate you've inputted (even to calculate the cost of the securities bought).

yup... :-)

Unknown

If you are looking for a program that might cope with multi currency transactions that flow through to reporting maybe look at the [removed]  combination. Or possibly even the [removed] may be enough.

No guarantees that it would work and it will cost more as it is cloud based but at least the help desk will get back to you on a timely basis by email and they might, half way, know what they are talking about from a financial point of view. I'm having a look at it now. I would suggest sending them through some questions first. I think you can trial it for a month.

Unknown

I stand corrected here. Following this discussion I discovered a button you can tick when generating the investment performance reports in quicken 2006 (! - still works well) which is called 'transaction exchange rate'. When this box is ticked the annualised return takes into account currency gains and losses as well as the dividend and share price movement. I thought I should mention this as I was dissing quicken when I shouldn't have been.

Unknown

The status of this thread says, "In Progress."  Can someone from Quicken comment on whether historical exchange rates are under consideration for a future release of Quicken?

jr7107

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

Unknown

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

For what it worth Quicken Windows Canadian version still has the historical prices saved.
BUT they in fact don't use them in any of the reports.  Their reports do the same thing Quicken Windows US does and only use the most current exchange rate.

jr7107

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

Can you see them in a list like security prices? I had Q for CAD last in 2002 and the reason I kept it was for that until they dumped it by 2004.

Unknown

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

jr7107, my memory on it is "fuzzy" because I only used it in beta testing.  But if I remember correctly you could see the exchange rate history on the Tools -> Currency List.

And you could manually enter exchange rates there too.

There was also the fact that it would get "old exchange rates".  It was like Quicken Us getting the current one and difference just saving it.  So if you didn't keep up with it you would have gaps in the history.

smayer97

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

FWIW, QM2018 (that is the Mac version) now has the feature to download currency rates. It will be interesting to see how they implement this in the long run.

jr7107

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

The benefit of the historical forex rates is that it did value the portfolio correctly (and net worth) by applying the latest rate that was downloaded into the HCX. Need this functionality in the US product.

Unknown

Once long ago circa 2002 Quicken used to handle prices in separate files. QPH were for securities, and HCX files were for currencies. Eventually the HCX (Historical Currency Files) were dropped entirely for the sake of "single currency price" that became a manual transaction, and the QPH file was rolled under the QDF file.

Historical exchange rates are ideal for the multi-currency user that needs real time updates for Net Worth. But this feature has been gone 15 years, and I ask every year for its return. That, and historical dividend rates for equities.

The average cost of currency, and currency moves have a big impact on an investment's return. Would be nice to manage it again in Q.

It might have worked correctly in the Canadian version at one time (and the US version for that matter), but it certainly doesn't now (reports wise).  It was one of the main things I checked out when I got a chance to do a beta test on it.  For years we (US customers) had been told that one of the advantages of the Canadian version is that it saved the exchange history.

And that is true, but it doesn't apply them to the reports or the portfolio for the past.

Also the fact that there isn't any way to fetch old prices just the most current seems like a very big limitation.  That means if you aren't updating everyday you are going to have gaps in the information.  And this might even be a limitation in the contract they have with the service provider.