Happiness vs Money: A Spreadsheet Tutorial

15th April, 2020
Michael Forrest

Happiness vs Money

Today I’ll show you how to find out whether money makes you happy. With data.

I track my happiness every day using an app I made myself called Changes which is available on the App Store.

1. Export data from Changes app

First go to Settings → Export Data → Export Spreadsheet to get a CSV file.

I’m leaving “Filter sensitive data” switched on so you don’t see my location or sensitive diary entries.

2. Import data into a new spreadsheet

Now import this data into a spreadsheet. I’m using Google Sheets, so in Chrome type sheets.new into the location bar, go to File → Import → Upload, drag in your file and then just click “Import Data”

3. Explore the spreadsheet

Let’s go over what’s in the spreadsheet

• Date / Time of each entry
• Text of each entry
• Rating ******of each entry: 🙂 = 1 😐 = 0 😞 = -1
• Starred ******- whether the entry was marked as a “high point” or “low point”
• Score - if a rating was starred its score is doubled, otherwise it’s the same as its rating
• Mood Trend - a smoothed trend line showing how your happiness has changed over time
• Place Names and Locations show where you were
• Changes shows the text of the life changes you tracked within the app, e.g. “Quit drinking”
• Hashtags - there is a column for each hashtag you’ve added, filled in with the rating for that hashtag wherever it was used, e.g. -1, 0 or 1

4. Create Mood Trend chart

Select the Mood Trend column and go to Insert → Chart.

Change this to a Smooth line chart, click Add X-Axis and select the Date column.

This will give you a chart of your happiness over time.

5. Import your bank statement

This step will vary wildly depending on who you bank with! If you have multiple accounts, credit cards and other equity or debts then you’ll need to consolidate everything into one spreadsheet.

Here’s roughly what you want it to look like to start with:

You don’t have to make all the dates line up but if your statement only has transactions then you’ll need to consolidate this into your balance using a formula (look up your balance on the earliest date, put that in the first cell, then just add the transaction amount to each subsequent cell with a formula).

6. Consolidate your bank statement into a “Money” value

I decided to group my balances by the first of the month since I’m looking at an overall trend as I slowly run out of money.

This is done in Sheets with *the slightly awkward formula =VALUE(EOMONTH(A2,-1)+1) (where A2 is the *Date column, -1 means the end of the previous month and +1 means “add one day”).

You could group by day using =VALUE(TO_DATE(A2)).

Now select all the columns and use Data→Pivot Table.

• In Rows we’ll add our Start of Month value
• In Values we’ll add the AVERAGE for each account

Now we can dismiss the Pivot table editor and calculating our totals.

• Create a new Total column that sums the account columns, i.e. =SUM(B2:D2)
• Then Normalise that total to be between -1 and 1 using another formula =E2/MAX(MAX(E:E),ABS(MIN(E:E))) - we’ll make this the Money column

Now select the Start of Month and Money ranges (holding down Cmd / Ctrl) and hit Copy.

7. Add your bank statement to the main sheet

In the main sheet, select the Date column → right-click → Insert 1 right and call that column Money. Scroll down to the end of the sheet.

Now use Edit→Paste Special → Paste values only to add your money data to your main sheet. You might need to select the date column again and format as Date Time.

Now we’re ready to see what this looks like! I’m going to switch to my real data first though.

8. Add this ‘Money’ series to your chart

Find your chart, and click Edit Chart, click Add Series and select your Money column.

🥁 Drum roll….

Here it is! My bank balance cross referenced with my happiness. My mood fluctuates as my money grows and grows and then falls and falls over an 18 month period.

What happened? Well, let’s see if I tracked any major changes in the app…

9. Cross reference with Changes

We can’t directly add the Changes column as a series because it’s expecting a number, so I’ll add another column to the right (also called Changes since that’s what we want the label to be). That column will have a formula =if(K2 = "", "", 0.75) where K2 is the Changes text column and 0.75 is the value where we want our labels to appear.

Now we can edit the chart and add this new column as a series, then

Tweaking the appearance and axes a little bit I can show you this.

In one little chart you can see my story of the last couple of years. In 2019 I did a lot of contracting to build up my savings. But this work wasn’t making me happy - you can see how I come in on a declining happiness curve. I decided to strike out on my own and try quitting contracting forever to get Good To Hear back up and running, and give myself more time to make music and other stuff. My happiness continued to fluctuate as my money ran out, but after six months my mood was in decline. I decided to review my progress (in great detail) and came into 2020 with a clear focus to finish Changes and take better care of myself. I achieved this at the end of February. Then Coronavirus hit, but that’s another story…

10. Tweet me if you find anything interesting!

I hope you’ll try this out. It will take a while to collect enough data but it’s amazing what you’ll discover when you do. I’d love to see your charts and hear your stories. Tweet me @michaelforrest with your own charts - it will really give me a kick to see what you come up with.

Changes is available for iOS in the App Store now.