August 28, 2007
Importing Slimtimer Tasks into Quickbooks
by Brian Donahue
This is the conclusion to my sordid affair with timesheets and Slimtimer.
I've worked in consulting agencies practically my entire career, and of course with consulting comes time tracking. And I've always been horrible at it, and I've always despised it. What is more counter-productive than having to report about every moment you are being productive?
As an employee though, there were some advantages. Once I got through the pain of recording my time entries, I could send them on their way and they'd eventually reach accounting, and I'd never have to think about them again. Now that I am in business for myselfBroken Link: http://www.vitreosolutions.com/, not only do I have to track my own time, I have to aggregate other people's time and expenses, bring them into QuickbooksBroken Link: http://quickbooks.intuit.com/, and create invoices for my clients. Woe, the responsibilities of a small business owner...
I started out as rough as you can get. Excel spreadsheets, and manual cut and paste (and massage) into Quickbooks. That was no fun. Then, one of our projects was being outsourced to us by another consultancy, so it began to look like I would have to keep my time in three places - Excel, for convenience of entry, Quickbooks for my own billing and payroll, and in our partner's time tracking system. I had to put my foot down!
I wanted to find a way to track my time in one place, and be able to export it to the other destinations. And it had to be more user-friendly and flexible than QB' nasty stand-alone Timer app. I preferred a web app, so I could update from any pc, any location. I quickly remembered Slimtimer,Broken Link: http://www.slimtimer.com/ the shiny Ruby on Rails time tracking app. In an nutshell, Slimtimer lets you manage your Tasks, and record Time Entries against each Task. You tag Tasks and Time Entries, and run reports. I raced off to see what kind of options it offered for exporting time data. CSV reports was the first answer. But it wasn't flexible enough. It might actually work for our partner agency, but would be a real pain to get into QB. I needed more control over which data I retrieved and how I retrieved it. Then I noticed the "APIBroken Link: http://www.slimtimer.com/help/api" link (you need a slimtimer account to see that link).
Slimtimer allows you to retrieve and manipulate your tasks and time entries through a very nice (almost RESTfulBroken Link: http://en.wikipedia.org/wiki/Representational_State_Transfer) API. Looking at the output files of QB's own Timer application, and this example pageBroken Link: http://www.unanet.com/unadocs/unanet32/userguide/admin_export_qb_time.html1, I set out to bring down my Slimtimer tasks and cram them into Quickbooks IIF file format to import. At the bottom of the API page, there happened to be links to two different .NET wrappersBroken Link: http://www.slimtimer.com/help/api#libraries for the Slimtimer API. At a quick glance, there were things I liked and didn't like about each, and I thought about writing my own. But, then I remembered the pain of parsing XML (at least until I get into LINQ for XML, SteveBroken Link: http://www.iqueryable.com/), combined with the fact that the API calls were not pure XML representations of objects, but hybrids including some other values, and I decided to build it on top of these chap. I chose Mike Pilone's SlimTimerDotNetBroken Link: http://mikeandrenee.pilone.org/Projects.aspx.
The key to this process is using Slimtimer's tags to add the QB info you need. I use 3 tags in Slimtimer that map to QB properties: "Billable, "qb_job," and "qb_item". "Billable" is simple enough, if the task or entry is tagged as billable, it is marked as billable in QB. The other two are "compound tags" in that you need to add info to them to make them useful. Slimtimer uses comma-delimited tags, which at first annoyed me, but now I am very thankful for. It enables me to add a tag like so, "qb_job:Big Company:Big Project" without having to worry about the spaces or colons messing up the tags. In my SlimTimerConverter object, I parse that tag, and know that that Time Entry should be charged to Big Company's Big Project. Same goes for the item: "qb_item:Consulting:Programming" This will make sure the entry gets charged at the appropriate rate for my Consulting:Programming item in QB. I make sure the user's name in Slimtimer matches their name in Quickbooks. Finally, I combine the name of the task in Slimtimer with the comments for that entry, to get a nice "Note" field for QB such as, "[Big Project - Deployments] Deployed some big features to production."
I set most of my tags at the Task level in Slimtimer, and you can share tasks with other Slimtimer users, either individually, or by tagging them with a "shared" tag. You can then override the Task level tags by adding different ones at the Time Entry level (so I could re-use a Task name, but bill to a different project). The only downside I have seen so far is that when you share tasks, the tags do not share, so each user has to re-tag their tasks. I hear that Slimtimer is working on adding the ability to share tags though.
Right now I have this all running as a console app with the command line:
<code>> "slimtimerconverter.exe <username> <password> <start_date> <end_date></code>
There is still work to be done, and I'd like to add a GUI to it, maybe even in SilverlightBroken Link: http://silverlight.net/! Please leave a comment if you have any interest in the code - I'd be happy to share, but it's not quite ready to post as a finished product.
1 Neither the QB Timer app I have nor the example page apparently shows the correct, current, Quickbooks 2007 IIF file format. This results in a warning that you are using an older format, but still works fine. Quickbooks includes a Timer app in their Quickbooks 2007 CD-version, but it is not available in the downloadable version that I purchased. I am hoping to get a copy from a friend who has purchased the CD version, and will update my format accordingly after that.