Skip to content

The Return of the Revenge of the Son of Accounting & Accountability

May 31, 2011

A little over a month ago, I asked a question about how to publicly report my progress on my writing projects.  I’ve been thinking a lot about that question in the months since.  And I’ve been thinking a lot about a companion question: what’s the best way to keep track of all that writing I’m (apparently not) doing?

In particular, I was interested in a specific, vexing problem: I’m a “planner”, these days.  So everything that I’ve been writing has been background.  I haven’t actually written anything that counts toward my presumed goal of, say, 125,000 words for my novel.  (The approximately 1,200 words towards my short story’s goal of 6,000 was something I’d written earlier.)  How do I keep track of that?

A few of my readers may have happened across David Sharp’s (of “A Wee Adventure“) “Novel Biographer“, an excel spreadsheet built to track your wordcount progress when writing a novel. It’s a well-designed little tool for tracking stuff like that. 

That tool was a pretty inspired idea, I thought, but it didn’t fully meet my personal needs.  I needed something a little more flexible.  I needed something that accounted for the fact that I’m a “Planner” who writes a lot of background notes before embarking on the novel itself.  I needed something that would allow me to track multiple writing projects of different types quickly and easily.

Well, the quickly part went right out the window.  But starting with the same idea David pursued in his Novel Biographer, I decided to kick it up a notch.  I built a spreadsheet from scratch, using David’s biographer as a conceptual template. 

What I came up with is a bit more… shall we say… complicated than David’s simple, straightforward tool.  Most of that added complexity is under the hood, so to speak.  But it’s also oh-so-flexible.  Perhaps a tad too flexible, but when you set me loose on a problem like this there’s no holding me back.  (Dear Wife knows.  She tried, mocking me endlessly when I said I was working on my spreadsheet instead of writing.)  I built in a lot of options that I don’t intend to ever use, personally, simply because someone else might.  And I tried to design it to be relatively easy to add new options that I didn’t think about.  But that flexibility does mean some added complexity on the UI side of things: there are more places to input things.

So, yeah, it’s complicated… but it’s not too complicated.  The tabs are arranged in a linear fashion – first step 1, then step 2 and so on.  Each tab is relatively straight-forward, with its own set of instructions (that can easily be hidden so they don’t take up precious screen real-estate), and I tried to err on the side of providing more information than you’d actually need to use the tool, although I expect most folks should be able to pick it up and use it with minimal instruction. You start by entering some key global variables that will apply across all projects, then  you start entering in information specific to each writing project you’re working on.  Pulling a random number out of a  hat, I built the file to work with up to 525 projects working concurrently.  So, you know, if you’ve got a lot of little projects you’re writing all at once, or something.

A lot of the work, for you as a writer, is on the front-end, setting up the file the way you want to use it.  Depending on the settings you choose, different options will be available throughout the rest of the spreadsheet.  After that, you enter  your progress each time you write, and you’re on a roll!  Then you can check your stats, look at the graphs, or whatever to your heart’s content.

Take a look at some of these screenshots of the tool in action (you’ll note that these were taken on different computers, one running Excel 2010 and one Excel 2003):

Settup for the Writing Project Progress Tracker

Setting up the Writing Progress Project Tracker (WPPT) is easy... start by deciding how you want to set your writing goals and how you want to input your writing progress...

 
Entering titles into your WPPT

Each writing project should have a unique title and a projected final draft wordcount. You might be working on Novels, Short Stories, or any of a number or different works.

 
You can set your own writing goals in the WPPT

You can choose how to set your own writing goals: you can set your own daily goals, or you can let the program set your goals for you.

 
 

It's easy to enter your wordcounts into the WPPT

It's easy to enter your wordcounts into the WPPT: select which project you worked on today, what type of wordcount you wrote, and your wordcount - depending on how you decided to enter wordcount on the global settings tab, you may see a different screen than this one.

 
Viewing your writing stats on the WPPT

You can view your stats for all of your current writing projects, or for each project individually.

 
The WPPT has a variety of charts and graphs

The WPPT has a variety of charts and graphs. Shown here: the daily and cumulative wordcount totals.

 
Progress Bar and Day-of-the-Week Stats

The WPPT has a handy Progress Bar and Day-of-the-Week Stats.

 
Details on your chapters

Last-but-not-least: Deets on your chapters.

This file is being released under a Creative Commons BY-NC-SA licence.

Stay tuned: A leaner Excel 2007-compatible version (which cuts the file size down by about 90%) is coming up next!

A Note of Caution: The “WPPT” is definitely a work-in-progress.  It works, but it may not be without its bugs, and there are probably some more user-friendly implementations of some of these features that I could attempt.

As a matter of fact, I had already planned this release post when I discovered an annoying bug (the file assumes the user is writing chapters in a multi-chapter work in linear order: i.e. chapter 1, then chapter 2 and so on; which I know is not how many writers actually approach their writing).  So I am knowingly releasing a flawed product here… but don’t worry, as time permits (I really should be writing, not working on this spreadsheet, but inevitably I’ll be retooling this over time when I become aware of problems and bugs) I’ll be fixing anything I come across that looks broken or anything that comes to my attention that doesn’t work.

And you can help!  If you notice any problems in the file: any errors or unexpected results or something that doesn’t seem to be behaving, or have other ideas or suggestions for improvement, let me know (you can reply to this post or send a comment on my Contact Page).  (I will point out that I have discovered at least one logical error in the file since posting, a correction of which will be forthcoming; the links here are still to the old version.)

Lastly, a few usage notes: First, there is one macro in the file that should make your life easier… so I suggest you enable Macros on this document.  (It’s a very simple macro that jumps to the current day to make daily wordcount entry easier.)  Second, the file is a bit of a beast when it comes to calculating formulas – there’s a lot going on under the hood.  If you have a very powerful computer, that shouldn’t be a problem.  But if you’re running a little lean on the computing muscle (as I’m afraid I am) you may not wish it to consume a lot of computing resources every time you enter something (and forcing you to wait a second or two before you do anything else).  In this case, you might want to set Excel to “Manually Calculate” formulas (2003 and earlier, 2007 and later).  Then it’ll leave well-enough alone while you enter your progress.  Just remember that you’ll have to hit “F9” before you look at your stats and graphs (and wait the requisite several seconds) or you won’t see the most up-to-date information.  Note also that you’d have to hit “F9” after you make any settings changes in order to for the file to do all the automatic showing-and-hiding of the various settings-dependent options.  And for you GoogleDocs and/or OpenOffice.org afficianadoes: I know this file is way too much for GoogleDocs to handle (I tried), but it’s not yet tested on OpenOffice.org.  In theory, the Excel 2003 version should work on OpenOffice.org… if you use that suite and have a chance to give it a try and see if it does or doesn’t drop a comment and share your experience.

A Final Acknowledgment: As mentioned, this was inspired by David D. Sharp’s “Novel Biographer”, but the implementation is all my own: I wrote all the formulas and built everything from scratch.  That said, I do owe it to David, because without his file I might never have known how to do dynamic ranges for graphs.  That’s a pretty handy tool to have added to my Excel toolbelt.  And of course, I have to acknowledge Dear Wife, who graciously put up with me working on a silly spreadsheet when I should have been writing these past few weeks.  Thank you honey.  I’m (mostly) done now… so it’s back to writing for me!  (I promise I’ll keep tinkering and bug-fixing time under control, and focus more on writing.)

So, now you have the tool.  You better get to writing… pronto!

Advertisements
19 Comments leave one →
  1. May 31, 2011 4:42 pm

    Hey Stephen!

    Glad to see that I inspired you to also delve into the world of spreadsheetery and thank you for the kind acknowledgement. I do have to argue with your calling the Novel Biographer “simple” though – you’ve seen the formulas going on underneath! (;

    But yes, the WPPT and Novel Biographer do seem to be very different beasts. Mine concentrates purely on the numbers and therefore forces the user to do the same whereas yours takes into account… will I say the “human” side of the story. I can see appropriate uses for both and will definitely give this bad boy a spin.

    Dynamic ranges… don’t get me started on dynamic ranges. I spent so long figuring those out, amongst various other bits. I think my knowledge of Excel tripled when I built the Novel Biographer!

    On a completely shameless final note – there’ll be an updated version of the Novel Biographer coming out soonish!

    Thanks again Stephen,
    David.

    • May 31, 2011 4:57 pm

      Glad to get your stamp of approval! I will say: I didn’t mean “simple” as a pejorative. I meant it in the sense of “straight-forward”… I included so many fiddly bits on mine that it requires a bit more instruction, I think, than Novel Biographer did. I look forward to seeing the next iteration of the Biographer. I will say, though, that I’ve always been a bit of a spreadsheet wonk. I’ve always been the go-to guy for Excel questions in pretty much every job I’ve had since Undergrad (and with each job my knowledge has increased substantially). This spreadsheet was my first time taking a whack at Macros (and also my first intro to dynamic ranges, which worked out great), but virtually every other tool used (both in Novel Biographer and in this spreadsheet) I’ve done before somewhere else. Once, years ago, I’d created a semi-D&D-like RPG system and built the character-generation system entirely into a spreadsheet, with pages for each step of the character generation process and the final output a cleanly-formatted character sheet. I think I learned most of the tricks I know from that little excursion. It’s one I hope someday to repeat when I get around to redesigning that game system…

  2. May 31, 2011 4:52 pm

    I’ll have to try this one out when I get a chance 🙂 Maybe for the next novel? I like that you can track multiple projects at once with this.

    You and David have mad Excel skillz! Thanks for sharing this with us.

    • May 31, 2011 5:04 pm

      Thanks, I hope you get some use out of it. I liked David’s spreadsheet, but ultimately it was my need/desire to track multiple writing projects at once that drove me to do something a little different. That, and there’s one feature built into this that I plan to use to help me with the other part of my original “Accounting & Accountability” question: how to publicly report my writing progress. I’ll talk about that specific feature, and the results from it, later this week.

  3. May 31, 2011 8:32 pm

    Wow! That’s pretty neat, Stephen. I don’t think I would ever use something like that, personally (I’m currently a do-one-story-at-a-time kind of person and can’t really be bothered with numbers, lol), but the fact that you sat down and created something like this is pretty darn cool and deserves uber kudos.

    I will gladly Tweet this, as I’m sure others would be interested.

    Also, I find it uncanny that you’re discussing methods for keep track of writing projects today because earlier I totally drafted up a blog post about my own methods to share tomorrow morning! (More along the lines of tracking ideas instead of results, though, but still.)

    • May 31, 2011 9:00 pm

      Cosmic karma or something, eh? Tracking your ideas is just as important as tracking your progress – or rather, let me ammend to say, it’s moreso. I’ve talked about ways I track my ideas on my blog before. Of course, I totally understood, going into this, that creating this spreadsheet was going to appeal to a very limited subset of writers (i.e. me) and would be totally useless to most others. But I’m glad you think it’s neat. 🙂

  4. May 31, 2011 9:40 pm

    Wow, that is a very fine spreadsheet you made. I use a fairly simple google spreadsheet that allows me to store my data in the cloud 😉 It calculates weekly, monthly, and yearly word count stats (but I don’t differentiate between my writing projects adding up all my words for short stories and novels together and ignoring background words). I also started keeping track of words per minute just because I was curious.

    • May 31, 2011 9:42 pm

      Wow, that sounds pretty nifty. I certainly can’t do WPM with this file. And it’s far too beastly for the cloud, right now. But I specifically wanted to track background words, because that’s writing work, too. Even if it doesn’t look like I’m making progress, based on the wordcount of my working draft, if I’m writing background detail that will inform what I’m working on, then I really am making progress, and I wanted to capture that.

  5. June 3, 2011 7:04 pm

    Wow! I realize that I’m the third person to say that…actually that’s kind of why I said it, ha ha 😀

    But seriously. This is very exciting. I am definitely downloading this today! Also, if you don’t mind, I have some ideas for expanding it that might be useful…I can either implement them myself, or I can email you the ideas. If you haven’t covered them already…(It wouldn’t be surprising if you did!)

    Sounds neat! Downloading now…

    • June 4, 2011 8:52 pm

      If you discover some additional features you’d like to see: (a) feel free to try to implement them yourself and (b) let me know, and I’ll see if I have the time and ability to implement them myself as well. You never know, your feature request could be the must-have feature that everyone didn’t even know they needed.

  6. June 3, 2011 8:22 pm

    Hmmm…Not opening. It takes forever to load, & even when it gets close to finishing loading, it doesn’t. Just a bug you may want to be aware of…Is it really that big? Considered compressing it?

    • June 4, 2011 8:58 pm

      Yes, the file is about 19MB, so it’s not small. (If you have Excel 2007+ then that version is considerably smaller, at 5MB, but I haven’t uploaded it, yet.) Did you try both links? They’re the same file, but different locations. I’ll upload a zipped version on Monday and update this post with a link, and you can try that, then. (I don’t think I’ll be able to get to it before Monday.) I’ll also upload the Excel 2007 version for those who’d like it.

  7. June 21, 2011 4:40 pm

    Wow, that is pretty amazing. I, unlike you, am Excel illerate. That happens, I suppose when you don’t use it, ever. I took a look at it, and it literally blew my mind. Good job on this one, and good luck tracking.

    • June 21, 2011 4:44 pm

      Thanks. I need it for my job, and daily use of it has given me a lot of opportunities to explore its capabilities. So far, the tracking has gone well… It’s the quantity of what’s being tracked that currently leaves something to be desired…

  8. December 23, 2012 10:28 am

    Wow! This is amazing. Thanks!

    • January 3, 2013 4:21 pm

      I’m glad you found it interesting and/or useful. I’m actually working (very slowly) on a follow-up project: something with the same basic premise and concept, but more advanced and with more features and (hopefully) is easier to use and causes less drag on the system. (The primary shortcoming of my crazy Excel experiment is that it’s a huge file that really slows down even some moderately-powered computers.) But this follow-up project is still a long way out from completion.

Trackbacks

  1. Writing Progress: Week Ending June 4, 2011 « The Undiscovered Author
  2. I’m Guest Posting It « The Undiscovered Author
  3. Writing Progress: Week Ending October 6, 2012 « The Undiscovered Author

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: