CVS Import where all values are in account currency

English Import/export
1

Hello.

I exported my account activity (Wealthsimple in Canada) to a CSV. This is a managed account where they invest your money mostly in ETFs.

The short version:
The problem is that they invest in US ETFs traded in USD but the account activity is all reported in CAD so PP complains that there is no exchange rate

The long version:
This is an example row in the CSV

transaction_date,settlement_date,account_id,account_type,activity_type,activity_sub_type,direction,symbol,name,currency,quantity,unit_price,commission,net_cash_amount
2021-01-01,2021-01-02,W111111CAD,TFSA,Trade,BUY,LONG,CRBN,BTC iShares MSCI ACWI Low Carbon Target ETF,CAD,10.955,139.218036707,0,-1525.13

The mapping template for Type of data: Portfolio Transactions is

transaction_date > Date
account_id > Securities account
activity_subtype > Type
symbol > Ticker Symbol
currency > Transaction Currency
quantity > Shares
net_cash_amount > Value

The problem is that the transaction is ignored because “Exchange rate of gross value is missing (transaction currency CAD and security currency USD)”

How can i get around this problem? transactions don’t have an exchange rate in the report. Adding the exchange rate by hand would also be a pain and the info is not even available online since banks give you whatever exchange rate they want

What’s strange to me is that I created a security CRBN and PP was able to load all the historical prices from yahoo, so it could deduce whatever exchange rate I was given by doing Value/Shares = unit_price_in_cad and then deducing the exchange rate unit_price_in_cad / unit_price_from_yahoo_in_usd

2

Hi, Although it specifically concerns how to use the import function for GBX priced securities when the cash account is GBP, given this is in effect a currency exchange I’m guessing this ‘how to’ guide could potentially be useful for your siuation as well:- How to use the CSV Import functions for GBX priced securities? - Portfolio Performance Manual

Good luck!

3

Thank you Rich.
I didn’t see that document before so thank you for that but I did read other threads with GBP to GBX problems. The problem is that those are trivially fixed by adding a column with a fixed exchange rate of 0.01 or whatever it is. In real currency exchanges, the rate is not easily known because each bank takes a cut from those currency exchanges so unless the bank itself reports the rate in every transaction, you have to calculate it yourself based on the unit price of the stock in local currency and the publicly available unit price in the currency the stock is traded in, which is what I would expect PP to do since it does have all that info. Adding that column in excel (libreoffice really) would be a pain since it would need to pull the stock prices from somewhere

My bank can’t be the only one reporting every transaction in local currency so I can’t imagine i’m the only one with this problem

4

No you aren’t the only one.

But to be honest, PP doesn’t calculate the fx rate for a couple of reasons.

  • external sources aren’t reliable
  • Bank date of exchange varies
  • fx rates can vary independently based on bank fees

In the end you get a summary result which is not in the sense of PP.

And to be honest, this is an issue of the broker/bank! Instead of „claiming“ PP to calculate something inaccurate people should pressure their broker/bank to disclose what they try to hide from there customers. This is where the real issue is happening.

2 Likes
5

i agree with the transparency point. That would be useful even if you don’t use PP

But where would the innacuracy come from? we have the stock price in local currency from the CSV. We have the stock price in the trading currency from yahoo finance, that will let you calculate the exact fx rate that was used by the bank, they can’t hide it

When you say

external resources aren’t reliable

you mean yahoo finance? or you mean external resources for the fx rate?

6

Yahoo Finance isn’t reliable. And even if they were:

if you don’t know the exact day of the exchange you can’t calculate the correct fx rate.
Just ask yourself why is the bank hiding information which they do need for their calculations anyway. Lagy transparency hides the hidden fees in what ever form they charge their customers.
Your result will never be accurate.

In theory you could try the math based on official fx rates and your booking. But this would lead to the fact of disclosing what the bank really is doing.
And PP is not in charge of doing so.

Imagine all the questions from PP users this would bring up.

7

Got it. that’s news to me, I thought stock prices were regulated by securities laws and had to be reported perfectly, but YF is a free service after all so I shouldn’t be surprised that it’s not super reliable

Having said that, if we can’t rely on the exact date of when banks say something happened then PP’s calculation of returns will be wrong too. I imagine PP takes the data at face value and does the best it can with it, when calculating IRR or if it were to calculate exchange rate.

In any case, that would be more of a feature request, which is not my goal here. The only question left for me then is what do others do in a similar situation? The answer can’t be “don’t use PP because your bank is not being fully transparent”. This is personal finance after all, not software for profesional accountants. I’d imagine there must be workaround

8

Stock prices are of course regulated but on the lvl of the stock exchange not on plattform lvl.

How others deal with fx rates is well documented in the forum. Your ways vary based on what you‘d like to analyze.

9

PP will not show exactly the same but for visualisation purposes it is enough.

As for your currency issue, I have several conversion scripts (XTB, Trading 212 and Freedom 24) which fill necessary columns automatically. Maybe you can use it as inspiration and with help of AI get what you need.

10

yes! i am already writing a python script to do the exchange rate calculation I mentioned above but a sample script would be much appreciated. where can i find yours?

11

I did that in PowerShell as it was the easiest option. With help of Claude or Copilot you should be able to modify it. I added few comments so it should be easy to understand. You basically just replace columns names and add your exchange rate. Here is the link