About my MSFT to GBP script

N.B. This is an edited version of an article originally written for my MSDN blog in December 2009

TL; DR;

1. I made a Google Spreadsheet at https://spreadsheets.google.com/ccc?key=0ApeEYUovh8d9dE04QjJnLW5CLTVrSVNtZkEzVU92NXc&hl=en that calculates the MSFT share price in UK Pounds
2. I wrote a Python script running on Google App Engine that takes the price from the spreadsheet and posts it on Twitter at @msft_in_gbp

What the script does

Although I've now officially left Microsoft I still have a few Microsoft shares, and like everyone else in the company I watch the price closely to figure out when it's a good time to sell.

However not being in the US adds an extra level of complexity to that question, as prices in USD but if I sell them they'll be converted to GBP. This means that my "true" price has to take into account the current exchange rate, which has fluctuated a lot recently with the weakness of the pound.

Now I have a little free time, I found a solution where I can automate this calculation, learnt a few new things and had some fun too.

Getting the MSFT stock price in GBP in a Google spreadsheet

Since starting my new business I've been using the standard version of Google Apps. This is a great package for small startups, getting email, calendar, Google Docs for your domain, plus the facility to associate Google App Engine applications to your domain too.

Playing about with Google Spreadsheets, I figured out I could do the following:

• Pull in the latest MSFT stock price (in $) using the simple command "=GoogleFinance("MSFT","price")'"
• Following a great article at http://www.post1.net/lowem/entry/how_to_insert_currency_exchange_rates_into_google_spreadsheets, I can get exchange rates into the spreadsheet by basically using the ImportHTML() function to parse the table data from a page on Yahoo! Finance (http://finance.yahoo.com/q?s=USDGBP=X) into an array, then referencing the cell I need that holds the latest exchange rate
• With the above information, it's trivial to calculate the latest MSFT price in pounds

You should be able to see the spreadsheet at https://spreadsheets.google.com/ccc?key=0ApeEYUovh8d9dE04QjJnLW5CLTVrSVNtZkEzVU92NXc&hl=en where I've added some annotations so you can see what's happening.

Reading the spreadsheet data from an external script

So far so good, but what I'd really like is a solution that regularly tells me the latest price, keeps a history of the changes and I can share with anyone else who's interested.

What I decided to do is to write a Python script that will run every 6 hours on Google App Engine to read the data in the spreadsheet and send a message to Twitter with the latest prices.

As you'd expect, as this is all Google code it isn't too hard to read in the data from the spreadsheet using the API code found at http://code.google.com/apis/spreadsheets/data/1.0/developers_guide_python.html. Here's the non-optimal (I'm still getting up to speed with Python!) code:

The code is pretty self-explanatory I think, but basically in the GetDataDescription() method we:

• Gets the spreadsheet as a "cells feed" from the Google Docs server - the parameters are the spreadsheet key and worksheet ID for my spreadsheet
• In the ExtractData method we iterate through the cells - clearly non-optimal but I just copied the code example from the SDK - and get the values from the known cells as properties of the object
• Format the cells into a string like "MSFT: 29.60 (USD) = 18.92 (GBP)"

Pushing the price to Twitter

To send this string to Twitter I wrote some simple code hacked from various sources:

Google App Engine lets you run your scripts at regular intervals as CRON jobs as explained at http://code.google.com/appengine/docs/python/config/cron.html. Now some of the example schedules given didn't seem to work for me, but after some trial and error I settled on running the script "every 6 hours"

You can see the result of all this at http://twitter.com/msft_in_gbp and obviously if you're one of the people who are interested in getting the MSFT share price in GBP regularly, then just follow @msft_in_gbp

Conclusions

1. Google App Engine is an excellent platform for developing on. The free quotas for usage are pretty generous for small applications like this, and the documentation, features and monitoring built-in to the platform are excellent too.
2. Being able to use Google Spreadsheets programmatically in a simple way make them really useful for scenarios like this, where building a "native" screen-scraping web app would be a lot more work.

Update: August 2015

Google changed the way you could access their spreadsheets, so I had to simplify the script to call Yahoo Finance directly to fetch the share price and exchange rate directly, and parse the JSON returned before posting to Twitter.

This now works again after a brief hiatus - now I have very few MSFT shares I didn't notice it the feed was down! - and it is (a little) less Heath Robinson if a little less fun :(

Update: May 2018

After switching to Google Finance for the stock price feed, that also stopped working! Now using a hopefully reliable feed from IEX