Google Spreadsheets

General Chat đź’¬
#1

Hi Guys,

Putting together a spreadsheet for my stocks. Using a template which can be found here for anyone interested. (Not my template)

Though the formatting for the sheet is in usd, I need some to be in GBP.

With the formatting of share price in the uk being different from US stocks, when the price is carried in from google finance, it is brought in incorrectly. E.g LON:CRS is 1088p (£10.88) but the spreadsheet assumes US format and price of £1,088. The cells are also formatted to $. Can someone advise on the commands to change the format to £ if the ticker contains “LON” along with altering the decimal point to adjust?

If any one has any nice google sheets for their portfolio and/or DD that they wouldn’t mind sharing a template, it would also be great!

3 Likes
#2

Assuming you have a symbol in cell A2, then

  1. Get exchange rate using =GOOGLEFINANCE(“CURRENCY:USDGBP”) and store in some cell, for example C1
  2. Get currency for your stock using =GOOGLEFINANCE(A2, "currency") and store it in B2
  3. And then =GOOGLEFINANCE(A2)*IF(B2="USD", $C$1, IF(B2="GBX", 0.01, 1)) in whichever cell you want your price in GBP to be.

You can do it all in one formula in one cell. But I think it is faster if you reduce number of calls to GOOGLEFINANCE, hence using cells to store intermediate.

6 Likes
#3

Hi @nickab

I’m having great difficulty getting these formulas into the into existing document, prpbably because I don’t know how to insert formulas into the correct ()'s . Would you be able to shed some light on this or even update one row in the google sheets doc and share it, if you had time. I know these things take seconds when you actually know what you’re doing, however I don’t :cry:

Can I pm you a link to a Test Google Spreadsheet to add the formulas?

Cheers
Kelv

#4

Try Add formulas & functions - Computer - Docs Editors Help first.
If it doesn’t work, PM me a link.

Notes:

  • You might need to add new columns and change cell references that I used, like A2, B2, C1, based on where the data is in your spreadsheet.
  • $C$1 – is fixed/absolute reference to C1 cell, i.e. when you copy your formula to the next row it will still reference C1 (where in my example, we have currency exchange rate).
#5

Hi Nick,

Thanks for taking the time to look over this.

I think the issue myself and the OP are having is that we would like the UK stocks to feature in the spreadsheet in their native GBP as what’s happening currently is it’s showing false values in Column E with the way it shows the incorrect decimal point placing because it’s using the Amercian format.

We don’t want to actually convert US Dollar stocks such as AAPL to £’s. - CBX, CCL and RMG are all £ UK stocks.

I guess this is correct formula that you’ve put on the sheet, *And then =GOOGLEFINANCE(A2)IF(B2=“USD”, $C$1, IF(B2=“GBPX”, 0.01, 1)) in whichever cell you want your price in GBP to be. but I don’t know where to put it in to the existing formula in column E.

Ta

K

#6

Decimal point is correct if you take into account stock’s “currency”. Some of the UK stock is in pence sterling (GBX), not pounds sterling (GBP). If you want all UK stocks to be in GBP, the general solution would be something like (assuming A1 is ticker):

=GOOGLEFINANCE(A1, "price")/IF(GOOGLEFINANCE(A1, "currency")="GBX", 100, 1)

Specifically for your sheet, (assuming row 7) you would replace formula in cell I7 from

=IF(ISBLANK(A7),,GOOGLEFINANCE(A7,"price"))

to

=IF(ISBLANK(A7),,GOOGLEFINANCE(A7, "price")/IF(GOOGLEFINANCE(A7, "currency")="GBX", 100, 1))

This will normalise any GBX stock prices to GBP. I’d also suggest replacing number formatting, currently it uses dollar sign, which will be confusing if you want to keep stocks in their native currency. Format -> Number -> Financial or custom number format to increase number of decimal places.

1 Like
#7

There was a typo in my initial post. It should be GBX, not GBPX.

#8

Ah, I was unaware what GBX was, so that’s making sense now.

Appreciate this very much, Thanks