Financial portfolio Daily calculation with FX

[AlejandroVazque]

I need some help to calculate our financial portfolio everyday using the right FX rate conversion.

I have a tables as follows:

Table One: Cash flows

Instrument	Cash Flow	Date
1	-100	31/12/2019
1	20	01/01/2020
1	20	01/02/2020
1	60	03/02/2020
2	-100,000	02/01/2020
2	40,000	03/01/2020
2	40,000	04/01/2020
2	10,000	05/01/2020
2	10,000	06/01/2020
3	-11	30/12/2019
3	8	30/03/2020
3	3	30/05/2020

Table 2 Instruments

Instrument	Currency
1	MXN
2	EUR
3	USD
4	USD

Then I have a nother table with the FX rates

FX	Date	Value
MXN	01/01/2020	20
USD	01/01/2020	1.1
EUR	01/01/2020	1
MXN	02/01/2020	30
USD	02/01/2020	1.2
EUR	02/01/2020	1
MXN	03/01/2020	25.2
USD	03/01/2020	1.1
EUR	03/01/2020	1
MXN	04/01/2020	20.1
USD	04/01/2020	1.3
EUR	04/01/2020	1
MXN	05/01/2020	22.3
USD	05/01/2020	1.23
EUR	05/01/2020	1
MXN	06/01/2020	21
USD	06/01/2020	1.04
EUR	06/01/2019	1
Etc....

What I need to report is to report our portfolio position in EUR eveyr day in a visual in Power bi (X axis Time, Y axis the portfolio value). The porftolio value is calculated by taking the cash balance at every day of reporting converting it into EURs ,so for example for

The reported Values for The folloiwng dates shoud be seen in the PowerbI plot

Date	Reported Value	Here I add a comment
01/01/2020	-14 EUR	Instr 1: -80 MXN Then Divide by 20 = -4 EUR Instr 2: 0 EUR Then Divide by 1 = 0 EUR Instr 3:  -11 USD Then Divide by 1.1 = -10 EUR
01/02/2020	-111.17 EUR	Instr 1: -60 MXN Then Divide by 30= -2 EUR Instr 2: -100 EUR Then Divide by 1 = -100 EUR Instr 3:  -11 USD Then Divide by 1.2 = -9.17 EUR
01/03/2020	-70 EUR	Instr 1: 0 MXN Then Divide by 25.2= 0 EUR Instr 2: -60 EUR Then Divide by 1 = -60 EUR Instr 3:  -11 USD Then Divide by 1.1 = -10 EUR
ETC....

Appreciate if you can help as I have tried to do some measures but cannot manage to indicate which FX rate to apply for example in USD rate in date 01/03/2020 because there is no cash flow that date in the cash flow table for USD

[daxer-almighty · Accepted solution]

@AlejandroVazque 

OK, I've done it (use the same link as above). There are 2 major approaches to this. One solution is to disconnect the Dates table from the other ones. The other is to disconnect the tables inside the relevant measures. I did check both options---they do work---but then opted for the latter one. It'll be harder to understand but I thought you'd like to maintain the relationships for other purposes.

[AlejandroVazque]

What I'm trying to report is the amount of cash the investor has everyday. But the amount the investor has everyday in the account (MXN , EUR and USD) ahs ot be converted to EUR every day to see his worth value at that day in EUR. So the investor has 3 different investments. One in MXN anoter in EUR and last in USD. He invests as follows:

MSN pesos: Invests 100 MXN in 31st December 2019. He gets repaid 20 MXN in 1st January 2020, 20 MXN in 2nd January and 60 MXN in 3rd January. ... For the second investment in EUR , he starts the investment only the 2nd January by investing 100 EUR, he gets repaid 40 EUR the 3rd January 40 EUR the 4th of January and 20 EUR the 5th January. On the USD investment, 11 USD the 31st December 2019, and gets repaid 8th on the 30th of March and 3 on the 30th of May. As mentioned, I have full list of FX rates for all MXN, USD and EUR day by day for all the dates.  The answer should tell to the investor (all figures in EUR) every day what is his position noticing that everyday we need to calculate the applicable FX rate. So for example in January 1st 2020 he has invested (80MXN which that date the FX rate is 20 --> so that makes 4 EUR, he has not yet invested any EUR so EUR balance is Zero, and For USD he has a balance of 11 USD - which FX rate on the January 1st is 1.1 = 10 EUR) In overall the report then of his balance on 1st Janaury is 4EUR+0EUR +10 EUR = 14 EUR. This is the figure I should see on the table for 1st January.

For 2nd of Janaury I should see (he has 60 MXN in his account, but now the FX rate is 30 so that is only 2EUR, for the EUR investment he just did the investment this day of 100 EUR and ofr the USD he still has the same 11 USD but this day 2nd January the exchange rate is 1.2 so this is only 9.17 EUR) The figure I should see for 2nd Janaury is then , 111.17 EUR.

For 3rd January, and every day I should have his balance

[daxer-almighty]

@AlejandroVazque 

Please refer to this article at www.sqlbi.com to know how to do such things correctly. Please do not try to reinvent the wheel. You don't want to waste your time.

[AlejandroVazque]

Hi Daxer,  Thanks for sharing, I can see that this is related to a single sales currency transaction. The difference here is that even when the  sale (cash flow) event happened at a certain date, I need to reconvert the balance very day as the FX change. Here the figure I need to convert is the balance of the sum (cummulated measure of cash flows until the required date) Appreicate if you can share with me how I coud do this

[daxer-almighty]

Does what @Jihwan_Kim did not cut it?

[AlejandroVazque]

Unfortunately no, as ou can see, in the answer he gently provided me , for example for 3rd of February 1/02/2020 I have lost the USD balance amount I had the previous day and the USD is not updated to the new USD rate of that date (should appear 9.67 USD)

[daxer-almighty]

Looking at the tables you posted above... The FX table does not have all the dates that can be found in the Cash Flow table. Are you absolutely sure you'll have exchange rates for all the dates in your model?

[AlejandroVazque]

Daxer, yes I do, if you want I can write the tables again here the FX rates by currency, date and Rate:

MXN 1st January 2020 = 20

MXN 2nd January 2020 = 30

MXN 3rd January 2020 = 25.2

MXN 4th Janaury 2020 = 20.1

MXN 5th January 2020 = 22.3

MXN 6th January 2020 = 21 

MXN 7th January 2020 = 23 (I can go on if you want you can use dummy data)

USD 1st January 2020 = 1.1

USD 2nd January 2020 = 1.2

USD 3rd January 2020 = 1.3

USD 4th Janaury 2020 = 1.04

USD 5th January 2020 = 1.14

USD 6th January 2020 = 1.23

USD 7th January 2020 = 1.12 (I can go on if you want you can use dummy data)

For EUR all FX rates are 1.0

[daxer-almighty]

@AlejandroVazque 

I'll tell you what I'm gonna do. Since I never trust anyone and even myself I'm going to assume that there'll eventually be gaps in your exchange rates (you'll thank me later). If you don't have gaps, everything will be calculated the way you want. If there is a gap for a certain day, I'll take the last rate that is before the day in question, that is, the latest rate available. As I said, if your rates are contiguous, it'll work the way you imagine but I have to make sure it works reasonably also in case you've got problems in your data. It's ultimately up to you to make sure the quality of your data is spot-on.

Please bear with me...

[AlejandroVazque]

Daxter, I would apprecaite enourmously ... (I run a powerquery to fill down all the FX rates, so the missing date is kind of sorted out from the power query excercise done to build the FX Table)

[daxer-almighty]

@AlejandroVazque 

Here's the solution.

[AlejandroVazque]

Daxer, I have checked and unfortunately the solution has similar problem. Here I print screen the solution done in Excel  - I can try to send this file to you so you can see the difference 

[daxer-almighty · Accepted solution]

@AlejandroVazque 

OK, I've done it (use the same link as above). There are 2 major approaches to this. One solution is to disconnect the Dates table from the other ones. The other is to disconnect the tables inside the relevant measures. I did check both options---they do work---but then opted for the latter one. It'll be harder to understand but I thought you'd like to maintain the relationships for other purposes.

[daxer-almighty]

Yeah... To get the sum of balances for all the visible instruments at any given point in time one has to either DISCONNECT the Dates table from Cash Flows or make the relationship inactive or create a measure that will do one of those two things in its body.

[AlejandroVazque · Accepted solution]

Daxer, perfect this is working.. Thanks

[Jihwan_Kim]

Hi, @AlejandroVazque 

Please correct me if I wrongly understood your question.

I made some assumptions like below.

- some of your dates are not clear. for instance, I could not know whether it was mm/dd/yyyy or dd/mm/yyyy. so I just assumed and amended some of them.

- some of your numbers are not clear. for instance, is the comma a thousand separator or a decimal indicator? so I just assumed and amended some of them.

https://www.dropbox.com/s/yymnwbtorha62d6/alejan.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[AlejandroVazque]

Hi Kim, My dates are in format dd/mm/yyyy. Actually I have for every date the FX rates for the currencies. (The table FX is full of data. I have the values of FX for every date. Could you run the solution now ocnsidering that I have all FX so not need ot use the last non blank value?