Summary
- Introduction to a Google Sheet file that can pull stock price information on global tickers.
- Description on how to use or modify the file to suit your needs.
- Enjoy!
Update 11/4/2016: There is a newer version of this post and spreadsheet which can be found here. It will help to read this post before moving on to the newer one though!
I recently created a Google Sheet file that can pull global stock price information from the Bloomberg website. It is far from perfect, but it is probably what those struggling with the google finance functions have been wanting. You can find this spreadsheet by clicking here. Here is a snapshot of the spreadsheet:
Screenshot 1
If you can’t already tell, I like color-coding my spreadsheets. The blue cells represent calculated cells (probably best to avoid messing with these). The cells in tan represent raw input. These are the cells that the user needs to type in – and they often feed information to the blue cells.
Disclaimer:
Before I go into describing the spreadsheet, I want to make it clear that I am not a techie. This spreadsheet took a bit of head-banging and hair-pulling to build (by the way, my head is okay). I probably won’t be much help for modifying the spreadsheet to tailor your needs. However, I will explain how I built it and hope that my explanation can help you modify the spreadsheet to suit your needs.
It’s all about Bloomberg
I read about this cool “ImportXML” Google Sheet function, which basically lets you import data from other websites. This Google Sheet file imports its data from Bloomberg.
To begin, here’s what a quote on Daihatsu Diesel (TYO: 6023) looks like on Bloomberg:
Screenshot 2
If you look at where the blue arrow is pointing, you’ll see “6023:JP”. 6023 is Daihatsu Diesel’s ticker and JP tells us that this ticker trades on the Japanese market. This is important because these are the two key data points we use to pull pricing information into our spreadsheet.
Here’s a screenshot that shows the formula that pulls price information:
Screenshot 3
So far, I’ve messed with US, UK, and Japanese equities on this spreadsheet. The market codes are US, LN, and JP. In a nutshell, the formula above is telling the spreadsheet to lookup ticker 2749 under the JP market code. If you want to add tickers from different countries/markets, you’ll have to look up the ticker on Bloomberg and take note of the ticker and market code information (refer to Screenshot 2).
Let’s add Korea’s Samsung Electronics to the spreadsheet!
If we lookup Samsung Electronics’ ticker symbol in the Korean market on Bloomberg, here is what we will see:
Screenshot 4
The ticker symbol is 005930 and the market code is KS. One thing about spreadsheets is that when there are preceding zeroes (like the “00” before the “5930” in Samsung’s ticker), they tend to get eliminated. Samsung’s ticker will show up as “5930” instead of “005930”, which will generate an error. To solve this, we need to use an apostrophe before the ticker number (“ ‘005930 ”) so the ticker will be seen as a text and not a number, which will allow the preceding zeroes to keep its place.
Screenshot 5
Currencies!
The pricing information we pull from Bloomberg does not reveal the currency used. In the “Current Price” column on our spreadsheet, I crafted an array formula that translates the pricing information into the respective currency. Here is a screenshot of the array formula:
Screenshot 6
I would like you to take a look at the section of the formula that reads “ {“US”, “$”; ….”. This section basically tells the spreadsheet “if the C column shows ‘US’ then use a US Dollar symbol”.
Now, the current array formula only accommodates the US Dollar, British Pound, and Japanese Yen. Since we added Samsung Electronics, let’s throw the Korean Won into our mix. Here’s how we do it.
After this chunk of code in the array formula:
”JP”,”¥”
We add this new chunk of code:
; “KS”, “₩”
The end formula should look like this (and we now have a quote for Samsung Electronics, straight from the Korean market!):
Screenshot 7
In Closing
I hope some of you guys found this spreadsheet useful. While I am not a particularly tech-savvy guy, I’m always looking for ways to improve things. If you have any suggestions for improvements, please send me an email or drop a line in the comments!
