Google Sheets For Global Stocks (Version 3)

Thinking Points

  • Quick update on my trip to Fukuoka.
  • New and improved Google Sheet for Global Stocks!
  • With the functions explained in this article, you can import various data from various sites.

Japan Trip Update

Before I get started with today’s article, here is a quick update for anybody interested in hearing about my trip to Fukuoka. We (our CEO, executive chef, and I) stayed 2 nights each in Fukuoka city and Kitakyushu city. None of the companies we visited are traded publicly, but all of them have a rich history. For example, we met the 5th generation family CEO of a fish cake factory with over 130 years of history. What was once a small town factory now operates 5 plants in 3 countries!

The main event, however,  was definitely the sushi robot manufacturer. I’m not entirely sure why sushi robots aren’t more common in the US. After watching these machines operate, it makes little sense to hire a sushi chef at a restaurant, especially from a cost-performance perspective.

Thanks to our executive chef, who is close friends with the higher ups in the sushi machine company, we were able to see machines that were still under development! I can’t discuss what the machines do, but let’s just say I saw a Yaskawa robotic arm and Keyence parts (probably machine vision?) for the first time. In fact, there were a couple Yaskawa and sushi robot co employees tinkering with the machine when we visited.

That’s my Japan update. Our next stop is Vietnam!

Overview of Google Sheets for global stocks

Now let’s get on with today’s topic – Google Sheets for global stocks. The formulas for version 1 & 2 (which can be found here and here) were published over a year ago and no longer work. I think Bloomberg changed their website up a bit. In any case, I received emails asking about the two old and broken spreadsheets, so I decided to create a third. Folks who have access to the Bloomberg Terminal or similar software should probably skip reading this.

Before I get started, I’d like to throw it out there that I am not a programmer. One thing I’ve figured out is that the function used to retrieve information from various sites into Google Sheets (ImportXML) is known to malfunction regularly for unknown reasons. Don’t freak out!

The new Google Sheet

First, it’ll be helpful in understanding how the spreadsheet works if you skim through version 1 & version 2. The key component to retrieving information from other sites into a Google Sheet is the ImportXML function. This is a highly versatile function which can retrieve just about any type of information available on any website. Today, we’ll be retrieving information from Bloomberg.

Create your own copy

Here is a link to the spreadsheet. The file is set for viewing only. If you want to play with it, you’ll have to save your own copy first. I believe this requires you to have a Google account.

To give you an idea, the blue cells contain calculations and orange cells require user input. The current spreadsheet only retrieves stock price data from Bloomberg.

Columns D through G are hidden in the image above. If you highlight columns C and H, then right click, you can unhide columns D through G. In the table, column E contains the formula that retrieves pricing data from Bloomberg:

The formula above is from cell E5. If you look closely, the first set of quotes inside the ImportXML function is a URL. There are cell references to B5 (Ticker) and C5 (Market Code). This is due to how Bloomberg’s index works.

Let’s take the first company, JP-Holdings Inc, as an example. The ticker symbol is 2749 and the company trades in Japan. The Bloomberg page URL for JP-Holdings looks like this:

Hence, cell B5 contains “2749” and cell C5 contains “JP” in the Google Sheet.

Believe it or not, we are already done with the first half of the ImportXML function. Next we will focus on the second half:

This part is a little more difficult to figure out, but I’ll save you some time!

The simple, not-entirely-accurate, non-technical explanation is this: When we look at stock prices on Bloomberg, that variable is named “priceText__1853e8a5”. How do we know this? Let’s start by right-clicking on the stock price:

I use Google Chrome as my primary browser. With Chrome, if we right click the stock price for JP-Holdings, we have the option to “Inspect”. This tells us where the highlighted field is (316.00 in the image above) in the website.

Once we click “Inspect”, a new window opens and the field is neatly highlighted (see above).

Essentially, the ImportXML function tells Google Sheets to go to the specified URL, then import a specific variable (which in this case is the stock price).

** If you look one line below the highlighted line in the above image, you’ll see the variable used in cell D5 to retrieve currency information (“JPY” or Japanese Yen).

Advanced (get your cup of coffee ready)

I’m sure some of you would like to import other information, like P/E, P/B, etc, from Bloomberg. This requires more technical “umph” as Bloomberg delivers this data in tabular form under one variable name. Since the last sentence probably made little sense, I’ll show you what I mean.

If we highlight the P/E ratio on Bloomberg, right-click, and inspect just like we did before, here is what we get:

Great! The field for P/E ratio is “fieldValue__2d582aa7”! So we enter the following formula, again, just like before:

And Google Sheets returns the following data (inside red rectangle):

One small problem: Google Sheets gave us 12 rows of data. If we take another look at the Bloomberg site, we can see that all of this data came from one table:

I am not sure if there is another way to retrieve the data we want (i.e., P/E Ratio), but here is how I did it.

As far as I can tell, every stock has the “Key Statistics” table on Bloomberg and they are all ordered the same way. Instead of trying to retrieve only the specific data point from Bloomberg, I created a formula to import all 12 rows, then filter down the results to only the row(s) I want:

The key here is the query function. Notice the “select * limit 1” chunk at the end of the formula. For those who have some experience with SQL code, this should look familiar. Basically what the code says is to limit the result to 1 row – so we only get the first row, which happens to be the P/E Ratio.

Unfortunately, if we want to get the P/B Ratio as well (row 5), it’s not as simple as rewriting “select * limit 1” to “select * limit 5”. This would just retrieve the first 5 rows of data. Instead, what we do is use the OFFSET function:

Let’s focus on the last chunk of code “select * limit 1 offset 4”. This tells Google Sheets that we only want 1 row, but instead of simply retrieving the first row, we would like to go down 4 rows and get that data:

As a reference, you can get various statistics off of the “Key Statistics” table on Bloomberg’s site by changing the number after “offset”:

Offset 4 gets us P/B Ratio, 5 is P/S Ratio, and so on.

If you scroll down further on Bloomberg’s site, they’ll give you simple income statement, balance sheet, and cash flow statement items:

Applying the query and ImportXML functions in a similar fashion as we did with P/E and P/B ratios, you’ll be able to retrieve this data into Google Sheets as well.

Quick note

The Google Sheets formula frequently returns “#N/A” or shows “Loading…” indefinitely. It’s useful, but I wouldn’t depend on it. Also, if you use too many ImportXML functions in one sheet, it can slow things down quite a bit.

Did you enjoy the article? If so, please consider following/liking Kenkyo Investing on Twitter, LinkedIn, and Facebook below!

If you would like to receive Japanese small & mid cap ideas, check out the Kenkyo Japan newsletter!

Author: Clayton Young

Hi! I'm Clayton. My value investing journey began in 2012 during my college days. It was not until recently (2016!) that I decided to leverage my Japanese language skills to research Japanese equities. I hope to provide valuable insight on Japanese companies to the English-speaking world through this blog!

14 thoughts on “Google Sheets For Global Stocks (Version 3)”

  1. Thanks for sharing this, I find Google sheets really useful.

    One thing I would like to be able to do, which I haven’t managed to as yet, is have alerts of some kind triggered from a Google sheet without opening it. The alert could be triggered by a cell matching a certain value (e.g. true), but that cell could link in to Googlefinance data or the ImportXML function.

    For example, I could have a formula which showed true if a share price was below a certain value, then it would send an alert.

    I suspect this is possible by running a script in the sheet, have tried to play around with this, but not successfully yet.

    1. Hi Alan, thanks for the comment!

      I don’t think any of the Google Sheet functions update unless you have the file open. Also, the ImportXML would probably run smoother if you can figure out a way to build it into your script along with the alerts.

      Frankly, I haven’t been able to spend much time coding so it’s more like a hobby now. I’d be curious to know if you get your script working! 🙂

      Cheers,
      Clay

        1. Wow, I’ve never heard of Twilio! Thanks for sharing Alan. I’ve had trouble retrieving stock prices for Japanese companies using the Google Finance function. Maybe some combination of Twilio with ImportXML & Query functions would allow for more sophisticated text & email alerts 🙂 I’d be curious to figure out how to get ImportXML and Query running on a script.

          1. I think the script generates the alert based on comparing the number in column B to those in C & D i.e. I don’t think the script is bothered where the price in column B comes from. Therefore, if you put your ImportXML function in column B, and return a number, I suspect it may work.

            If you want to test / use without Twilio (which is what I’ve done so far), then you can use email like this to send the “sms” in an email (part of the “main” function):
            if (sms.length > 0) {
            MailApp.sendEmail(‘xx@xx.com’, ‘Stock alerts’, sms)

    1. Hi Baani,

      明けましておめでとうございます。

      This is neat! Thanks for sharing 🙂 I like the keywords associated with each company. I’ve been looking into developing something similar, but with a value twist. I have nowhere near the technical aptitude needed (yet). Something like this site, combined with research, and metrics generally useful for value folks (like EV/EBIT, Greenblatt ROIC, ex-cash ROE) would be golden.

      How did you come across this site? Have you looked at Shared Research before?

      Thanks again!
      Clay

      1. Thanks Clay. I came across ConceptsEngine while looking for fundamental data on Japanese equities. It used to be called Market Newsline and then one day that disappeared. It took a while before I found them again 🙂 I was looking for something that had a Python API, but it looks like this does not.

        Quandl.com has data, but fundamentals are generally through paid subscriptions. It is still cheaper than other options, though. On Quandl, please see Mergent Global Fundamentals Data and Robur Global Select Stock Fundamentals if you like. I don’t think they cover the entire Japan market though.

        I have seen Shared Research before but I think it is effectively sell-side / sponsored research which makes me wary, but I have not done enough work yet to be sure.

        Good luck with your development plans!!

        1. Thanks Baani!

          I’ve never used Quandl before, I’ll have to take a look sometime.

          Shared Research is sponsored research, but I think it provides decent coverage in English and Japanese.

          It seems to me like many of these data services broadly cover various markets. I’m hoping I can eventually provide in-depth coverage specifically for the Japanese market 🙂

          Cheers,
          Clay

  2. Hey Clayton, Baani, Alan, I am looking for webistes with fundamental metrics such as (DCF per share, FCF per share, Earning yield etc.). I know, there are great services such as Bloomberg Terminal, Reuters, GuruFocus but they are quite expensive do you know how to get data from annual reports of any companies in the world and update them yearly (what I mean by any company in the world, is companies in New Zeland, Australia, Japan, Singapore, Korea, China, Malysia, South Africa, USA, Canada, Brazil, Peru, Chile, Poland, France, Germany and other countries where are listed securities on stock exchanges I didn’t mention)?

    1. Hi Tomasz,

      I don’t know of a single resource for the kind of coverage you want. You could subscribe to individual data feeds from data providers. Or you could extract from places like Google Finance.

Leave a Reply

Your email address will not be published. Required fields are marked *