Category Archives: Excel

Words from the future

A message from the future you

I’ve always been interested in listening to people talk about their past. There are lots of good lessons in listening to the advice people might have given to a younger version of themselves.

Someone asked the question “If you could send an anonymous text to yourself 5 years ago, what would you write?” on reddit. Naturally I was interested in the replies. You can see this thread in the following link. Text from the future thread – reddit

The few short words from the responses stuck out as a mixture of real, sad, funny, and crazy. Think about the question for yourself. What would you text the younger you from five years ago. Maybe nothing important. Maybe the only thing that has ever mattered. These few, brief words provide a portal backwards into time. Each word crtitical. A half decade’s worth of knowledge and advice tailored specifically for you. How powerful. How truthful. Maybe painful. Maybe funny. Maybe some other emotion. Or none at all.

I went through about 2,000 of these comments to find which specific individual words had the most meaning for the people who cared to respond to the question. I’ll go over the process for anyone who is interested in doing this with similar data. It’s a pretty inefficient process as it uses Excel and the whole process can be automated but it’s listed here regardless.

  1. Copy and paste the data to Excel.
  2. Manually delete rows of data that are not appropriate. Many of the rows were blanks or included only the username and did not contain the responses so I deleted these.
  3. Parse out the individual words from the responses using a word frequency macro. Excel word frequency macro
  4. Use a stopword list to remove the most commonly used words (a, about, above, … , your, yours, z) that likely do not have any impact on the words we are interested in. Stop word list
  5. Manually go through the list of words and remove words that seemed unimportant (Ago, Didn’t, Doesn’t, Doing, Five, He’s, I’d, I’m, Isn’t, It’ll, Lot, Message, Oh, Send, She’s, Tell, Text, Wouldn’t, You’ll, You’re, Yourself, You’ve)
  6. Use an online word cloud tool Word cloud tool

If you are working with larger data then I would advise you to look into some tools specific for the job. There are plenty of online word cloud generators, such as the one linked above, that you can paste an entire text into. This removes the Excel work and makes generating word clouds pretty much instantaneous. For me it was only a couple minutes of extra work and I had complete control over the end result so the trade off seemed worth it.

The end result is the following:

Words from the future
Words from the future

The top 100 words and the number of their occurrences are included here:

  • Don’t: 724
  • Time: 162
  • Life: 147
  • Stop: 145
  • Buy: 116
  • Love: 111
  • School: 96
  • Fucking: 93
  • Stay: 81
  • Fuck: 79
  • Shit: 78
  • Won’t: 77
  • Job: 75
  • Start: 74
  • Girl: 71
  • Million: 70
  • Money: 70
  • College: 68
  • Leave: 64
  • Trust: 57
  • Roll: 56
  • Worth: 56
  • Friends: 55
  • Move: 53
  • Try: 52
  • People: 51
  • Date: 50
  • Save: 50
  • Lottery: 47
  • Break: 46
  • Gonna: 45
  • Quit: 45
  • Meet: 44
  • Bitch: 43
  • Actually: 42
  • Future: 42
  • Regret: 41
  • Bitcoin: 40
  • Guy: 39
  • Spend: 39
  • Help: 38
  • Look: 38
  • Bitcoins: 36
  • Probably: 36
  • Sell: 36
  • Day: 35
  • Change: 34
  • Friend: 34
  • Hard: 34
  • Dad: 33
  • Listen: 33
  • Invest: 32
  • Dump: 31
  • Home: 31
  • Winning: 30
  • Happy: 29
  • Little: 29
  • Lose: 29
  • Girlfriend: 28
  • House: 28
  • Late: 28
  • Waste: 28
  • Believe: 27
  • Care: 27
  • Crazy: 27
  • Feel: 27
  • Marry: 27
  • Worry: 27
  • Yes: 27
  • Family: 26
  • Please: 26
  • Relationship: 26
  • Study: 26
  • Talk: 26
  • Worse: 26
  • Idiot: 25
  • Live: 25
  • Mom: 25
  • Months: 25
  • That’s: 25
  • Bad: 24
  • Drinking: 24
  • Instead: 24
  • Play: 24
  • Stock: 24
  • Ass: 23
  • Past: 23
  • Ruin: 23
  • Trying: 23
  • Car: 22
  • Class: 22
  • Forget: 22
  • Seriously: 22
  • Matter: 21
  • Okay: 21
  • Own: 21
  • Can’t: 20
  • Dick: 20
  • Die: 20
  • Enjoy: 20

A mix of regret, get rich quick schemes, love, financial advice, school, work, feelings, hope, important people, relationships, mixed in with some internet language. Seems about right.

So you may not be on reddit, and you may not feel like answering, but if you could send an anonymous text to yourself 5 years ago, what would you write? Or maybe the better question is, if you received a text message today from the future you, how would you respond?

Cheers.

Pimp My Excel

Pimp My Excel

Wow your boss or your friends with this beautiful Excel macro.

It takes a boring Excel worksheet and pimps it out. A lot of people use color senselessly when using data, so I wanted to automate that process.

Here are a couple examples so that you can see this macro in action. Here are statistics for the New York Red Bulls. They are so boring. You won’t be able to make any convincing arguments using these stats.

NY Red Bulls Stats... Lame Sauce
NY Red Bulls Stats… Lame Sauce

Wow! Look at the following pimped out chart. Everyone will believe your arguments now.

NY Red Bulls Stats Pimperish!
NY Red Bulls Stats Pimperish!

Ugg. This income statement for Google is so boring. You can’t use this data to successfully invest in the market.

Google Income Statement... Boring
Google Income Statement… Boring

Pimperish! Now you are ready to make some bucks.

Google Income Statement Pimped Out!
Google Income Statement Pimped Out!

How to Use

Download the .bas Excel macro file of Pimp My Excel

Make sure your Excel is able to run macros. You may have to change your Excel settings to do this.

Open the Visual Editor in Excel. Do this with Alt+F11.

Right click on the project area and select Import File. Select the PimpMyExcel.bas file.

Make sure you have the sheet open in Excel that you want to pimp out.

Run the macro called PimpMyExcel.

Programming Notes

This was a pretty straightforward macro to create. The big issue I ran into was the limitations that Excel imposes on the number of font types and cell formats. Excel has a maximum limit of 512 different fonts. In Excel 2000 and later the maximum number of cell formats is approximately 4,000 different combinations and in Excel 2007 and higher the maximum number of cell formats is 64,000. In order to prevent going over these numbers I hardcoded the font colors and cell background colors in the code.

I also chose to On Error Resume Next to handle the errors as Excel continued to throw errors in regards to the cell format issues. This seems to work for most large worksheets.