So, one of my friends asked me if I can help him with this google doc spreadsheet he uses to track his stock portfolio. He only have 7 stocks right now but he finds it cumbersome to update the current price one by one so he’s thinking there might be a way to update the price automagically. In that way, he can immediately see how his stocks are doing for the day. I know google docs allows some kind of scripting and in-cell commands to retrieve information from the outside world.

After a few minutes of googling, we found out that we can use importXML to retrieve the data coming from Bloomberg website and then use xPath to retrieve the price from the HTML. Easy right? Well, for beginners like me, it’s not exactly.

If it’s TLDR; for you, here’s final the code.
xpath_google_docs

=importXML("http://mobile.bloomberg.com/quote/" & A3 & ":PM","(//span[1])[1]")

What it Does

  1. It fetches the HTML of the mobile site of bloomberg. Specifically, it gets the current quote for a specific ticker. In my example, it looks for the PSEi (Philippine Stock Exchange).
  2. Using xPath, I fetch the first instance of span. I enclosed it in parenthesis to get the very first instance only.

Obviously, the xPath is very dependent in the HTML structure of the mobile site of Bloomberg. Which is exactly what slowed me down to do the changes. I know my syntax is correct but it just doesn’t work. I didn’t realize that the HTML being spewed by the mobile site is different when you use the importXML as when you look at its source via browser.

Even the xPath generated by FireBug doesn’t work. It’s only when I made the appropriate changes that it finally worked.
xpath_firebug

I’m pretty sure there are better ways of doing this. Actually, the code running on my spreadsheet is already modified to circumvent the 2hour caching by Google Docs. But for beginners like me, it’s a good start.

[strong]UPDATE May 2015[/strong]
The original post was created 22 months ago. The HTML structure of Bloomberg’s feed already changed. It simply means that the actual code sample no longer works. However, the logic is already there. Admittedly, you have to know how to define your xpath which could be a challenge for some people. In that note, I hope you understand that I no longer update the sample code.

2 Thoughts on “Retrieve Bloomberg Data in Google Docs

  1. anonymous on March 10, 2015 at 5:19 am said:

    not working anymore … can you update?

  2. Athan on April 1, 2015 at 3:00 am said:

    wasn’t able to make your code work but based on your approach I tried this:

    =importXML(“http://www.bloomberg.com/quote/AC:PM”,”/html/body/div[2]/div[1]/div[1]/div[1]/div[2]/span[1]”)

    works for me at the moment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Post Navigation