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:
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
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!
Author is long FFP