Part 1-The Spreadsheet Past

To reiterate our mission from 'The spreadsheet is dead long live the spreadsheet ' our task is to solve the service capture conumdrum for small businesses and enumerate it in a manner suitable for our invoicing/account department to be able to handle, that is it must end up spreadsheet.

First we should confine ourselves to just the essential and build out and up from there in a agile fashion, the key here is quick and dirty lets get something we can use and then refine it. So lab coats on here are the initial constraints (we like constraints, particularly early on) :

1. To keep it simple we just want to capture date, time spent and a description (the simpler the more likely they will use it).
2. We will code the first hack in Groovy, because its great for hacking and has all that Java can offer on the backend which we may require later.
3. The results must end up in a recognisable spreadsheet, lets use a web based spreadsheet with a simple time and cost summary.

Right lets get to work, first of all after a little thought I figured that the easiest tool to use to record the basic data in constraint (1) would be a calendar. Obviously it needs to be open and accessible from our tools in (2) and given this is the Folk labs it should of course be web based. Given I will be trying it here at work we will use our Calendar tool of choice GCal (Google Calendar) which we use as part of GApps.

Thus from an operational standpoint we expect participants in any given project, service or function to enter tasks directly into their calendar as they perform them (not plan them!!). Hence if I have just finished doing a couple of hours work on a project I enter that into my calendar. We have a choice here to setup shared project/client based calendars as we wish to enumerate them at the other end. For simplicity here we will just use a single shared calendar called chargeable that all participants have write access to.

We could use GCal Java libraries to get excellent access to our GCal data, but unfortunately on the system we are working on OSX 10.3.9 we only have Java 1.4.2 and the GCAL API requires Java 1.5 doh!! damm Apple.. I am not going to upgrade this Power Book to Tiger just for this labs mission, maybe later. A bit of hacking around reveals that there is an Atom feed for each calendar (under the private XML icon within the calendars publishing settings), unfortunately the date/time data is encoded into the contents of the entry items. Further hacking reveals that if 'full' is appended to the end of the URL one gets the complete atom feed for the calendar including Googles GData schema'd namespace entries, these hold the date and time info we require.

Ok so now we just have to hack that to produce a csv (Comma Seperated Version) of the data :

1    import groovy.util.XmlSlurper 
2    import java.text.SimpleDateFormat 
3     
4    // For decoding the Date/Time info in GCals atom feed 
5    def df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS") 
6    // For formating the date output into our CSV file 
7    def sf = new SimpleDateFormat("yyyy-MM-dd' 'HH:mm:ss") 
8    // To convert the millisecond to hours (our time measurement) 
9    def toHours =   1000 * 60 *60 
10   // A nice hourly rate 
11   rate = 45 
12   // Our chargeable calendar feed url 
13   def feedUrl = "http://www.google.com/calendar/feeds/somecode@group.calendar.google.com/private-someothercode/full" 
14   // This is a groovy way of reading XML files like our feed 
15   def slurper = new XmlSlurper(); 
16   // Account for googles proprietary atom namespace extensions 
17   def feed = slurper.parse(feedUrl).declareNamespace(gd:'http://schemas.google.com/g/2005'); 
18   // Process the feed 
19   feed.entry.each(){ // step through once for each item in the feed 
20       // Grab the startdate/time of the task 
21       // Notice the splice hack to remove the incompatable time zone info 
22       def start = df.parse(it."gd:when"."@startTime".text()[0..-4]) 
23       // Grab the enddate/time of the task 
24       def end = df.parse(it."gd:when"."@endTime".text()[0..-4]) 
25       // Calculate time spent = time finished - time started 
26       def dif = (end.getTime() - start.getTime()) / toHours 
27       // Calculate charge based on time * rate 
28       def cost = dif * rate 
29       // print it out to the console in comma seperated format (not forgetting to enclose the title in speech marks) 
30       println sf.format(start)  + "," +  sf.format(end)  + "," + "\"" + it.title.text() + "\"" +  "," + dif   + "," + cost 
31   } 
32   // Done !!

Thats the code, here is the output :

2006-11-06 10:00:00,2006-11-06 14:00:00,"ClientA,Optimising query and unioning evidence functions",4,180
2006-10-30 10:00:00,2006-10-30 14:00:00,"ClientA - eveidence report work",4,180
2006-10-24 12:00:00,2006-10-24 13:00:00,"ClientB solving timeout issues, by adjusting session timeout,solving navbar height issues, couple of other minor css issues on phone",1,45
2006-10-23 10:00:00,2006-10-23 14:00:00,"ClientA Evidence Report Queries",4,180
2006-10-20 15:45:00,2006-10-20 16:15:00,"ClientB updating blog/news CSS, solved issues with UL spacings",0.5,22.5
2006-10-20 12:30:00,2006-10-20 14:30:00,"ClientB adding secure version of Vacancies all and by category into the associate secure section",2,90
2006-10-19 15:00:00,2006-10-19 15:30:00,"ClientB fixing issues with news and secure section",0.5,22.5
2006-10-18 11:30:00,2006-10-18 12:30:00,"ClientB changed news template , updated nav bar tpo new scheme, added summarize closure to grab first para",1,45
2006-10-17 09:00:00,2006-10-17 09:30:00,"ClientB Ecto support for Claire, issues with line spacing first para summaries etc.. and preprocessing settings",0.5,22.5
2006-10-16 09:55:00,2006-10-16 13:55:00,"ClientA various",4,180
2006-10-13 16:00:00,2006-10-13 17:00:00,"330pm to ClientB Fixing issues with rogue characters and markup in Ecto",1,45
2006-10-13 11:00:00,2006-10-13 11:30:00,"ClientB helping claire configure and correct Ecto",0.5,22.5
2006-10-12 16:30:00,2006-10-12 17:00:00,"ClientB updating links top new news area",0.5,22.5
2006-10-12 14:30:00,2006-10-12 15:00:00,"ClientB Minor changes to blog archive view to just show older items",0.5,22.5
2006-10-12 10:15:00,2006-10-12 10:45:00,"ClientB fxing Css issues with multiple classes",0.5,22.5
2006-10-11 11:00:00,2006-10-11 15:00:00,"ClientB moved blog over to new system associate secure and remade template",4,180
2006-10-09 16:30:00,2006-10-09 17:30:00,"Fixing multiple CSS para class issues",1,45
2006-10-09 09:45:00,2006-10-09 13:45:00,"ClientA",4,180
2006-10-05 14:00:00,2006-10-05 15:00:00,"ClientB hcanging Job category and all configurations",1,45
2006-10-02 09:30:00,2006-10-02 13:30:00,"ClientA",4,180

Now lets just copy and paste that output into a text file say timesheet.csv and upload it to Google spreadhseets, go to Docs & Spreadsheets (Nice novel name) and choose upload, and give it a name ('TimeSheet' ?) and click upload file.

GSH (Google SpreadSheets) will now create a new spreadsheet by that name 'Timesheet'. The browser will open this new sheet for you, the first thing to do is adjust the column width's so it's readable.

Then we will probably want to put some heading in and maybe add some formatting etc.. like hours/costs. Finally hit the collaborate tab and invite your bookeeper or accountant with a descriptive note in the invite box. Bingo! we are done

So thats it down and dirty for part 1 check out part 2 where we take this to the spreadsheet of the present using the state of the art EditGrid web based spreadsheet service where we start turning this into a much more sophisticated and less manual process. We will be using a feature that EditGrid provides which is unavailable with Google Spreadsheets and API ...

Until then happy hacking folks

Continued - Part 2 The Spreadsheet Present...

Things you will need for this financial hack :

• Programming tools and libraries, We used the Groovy-All download you will also need java at least V1.4.2 installed + and IDE or just editor. Or use your own favorite language like python,ruby,c#etc..
• A Google account with GCAL and GoogleSpreadsheets enabled (contact me if you need an invite)
• A shared or private calendar account setup as say chargeable with some task entries in it
• A little spare time and desire to explore this new business frontier

Let me know if we are missing anything here or if you need assistance feedback or whatever. Let us know how it goes if you try it out.

Technorati Tags: Atom, Business2.0, financials, GCal, Google, GoogleCalendar, groovy, Spreadsheet