Support an external exchange rate table

[GoogleCodeExporter]

From Dave said February 17, 2011 @ 1:15 pm
(http://microsoftmoneyoffline.wordpress.com/2010/02/12/java-app-to-update-quotes
/)

Sorry for how long this is… Re the multi-currency question, the only side 
effect I can see is incorrect calculation of account and portfolio value. I 
tried to talk to this in my previous post but perhaps I can do better with an 
example.

One account, 2 holdings. One holding has current ticker at $10.00 CAD, the 
other is at $10.00 USD. OFX shows $10.00 for both. Because of real world 
exchange, lets pretend the exchange is 1.01, so $10.00 US is worth $10.10 CAD. 
Lets pretend we have 10 units each. If we leave the USD holding configured as 
US in MM, and force the exchange in MM to 1.0 to stop the price from being 
changed (as per Hung Le’s suggestion), you end up with

Holding A : in CAD : 10 x $10.00 CAD = 100.00 CAD
Holding B : in USD : 10 x $10.00 USD = 100.00 USD
Portfolio value will show as $200.00

It SHOULD show as $201 CAD, because the $100 USD is really $101 CAD, so true 
portfolio value is really $100 CAD + $101 CAD. MM won’t show this because it 
will also use the exchange rate of 1.0 to convert the Market Value of the USD 
holdings to CAD when it tries to compute the account or portfolio value.

Bottom line – the technique can be used to get the numbers for the individual 
holdings into MM without messing them up, but you won’t get accurate account 
or portfolio totals anymore. Totals will assume all holdings are in the 
portfolio’s currency.

Outlining one potential solution scenario (because it involevs working with 
exchange rates don’t know if it will ever be viable using the OFX approach) : 
To really work properly, price output in the OFX for foreign holdings (ie not 
in the base currency) would have to be altered based on appropriate exchange 
rate (in the above case, with exchange at 1.01 OFX should show 9.90099 for the 
US holding instead of 10.00), exchange rate would also have to be updated in MM 
to also reflect 1.01, when value is imported MM applies the exchange and ends 
up coming back to the original $10.00 USD, and Market Value would be $100 USD. 
Since MM knows the exchange is 1.01, when it computes account or portfolio 
value it applies the exchange to the Market Value of the US holding and gets 
100.00 * 1.01 = $101 CAD equivalent, so overall total is properly reflected as 
$201 CAD. Again, two wrongs make a right, but a different two wrongs than Hung 
Le proposed.

Again, don’t know if we can get there or not… but thought I would throw it 
out for consideration.

Original issue reported on code.google.com by hle...@gmail.com on 19 Feb 2011 at 3:23

[GoogleCodeExporter]

I went ahead a implement the suggestion made by Dave (February 17, 2011 @ 1:15 
pm).

I think we are getting the desired result.

Scenario:
. MM2005UK (running in a VM), base currency is GBP, a non-Dummy account (also 
has GBP as currency) has an existing BBT transaction (entered on Feb 01, 2011)
. Dummy account (GBP currency) will house incoming OFX import.

. On the tool side, I added a column in the mapper.csv to be able to specify 
that the quote source currency for BBT is USD
, Quote source retrieves price for BBT in USD as $28.32
http://www.le.com/~hle/out/hle/2011_02_18/from_source_quote.jpg
. A new fx.csv file has FX rate: (FromCurrency,ToCurrency,Rate)
GBP,USD,1.6195
. When tool starts to write OFX, it converts $28.32 into 17.4930GBP
http://www.le.com/~hle/out/hle/2011_02_18/ofx.jpg

. On import, MM notices that the currency for BBT is actually is USD, so it 
uses its internal FX table
http://www.le.com/~hle/out/hle/2011_02_18/ms_fx_table.jpg
And the account ends up having the value for BBT at the correct price which is 
$28.32
http://www.le.com/~hle/out/hle/2011_02_18/portfolio_manager_01.jpg

I know there are a lot of moving parts, and need to synchronize files (fx.csv 
and MM internal FX table) but I though Dave had a good idea and I did not want 
it to go stale.

In light of MM2005UK is still getting quote price update, I don’t think 
anyone will want to try this out so I am not going to rush this out yet. Let me 
know otherwise.

Best,

Original comment by hle...@gmail.com on 19 Feb 2011 at 3:24

• Changed state: Started

[GoogleCodeExporter]

Original comment by hle...@gmail.com on 26 Feb 2011 at 3:31

• Added labels: Build_20110226_001

[GoogleCodeExporter]

Done.
See: https://code.google.com/p/hle-ofx-quotes/wiki/Build_20110226_001

Original comment by hle...@gmail.com on 26 Feb 2011 at 5:50

• Changed state: Verified