Hi everyone. Can anyone explain how to import account transactions via CSV when the account is in GBP, but some but not all of the securities held are in GBX (because their quotes are provided in GBX)?
I seem to have gone around in circles. Initially, I just provided all in GBP:
But I got
"Exchange rate of gross value is missing (transaction currency GBP and security currency GBX): 12 Oct 2021 Buy GBP 30,884.34 HSBC MSCI World UCITS ETF "
Then I tried adding Transaction Currency, Gross Amount, Exchange Rate for any transactions on securities that are quoted in GBX. For transactions on securities that are quoted in GBP, I just left as-is:
But now I get
"Transaction currency GBX does not match account currency GBP: 12 Oct 2021 Buy GBX 3,088,434.00 HSBC MSCI World UCITS ETF "
If I have a mix of securities that are
understand it, PP assumes transactions occur in the currency of the security. The problem is, the currency of the security (in terms of GBP/GBX) has to be determined by the currency the preferred quote provider is supplying it in.
As such, I’m having to modify all my CSV import data for the specific securities that I can only get GBX quotes for. Otherwise I get this:
I previously came across similar issues for PDF but I still haven’t found a good solution to this. Am I missing something obvious?
The most important issue is that Buy isn’t an “account transaction”. To import a Buy transaction, you need to change “type of data” at the top to “portfolio transactions” (or similar).
Then, it should work if you set the “transaction currency” to GBP (the currency of the cash account), “currency of gross amount” to GBX, “exchange rate” to 100, and “value” to the amount in GBP. You don’t need to specify “gross amount”.
Thanks @chirlu. Splitting out the Buy/Sell transactions for an ‘portfolio transaction’ import seems to have worked. However, when I then try to import Dividend/Interest etc as an ‘account transaction’, like this:
Exchange rate of gross value is missing (transaction currency GBP and security currency GBX): 15 Nov 2021 Dividend GBP 111.64 HSBC MSCI World UCITS ETF
Any suggestions? Many thanks
For the dividend, unlike buy/sell, you need the “gross amount”. (Why? I have no idea; a bug, probably.) Should be “value” times 100, obviously.
I’m fairly sure there’s a wider bug here, but I could be mis-understanding.
With this as input:
I then got:
"Imported gross value (GBP 1,116,400.00) and calculated gross value (GBP 111.64) do not match: 15 Nov 2021 Dividend GBP 111.64 HSBC MSCI World UCITS ETF "
I then tried various combinations of gross value, value and exchange rates just to understand what might be going on:
And this results in:
The first row works, but is incorrect. The last row is as close to me ‘tricking’ it into doing the right thing, by specifying an incorrect gross amount (reducing by a factor of 100) - but then rounding means it doesn’t match anyway
The exchange rate should be given as 0.01 here. The other parameters are the same as for a buy (except that a gross value is required).
Hah, doh - should have spotted I was using 0.1 instead of 0.01. All working now, having changed all to 0.01, thanks @chirlu.