Analyzing your RescueTime Data – Part 2: Hours/Day by Month

This is part 2 of my series on performing a custom analysis of your RescueTime computer usage data.

By the end of this tutorial, you’ll be able to produce a chart showing how many hours/day you spent online for each month in 2009.

Here’s what the final product looks like for me:

fin

This tutorial does not walk you through every little step in Excel, only the key parts. It assumes you know how to navigate around the software, use formulas, etc.

If you don’t want to see how to do it and simply want to download the final product, click here. Paste in your RescueTime CSV data into the sheet titled “Data” then check out the “Hours per Day per Month” sheet.

But if you do want to see how to do it, make sure you check out part 1 of this tutorial: please read it first. It shows you how to export your data using the RescueTime API and how to set yourself up for this analysis. This tutorial assumes you completed part 1, meaning that you:

a) Downloaded your RescueTime data and

b) Saved the CSV file as an Excel Macro-Enabled Workbook (*.xlsm)

Now on we go:

1) Rename the sheet that contains your RescueTime logs to “Data” so you can later reference it with code:

data

2) Add a new tab and title it “Hours per Day per Month” or something a bit more eloquent–the name of this sheet isn’t important; it’s just for your reference.

hpdpm

3) Now it’s time to write the code that’ll perform the analysis.

Open up Excel’s Visual Basic Editor by pressing Alt+F11. Right click on “Microsoft Excel Objects“, hover over “Insert“, and click “Module“. This is where the code is going to be.

module

4) Copy and paste the following code:

Option Explicit

Type TimeInfo
    Date As String
    WeekdayName As String
    Time As String
    Day As Integer
    Month As Integer
    Year As Integer
    Hour As Integer
    MonthName As String
    DateFormatted As String
End Type

Const DATETIME_COL As Integer = 1
Const TIMESPENT_COL As Integer = 2
Const ACTIVITY_COL As Integer = 4

'------------------------------------------------------------
'  Name    : GetTimeInfo
'  Purpose : Takes a time formatted like this: 2008-07-07T21:00:00
'            And converts it into information that you can quickly analyze
'------------------------------------------------------------
'
Function GetTimeInfo(sDateTimeStr As String) As TimeInfo
    Dim sDateArray() As String
    
    With GetTimeInfo
        .Date = Left(sDateTimeStr, InStr(sDateTimeStr, "T") - 1)
        .WeekdayName = WeekdayName(Weekday(.Date))
        
        .Time = Mid(sDateTimeStr, InStr(sDateTimeStr, "T") + 1)
        .Hour = Left(.Time, InStr(.Time, ":") - 1)
        
        sDateArray = Split(.Date, "-")
        .Year = Val(sDateArray(0))
        .Month = Val(sDateArray(1))
        .Day = Val(sDateArray(2))
        .MonthName = MonthName(.Month)
        
        .DateFormatted = .Month & "/" & .Day & "/" & .Year
    End With
    
End Function

'------------------------------------------------------------
'  Name    : RescueTimeTotal
'  Purpose : This function adds up the seconds for all the rows that
'            meets all of your criteria
'------------------------------------------------------------
'
Function RescueTimeTotal(Optional iDay As Integer = -1, Optional iMonth As Integer = -1, _
        Optional iYear As Long = -1, Optional iHour As Long = -1, Optional sWeekdayName As String = "", _
        Optional sMonthName As String = "", Optional sActivity As String = "") As Long

    Dim oDateInfo As TimeInfo
    Dim iSeconds As Long, bCountRow As Boolean
    Dim indexRow As Long, sThisActivity As String

    For indexRow = 2 To RescueTimeDataCount + 1
        oDateInfo = GetTimeInfo(Sheets("Data").Cells(indexRow, DATETIME_COL))
        iSeconds = Val(Sheets("Data").Cells(indexRow, TIMESPENT_COL))
        sThisActivity = Sheets("Data").Cells(indexRow, ACTIVITY_COL)
        
        bCountRow = True
        If iDay  -1 And oDateInfo.Day  iDay Then bCountRow = False
        If iMonth  -1 And oDateInfo.Month  iMonth Then bCountRow = False
        If iYear  -1 And oDateInfo.Year  iYear Then bCountRow = False
        If iHour  -1 And oDateInfo.Hour  iHour Then bCountRow = False
        If sWeekdayName  "" And oDateInfo.WeekdayName  sWeekdayName Then bCountRow = False
        If sMonthName  "" And oDateInfo.MonthName  sMonthName Then bCountRow = False
        If sActivity  "" And sThisActivity  sActivity Then bCountRow = False
        
        ' Only add the seconds if the row meets all the criteria
        If bCountRow Then RescueTimeTotal = RescueTimeTotal + iSeconds
        
    Next indexRow

End Function

'------------------------------------------------------------
'  Name    : RescueTimeDataCount
'  Purpose : Take advantage of the CountIf function to determine
'            how many rows of RescueTime data you have
'------------------------------------------------------------
'
Function RescueTimeDataCount() As Long
    RescueTimeDataCount = Application.WorksheetFunction.CountIf(Range("Data!A:A"), "") - 1
End Function

'------------------------------------------------------------
'  Name    : DaysInMonth
'  Purpose : Returns the # of days in a given month
'
'------------------------------------------------------------
'
Function DaysInMonth(sMonthName As String) As Integer
    
    Dim sLastTime As String, oTimeInfo As TimeInfo

    ' There's one small adjustment we need to make and that's for the last month of data
    ' If the logs end on September 21, you don't want to use 30 days in September because
    ' you only have 21 days of data
    
    sLastTime = Sheets("Data").Cells(RescueTimeDataCount - 1, DATETIME_COL)
    oTimeInfo = GetTimeInfo(sLastTime)
    If sMonthName = oTimeInfo.MonthName Then
        DaysInMonth = oTimeInfo.Day
    Else
        ' Otherwise, simply return the # of days in the month
        Select Case sMonthName
            Case "January":     DaysInMonth = 31
            Case "February":    DaysInMonth = 28
            Case "March":       DaysInMonth = 31
            Case "April":       DaysInMonth = 30
            Case "May":         DaysInMonth = 31
            Case "June":        DaysInMonth = 30
            Case "July":        DaysInMonth = 31
            Case "August":      DaysInMonth = 31
            Case "September":   DaysInMonth = 30
            Case "October":     DaysInMonth = 31
            Case "November":    DaysInMonth = 30
            Case "December":    DaysInMonth = 31
        End Select
    End If
    
End Function

The programmers out there will be able to follow it fairly easily and if it doesn’t make any sense to you, a brief explanation isn’t really going to help anyway. :)

5)  Back in “Hours per Day per Month” create a column called “Month” that lists all the month names.

Title Column B “Seconds” and in Cell B2 type

=RescueTimeTotal(,,2009,,,A2)

The order for the RescueTimeTotal function is

month, day, year, hour, weekday name, month name, activity

All of the parameters are optional, but you must include a comma even if you aren’t using a specific item and you want to include a parameter that comes after it.

So, for example, if you wanted to total up the # of seconds on Wednesday’s in January 2009 you could use:

=RescueTimeTotal(,,2009,,"Wednesday","January")

And the # of seconds you spent on HackerNews this year:

=RescueTimeTotal(,,2009,,,,"news.ycombinator.com")

Clear as mud?

Hit enter and if all went well, Excel should total the number of seconds you spent on the computer in January 2009.

rttotal

6) Fill in the rest of Column B.

7) I recommend making Column C “Minutes”, which simply equals the seconds divided by 3600.

8) Column D title “Days in Month” and set D2 equal to:

=DaysInMonth(A2)

Do this for all the cells in that column.

9) At this point you should have the # of hours you spent on the computer in each month as well as the # of days in that month. Title Column E “Hours/Day” and set the cells equal to the hours from Column C divided by the days in Column D.

10) Tidy things up, add a chart, some style, and voila, you should have how many hours you spend on the computer per day per month:

If while going through this you come across anything that’s unclear or incorrect, please drop me a comment below and let me know so I can update it.

Next up: Calculating the % of time you spend online, per month

One Thousand Hours of RescueTime Data – Part 1: Getting Started

I spend a lot of time on the computer: 8-9 hours/day at my day job and another few at night and on weekends preparing for a startup.

Ironically, despite an proclivity for analysis and plenty of data to analyze, I’ve never taken the time to see exactly where all those hours go. I don’t have any data for my day job, but I do have RescueTime installed at home on both my Macbook and my PC, which has been busily recording my computer usage since I first installed it in July 2008.

I’d like to consider myself a pretty productive programmer, but before I embark on a new project, I wanted to see if there was anything I could do to improve. I spent the last several days going over this data and the results were, well, stunning. My reactions can be broken down into two broad categories: “Holy shit I spend a lot of time on the computer” and “Holy shit I waste a lot of time.” And so over the next several posts, I’ll be walking though an analysis of my 2009 RescueTime data as well as explaining, with code, how you can do the same.

Getting Started

RescueTime, for the unenlightened, is a free program that quietly logs information about what software you use and how much time you spend using it. The RescueTime software uploads that data to rescuetime.com, which then displays it for you in a variety of nifty visualizations.

Here, for example, is a breakdown of my January 2009 Activities, as seen on my RescueTime.com Dashboard:

rtt

There’s a few other visualizations too: efficiency summary, activities by day, breakdown by category, and a productivity analysis.

My only complaint about RescueTime is that there are not enough visualizations. For example: I want to see how much time I spend on the computer per week over the last year–how do I do it? How about average computer use per day of the week? Broken down by hour on each of those days? By month, how much time do I spend on websites? These are actionable metrics, but unless you do your own analysis or are prepared to navigate through dozens of different time periods on the dashboard, it’s not easy to get. My 2c: Have a separate page  with links to dozens of user-requested visualizations. Not everyone will want it, but it’ll be there for those that do. Could be a pro feature too.

To do your own analysis, you’ll first need to sign up for a Pro account so that you can download the data, which costs $8/mo.

Next, download your usage data by using the RescueTime API. Once you have an API key, simply go to the following URL to download your usage history:

https://www.rescuetime.com/anapi/data?rtapi_key=yourAPIkey&perspective=interval&format=csv&resolution_time=hour&restrict_kind=activity&restrict_begin=2009-01-01&restrict_end=2009-12-31

There’s plenty of ways to customize the output per their API Documentation, but I found the one above gets me everything I need: organized by time and activity, formatted as a comma separated value file, broken down by hour, and spanning all of 2009.

Here’s what the file looks like:

apiex

We’ll use this Excel file to do all of the future analysis. When you save it, save it as a “Excel Macro-Enabled Workbook (*.xlsm)”, which will let you write macros to analyze the data.

For a quick example, you can total Column B, Time Spent, and divide by 3,600 to calculate the number of hours you spent on the computer. For me, that works out to be a whopping 3,018,243 seconds, or 997.52 hours. (You can also get this by clicking ‘year’ next to ‘show current’ at the top of the RescueTime dashboard).

Next post: How to calculate the average hours you spend per day on the computer broken down by month.

3018243

Break

If I had 20 days to solve a problem, I would take 19 days to define it.

— Albert Einstein

I’m taking a break this month in order to brainstorm and figure out what to work on next.

I hope to start hacking away again sometime during the next few weeks and honestly I can’t wait; the idleness is driving me crazy ;)

The Appearance of Accomplishment

Twitter made the news today because of CEO Evan William’s disclosure to employees that the company is valued at $1 billion.

Humorously, I also came across this today from the Editor in Chief of Esquire, David Granger, in the Letter from the Editor section of the October 2009 issue:

“We live in a culture that has begun to prize the appearance of accomplishment over actual accomplishment–it continues to amaze me that private enterprise and venture capital spend so much time and energy (not to mention cash) on “innovations” like Twitter and other forms of social blathering rather than marshaling the energies and talents of our engineers to create new industries to replace the ones that enabled the American Century.”

I admire Twitter for building such a seemingly simple product that appeals to so many people, but I can’t help but agree with Mr Granger: is this the best we can do?

Update: Sara Lacy posted an article on TechCrunch with similar sentiments.

edw519 responded to her article with the following comment on HackerNews, which, like most of his stuff, is thoroughly thought-provoking:

Investor: What are you building?Entrepreneur: Artificially intelligent software that automatically builds sophisticated business applications based on the enterprise’s business rules.

Investor: Your competitors are too entrenched. What can you do that’s simpler?

Entrepreneur: Small business software that ties all a company’s applications together.

Investor: You’ll never compete with Microsoft. What else?

Entrepreneur: Tiny apps that all kinds of people can use to run their stuff.

Investor: 37signals will kill you. What else?

Entrepreneur: Social software that enables your sales people to understand what’s happening in the global marketplace.

Investor: It’ll never work. Can you do something more practical?

Entrepreneur: An intelligent e-commerce system that guarantees the consumer the best value.

Investor: You’ll never compete with Amazon or Ebay. Got any other ideas?

Entrepreneur: Recipe software.

Investor: OK, if that’s the best you can do, we’ll go with it. Geez, I just wish you guys would dream a little bigger.

Domain Pigeon on Smashing Magazine

Domain Pigeon was recently mentioned in a Smashing Magazine article titled 10 Tools For Finding, Registering And Managing Domain Names:

Domain Pigeon’s approach is different to the search tools mentioned before. It automatically generates lists of available domains as well as Twitter names. The names are displayed in different colors, depending on how many people have showed interest in a certain term. The darker the color the more popular the name. You can also order the lists by length, popularity and show only domains or Twitter usernames. Examples of generated domain names are ablebo.com, meliori.com, minecafe.com or shopshost.com.

Domain Pigeon is an ideal source of inspiration for domains and short available Twitter names.

Cool cool.

Quotes

“Determine never to be idle.  No person will have occasion to complain of the want of time who never loses any. It is wonderful how much can be done if we are always doing.”

– Thomas Jefferson

“Only programmers, customer service reps, and accounting staff work at Craigslist. There is no business development, no human resources, no sales. As a result, there are no meetings.”

Wired

“If all you have is a hammer, everything looks like a nail.”

– Maslow

“By inverting this list, we can get a portrait of the “normal” world. It’s populated by people who talk a lot with one another as they work slowly but harmoniously on conservative, expensive projects whose destinations are decided in advance, and who carefully adjust their manner to reflect their position in the hierarchy.”

Paul Graham

“So many of my friends see their jobs in terms of an “annual salary” or an “hourly wage.” The security they crave turns quickly to complacency, and they wind up dissatisfied with a job they’re afraid to leave. Why? Because they are not rewarded for their efforts, a productive day is no more lucrative than an unproductive day. They get paid the same, regardless.”

Mike Rowe

“I think it’s in everyone’s DNA to want to be a builder.”

Dan Phillips

“The real company values, as opposed to the nice-sounding values, are shown by who gets rewarded, promoted, or let go.”

Netflix

“If you want to build a ship, don’t drum up the people to gather wood, divide the work, and give orders.  Instead, teach them to yearn for the vast and endless sea.”

– Antoine De Saint-Exupery

“I can’t understand why people are frightened of new ideas. I’m frightened of the old ones.”

– John Cage

“We live in a culture that has begun to prize the appearance of accomplishment over actual accomplishment–it continues to amaze me that private enterprise and venture capital spend so much time and energy (not to mention cash) on “innovations” like Twitter and other forms of social blathering rather than marshaling the energies and talents of our engineers to create new industries to replace the ones that enabled the American Century.”

– David Granger, Esquire Editor in Chief, October 2009 issue

Last updated 16 September 2009