Summary
- This post covers the updated Google Sheet for Global Stocks, the original post can be found here.
- The updated sheet provides the ability to have a “base currency value” for global stocks.
- Enjoy! (version 2)
Thanks to….
First of all, I would like to thank Redditors Zeltergiset and Racemize for providing improvement suggestions on our Google Sheet for Global Stocks on the Security Analysis subreddit. In case you prefer to use the original version, I have left the original copy untouched. The original post and the original copy of the Google Sheet can be found here.
The new sheet can be found here.
Key Differences
Currency fix:
The original sheet used the market code to determine the currency of a ticker. Zeltergiset gave an example of a US Dollar denominated ticker on the Hong Kong market (Ticker: 2821). With the original sheet, this would show the currency in Hong Kong Dollars, which is incorrect. I incorporated Zeltergiset’s proposed solution and pulled the ticker-specific currency data from Bloomberg in the new sheet.
Base Currency Values:
The original sheet only went as far as importing prices on tickers in various global markets into one sheet. Zeltergiset suggested that we can pull exchange rate information and convert the various currencies from global tickers into one common currency. Racemize added to Zeltergiset’s suggestion and brought to our attention that Google Sheets has the functionality to provide currency conversions directly. I added another tab called “Exchange Rates” to this file in order to accommodate the Base Currency Value functionality.
How to use Google Sheet for Global Stocks (version 2)
I believe the easiest way to explain how to use the updated sheet is to actually use it – we will add a ticker and a currency in this post. I would like to note here that this post will probably be easier to understand if you read the original post first. In the last post, we added Korea’s Samsung Electronics to our spreadsheet. This time, we will add Germany’s Volkswagen AG. Here is a screenshot of their ticker on Bloomberg:
Screenshot 1
The ticker for Volkswagen AG here is VOW and the market code on Bloomberg is GR. This ticker is traded in the Euro currency.
First, here is a screenshot of our new spreadsheet before we start adding Volkswagen AG:
Screenshot 2
If you recall from the original post, the blue cells are calculated (formulas) and the tan cells are user inputs (manual entry). Let’s add the ticker and market code for Volkswagen AG and copy/paste the blue cells into row 11:
Screenshot 3
We generated errors in the F, G, and H column on row 11. This is because we have not added the Euro currency to our list of currencies yet. So let’s add the Euro currency. To do this, we go to the “Exchange Rates” tab:
Screenshot 4
So far, the Bloomberg and Google currency codes have been the same. I separated them into two different columns just in case there is a discrepancy somewhere down the road. Since the tan cells are manual entry cells, let’s go get the information to put into these cells:
BloombergCurrency: This can be found in column D of the “Global Tickers” tab (see Screenshot 3).
GoogleCurrency: This can be found in Google’s list of currency codes here. Run a quick conversion and this will reveal what the currency code is:
Screenshot 5
This tells us that the currency code on Google Finance for the Euro is “EUR”.
Symbol: This is a quick Google search for “Euro currency symbol”:
Screenshot 6
After making all the manual entries, our sheet should look something like this:
Screenshot 7
The “ExchRate” column pulls the base currency figure from the “Base Currency” cell (cell B2) found in the “Global Tickers” tab. It is currently set to the British Pound (refer to Screenshot 3). Here is what our “Global Tickers” tab should look like now that we are done adding information about the Euro to the “Exchange Rates” tab:
Screenshot 8
Yay to our British friends! Now, if our American friends want to set the base currency to the US Dollar, all we have to do is change cell B2 to “USD”:
Screenshot 9
In Closing:
I hope some of you guys found the updated sheet useful. If you have any questions (reemphasize: I am not an IT guy….) please send me an email or drop a line in the comments. Thanks again to Redditors Zeltergiset and Racemize for making suggestions in the Security Analysis subreddit!
