The Ticker of the ETF for Google Sheets

General
1

Hi all,

I am making a Google Sheets to keep track of the ETF prices in my portfolio real-time. Do you know the correct format of the tickers to be used with the googlefinance() function?

Thanks.

Cheers,
OJ

1 Like
2

This works for me, to get e.g. BCOG - L&G All Commodities: =GOOGLEFINANCE(“LON:BCOG”)

3 Likes
3

The function given by @pifflevalve works for me also.

Bear in mind that the return value will not necessarily be in pounds sterling (GBP) depending on the ticker. It could be GBX (effectively pennies), USD, EURO etc. You can use a 2nd formula that just gives you the currency, which is the same format but with an extra parameter of currency e.g. =GOOGLEFINANCE(“LON:BCOG”, “currency”).

Note for a reason I don’t understand it returns GBP for GBP, USD for USD, but N/A for GBX.

2 Likes
4

For the ETF I hold, including EEWG and FASE, when I add the currency parameter it returns error.

For FASE, without the currency parameter, it returns a value about 100 times of the reasonable value, so I divide it by 100. It still can work, but just not too ideal as I sort of guess that is the correct value.

5

Yes this is what I would expect. If you get an error with the currency parameter then this means the currency is GBX, i.e. in pence, and therefore you will need to divide by 100 if you want the value in pounds.

If you get a return of GBP with the currency parameter then happy days. If you get a return of USD then you will need to convert at the current exchange rate (which can also be found using googlefinance).

6

If you want to double-check that the #NA error returned by googlefinance really does represent GBX then you can cross-check by putting the ticker directly into the googlefinance website (https://www.google.com/finance/).

1 Like