Mobile Apps and Web Solutions

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:

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:

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 :(