Getting Tokyo stock price data to appear on Google spreadsheet

Question

I have no problem retrieving price data of US stocks. For example, to retrieve Apple price data, this formula =GOOGLEFINANCE("AAPL","changepct")/100 works fine. When I tried to retrieve Tokyo stocks price data, this formula GOOGLEFINANCE("TYO:1969","changepct")/100 cannot work. Both formulas look similar. I cannot figure out why. Is it because Tokyo stocks are not supported by Google Finance?

Gaudette

=GOOGLEFINANCE("TYO","changepct")/100 seems to work – Transalpine

Unfortunately, TYO is listed in the US exchange, not Tokyo. – Gaudette

support.google.com/docs/answer/3093281?hl=en-US says "GOOGLEFINANCE is only available in English and does not support most international exchanges" – Transalpine

Actually, it support most international exchanges so far except Tokyo so far from my experience. I was hoping I made some mistake. – Gaudette

it's a shame, actually. I've heard that old google spreadsheets could support tokyo stocks – Transalpine

Answer 1

Is it because Tokyo stocks are not supported by Google Finance?

Yes.

Try and the error message presently includes:

When evaluating GOOGLEFINANCE, Google Spreadsheets is not authorised to access data for exchange: 'TYO'

Pluri

Answer 2

My solution: Scrape the price from Google Finance website.

=IMPORTXML("https://www.google.com/finance/quote/1969:TYO","//div[@class='YMlKec fxKbKc']")

This will give you "¥1,570.00". If you want to convert it to a number use the below:

=VALUE(REGEXREPLACE(IMPORTXML("https://www.google.com/finance/quote/1969:TYO","//div[@class='YMlKec fxKbKc']"), "[¥]", ""))

Johniejohnna

Answer 3

pls using yahoo fiance, using sheets extension func

function yahooF(ticker) {

 const url = `https://finance.yahoo.com/quote/${ticker}?p=${ticker}`;

 const res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});

 const contentText = res.getContentText();

 const price = contentText.match(/<fin-streamer(?:.*?)active="">(\d+[,]?[\d\.]+?)<\/fin-streamer>/);  console.log(price[1]);  return price[1];

}

https://www.lido.app/tutorials/yahoo-finance-google-sheets

Destruct

Answer 4

A simple solution is to use the German stock exchanges and convert the euro rates to dollars or yen with googlefinance. So for example: DIP for KDDI

Paluas

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Helping