How does Freetrade convert $ to £? Need for Google Sheets Tracker

Help 👋
1

Hello! I’m trying to use Google Sheets to track my stocks and it works perfectly for the UK ones cause theres no conversion in currency… but for the US stocks in $ I can’t work out how Freetrade are getting the value back into £??

When I do the conversion using the live exchange rate my £ value is always out by a little bit so it isn’t reflecting my portfolio correctly.

I know that theres a 0.45% charge on US stocks so I guess my question is how are freetrade using this value? Is it a fixed live thing that is added onto the live exchange rate? or is it taken at time of purchase?

For example

EA is at $141.56
The exchange rate is 0.7906 USD to GBP
So therefore EA is at £111.92 in
now I haven’t added the 0.45% cause I don’t know were it goes?

I own 1.0283 shares and market value of my stock below
Google Sheets - £115.09
Freetrade - £115.11

I know it’s not a delay either because the UK stocks show correctly?

Help???

2

0.7906/1.0045

3

Can you be more specific cause I don’t think is works?

1 Like
4

Is there a google finance USD to GBP tracker?

edit: Found it!!

=GOOGLEFINANCE(“CURRENCY:USDGBP”)

5

Freetrade applies 0.45% on the base fx rate at order execution.
The current value on the Freetrade app doesnt factor this fee in

The FX rate is used to work out the current value, however I believe the base FX rate aka Spot rate is what is used at order execution as this is the rate available for the trade

1 Like
6

Despite Google saying so, you don’t need the CURRENCY: bit.

Note if FT use spot, you won’t get that with the GOOGLEFINANCE function, which only offers

a. real time at the time you use it, or
b. daily open/low/high/close for historical rates

1 Like
7

Right so lets say I brought the stock at £109.93…
I brought 1.02834895 shares…
My FX rate is £1 = $1.2486
Base FX rate is £1 = £1.243
and theres a £0.49 FX fee (45bps)

The current price is $140.14…

How do I calucate my value in £ from all this? Cause I’ve tried it all and it doesnt work correctly.

Freetrade is telling me the market value for this is £113.09…

The way I’m doing it is 1 divided by my FX rate 1.2486 to give me 0.8008970046 conversion rate…
Then I’m multiplying my currently price $140.14 by the 0.8 above and getting £112.24…
But freetrade seems to think it’s £113.09 .

I’ve tried adding the 0.45/0.49 to everything everywhere and still nothing!

8

Sorry I’m not exactly sure how FT calculates the Current Value. Some clarity from their team would be great!