N.B. This is an edited version of an article originally written for my MSDN blog in December 2009
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.
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.
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:
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
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 :(