Retrieve Bloomberg Data in Google Docs
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.
What it Does
- 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).
- 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.
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.