Reputation: 2530
The GOOGLEFINANCE function in Google Sheets allows you to lookup the price (or other attribute) of a stock or instrument.
https://support.google.com/docs/answer/3093281?hl=en
However, the documentation for which symbols it supports is sparse.
For example, this fund has a symbol "GB00B59G4Q73", and searching for this in Yahoo finance works.
But neither this nor the symbol "0P0000KSP6.L" works in Google Sheets, giving the error "When evaluating GOOGLEFINANCE, the query for the symbol: 'GB00B59G4Q73' returned no data."
How can I determine if I am using the correct symbol, or if the function does not support this symbol?
Upvotes: 6
Views: 33634
Reputation: 1986
If the ticker is on Google Finance but it doesn't show the price on Sheets, try fetching from Google Finance itself:
=IFNA(IFNA(GOOGLEFINANCE(A1&":"&B1, "price"), IMPORTXML("https://www.google.com/finance/quote/"&B1&":"&A1&"","//div[@data-last-price]/@data-last-price")),0)
This uses:
IFNA
to catch any errorsGOOGLEFINANCE
with market (A1) and ticker (B1)IMPORTXML
to get data from Google Finance's page with ticker (B1) and market (A1)//div[@data-last-price]/@data-last-price
to find a DIV element with an attribute called "data-last-price" and then get that attribute.It's not quite the same, but it helps.
Upvotes: 0
Reputation: 1
here is your "official documentation":
ticker - The ticker symbol for the security to consider.
Note: Reuters Instrument Codes are no longer supported. For example, ticker 123.TO or XYZ.AX would not work. Instead, use TSE:123 or ASX:XYZ.
Recommended: Add an exchange to avoid discrepancies. For example, use “NASDAQ:GOOG” instead of “GOOG." If an exchange is not specified, GOOGLEFINANCE will use its best judgement to choose one for you.
meaning that the best you can do is to go to https://www.google.com/finance and search for your ticker there instead of yahoo or other suppliers that are not supported
but you can always scrape it from yahoo:
=SUBSTITUTE(SPLIT(REGEXEXTRACT(QUERY(IMPORTXML(
"https://uk.finance.yahoo.com/quote/0P0000KSP6.L?p=0P0000KSP6.L&.tsrc=fin-srch", "//*"),
"select Col1
where Col1 contains 'Vanguard FTSE Dev World ex UK Equity Index AccLSE - LSE'
limit 1
offset 8", 0), "GBp(.*)"), "-"), ",", )*1
Upvotes: 9