Reporting in different currencies

[pg888]

Hi, I keep all my records in £ Sterling (as they are £ Sterling investments), but I want to report (historic as well as current figures) in US $. For example I want to show capital gains over a year on the basis of how many US dollars was each £ investment worth when I sold it, less how many US dollars was each such £ investment worth when I sold it.

I have got a GBPUSD=X investment and daily data (which by the way I had to manually import, but that is another post), but I cannot find a way to make the program report in the way that I want.

I would think that if it were possible to create custom calculated fields in custom reports, and to amend existing reports into custom reports, I could start with a capital gains report and turn it into what I want by adding dollar columns in the right places which take the £ values in the adjacent columns and apply the GBPUSD=X factor for the appropriate date in each case, e.g. $ = £ X GBPUSD=X on the given date.

I could export a capital gains report in £ and do this is a spreadsheet, but it would take a long time, so I would prefer an automatic solution.

Any ideas/how-to's on this would be appreciated.

[Mark]

Hi pg888,

Yes, you can do this. Have you assigned the GBPUSD=X as your exchange rate between £ and your default currency of $? You do this under "Options / Currency Settings...". You would want the default currency to be $ in this case, and make sure all your investments are assigned to £ as their native currency under "Investment Properties...". Once you do this, then you can simply create a Capital Gains report, and make sure the option "View / Exchange Rate Adjusted" is turned on. All of the values in your report will be expressed in the default currency ($). To have the report display all values in the native currencies (£), turn off "View / Exchange Rate Adjusted".

[pg888]

Mark

Thanks for your response last year which worked then, but I cannot get it to work now.

What appears to be happening is when I assign $ (instead of the usual £) as my default currency, all of the investments which are usually in £ in their individual properties are now shown as $. When I switch the default currency back to £ all of the £ investments go back to showing £ in their properties.

I think my problem is that because the £ investments are changing to $ in their properties, the change the default currency is not changing the value shown when I switch on Exchange Rate Adjusted. I guess the program is thinking, hmm, all these investments are already in $ so I don't need to convert them. It doesn't know they all changed from £ to $ (in their properties) the moment I chose $ as the default.

So I think the answer to my problem is if I can stop the £ in properties changing to $ (in every £ share's properties) when I choose $ as the default currency then the program will see that they are £ and convert them to $, which is what I want, i.e. so I can report in $.

Can you please suggest a solution so that when I designate $ as the default currency the £ shares do not change in their properties to $.

Many thanks in advance.

[Mark]

Hi pg888,

It isn't intended that you change your default currency periodically. I would suggest picking one, and staying with that. Each investment is assigned a currency in the properties of that investment. This assignment can be thought of as saying this investment uses the "first" or "second" currency, no matter what the label is. When you go to "Options / Currency Settings..." you are just changing the labels of the "first" and "second" currencies, but each investment is still assigned to the currency in the order you picked. Changing the label of the second currency from $ to pounds, and first currency from pounds to $ does not change your investment's assignment.

Hopefully this clarifies how the currency assignment and labels work, so you can decide how you want to set things up.

[pg888]

Dear Marc

Thanks for your rapid response above.

My problem is that all my shares are UK shares in £, but I need to print reports of capital gains and dividends in US$ (as well as being able to do it in £ too on other occasions). For each transaction I need to report the $ value of the £ receipt or payment at the date of that receipt or payment. I have got a separate investment for GBPUSD=X (and also one for the other way round USDGBP=X) all set up, with the daily exchange rates throughout the periods concerned entered as price data.

What I haven't been able to do this year is to repeat what I know I did last year after exchanging posts with you (see above), which is to report the £ transactions in $.

Any thoughts on how I could do this? I only need to do this a couple of times of year, but the easier the method the better!

Thanks.

pg888

[Mark]

Hi pg888,

Since you actually hold the shares in £ and want to be able to view in £ or $, I would make the default currency $, and assign all your investments to use the £ currency, which would be the 2nd currency listed under "Options / Currency Settings...". You want to record prices/transactions in the native currency of your investments, so that would be £ in your case. You can view data in £ by leaving off the "View / Exchange Rate Adjusted" option. When you want to view everything in $, turn on "View / Exchange Rate Adjusted".

[pg888]

Marc

Thanks for that response and I will try that as an immediate solution.

However, soon I am going to open a US$ brokerage account and will want to be able to see my £ and $ accounts in £ and in $. In other words to report on all my investments (bought/sold in £ and $) either as to their £ value/gains/income or their $ value/gains/income.

Is there a way to do this?

Also you mention making £ the second currency on the list of currencies on the Options / Currency Settings page. I wasn't aware that the currencies have to be in any particular order on that list, but should I take it from what you say that the order in which the currencies are listed is important?

Further question. When I switch the View / Exchange Rate Adjusted on or off does it matter which portfolio is highlighted in the Portfolio tree, e.g. Master Portfolio or £ or $ portfolios?

Thanks for all of your help.

pg

[Mark]

Hi pg888,

If you have 2 different currencies, there is no way to view all of them converted to each of those 2 different currencies. There is only one "default" currency, that you can convert everything into. You can either view everything in its native currency, or everything converted into the one default currency.

The "View / Exchange Rate Adjusted" option is global, it doesn't matter what sub-portfolio you have selected in the Portfolio Editor.

[pg888]

Marc

Yet another question ...

Am I right to think that if I have all of the daily currency conversion data from date X to date Y for £/US$, and I buy in £ at date X and sell in £ at date Y, and I report ny gain in $, the program will give me the value of the buy in $ at the conversion rate at date X and the value of the sell in $ at the conversion rate at date Y, as opposed to the value of both the buy and the sell at the date Y conversion rate?

pg

[Mark]

Hi pg888,

Yes, this is correct. Your gain in this case would be the value on date Y, using the conversion rate of date Y, minus the cost on date X, using the conversion rate from date X.

[pg888]

Marc

Thanks for all of your help and rapid responses, which I greatly appreciate.

If I may suggest as a candidate for future upgrades, many people seem to be investing globally these days, and needing to report in multiple currencies, for example for tax purposes in different jurisdictions. If it was possible to report on worldwide gains or income in different currencies then that would save a lot of work with a spreadsheet!

pg

[Mark]

Hi pg,

Thanks for the feedback, I can see that being a good improvement.

[pg888]

Mark

As you suggested, I am permanently changing my default currency to $.

However, when I do that in the Currency options screen the currency of all of my investments changes to $. They were all in £, but when I change the default currency they all change to $. If I change the default currency back to £, they all change back to £. I guess if I changed the default currency to Euros or something, they would all change to that currency. I do mean all the investments, including ones from long ago that I no longer own.

This seems to defeat the object of setting an investment's currency, which surely ought to be a once and for all thing, as in reality an investment is in one currency and it never changes? Is there some way to stop this automatic changing of all investments' currency when the default currency is changed?

I can change all of my investments back to £ one by one in properties, but this is very time consuming and a bit of a pain, even only doing it once, as there are a lot of them.

Is there a solution to this?

Thanks

pg888

[Mark]

Hi pg888,

This is expected. As you've observed, an investment is assigned to a certain currency position (first, second, third...), and the labels/rates can be changed, but the investments are still assigned to their same position. I can see how this can be inconvenient if changing after owning a lot of investments. Maybe we can have an option in the currency settings dialog that says something like: "Keep investments assigned to the same currency symbol", and FM would automatically change all your investment assignments, so if it was assigned to $ in the third position before, and you moved $ to the first position, it would move the assignment to $ in the first position. I don't have a good work-around to suggest for now though.

[pg888]

Mark

I've stumbled across what appears to be a solution to this issue. I am setting out an explanation below for the benefit of all. No doubt if you think there is an issue arising out of this you will let me (and everyone else know) here.

Using this solution I think it is possible to report investments in any currency, no matter what the default currency.

The key to this is that in all of my ramblings above I have done something that I now know to be wrong, namely I was setting my investments' currency by reference to the default rate. In other words, my default rate was £. That was the currency at the top of the Currency settings box. When I set my investments' properties I was selecting £ in the drop-down list, that is the top currency in the drop-down list (which irepresents the default currency, in this case £).

So I now understand that when I changed the default currency in Currency Settings to $, that changed all of my investments' currencies to $.

What I should have done is used a separate £ currency in Currency settings, in other words my default currency at the top is £ and then further down the list I should have had (and now have) a £ symbol currency. It so happens that this is tenth in my list, but I guess it could be anywhere from 2nd to anything.

Against that £ currency item I have a symbol which is GBP->USD. I have that set up as an investment (with zero shares) in just one of my portfolios. The Yahoo symbol is USDGBP=X, though Yahoo don't do historic currency conversion rates (only today's), so I have to import historic rates using CSV, and I get the data from Oanda.com. (These rates are imported as the "price" of the currency for every day of the period.)

(If anyone knows where I can automatically download historic currency data into Fund Manager I would be most grateful.)

(Why GBP->USD means USDGBP=X, i.e. back to front, I don't know, but that seems to be the convention, so I have adopted it.)

I then set all of my £ investments in properties to £. IMPORTANT - it has to be the £ symbol that is tenth down the drop-down list of currencies in properties (which is equivalent to the exchange rate set in Currency options to exchange rate GBP->USD, and NOT the £ symbol at the top of the drop-down list in properties (which represents the default rate). REPEAT - do not select the (top) default rate in the investment properties dialogue drop-down list of currencies, select the separate symbol that you have already set up in Currency options which is further down the investment properties currency drop-down list (in my case tenth).

So with the set-up above, I don't even have to change the default currency from £ to $ (in fact doing so has no discernible effect). I just have to switch on Exchange rate adjusted in the View menu, and all reporting switches to $.

I guess if I changed the conversion rate for £ in Currency settings (i.e. at position 10 in my currencies list) to a different exchange rate (e.g. Euros), which I had set up as an "investment" (with zero shares) somewhere in a portfolio, then switching on Exchange rate adjusted in View would report all £ items in Euros.

The really great thing is that I also now have a US$ portfolio, where the default currency is US$. I have got a $ symbol set up in Currency settings (happens to be Currency 3 in my settings, but I guess could be anywhere) and the exchange rate is USD->GBP, i.e GBPUSD=X. I have imported that data too (in effect, it is the mirror image of GBP->USD) into a separate investment (with zero shares) in one of my portfolios. Again, I have the currency for each $ investment (in its properties) set to $, i.e. the $ symbol appearing third in my drop-down currency list in investment properties.

With this set-up, when I change to Exchange rate adjusted in View the $ portfolio reports in £. No doubt I could make it report in any currency by setting up a different exchange rate in Currency settings.

Another thought. What this doesn't allow me to do (I don't think, I don't have to do it, so I haven't applied the brain to this one) is to report all investments in the Master Portfolio to a single currency (to get a "what are all my investments taken together worth in £ or $ today" figure), but I guess if I grouped all of my $ portfolios (if I ever have more than one $ portfolio) under one sub-portfolio, I could report that entire $ sub-portfolio in any currency I wished (currently in my above-described set-up in £ or $). That would save running multiple reports to get to the "what am I worth in £ or $ today" numbers). I would just have to run two reports, one for the $ sub-portfolio, and one for the £ sub-portfolio (engaging Exchange rate adjusted in the View menu as required).

Tell me if that is not a great (set of) solutions(s). (And maybe saved you a bit of development time?!)

pg