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.

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=""> <strike> <strong>

Post Navigation