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.
