FFP released their semi-annual results last Friday which prompted me to update my Google Drive spreadsheet that tracks the NAV of the company in real-time. The spreadsheet is publicly accessible using this link and currently looks as follows:
In the past months NAV/share has been going up while the discount has been getting a bit smaller, so that’s obviously good news for me, but that is not what I want to talk about. What I want to focus on is on how you can create a spreadsheet like this with real time prices, and specifically how to incorporate price data when the standard =GoogleFinance() function is unavailable. This is a recurring issue when you use Google Drive because foreign exchanges are often not supported. The FFP spreadsheet ran into this problem for the first time because FFP now owns Peugeot warrants and it has made an investment in a Mauritius listed stock.
The easy way
Step one in adding a quote that is not supported by Google Drive is finding a webpage that does have a quote. Bloomberg.com or ft.com are often a good source for foreign stocks. With the =ImportHTML() function it easy to grab data from a webpage. It is limited since it can only get stuff that is presented in a table or a list on the webpage, but luckily Bloomberg does have some relevant stuff in a table. To grab for example the latest price for Fujimak (A Japanese company I own) you can use the following command:
=ImportHTML("http://www.bloomberg.com/quote/5965:JP", "table", 1)
The first argument specifies on what web page the data can be found, the second argument specifies if you want to grab it from a list or a table and the last argument specifies the number of the table or list (there are often a couple of different tables on one page). Because Bloomberg doesn’t have a quote for the Peugeot warrants nor for the CIEL group on its website we have to take a slightly more complicated path.
The hard way: CIEL Group
If you want to be able to access everything you can use the =ImportXML() function. It takes two arguments: an URL and a XPath query. The XPath language can be used to select nodes in a HTML document (plenty of tutorials available online), but we don’t need a lot of fancy stuff to find a quote. You could use "\\div" as a XPath and you would basically get the whole HTML page dumped in your spreadsheet. This isn’t a very neat solution because there is a high risk that something will change in the page and that the cell that contains the latest price is suddenly moved: breaking the spreadsheet. The better solution is to specifically search for the HTML element that contains the price.
The latest price of the CIEL group can be found here on the site of the Mauritius stock exchange. Right-click in Google Chrome on the price and select “inspect element” and you will see a bunch of HTML code with one highlighted line, a div with the id “general_price_details”:
We can now select this specific element with the following XPath query:
=ImportXML(B19, "//div[@id='general_price_details']")
Getting the warrant price
The price for the Peugeot warrants can be found on the site of the Euronext stock exchange. Here things are made even a bit more complex because the website is designed in such a way that the price is loaded asynchronously with the main page. We can once again use the Google Chrome development tools to figure out where the data is coming from.
Right-click on any page element and select “inspect element”. Select the “Network” tab and refresh the page by pressing F5. You will see a list of all kinds of files that are loaded by your browser when the page is rendered. Most of the files are small images, scripts and style sheets, but there is also a small HTML page that contains the price data. We need to copy the URL of this page to Google Drive for our =ImportXML() function:
We can now import the price in Google Drive using this command (B3 refers to the URL):
=ImportXML(B3, "//div[@class='quote with-label']")
There remains one tricky issue that needs to be resolved. Google tries to automatically convert the price data to a number, but the Euronext website uses a comma as a decimal mark while Google drive expects a point. When the price is for example €1,641 Google drive translates this to €1641 (and it is easily fixed by a division by one thousand). If the price is €1,64 Drive doesn’t recognize a number, and we need to remove the €-sign and convert the comma to a point our self. This can be done with the following command:
=VALUE(RIGHT(REGEXREPLACE(D3, ",", "."), 5))
Hopefully this was educational!
Disclosure
Author is long FFP
This is terrific information. Thank you
Good to hear
Wow this is really great. I’ve thought about doing this a few times but didn’t want to wade through a million forums looking for answers to all the little traps.
Cool stuff!
Helps to have a computer science background 🙂
alphavulture,
Thanks for addressing this topic. I have been looking for a way to get Japanese company quotes into my google spreadsheet.
However, I am having a problem getting your example to work. When I input:
=ImportHTML(“http://www.bloomberg.com/quote/5965:JP”, “table”, 1)
I end up getting this:
Open: 834 Day’s Range: 830.0 – 834.0 Volume: 600
Previous Close: 834 52wk Range: 690.0 – 957.0 1-Yr Rtn: +19.14%
I am trying to get just the latest stock price in the cell. I am not sure what I am doing wrong.
As an aside, here is what I use to get latest currency price:
=substitute(INDEX( ImportHtml(“http://finance.yahoo.com/q?s=JPYUSD=X”, “table”,2),1,2),”*”,””)
It seems to work well.
Sincerely,
Hugh
You are getting what you are supposed to get. I usually put stuff like this in a separate sheet (like I have done with the FPP spreadsheet) and then refer to it from the main sheet. Alternatively you can use the index() function to directly grab the right item from the list.
You can get a currency price a lot easier. Use: =GoogleFinance(“CURRENCY:JPYUSD”)
Thanks for your examples. They are working in my sheets. However, I would like to tailor some of the data coming from Bloomberg and cannot figure out the code. For instance, I would like to get foreign stock price and in one column and the %change in the next column. I’m sure that there must be a way to capture the data, but I cannot figure that part out. Any tips??
Thanks
Alphavulture,
Thanks for clearing that up for me. And thanks for the google finance currency quote code. I do not understand programming, so this information has helped me.
Also, here are the three Japanese companies that I own:
JP:4705 Clip corp, negative enterprise value, roic > 50%, for profit cram school
JP:3800 Bsp inc, EV/ebit 50%, I.T. company hardware/software
JP:4333 Toho system science, EV/ebit 50%, I.T. company, bank and insurance financial software.
I am not sure if these numbers are correct, I may have made mistakes. I also am not sure if they are possible value traps/investment mistakes. Other investors that I have spoken with feel that they may not have an investment moat. However, I continue to own them. I am not sure if you have come across them, so I figured that I would mention them here.
Thanks again for all the great articles!
Sincerely,
Hugh
Not familiar with the Japanese companies you list, but would be a bit cautious with the valuation metrics you use (see also my post on my Japanese stocks: http://alphavulture.com/2014/08/08/bought-two-stocks-in-japan/). High EV/EBIT or ROIC ratio’s/numbers are not very meaningful when you look at a very cash rich companies. You don’t want that the operating business is just a small piece of the pie.
Thanks for responding. I appreciate hearing your thoughts about this topic.
Sincerely,
Hugh
Thank you very much for sharing this information. I dabbled with Google Drive once, but couldn’t figure out how to do international stocks. Thanks again!
You’re welcome 🙂
Do you have any idea if such a sheet (ie the formulas grabbing quotes from websites) is possible with the Outlook-pendant of GoogleDocs?
Don’t know, never used it.
Thanks for a great post! I struggled a bit to get it working though. After a while I realized that I have to use “;” instead of “,” as a separating character in the formulas. This might be related to me using the google docs in Swedish and not in English. Just wanted to leave this comment if someone else is facing the same issue.
I guess that’s indeed the issue
Hi
Really good information.
Do you know how to just get the stock price data/ PB ratio from bloomberg using ImportXML()?
Thanks
Price from bloomberg:
=ImportXML("http://www.bloomberg.com/quote/7399:JP","//span[@class=' price']")P/B from bloomberg (and all their other key stats):
=ImportXML("http://www.bloomberg.com/quote/7399:JP","//td[@class='company_stat']")But you can just as easily use ImportHTML for this since all this data is in a table
Hi,
please update the Bloomberg price query examples, those are not working anymore. Thanks.
=ImportXML(“http://www.bloomberg.com/quote/OIH:US”,”//div[@class=’price’]”)
Where OIH is the ticker you are looking at should work.
Any ideas on importing bond prices from Finra?
For example, http://finra-markets.morningstar.com/BondCenter/BondDetail.jsp?ticker=C635761&symbol=KSS4268840
That’s a difficult one, because the data you see on the page is loaded asynchronously with Javascript. The data you are looking for can be found here: http://quotes.morningstar.com/bondq/quote/c-banner?&t=C635761®ion=usa&culture=en-US&productcode=QS&refresh=true But will require some work to extract the price from it.