Summary

  1. I wrote 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 in on Twitter at @msft_in_gbp

Article

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:

 

import gdata.spreadsheet.service
import gdata.alt.appengine

class StockData():     
    def __init__(self, runningOnAppEngine):
        self.client = gdata.spreadsheet.service.SpreadsheetsService()
        
        if (runningOnAppEngine):
            gdata.alt.appengine.run_on_appengine(self.client) 
        
        self.client.email = "johnp@bravelocation.com"
        self.client.password = "XXXXXXXX"
        self.client.source = "bravelocation-msftpriceconvertor-1"
        self.client.ProgrammaticLogin() 
    
    def GetDataDescription(self): 
        spreadsheet = self.client.GetCellsFeed("0ApeEYUovh8d9dE04QjJnLW5CLTVrSVNtZkEzVU92NXc", "od6")
        self.ExtractData(spreadsheet)
        
        return "%s: %.2f (%s) = %.2f (%s)" % (self.symbol, self.originalPrice, self.fromCurrency, self.convertedPrice, self.toCurrency)

    def ExtractData(self, feed):
        for i, entry in enumerate(feed.entry):
            if (entry.title.text == 'B2'):
                self.symbol = entry.content.text
            elif (entry.title.text == 'B3'):
                self.fromCurrency = entry.content.text
            elif (entry.title.text == 'B4'):
                self.toCurrency = entry.content.text
            elif (entry.title.text == 'B6'):
                self.originalPrice = float(entry.content.text)
            elif (entry.title.text == 'B7'):
                self.exchangeRate = float(entry.content.text)
            elif (entry.title.text == 'B8'):
                self.convertedPrice = float(entry.content.text)

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)”

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

import urllib
import urllib2
import base64

class SimpleTwitterUpdate():
   
    def __init__(self, userName, passWord):
        self.username = userName
        self.password = passWord
        self.twitterUrl = 'http://twitter.com/statuses/update.xml'
   
    def PostMessage(self, message):
        values = {'status' : message}
               
        base64string = base64.encodestring('%s:%s' % (self.username, self.password))[:-1]
        headers = {'Authorization': "Basic %s" % base64string} 

        data = urllib.urlencode(values)
        
        req = urllib2.Request(self.twitterUrl, data, headers)
        response = urllib2.urlopen(req)
                
        return response.read()

 

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

 

If you want to reuse any of this code, feel free to drop me a line at johnp@bravelocation.com 

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. Not sure how much I’m liking Python so far, but it may well just be teething problems with the unfamiliar (to me) syntax.

3. 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.