A thought on currency conversion

SwissDGI

Dear Vidas

first and foremost, your Portfolio Slicer looks very good and seems to me the most promising FREE tool I have researched in the WWW to manage my portfolio in the future, and I have been following the evolution in recent months (BTW: there aren't many professional tools available that match PS's reporting capabilities until you spend A LOT of money ;-)). Thank you for dedicating so much time and effort in this great project. The migration of market data retrieval to external files and that it is not necessary anymore to maintain "my data" is a great development IMHO.

The only thing that has me kept away from starting to track my portfolio with this great tool is the currency "issue". As an "international" investor I have positions in more than 5 currencies (USD, EUR, CHF, CAD, GBP, JPY) managed on accounts with at least four different currencies.

So here are some thoughts from my side.

• 3 reporting currencies sounds great, but in general to compare investments in different currencies I usually watch them in one reporting currency (which is my home currency CHF), or grouped by position currency (all my EUR investments etc.). In order to analyse FX effects one currency would be sufficient for most users...
  
• I understand that in your data model you have to retrieve all currency pair permutations (no sure about that). If you restrict to one reporting currency (and "original", i.e. position currency) would that be beneficial for loading/calculation times even if more transactional/position currencies and account currencies are allowed? (as a consultant for a investment management software I see that most "professional" software tools do this in a similar way, i.e. bookkeeping in the "transactions" table for position and "base" currency).
  

I really, really would love to use PS, it is the nearest to perfection regarding my requirements, only the 3 currencies-restrictions holds me back (and my PowerPivot knowledge is zero, so "customizing" your wonderful tool to overcome the restriction is not an option ;-)

Thank you very much in advance for overthinking the currency topic once more and best regards
SwissDGI

PS: Please forgive me (and everyone else) for any English errors made above (I am not a native speaker).

VidasMatelis

Hello,

Thank you for sharing your thoughts - I appreciate that and I keep in mind user suggestions when I am doing development.
So let me explain my problem:
I am supporting 2 interfaces now - Excel and Power BI. Actually, there are 3 Excel versions that also behave differently (Excel 2010, 2013 and 2016), but lets ignore that.
So the issue with Excel is that every time you initiate refresh - all Pivot Tables are refreshed (all pages, all reports). That takes a long time - 1min or if you are using generated dividends, could be 2-3 min and even more. That is very long time for a refresh to run. I tried to add 5 currency support to Excel, but removed it because it noticeably increased refresh time and I decided that for the majority of users this would be an issue.
For Power BI Desktop users behavior is different - refresh is done just to the model and existing page reports. That is much faster - about 8 sec on my working data, so data refresh experience is much nicer. Current version of PowerBI supports up to 5 currencies - because performance impact was barely visible.
I myself now do refresh about 90% in PowerBI and about 10% in Excel. I just finished latest development in PowerBI - for better index comparison and all development was done in Power BI. Now I have to migrate that to Excel and that will take some time. Of corse, all data entry is done in Excel, just reports are now reviewed mostly in PowerBI. But my understanding is that majority of PS users are still working in Excel. So I have to deliver a product that is good for average Excel user. And I know that adding more currencies support will impact performance, even if the majority of them will not use extra currencies.

Now the good news - old version of Portfolio Slicer had "hard" limit of 3 currencies. Current public version of Portfolio Slicer has a limit of 3 reporting currencies, but no limit of currencies used. My expectation is that you should be able to add 10 currencies into Report Currency table and have accounts or symbols with any of that currency, but you will be able properly see results that are calculated in first 3 reporting currencies. For that to work you still will need to provide exchange rates between all currency pairs. I have to point out that I have not tested this and there is a good chance that I am wrong, but if you have time - please test this with few records and see if it works as you would expect.

Do not worry about english - I see no problems in your post. For myself, english is 4th language that I started to learn when I was 18 years old. I make many mistakes, but if other understands what I want to say - that is good enough .

SwissDGI

Hi Vidas

thanks for your quick and insightful reply. I see and understand that this is a lot of work for you to do - the more thankful I am (and I am sure all your happy users are as well) that you dedicate your time also answering the questions in the forum.

Thank you for the hint with the "soft" limit on currencies. I have started to setup a test file. After some errors I found all the errors and typos in data entries and was able to setup some test cases (starting one currency after the other). My observations up until now:

• The Excel version 2.3 (Excel 2013) seems to cope also more than three reporting currencies (if there is a slicer for reporting currency). and also in the symbol currency slicers.
  
• I haven't been able to verify all translations but it seems to work (the amounts switch to another value)
  
• I now have tested a case where symbol currency is not equal account currency. according to documentation this should work well if I enter units/price/fees in symbol currency and add the exchange rate to translate the cash impact/account impact to account currency. I think I might have detected an error there: The last column CBI (cost base impact) is calculated also in account currency. If I look into e.g. the holdings overview (slicer on *original") the cost base figure is the account currency amount. As a consequence, since price quotes are in symbol currencies the unrealised gain/loss computes wrong IMHO.
  

As a consequence of the last bullet I fear it might be necessary to revise the srcTransactions table formulas? Since I don't have the prof+ version of Excel I cannot access the data model or the measures definitions. But since you calculate CBI on the transaction table (which is the hell of a formula, I have spent a while to grasp it and still have the gut feeling that you tricked Excel into ignoring a circular reference?!?) It might be necessary to compute CBI for other currencies as well (for the Reporting currencies) in before and maybe calculate realised FX gains/losses or did you find a way to evaluate that in the model?

Thanks for thoughts on this in advance and all the best to you

VidasMatelis

Hi,

Regarding your 3rd point - this should work if you are looking at reports in one of the first 3 reporting currencies. If you are using original currency, then I believe when you are using transactions where Account Currency <> Symbol Currency, you will get numbers that do not make sense.
When I calculate reports, I either convert currency (reporting currency is selected other than "Original") or not. Portfolio Slicer will be able to calculate proper cost basis (CBI=Cost Basis Impact) just for first 3 currencies.