HomeTime-Tracker Reporting with Excel
I’m planning on a major overhaul of the Time-Tracker this month. There have been a lot of suggestions (and sometimes complaints) and there’s no shortage of ideas for this new version. I’ll post more details about it soon, but now I’d like to present the first user contributed extension to the Time-Tracker: Bryce Shober’s Excel spreadsheet.
This is particularly interesting because this gives a great tool to visualize your data and build your own reports. The spreadsheet is tied to your account (using the export key available in the ‘account panel’ on the Time-Tracker) and can be updated in a single click (see screenshot below).
Unfortunately it only works with the latest version of Excel, so couldn’t check it out yet, but here’s a couple screenshots for you.
If you have any question or comments, please post them below. Thanks.
Technorati Tags: Productivity, Time Management, Ajax, Freja
December 9th, 2005 at 1:42 am
Wanted to give you a heads up that the user / password information doesn’t save correctly in Firefox 1.5 on OSX. I have to re-enter my user/pass everytime I visit the site. Possibly some type of
I absolutely love your site, btw. Keep up the excellent work!
best,
thomas
December 10th, 2005 at 5:27 am
I downloaded the Excel sheet, but haven’t had a chance to make it work yet. The FullList tab is currently static data. I wonder if there is a way to distribute this Excel sheet with a query built off of a public time sheet. This would allow the pivot table to have the correct links into the FullList and allow the user to simply Edit Query, instead of having to figure out how to make their own query fit into the sheet. I will let you know if I have a chance to try this idea out.
December 12th, 2005 at 10:34 pm
Eden: The spreadsheet requires a new feature in Excel 2003 called “XML Lists.” It won’t work as advertised unless you have it. If you do have Excel 2003, then you can simply update your data from the web as shown in the second screenshot.
December 12th, 2005 at 10:45 pm
All:
In order to get this working with your account:
1. In your TimeTracker account, expand “Your Account.”
2. Right click your export key and choose the option that copies the full url.
3. In the demo spreadsheet, right-click inside the blue-bordered section on the “FullList” sheet and select “XML->Import…”
4. In the import dialog that appears, paste your export url into the “File Name:” box and click “Import.”
5. The spreadsheet should now import your data.
6. Switch to the “Report” sheet
7. Right-click in the table and select “Refresh Data.”
8. The report data should now reflect your data.
P.S. I apologize for not getting directions included in the initial post.
P.P.S. As slick as the xml import is, there are some annoying issues w/ Excel that I will list here:
- The xml can’t be set up to auto-refresh.
- The pivot table can’t be set up to auto-refresh w/ the xml source.
- The pivot table retains old data in the drop-down selection boxes that can only be removed by deleting the entire pivot table and rebuilding it.
P.P.P.S. If anyone knows of an *easily* configurable xml-accessed-as-a-database utility, that could make this data much more accessible, including older versions of Excel, and OpenOffice.
December 13th, 2005 at 12:19 am
Bryce:
I thought I had Excel 2003, but I only have Excel 2002. Why not use Data.Import External Data.Web Query to grab the Time Tracker data? This feature is available in Excel 2002 and maybe even older versions. Did they remove it from 2003? I was able to connect to my Time Tracker data this way by giving the query the static html address for my export key. Also, web query supports automatic data refresh. Check out “Data Range Properties”. The refresh can be set as short as 1 minute. If I get some free time I will make a clone of your sheet using the web query option.
December 13th, 2005 at 4:17 am
Eden:
Another reason I’m using 2003 is because of its “Lists” feature, which automatically adds auto-filtering, auto-sorting, and total rows. In fact, if I try to do as you describe, Excel creates an XML list that has no “Data Range Properties” enabled.
December 13th, 2005 at 1:25 pm
Bryce:
I just confirmed that Excel 2003 does indeed turn a web query against a xml source into an xml query and thus stop you from using the auto-update in Data Range Properties. Too bad since 2002 allowed for auto-update in this case.
December 13th, 2005 at 8:10 pm
Wow, something happened since Saturday and the interface is incredibly jerky. I’ve rebooted (it was time anyway) and it doesn’t seem to be my PC. I’m using Firefox 1.0.7.
December 13th, 2005 at 8:55 pm
Lisa, I don’t think anything changed since Saturday and it looks as usual in Firedox 1.5. Let me know if the problem persists.
December 14th, 2005 at 9:41 pm
I’ve got the same problem you reported Lisa, using Firefox 1.5. Mousing over the task list is extremely CPU-intensive. I updated a bunch of extensions tonight, so there’s every chance one of them may be the culprit.
December 17th, 2005 at 5:57 pm
cedsav,
I just tried to change the end time of a period, and now I have the endtime saying that it is an invalid date. So that period I can not chaqnge anymore. Any ideas about why? And how can I redo this?
Besides this: great service this timetracker!! Keep up the works!
December 19th, 2005 at 9:32 am
Rein,
At this point you can’t fix that, but the invalid timeslice will simply be ignored. If adjusting the clock *always* returns an invalid date, let me know, and send me your date localization settings if possible (if you’re a window user that would be in the control panel -> regional and language options).
Thanks.
December 20th, 2005 at 7:20 am
I can report a success. Thanks for the information Bryce. Excel 2003 SP1, Windows XP Pro. Works fine. One of the few times I’ve been happy to have a PC!
Thanks. Be happy to test things as time permits.
Will use this to replace Time Tool.
December 20th, 2005 at 7:57 pm
The application won’t let me logout. After I choose logout it starts loading again and brings up my page without me having to login.
December 20th, 2005 at 8:31 pm
yep..I’ll fix that asap.
December 20th, 2005 at 8:33 pm
Cedsav,
I tested the problem. It indeed has to do with my languagesettings.
If I try to change my times on my desktop which has Dutch languages settings, it causes the error.
The month name of the start and end time is in Dutch. If I try to change the time it says it’s an invalid date.
So for now, I’ll put my desktop to English settings
December 22nd, 2005 at 6:45 am
Hi
Well, I’ve been looking at timetrackers, more of the resource management type but - timetrackers.
I’ve seen a few, and have been looking at the possibility of actually having one custom-tailored.
It’s a small operation, 1 resource to be managed but I need some stuff that I’m not sure you’re about to offer as part of the final services you are about to offer.
I’d need alerts - mainly alerts as to free time slots that are not being used, daily, and weekly (on demand or automatic).
Also, I’d need it to work with the Hebrew language (Right-To-Left, RTL), found your beta anonymous test account to work quite well with hebrew.
Was wondering if you’re about to offer custom-tailoring or if these options are scheduled to be implemented.
Thanks in advance,
*) Gl33mer
December 23rd, 2005 at 10:24 pm
Gl33mer,
It doesn’t look that this type of alerts would be useful to a lot of people, so I probably won’t implement it. On the other hand, there’s an API coming soon, so you might be able to build on the existing service and add what you need.
January 27th, 2006 at 7:49 pm
Anyone know if this feature is available for Excel for Mac 2004?
January 30th, 2006 at 9:09 pm
Scott.. I have no idea, but if the mac version is the same as the PC I guess it should work.