To the Researchmobile! : Identity theft

So, here I was, planning to do another installment in the Personal Libraries series. And then last Friday happened.

To be precise, I got a call from our head of HR, saying she’d gotten an unemployment request from me, and thought I should know about that. Someone had gotten hold of my social security number and name and used it to file a fraudulent claim.

So, for today’s post, here’s a guide to what I did to check that I was doing all the necessary things.

Quick! Research Needed! (gold exclamation point on a dark background)

Background is useful

One thing that makes for really excellent research is having something of a background in the topic.

Obviously, we’re not going to be experts in all the things, all the time – no one can do that. But we can help ourselves out by taking in a steady stream of information that makes it easy to get ourselves up to speed on specifics quickly if we have to.

For me, this means reading or skimming a couple of general purpose sources of news and information. I subscribe to online editions of two newspapers and support one of my local NPR stations (and they send a summary of current major stories daily), plus I read several general purpose sites that cover a wide range of topics (Metafilter, in my case), plus a couple of general financial and lifehack sites.

I specifically wasn’t trying to build expert knowledge in what to do if I got hit with identity theft (because the specifics on what to do change periodically, as services and government resources change), but all of this meant I was well aware it happens sometimes, that it’s not always easy to figure out where the breech happened, and that there are in fact steps in what to do about it.

That meant that when I got that call from HR, I didn’t have a tidy list of what to do. But I knew they were out there, that ‘identity theft’ was the term I wanted to work with, and that I’d just need a little time to do those searches and check my information.

Oh, and a bit of background:

If you’re outside the US and trying to figure out what this means: in the United States, the social security number is the closest thing we have to a personal identification number.

It’s technically only supposed to be asked for in a limited number of situations (like taxes, or some kinds of financial accounts) but it’s often asked for in a bunch of other places – everything from college applications to rental applications to medical records.

This makes it rather easy to abuse, unfortunately.

(For those curious about the history of the number designations, here’s a page from the Social Security Administration.)

Habits are also useful

Fortunately, I already have a routine for keeping an eye on financial accounts (more on that in a few steps).

So I knew right where everything was for the different accounts, and could check quickly to see that there were no unexpected charges, and that no one had opened up accounts in my name recently.

First steps

The first step here is to take a deep breath. Panicking isn’t going to make this go better, and it won’t solve the problem (no matter how tempting it is.)

I was at work when I got that call. I did a little quick searching that made it clear that yes, I was going to want to make half a dozen phone calls, and a couple of them probably needed to be during business hours, which helped.

I’d been out sick for two days on Wednesday and Thursday, and had already been considering going home early, so I arranged to do that (because making the calls from home would be a lot easier.) Fortunately, I’d already done the things that I really needed to be in the office to do.

The drive home was fast (no traffic!) but it gave me about fifteen minutes to process through things and sort out what I wanted to do in my head, so it would be easier to take steps in a useful order when I got home.

Initial searches

I started by doing a search on “identity theft social security number” because that was the thing I knew had been compromised – and it’s a slightly different kind of issue than someone who potentially has your credit card info.

I browsed through the results, looking for highly reliable sources – for example, there’s this PDF guide direct from the Social Security Administration. I also found less official guides like this one, that still had a useful set of tasks and suggestions.

I focused on recent pages, written in the last year, since advice changes as people try new scams, and technology has new options. I also looked at my state attorney general’s site for information.

(If you search in Google, you can use the “Tools” option and select “Past year” instead of “Any time” in the option that will pop up below the main search tabs.)

I didn’t take the advice from any one source (even the Social Security folks!) Instead, I looked at about 20 sources and combined them into a list of things to do. (That’s also why I’m not giving you a ton of links here: the best resources will change over time.)

Here comes the spreadsheet

You knew there was going to be one, right, if you’ve been reading this blog.

I set up a spreadsheet with multiple sheets in it.

The first sheet has conversations I’ve had or steps I’ve taken (like online reports). It has columns for date, time, who I talked to, what the general topic was, how (phone, online, etc.), and then notes for the conversation and any follow up I need to do or pay attention to.

The second sheet has links of things I still need to do.

The third sheet has specific contact information for people I may need to get in touch with again, so I don’t have to hunt up the numbers or web addresses.

What did I do?

1) Put a fraud alert on my credit account.

This is a 90-day alert, and if you call one of the three agencies in the United States, they will pass the alert on to the other two. The call was entirely automated and very straightforward for being an automated call.

I got a reference number and asked for my rights to come in the mail, rather than hearing them over the phone, so I’ll have a confirmation of what they are. It’s possible to extend these alerts or put a credit freeze on for longer, but it’s easier to do that once I have a completed police report.

2) Put in a police report with my local police department

This produces a temporary report (the instructions say very clearly not to use the confirmation number until they’ve followed up) but a police report opens up some additional options for later (and if there ever is a problem down the road, being able to demonstrate that I reported it is helpful.)

My police department has an online form that you fill in, or I could have called the non-emergency line. This was the second step because I wanted to be able to say I’d made the report to any later calls.

3) Called the Massachusetts Unemployment Fraud line.

I found them by looking at the Unemployment Office site. Since this is the place where the actual identity theft happened, it was high on the list. I spoke to a really pleasant man who was glad to confirm they’d already flagged it as a problem in their system, and that the address they had wasn’t the one I gave them.

The big issue is that if I ever do need to file for unemployment in Massachusetts, as long as that claim is on file, I’ll need to have additional identification and documentation. (This means that police report is important! But also things like a photocopy of my ID, and current mail to demonstrate my address, etc.)

4) My bank

I bank with a small local independent bank who have the best customer service (Thanks, Leader Bank!)

I got a real person right away, no phone tree, and he was great about checking and making notes in my file that if there are any inquiries about my account, to ask for an agreed on passcode, or call me for verification.

5) Credit cards

I didn’t put a freeze on my credit accounts just yet (it will take a little more paperwork and I want to have the police report to reference before I do).

I did turn on alerts on all of them to let me know if there are more than very minimal charges on any of them. I already check my accounts manually twice a week. (I will be bumping that to three times a week.)

6) Reporting to the FTC

Many of my sources (including the SSA) encouraged me to report it to the FTC’s Identity Theft site. They ask you a series of questions about what happened and advise what steps you should take. You can also get a confirmation number saying you filed a report with them, which helps demonstrate that you took action on the problem.

7) Social Security Administration

I was able to lock access to my account online but will need to do a more elaborate process to sort out some of it. Again, some of that will be much easier with the police report.

Things I need to do in the future

Once I have the police report, then I’ll do additional paperwork for a credit freeze and to clear up documentation with the social security offices.

It’ll be important to keep that documentation somewhere easy to access if I need it (i.e. all in one place) so that if I do need it, I can grab it quickly. I’ve been a little unhappy with my current ‘important papers’ filing for a while, so this is a good time to rework that system into something a bit easier to use.

I live by myself, so one of the things I’m thinking about here is if something happens where friends need to help me with filing for disability or other benefits, what I need to document now to make that easier. My ideal is to be able to identify a folder that has a summary of everything.

Along the way, I also read a bunch of advice – for example, I may get scam calls with threats if I don’t make payments, pretending to be from the IRS, etc. The sites I looked at had advice on ignoring those and explained how the IRS actually contacts you.

I’ll also just need to keep an eye out for weird stuff, in case something else crops up. Some of the things I found suggest people try the unemployment scam first and then move on to other things if it works. On the other hand, this might not be the only person who has my information, depending on how they got it, so it could be an issue for credit, leases, etc.

One spreadsheet to rule them all : tracking 2018

Why track things like this?

I’ve mentioned in previous posts that I kept a somewhat absurd spreadsheet in 2017, and updated it for 2018. (2017 part 1 here, part 2 over here, and initial 2018 thoughts here.)

Why another post?

I had a question from someone wanting to know if I could share in more detail (so here’s a sample you can copy and save as a Google Sheets file – it has a week of data in it so you can see how it works.) Also a few improvements in layout.

I’ve made a few advances in layout since I wrote the most recent of those posts, so here’s a more complete explanation. (I am still using the same tracking apps as in the first part of the 2017 posts, so I’m not duplicating that info here.)

In addition, I had some thoughts (useful to me, and maybe others) about how to give incentive to the things I want to do more of.

Green leaves curling up around the word "productivity"

What changed in 2018?

Going into 2018, I thought about what I wanted to track better, or make more of a priority.

How was my day?

In 2017, I was tracking on a 4 point scale to get a rough idea of my day. In some ways, this was very helpful – I learned that about one day in 4 or 5 is a not good day for me (I feel lousy, don’t get much done, can’t focus) which is very helpful to know in terms of long-term planning. (It also suggested that I should be more consistent about sleep in a couple of specific ways.)

But four points isn’t very nuanced. I wanted a system that gave me more nuance, and maybe more incentive for doing more of the things I wanted more of in my life. So this year’s is seven, with some variations possible.

(I should note that I expect it to be very rare to get only 1 or 2 points.)

More writing

I joined two online writing communities, with two different commitments. I was already tracking what I was writing, but one of the communities I signed up with a habit pledge (number of days writing) and the other is words.

My words goal is actually well under what I did last year, but the number of days is a significant stretch. (Last year, I wrote on about 150 days, and I’m aiming for 240. That’s a big jump.) Last year, I had a lot of days in which I didn’t write, and then would do 1500 or 2000 words at once. This year, I’m experimenting with more consistency but maybe hitting smaller totals each day.

Besides those communities, I want to track what I write to see patterns in what I’m working on (especially the fiction / non-fiction divide and how long it takes me to do things like write course materials.)

More reading

I’ve always been a big reader. My number of books has dropped off significantly since there has been a lot more content online (even through college, I was at about a book every 2 days). In 2017, I read 77 which is significantly fewer than I wanted.

I also wasn’t happy with my tracking. I was tracking in a separate wiki, and in practice I would get behind on updating (and especially the data entry part of that.) So this year, I’m trying a plain list that has author, title, genre, and then a link to more about the book.

Mid-length goals

I’ve mentioned that I was thinking a lot about this post from Shawn LeBlanc about using an 8 week cycle for projects.

I’m Pagan. And specifically, a kind of religious witch who celebrates 8 Sabbats. Four fire festivals, two solstices, two equinoxes. So, for me, it made sense to split my year into 8 (slight uneven) segments for planning over about 6-7 weeks. Each cycle, I’m going to pick 3-4 longer-term goals (which might not be complete projects) in different parts of my life.

Use it for other useful data

Since this spreadsheet is almost always open in the browser for personal stuff (email, Todoist, and then this sheet, usually), I have decided to use it for other stuff I want to keep handy.

Topics for blog posts, for example, so that whenever I think of a new one, I can stick it there and skim when I want something to write about.

Reference lists, like my cataloging terms for LibraryThing.

Knitting. (That sheet doesn’t appear here: I’m working on a project which is ‘make lots of smaller objects’ that will eventually be joined, so that tracks how many of which colour I’ve made.)

What I don’t track

I try to keep my tracking to things that I, personally, am motivated by but not obsessive about in the way that doesn’t help. I don’t track food things in general because I get into a bad place about it. If I need to do it temporarily, I use an app or use a new file. I also try not to double-track things. I’ll enter data from apps that are tracking it, but I try to only have to track it once.

Points

Seven point system, with some possible adjustments.

  • Did I get at least 30 minutes of physical activity (not exercise: this includes walking around at work, light housework, etc.)
  • Did I take my meds? (or take my meds + have a health-related appointment?)
  • Did I get both more than 7 hours sleep and more than 70% sleep quality?
  • Did I write any words?
  • Did I do more than 4 large tasks. (see below for description)
  • Did I do my daily spiritual practice? (More on that on the spirit sheet)
  • Did I do anything creative? (Writing isn’t counted here, because it gets counted elsewhere, but drawing, knitting, etc. all count. Going to a concert or theatre would too, but I don’t count routine TV/movie viewing or reading.)

Adjustments:

  • Extra point for more than 1000 words.
  • Minus one point if I have anything in the ‘sick’ column.
  • Plus one point if I declare a rest day (to encourage me to take a day easy without messing with points averages.)

Note that it’s possible to get more than 7 points (which I did January 1st.) The additional point for days I feel sick is to make sure I get a more accurate account of days when I’m not doing as well, even if I turn out to be reasonably productive.

I also hope it’s clear that I don’t expect to get all 7 points every day. Two are pretty easy for me to do (take my meds, and do the very brief spiritual practice that is what I track with ‘North Star’.) I fairly reliably don’t get the points for movement at least one day on the weekend (and sometimes that’s very deliberate, because I’ve overdone it on previous days.) I’m hoping to get the points for writing every day, but I know from experience I will have days where I don’t manage it.

I basically consider everything from 4 points and up to be a reasonable day, in terms of feeling good I did things that are priorities.

Overview

The file has a number of different sheets within it. (I’ve deleted a couple that have personal data or lists: knitting, the list of cataloging terms I use for LibraryThing, etc.)

Here’s the sheets in order, with a brief explanation. This is from the sample sheet, so I’ve removed a couple of more personal things as described above. You can click on the image to get the full size version.

Screenshot of summary page: described in text.

Summary (image above) : Overview of entire calendar month, with rating for each day. Columns where I have gotten a point are light gray. (Sleep is a little different: it shows me where I’ve hit my goal, but I only get the point if both meet my requirement.)

The type of day columns allow me to get a count of rest days, unusual days (not my usual schedule), days with errands, and days I was sick. (R, U, E, and S). And then of course notes, so I can say things like “snow day”. I’m also noting weekends this year, to see if those are a pattern.

Body : Tracks overall activity, minutes of exercise, then category. (Human is my tracker. Other activity is usually household chore stuff.)

Doing : Tracks tasks done – I track in Todoist, and use the Potterverse money system for size of task, because a completely non-metric system works better for me than dithering over the difference in size.

In practice, knut is something that I read or reviewed quickly (like reminders), sickle is a task that takes 10-15 minutes (like a straightforward reply to an email) and a galleon task is something that takes me an hour or more or involves a significant effort. (If it’s multiple hours, I count it the appropriate number of times.) They calculate a total.

Writing : The left column is the total number of words. The columns are specific projects, broadly defined. At the right, I calculate total number of days written, and have links to the communities I’m doing challenges for this year. And the average words per day so far.

Read : What books I’ve read. I’m not tracking days, but am tracking number per month, and genre. (More on data validation in the next section.)

Spirit : These are spiritual/religious things I want to keep track of doing. My actual daily practice at the moment is listening to at least one song off a playlist I set up that has about 65 songs in it. (I hit shuffle a couple of times), and the list in my working sheet pulls the titles in when I start typing them.

Ritual is to note if I did anything that was more involved ritual. Creativity is what I did that was creating something (as described above.) The next three columns are my Tarot summary column (colour coded by suit) and then the cards for the entries. The last column gives me totals for the month.

(Note that on the sample I’ve replaced the songs with ‘yes’ when I did them, and removed the actual card names from the Tarot reading column. Some things feel like oversharing.)

Week : This is the week by week summary – I use this to see if there are any broad patterns I should be aware of (big changes in activity or exercise for a not-obvious reason).

Month : Same deal, but for months. (The calculations for this are more complicated because they involve total days in the month.)

Goals : Sheet for the mid-term planning.

Written : Tracking sheet for writing details – the samples should give you an idea. (This is helpful for seeing what specifically I was working on.)

Topics : Parking place for possible blog posts.

Archive : At the end of each month, the entire month’s summary gets pasted here (use paste special -> values!) and it will do a running count of quality of day.

Cyclical planning : My math on how many days are in each cycle. Entirely ignorable (and deletable) if not applicable to you.

Goals : Template for the mid-term goals. Year goals go at the top, as I finish cycles I’ll past them in rows below.

Finally, two sheets for calculations and validation, which I’ll describe below.

Calculation

Last year, I ended up doing a lot of the calculation semi-manually (getting the totals from the individual sheets, then scribbling them on a piece of paper to transfer to the week or month.)

This year, I wanted to set it up so I could copy and paste the week and get the totals. (The month is calculated on the month summary sheet already.) This meant a bunch of careful alignment of columns.

This is why there’s a ‘days written’ column in the daily summary, when it’s not really necessary. It’s so that when I do larger totals, I have that space filled.

The books category needs to be manually entered (that’s why it’s highlighted in gray) since I need to look at the actual dates. (Writing this has made me realise I really should turn that month into a proper date. Right then!)

I’ve caught a couple of glitches in the calculations, so there may be more lurking. It’s sometimes hard to proof these without a certain amount of variable actual data.

I pull the month onto the calculations page since it’s sometimes handy for proofing and just as a comparison.

Data validation

This sheet does two main things. At the top, it does the actual calculations for each day’s points for the month. (Usual for checking it’s working right.) The gray columns are the ones that actually give points.

(“Both good” counts the total for sleep time and quality, and “total” gives me the point if that number is 2. “More words” is for the extra point for more than 1000 words.)

The bottom of the sheet contains data validation columns for different things. There are two benefits to this – one that you can get a drop down menu for the things that are more complex (Tarot card names, song titles) and second that you can do counts for things like Tarot suites more easily without worrying about typos.

Summary

Obviously, much of this can be edited or adapted to your needs – if you’re doing data validation, you may need to double check that the range is correct (this is on the sheet that references it).

Questions welcome, either here or through the contact form. (I’ll likely see the form a bit faster.)

Productivity roundup

It’s the time of year when people start thinking about being organized next year, isn’t it? Here’s a roundup of thoughts about productivity. Here’s a recommendation for the productivity geeks out there, a few thoughts on planners, and my spreadsheet for 2018.

Productivity Alchemy

If you do podcasts at all, and you haven’t dipped into Productivity Alchemy, I highly recommend it.

It’s done by Ursula Vernon (writer and artist – her stuff is fabulous!) and her husband Kevin Sonney (programmer, for his day job), and together they have been exploring all sorts of different productivity approaches, tools, and techniques.

The podcasts are a great mix of Ursula and Kevin talking, Ursula as Wombat Test Subject for trying out different techniques (or refusing to try some out, as the case may be), Kevin interviewing other people about what works for them, letters from listeners, and other tidbits of their lives. They are funny, thoughtful, and interesting.

One thing I particularly like is the focus on talking to people who have a wide range of different kinds of needs and goals – the interviews include a lot of people doing arts or freelance work, as well as people with demanding office-based jobs.

They’re also great about including people who’ve got chronic health or mental health issues that make figuring out what to do or getting it done more challenging. So much productivity geekery focuses on doing more, more, more, that it’s really refreshing to have people focusing on ‘how do I get the necessary stuff done so I can do more of the things I really enjoy or want to do or feed my soul.”

Looking for a planner?

Episode 23 of Productivity Alchemy reviewed several planners (links are in their show notes, even if you don’t want to listen to the show.)

The Simple Dollar had a nice roundup of twelve planners with info on what they focus on that included a few I hadn’t seen before. (Also, in rummaging around later links in this post, I found my way to Rachael’s 52 Planners in 52 weeks series, which highlights some other approaches.)

I’ve also been thinking a lot about this post from Shawn LeBlanc, about working on projects in an eight week cycle: six weeks of focused work, a week to wrap up loose ends, and a week off. Specifically (being me, and with my religious life running on a Sabbat cycle), I am thinking about how to apply that.

(In my case, it would not be for work projects, because this model is not a great fit for librarianship or other jobs that are reactive to questions, and also because I don’t have final control of a lot of long-term project choices.)

I’m finding the Momentum Planner’s approach (which I discovered from the Simple Dollar roundup) might be a way to bring this together, and let me do some longer term planning. They have free versions you can check out over here, and you can buy a full year’s version with additional tools for $12.

If you’re the kind of person who really wants to build your own, I came across Agendio. That looks very promising for people like me who need some things in a planner, but not, for example, a major task list or daily schedule. I’m eyeing this as a thing to build, but haven’t tried it yet. Also very customisable in terms of colours and fonts.

Also in the mix for my personal planning is Briana Saussy’s Book of Hours, “a planner for sacred artists” that includes astrological information, brief but illuminating questions for particular events. You can get the questions and brief date info from her 2018 Astro Rx page, but the planner is gorgeous.

I put the questions for last year into Todoist, where all my actual tasks live, and I’m looking forward to taking a little more time this year to sit down and actually write.

My tracking for 2018

I’ve been working on updating my tracking spreadsheet for 2018, and it’s got some new categories, and a new layout.

Summary :

The first sheet pulls in the month’s data from all the other tracking sheets, and gives me a score for the day (out of 7, but it’s technically possible to get a total of 9 points). This helps me see if I have a run of especially good days or especially bad days, so I can make some corrections.

Embodied life:

Physical things, or relating to my physical body.

  • Physical activity
  • Exercise
  • Upkeep (did I take all my meds? Or was it a day I had a health-related appointment?)
  • Sleep time
  • Sleep quality

Several of these are tracked with apps (Human for activity, Sleep Cycle for sleep) as before. I get a point for at least 30 minutes of activity, and having more than 7 hours and 70% sleep quality. Also a point for doing whatever the necessary upkeep stuff is.

Doing:

  • How many words did I write? I get a point for any words, and an additional point for more than 1000, because I would like to do a lot more focused writing this year.
  • Tasks (total number of tasks, using my calculations for small/medium/large). I get a point for doing the equivalent of at least 3 large tasks.

Spirit:

  • Daily spiritual practice : space for me to track what it was, and I get a point if there’s anything in that space.
  • Did I do a creative/artistic/crafting thing today? What was it. (I get a point if there’s anything entered.)
  • Tarot card of the day.

Type of day:

This year, I’m adding a set of three columns. One for a rest day (if I decide I need a day to recover.) I get an extra point for any day designated as a rest day. (This is meant to make up for losing points on the ‘tasks accomplished’ because I want to encourage myself to rest when I need it.)

It also has space for unusual days (like travel, or other times my usual routine is knocked around) or if I’m sick. For the latter, I put in what kind of sick it is, so I can see any patterns. I lose a point for sick days, because even if I actually manage to get things done, they are not great days.

Summary

At the bottom, a set of 7 rows counts up the number of days with each set of point totals. Now that I’ve added in two points for things I should be able to do almost any day (the basic daily practice and taking my meds), it’ll be interesting to see how the adjustments go.

Additional sheets

I have summary sheets by week and by month (to help me see larger patterns), a sheet to track what I’ve written, a sheet where I store things I want to write about (since this spreadsheet is often open and handy).

And then the two I mention above, for tracking goals, and looking at them across seasonal blocks of time.

Productivity : Tarot spreadsheet

Last post, I talked about what I track in my daily spreadsheet. Most of the things I count are pretty straightforward (at least if you’re comfortable with spreadsheets), but the way I set up the Tarot sheets is a bit less intuitive.

One of the things that’s really true about spreadsheets is that they can do a ton of things, but it’s often hard to see the potential until you have a thing you want to track (and play with it) or can explore some examples.

It relies on a couple of more complex formulas, and has three basic sheets. I’ve made a copy of it so you can see. You won’t be able to to edit it, but if you have a Google account, you can make a copy for yourself – go to File and ‘Make a copy”. I left a week’s worth of card draws there so you can see how it works, but you can delete those (just delete what’s in the B and C columns)

I’m also going to explain the basics of how it’s set up, so you can play with it in Excel or Numbers or LibreOffice, or another spreadsheet tool if you like. (These other programs use the same formulas, though some of the syntax may be slightly different.)

Image of spreadsheet screenshot showing different coloured totals. Text says: Productivity, Tarot spreadsheet

What I track

While I love having a Tarot deck in my hands, I discovered I often don’t remember to check for a card of the day before I get myself out the door (that’s the downside of an early start time and an early morning). Via the app, I can check when I get to work, easily and conveniently.

Sheet 1 : Daily cards

This has four columns:

  • Date (in whatever format you prefer)
  • Card
  • Suit or Major
  • A column that combines these.

Screenshot of spreadsheet showing daily Tarot card readings : described in text.

Here are the daily cards for the first week of this year: The Heirophant, Page of Pentacles, The Wheel of Fortune, the Page of Swords, the Ace of Pentacles, the Star, and the Four of Pentacles. The name of the card is in the second column, the suit in the third, and the combination of the two in the third column.

You can actually just enter the card name manually (so long as you are completely consistent) but I use two optional tools to discourage random typo errors. Typos will mess up your statistics, because this spreadsheet is only going to count things that exactly match what you tell it to count.

I use data validation on the second column to verify the card names. This looks at a column of the card names on the “data validation” sheet and will only allow me to enter names on that list. As a side benefit, this means that as you start typing, you get a drop down menu of the choices that match that card. If your deck uses different names for some cards, you can adjust the text on the data validation sheet.

The third column is suit : I used conditional formatting to change the color based on the suit, because I like to be able to glance at it and see the difference. In this case, highlight the column, and then set up five rules, one for each suit plus the majors. Conditional formatting looks at what’s in the area you select and changes the formatting based on what’s there. In this case, it does a different background colour for each suit. On the stats page, I did something a bit more complicated with conditional formatting. We’ll get there in a minute.

I use the fourth column to automatically generate the statistics consistently. This uses the concatenate function which combines text strings. In this case, it combines the thing in the second column (B), a hyphen and spaces (the thing in the quotes), and the thing in the third column (C).

It looks like this as a formula:
=Concatenate(B2,” – “,C2)

The results will then say things like “The High Priestess – Major” and “Six – Cups”

Once you set up one row, you can click, hold, and drag it down the entire column to copy the formula line for line (or if this doesn’t work for you, you can edit it manually.)

The quote marks indicate that text should be inserted. You can put anything you like in there, but the – mark is nice and consistent, and lets me count both majors and suits easily.

Sheet 2 : Statistics

This is the more complicated one, since it counts automatically from things on sheet one. Basically, there are six columns. Four suits, the Major Arcana, plus a general statistical count of type (suits, numbers, court cards). I use additional columns to make the spacing attractive and more readable for me.

Screenshot of Tarot statistics sheet, described in text

The basic formula looks like this: =COUNTIF(‘daily cards’!C:C,“Major”– this is an example from the first count, for Major Arcana cards. 

  • = tells the spreadsheet that the next thing is a formula.
  • COUNTIF is a formula that counts only if an entry in the identified range matches the identified text “Major” in this case)
  • The part up to the comma tells it where to look (up to the comma). In this case, it is a range. You can click and identify ranges in other sheets in most spreadsheet apps, so this is looking at column C on the ‘daily cards’ sheet.
  • The thing in quotes is what it’s looking for. “Major” in this case. (This is why consistent terms are important.)

Here are some other examples:

  • The specific card “The High Priestess” : =Countif(‘daily cards’!D:D,“The High Priestess – Major”
  • The specific card “Six – Swords” : =Countif(‘daily cards’!D:D,“Six – Swords”)
  • All Pages : =COUNTIF(‘daily cards’!B:B,“Page”)
  • All Cups : =COUNTIF(‘daily cards’!C:C,“Cups”)

Different columns for different goals:

Note that these look at different columns, depending on whether you’re looking at for a class of card (Pages, in column B), a suit (Cups, in column C) or the combination (column D). This is why the first sheet is laid out like it is – it allows for much more elegance in counting the stats.

Color formatting:

I’ve also applied conditional formatting so it’s easy to see at a glance which cards come up more often. There are an absurd number of variations possible in how you set this up, so find something that’s pleasing to you. Here, I’ve chosen colour scales relating to the suits (with purple for the majors, and blue for my generic statistics because I like blue.)

These scales weight the colors, so you can see that there are differences depending on the totals. (In this case, I’ve set the midpoint colour to be 50% of the highest number in the range.) This means the shades will change as you enter more data.

Looking at smaller amounts of time

This spreadsheet looks at everything in the main sheet – so in my case, it’s all the cards I’ve pulled from January 1, 2017 to July 31, 2017. (Because it’s still the middle of August, and I haven’t put August’s data in.)

What happens if I just want to look at a month? Or three months? In that case, I can easily look at a smaller portion with just a couple of steps (though I should be careful to avoid deleting the data I want later.)

  • Make a duplicate of the Daily Cards sheet. Maybe move it to the end where I won’t accidentally click on it.
  • Edit the daily cards data to just show the time period I want.
  • Look at the statistics and save a copy.
  • Copy the data from all the days back to the Daily Cards sheet.
  • Delete the extra duplicate sheet I made in step 1.

If this sounds too complicated, you can just count manually. If you want to have months separate, you can make duplicates of both the daily sheet and the stats sheet, rename them (for example : June 2017), and then edit the part in the formula that says ‘daily cards’ to the new name you’ve chosen, i.e. ‘June 2017’. Obviously, this is sort of a pain in the neck.

One more example of spreadsheet power

After writing the last post, I did some more fiddling with my stats sheets. I have multiple chronic health things, so part of why I’m charting things is to see how I’m doing, and whether there are any patterns I should be aware of.

Screenshot of tracking spreadsheet: described in following text.

Here’s an example from the week I took vacation in July (I stayed home and set up this site, mostly.)

  • Column A : The date
  • Column B : Number of items in the next columns that qualify as ‘good’ or better.
  • Column C : Moon phase (it turns out I do usually do a bit worse over the full moon. Good to know!)
  • Column D: How much activity I got (general movement + exercise).
  • Column E : How much exercise I got (in this case, I walked downtown a couple of times).
  • Column F: How long I slept (I color code particularly long nights so they stand out)
  • Column G : Quality sleep (a percentage my tracking app gives me)
  • Column H : How many words I wrote
  • Column I : How many tasks I completed.
  • Column J : Tarot card of the day (colour coded in text.)
  • Column K and L : Notes for unusual days and if I was sick.

What you can’t see in this screen shot is a set of columns used to generate the number in Column B.

  • Column M : Total number of “good” or better for that day.
  • Column N : Uses CountIf to count if activity was more than a certain level. (30 minutes, in this case)
  • Column O and P : Count sleep info, using CountIf (more than 7 hours, more than 70%)
  • Column Q : Adds them, so I can do the calculation in Column R.
  • Column R: Looks at the total in column Q. If they were both good (i.e. the total is 2) it uses CountIf to give me one point. If the total is less than 2 (i.e. I didn’t sleep enough, or not well enough) it doesn’t count it, so no points. I figure that if either one was below my fairly generous margin, I didn’t actually sleep well.
  • Column S: Did I write things? Counts if I’ve written any words that I track (not casual discussion, but anything lengthy)
  • Column T: Was I reasonably productive? My baseline here is 3 or more big tasks.
  • Column U: Uses CountIf to count if there is anything in my “Sick” column

This part takes a little explaining. For Column U, I wanted to note why I was sick – a cold? A migraine? Feeling generically lousy (multiple autoimmune issues means that happens to me sometimes). But I wanted it to count that I felt sick no matter what the text was. So, I used what’s called a wildcard – something that will match any text in that cell. In Google Sheets, * (an asterisk) is the usual wildecard.

Here’s what that looks like for a day I was sick (May 9th) : =Countif(L130,“*”)

The total (column M) adds up the good points (Activity, decent sleep, writing, tasks), subtracts a point if I was sick (otherwise it just subtracts 0.)

Then I just had to drag the formulas down the screen so they covered the entire year, and there we go! I’ve got another sheet that calculates percentages of how the days went (so I can tell you that I had good days about 3 days out of 4. Which is useful to know – and useful to know that about one out of four days, I can expect to not get as much done as I hoped for, whether that’s because of a cold, a migraine, or feeling ill in other ways, or just plain lack of brain. (My stats also tell me that I was ill about half those days, so the other half are my brain just not working well.)

Total spreadsheet geek

As I said last time, if you’re baffled by how I did this but want one for yourself? (Putting the data in is so much easier than setting it up!) That’s the kind of thing I’d love to help with as a consulting project.  Get in touch from that page if you’d like to talk about the options.

I’m also very glad to answer questions here, or via the contact form, if you’re just trying to figure out how to do a specific thing.

Want more stuff like this? My next set of posts coming up are going to be about copyright and some related topics, but I’ll be circling back to productivity in the not too distant future. Check out my newsletter which will have occasional links about it as well as other things I’ve found interesting in my travels around the net.

Productivity : Spreadsheet of doom (how I do my personal tracking)

I have spreadsheets for a lot of things. (Enough that my friends tease me about them.)

The one I use most often is my personal tracking sheet. Why do I track things over time? Because it gives me a comparative sense of I’m doing.

I’ve had a rough few days, brain-wise: a grand state of exhaustion for no obvious reason and brain fog that’s making it hard to get much done. At the same time, I can look at the data and figure out if there are particular patterns.

Screenshot of tarot tracking speadsheet with statistics : text reads productivity : spreadsheet of doom

What I track right now:

Sometimes I track more, sometimes I track less. These have been pretty consistent for at least a couple of months now, sometimes much longer. (The last ones I added were sleep time and quality, which I’ve kept data on for years, but weren’t in the spreadsheet.)

  • Activity I get (and how much deliberate exercise)
  • Sleep amount and quality
  • Tarot card of the day
  • Words written
  • Productivity
  • Number of unusual days (outside my normal schedule) and days I was sick.

And then I do a summary page by week (so I can see changes over time) and by month (for larger chunks of time)

Unusual days are the number of days that were outside my ordinary schedule (so vacation, travel, etc.) and sick days are days in which I felt sick enough to not do at least some of the things I would normally do (so when I’m home sick from work, but also ‘I am getting over a horrible cold and sleeping miserably and can’t brain at all’ which took up two weeks this May. Just as a random example.)

I divide it up into different sheets: here’s what that looks like. E is exercise, T is Tarot, W is writing, S is productivity stats. Since it’s hard to show you data that isn’t very personal, here’s a list of what the sheets look like instead.

On my list to add in (probably starting in September) are astrological transits, to see if that is related to any particular pattern.

Screenshot of spreadsheet sheets (described in nearby text)

I also keep writing topic ideas and a log of things written in this sheet (since I have it open a lot and it’s easy to add things here), and then the summaries by week and month. The last sheet is data validation for the Tarot cards, and for categories for my writing topics. I prefer having that on a separate sheet for tidiness.

I used to have all the archive data on the same sheet, but found it annoying to scroll back and forth, so I separated the archival info out into its own sheet. I copy each month’s data to the archival sheet at the beginning of the new month, and update the summaries and do some additional number crunching on it. This week’s addition to that is looking at how good the day was by different categories and counting that up.

Tools I use:

Two Google Sheets spreadsheets. Why Google? So I can access them from home or work (or with some annoyances, from the iPad while travelling. Also, I like the formatting tools a lot.

One sheet has my current data (by calendar month) plus a summary. The other has archival data (previous months).

I track the information that goes into the spreadsheet in multiple apps. (The ones I use are all iOS, but equivalents exist for other phone OS)

General activity

I use Human.

This app tells me how many minutes I moved for. I add in exercise manually (since that’s usually swimming, and my phone and the pool are not friends.)

If I walk somewhere for more than 10 minutes, I manually edit the time to count that as exercise. I also have a column for activity my phone doesn’t count (mostly housecleaning, where the phone is usually on my desk while I’m doing things.)

I use my phone rather than a specific fitness gadget because the phone’s basically always on or near me, and I lost two Fitbits before I figured out that part.

Because of the chronic health issues, part of why I track activity is so that I know if I’ve had an unusually active day so I can take steps to rest, recover, and take care of myself – more activity is not necessarily better for me!

Sleep quality and amount:

I use Sleep Cycle.

This is not always the most incredibly accurate (I’ve had nights that felt pretty lousy that the stats said were pretty good, and vice versa) but it is good at catching when I actually fell asleep, and if I was up in the middle of the night and I feel like the overall trends match my experience.

It also works very reliably for me as an alarm. (I should note I’m a light sleeper, though). It can be set to wake you up in the lightest part of your sleep phase.  It will also make note of weather, heart rate (using a pulse tool with the light from your camera’s flash) and some other useful statistics.

For example, I sleep less well pretty reliably around the full moon and new moon, and sleep better between them. Perhaps more usefully my sleep quality tends to be a lot better on Friday and Saturday nights (aka the days I don’t get up early for work) which makes me more protective about scheduling them. I try to avoid scheduling things that mean I need to be up and moving at a set time (at least before about 10) now.

Tarot card:

I use the Shadowscapes deck app for my cards.

Anything that produces a card will work for this, whether that’s a deck or an app. (And if you like apps, the people who made the app for the Shadowscapes one also have a number of other decks.)

I track what cards I get over time, and find the summary of what cards came up interesting. My weekly summary does a simple count by type (Major Arcana, Swords, Wands, Cups, Pentacles), though I’ve got a more thorough card counting sheet I’ll talk about in a minute.

The Tarot card stats look like this (this screenshot has all my daily cards from January 1, 2017 until June 30, 2017. I actually find it fascinating that the suits come out almost even over time. And yet, over six months, there are some cards I’ve never pulled for a daily, and a number I’ve pulled five or six times.)

I’ll be talking about how to set up a spreadsheet like this in my next post.

Screenshot of Tarot card statistics : described later in text

Productivity:

I use Todoist, as described in the previous blog post. I then count up the number of each size of tasks, and add it to the spreadsheet.

This is just a quick slash and tally on a scrap of paper: since I divide my tasks up by size, I write K (for knut), S (for sickle) and G (for galleon) across one side, the dates down the left, and just count and tally, then add them all up. It goes very quickly for me.

Putting the task count in a spreadsheet lets me measure how productive a week was (overall) against other weeks, and figure out if there’s something that’s messing me up.

Words written:

Counted in whatever app I’m writing in (or copy and paste into a thing that will tell me) and put in the appropriate column.

I track both number of words, and number of days I wrote that week or month. My current goal is to write at least 5 days a week, and I’ve got half a dozen projects, so there’s a column for each general project. Over time, that helps me see where I’ve been doing more writing or less.

Fun with spreadsheets

Of course, these techniques can be applied to a lot of other topics – one of the things I learned about spreadsheets is that a lot of people use them only in the ways they’ve come across before. I hope seeing some other examples and hearing about some additional things they can do help.

Come back on Saturday for how I set up the Tarot spreadsheet!

Baffled but want a spreadsheet set up for your own personal goals? That’s the kind of thing I’d love to help with as a consulting project. (Spreadsheets like the one described above are probably under an hour’s work on my end, especially if you can explain clearly what you’re hoping for.) Get in touch from that page if you’d like to talk about the options.