Dear Analyst

I recently had the opportunity to build a growth model at work, and it's been fun getting back to my roots in Excel/Google Sheets. Been a while since I've built a model from scratch so I of course referenced previous models my colleagues have built. Interesting to see the use of FILTER and INDIRECT in the models for referencing data, so I'm sharing how you can use these two functions when building a model (specifically, referencing raw data). The example Google Sheet uses an open data set for CO₂ emissions found on Kaggle.

The data set

The data set simply looks at all CO₂ emissions for every country by year (in some cases going back to the 1800s). Not quite sure how Our World In Data was able to get data going back this far, but it's there. The data set consists of four columns and is denormalized (just one long stats table):

About a year ago, National Geographic released a report card on how various countries are tracking towards emissions targets following the Paris Agreement a few years earlier. National Geographic put a few countries in the following buckets: "Top of the class," "Shows some promise," and "Barely trying":

Our raw data set has emission data for every country in the world, and our goal is to build a simply model that outputs the emissions for a few select countries for let's say the most recent 10 years of data. With this summary data set, we can then start looking at trends, growth patterns, and more. Basically, how do we get something that looks like this (and we can plug in whatever country we want in the first column)?

Modeling with named ranges vs. PivotTables

Since we have a denormalized data set, the easiest way to get the data into the "summary" table structure in the screenshot above is do a PivotTable:

You can play with the filters for Year and Country to just show the data you want. As much as I like PivotTables, it's not easy to manipulate when you want to change the filters on the fly, so instead we can create a separate summary table that references the raw data using formulas. This is where named ranges come into play.

Applying named ranges to columns in the data set

This is a new technique that I haven't done before in my models when I was more actively building models, but you'll see the flexibility once you see how named ranges can work with the Filter function. In the gif below, I've named the Country, Year, and Annual CO₂ emissions columns. The named range just represents the entire column. You can access named ranges by going to the "Data" menu in the toolbar.

Referencing named ranges in summary table

Back on the "Model" spreadsheet, I have my countries broken out into the three buckets listed in the National Geographic article. This is a screenshot of the summary table I need to fill out:

Since I have the country in column C and years from columns D onward, I can write a formula to pull in the data I need from the raw emissions data to fill out the table. Here's what that formula looks like:

This episode is the audio from a presentation I gave a few weeks ago to members of Betaworks based in NYC. Betaworks is a startup accelerator, co-working space, and community of founders. No-code is a pretty hot topic right now, and in this presentation I talk about how spreadsheets is one of the first no-code "platforms" and how your spreadsheet skills can be extended to build real tools. The presentation is adapted from a talk I gave last year at Webflow's No-Code Conference. I embedded the "slides" at the bottom of the post, and here is a link to the slides if you want to look on your own.

Summary of presentation

* The skills you've learned in Excel/Google Sheets — include data structuring — translate to building workflows for any part of your business* Thinking beyond spreadsheets as a way to do data analysis or "number crunching"* Any tool that helps automate or solve some workflow at your company can be built with spreadsheets* Why learning spreadsheets can set you up well for learning "no-code" tools

Spreadsheet examples from presentation

During the presentation, I showed actual spreadsheets (Excel and Google Sheets) I've built in the past for freelance clients and friends. The main concept I'm trying to convey is that each of these spreadsheets look and feel more like an application rather than a model that forecasts out certain values. Each of these examples consists three core elements:

* Database - A place to store information* User Input - Fields and forms for someone to fill out* Calculations/Display - Formulas (e.g. "business logic") to make the spreadsheet output something for you (the administrator) or the user

My 2 cents: When you're building an application in a spreadsheet, you're extending the original purpose and audience Excel and Google Sheets was meant to serve: financial models for accountants. But this is what makes the spreadsheet so versatile. The fact that an analyst can string together formulas to make a spreadsheet look and feel like an application is what gives the spreadsheet power. This innovation also pushes Microsoft, Google, and other platforms to release new features that give analysts the ability to build tools, not just models.

I've written extensively about this subject in the past, so will leave my soliloquy at that. On to the examples

Bachelorette planning Google Sheet

The first example I discuss is this bachelorette party planning Google Sheet I built for a friend. This spreadsheet has been duplicated quite a few times by friends of friends, and all it does is help a to-be bride plan figure out which weekend works best to have a bachelorette party.

The key insight is that the database is everything from column B onwards and row 3 and below. All the availability for each person is captured in each of these cells and there's some conditional formatting to give the bride a visual indicator to see when a weekend is available.

The user input is the ability for each friend who is shared the Google Sheet to edit the cells. "Yes," "No," and "Maybe" are the only inputs that matter for this Google Sheet. Finally, the calculations are in rows 31-33 which tallies up the user inputs for each w...

SPACs (Special Purpose Acquisition Companies) or "blank check" companies have been in the news recently, so I used some real SPAC data for this episode. Your spreadsheet has empty cells in column A, and these empty cells should be filled with values. Your task is to fill values down up until you find another cell with a value, at which point you need to fill that value down. This episode walks through how to do this programmatically with a script in Google Apps Script (for Google Sheets) and VBA (for Excel). This is the Google Sheet associated with the episode. The Google App Script is here and VBA script is here. See a quick example of what the issue is in the gif below and how the script "fills in" the values for you.

See the video below if you want to jump straight to the tutorial:

https://www.youtube.com/watch?v=t-32QkyjKVE&feature=youtu.be

Why is this data structure a problem?

You've inherited a spreadsheet and the data structure looks like this:

It's a list of data but there are empty cells in column A. This is usually a category or dimension in your data set that needs to be "filled down" so that the data set is complete. In the Google Sheet, each row represents one person that is associated with a given SPAC, but the SPAC Ticker column is incomplete. You'll usually get this type of data structure through the following:

* Data was manually created by someone who didn't fill down the values in column A since they thought it was a "category" * You are working a data set that originally came from a PivotTable but you only have the "values" from the PivotTable, not the PivotTable itself

This data structure is a problem because if you want to do any type of analysis on this data, it will be extremely difficult since you have missing values in column A. Sorting, filtering, and PivotTables are all out of the question if your data set looks like that screenshot.

Solving this with keyboard shortcuts

Totally doable for this Google Sheet. This is what you could do:

All I'm doing above is the following (on PC):

* SHIFT+CONTROL+DOWN ARROW - Select all the empty cells from the current cell with a value up until the next cell with a value* SHIFT+UP ARROW - Reduce the selection by one row* CONTROL+D - Fill the value from the first cell in the selection down* CONTROL+DOWN ARROW - Skip to the next value that needs to be filled down

The obvious tradeoff here is time vs. human error. Every time I have to do this task on a spreadsheet, I think about whether it was worth filling the values down "manually" using keyboard shortcuts or using a VBA script (in Excel) to do this programatically. It really depends on the number of rows. For the example SPAC Google Sheet, doing this with keyboard shortcuts takes 10 seconds tops. If this spreadsheet was 1,000,000 rows, then we have a problem.

Don't worry, I got you. Here's the script you can use to do this programmatically.

Using Google Apps Script in Google Sheets

First off, here's the script you can use for Google Sheets (gist

Since TikTok is in the news right now about who is going to buy them, I thought using some fake-ish TikTok acquisition data would be relevant for this episode. A classic Excel/Google Sheets challenge: how to do a VLOOKUP to the "left" e.g. your lookup column is not the first column in your lookup table. There are all sorts of strategies to overcome this issue with how your data is structured. Notably, the INDEX/MATCH strategy is the most commonly-cited strategy when good 'ol VLOOKUP is not at your disposal. In this episode I walk through a strategy that allows you to use VLOOKUP: array formulas. Skip to strategy #3 below if you want to see the answer. Associated Google Sheet for this episode if you want to follow along.

Was trying to find some gif associated with "looking up"

See the video below if you want to jump straight to the tutorial:

https://www.youtube.com/watch?v=6JluR45VJl4

Why the VLOOKUP won't work

If you are new to why VLOOKUP won't work in this scenario (see Google Sheet), take a look at the data data structure below:

We have ID in column A and we want to find Company Name and Market Cap in columns C and D, respectively, for these IDs. The ID in column A is the unique identifier for the row, and we need to do a lookup to Company ID in column I.

While you can eyeball the result for the first row ("Triller" is the company for ID 3), we want to find a scalable solution using formulas.

As you start writing the VLOOKUP formula in column C, you'll start to notice the problem: the Company ID column is not the first column in your table to lookup the ID value in column A:

Here are a few strategies for solving this problem (#3 is probably the one you haven't seen before).

Strategy #1: Move the lookup column to the first column position

This is not the most ideal solution, but you could just simply cut and paste the Company ID column and move it to the left-most "first" column of your lookup table. In Excel you would have to do a cut and paste, but in Google Sheets you can just drag and drop the column into the proper position:

Now the VLOOKUP for Company Name will work correctly since Company ID is the first column in your lookup table:

I don't like this strategy because it involves some manual cutting and pasting of columns. If your lookup table isn't static (e.g. might be sales data that gets added daily), then you might be ruining the "structure" of your data on subsequent updates. Let's see what else we can do.

Strategy #2: Make copies of the columns to the right of the lookup column

Also not an ideal solution, but it works in one-off cases where your data is static and you don't care about showing your back-end work to a colleague. It looks like data is duplicated, but you're basically referencing existing columns in your table so that those columns appear to the "right" of your lookup column:

Now you can do a VLOOKUP for columns I to K to get the Company Name and Market Cap values to show up in columns C and D:

It's 2010, and the world is coming out of recession. Two Harvard professors--one of whom is a former economist for the IMF and chess Grandmaster--publish a paper suggesting that a country with a high public debt-to-GDP ratio of over 90% is associated with low economic growth. Turns out the Excel model the professors use is riddled with some basic statistical and formula errors. The results potentially lead to incorrect economic policies, austerity measures, and high unemployment around the world. This is a Google Sheet which shows one of the spreadsheet errors, and I show how you can prevent such an error in this post.

See the video below if you want to jump straight to the tutorial:

https://youtu.be/mXUynkQQ1uM

Background

Economists Carmen Reinhart and Kenneth Rogoff published a paper in 2010 called Growth in a Time of Debt (originally published in the American Economic Review) where they argued:

[...] median growth rates for countries with public debt over 90 percent of GDP are roughly one percent lower than otherwise; average (mean) growth rates are several percent lower.

In 2013, PhD students Thomas Herndon, Michael Ash, and Robert Pollin of the University of Massachusetts, Amherst had re-created the study from Reinhart and Rogoff's paper as part of their PhD program. The students had to analyze the original Excel files that Reinhart and Rogoff used, and they weren't able to replicate the original results. They cited in their own paper entitled Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff :

[...] coding errors, selective exclusion of available data, and unconventional weighting of summary statistics lead to serious errors that inaccurately represent the relationship between public debt and GDP growth among 20 advanced economies in the post-war period.

Reinhart and Rogoff suggested that the debt/GDP ratio and economic growth is simply a correlation, and that correlation still holds after correcting for the spreadsheet mistakes. However, that correlation is not as strong as their original paper posited.

Why this was a big deal

The implications of their findings resulted in news outlets, politicians, and policymakers using the 90% benchmark as a signal that a country is heading for low economic growth. Some notable examples:

* 2012 Republican nominee for the US vice presidency Paul Ryan included the paper in hi proposed 2013 budget* The Washington Post editorial board takes it as an economic consensus view, stating that "debt-to-GDP could keep rising — and stick dangerously near the 90 percent mark that economists regard as a threat to sustainable economic growth." * Austerity measures are put into place around the world despite the advice from economic advisers, pushing unemployment rate above 10% in the eurozone

3 main Excel spreadsheet problems with the model

The three main errors that Herndon, Ash, and Polling discovered are the following:

* Years of high debt and average growth where selectively excluded from the data set* Countries' G...

Let's say you have a set list of names (in this case TV characters from popular 90s TV shows). You want Google Sheets/Excel to generate a random list of names from your list as if you were picking names out of a hat. How would you do this? It most likely would involve the RAND function, but let's take it a step further and say you want to give the end user the ability to dictate the number of random names to return from your list (e.g. out of my list of 100, give me 5 random names). This is the Google Sheet with all the completed formulas. In addition to the audio format of this episode, I'm also going to start releasing the video tutorial:

https://www.youtube.com/watch?v=icKppdnxJRk

Create your list in column B

Start with your list of names in column B. This can be any list you want to randomize. My list is just a bunch of TV characters from shows I watched when I was a kid.

Source: Fandom

In column A, you put the RAND function and copy it all the way down to the bottom of our list. You'll get a decimal with random numbers. Doesn't look that useful now, but this random number column will drive the rest of the tool to generate your list of random names:

Sort this random list of numbers

It sounds kind of weird, why would you sort a random list of numbers? What does that even mean? As you have probably seen, every time you refresh your Google Sheet or commit an Excel formula by hitting ENTER, all those random numbers in column A will change. This means if you sort this list of random numbers, the sorted list will change too. I put a space in column C so in cell D2, you enter this formula:

The SORT function takes in a range of cells as the first parameter, the sort index as the 2nd (which is just the number column we ant to sort on, column #1), and then true or false for sorting in ascending or descending order. You can also put 0 to indicate false which is what I did in this example to sort in descending order.

The nice thing about the SORT function is that it automatically fills the formula down to the bottom of your data set. This is a relatively new function in Excel since it kind of acts like dynamic array formulas or array-entered formulas. The formula kind of "spills" down for you as your list grows so you don't have to worry about dragging the formula down until the last row in your data set.

A good 'ol VLOOKUP

What does this column of sorted random numbers do for us? Well, we know that each random number in this sorted column corresponds to one of the numbers in column A where we generated the random number. So in column E, we just do a VLOOKUP using column D as our lookup value and columns A:B as our lookup table to get the name associated with the random number in column D:

This is not the usual way you might use VLOOKUP because you're usually using VLOOKUP with some unique identifier as the lookup value. Column A isn't really a unique "TV character ID" since that "ID" changes all the time with the RAND function. We don't really care about that, because now when you refresh the Sheet, column E will always have a random list of names:
...

You blink a few times at the screen and realize what you're seeing is not a typo. $6.2B has left your bank due to some rogue trader making untimely bets on the market. That's B as in billion. You call up the modeler who was supposed to make sure this never happens to your bank. The modeler takes a closer look at his model, and realizes that he made a fundamental error in how he calculates one value that caused the dominoes to fall. This is the story of the "London Whale" at JPMorgan Chase in 2012 who cost the bank $6.2B and a breakdown of the Excel error that may have caused the whole thing. This is the Google Sheet if you want to follow along with the Excel error.

Derivative of a derivative

I'm not going to pretend like a know the intricacies of all the financial products involved here, so you can read the Wikipedia article if you want the full details. In 2012, there was a CDS (credit default swap) product called CDX IG 9 that the trader at JPMorgan may have made large bets on, and ended up on the wrong side of the bet. The London trader's name is Bruno Iksil, and it was a classic scenario of a gambler trying to get out of his losses by doubling down on black at the roulette table.

Source: The Fiscal Times

Multiple investigations were taken by the authorities in the U.S. and U.K., the the investigations show that a variety of institutional failures may have facilitated the large bets made by the London Whale. This HBR article by Ben Heineman, Jr. provides a nice summary of all the key players:

* London traders - The traders simply didn't understand the complexity of the derivative products they were buying and selling* Chief Investment Office (CIO) - The head of the CIO didn't monitor the trading strategies and put in the proper controls for the portfolio of products the office was buying. The Value at Risk (VaR) model was flawed (see more below).* Firm-wide Leaders - Not enough oversight by the CFO and CEO (Jamie Dimon) * Board and Risk Policy Committee - The committee was told that everything was fine with the CIO, and didn't get accurate pictures of what risk officers really felt about the risky trades being made.

Appendix of the Task Force Report by JPMorgan

There is a 130-page report created by JPMorgan Chase in 2012 which details what happened internally that led to this debacle. In my opinion, the juicy stuff starts in the appendix starting on page 121 of the report. I read off some parts of this appendix in this episode, but the appendix basically details issues with the VaR models created by one of the quantitative modelers at JPMorgan to more accurately value the complex traders that were happening. Or at least they thought the model was more accurate.

At the very end of the appendix, there's a section called "Discovery of Problems with the New VaR Model and Discontinuance" where the report details the Excel error that contributed to the large inaccuracies in how the model valued risk.

The $6.2B Excel error

This is how the error is described in the report (emphasis mine):

Following that decision, further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically,

In Excel or Google Sheets, text manipulation is usually associated with data cleaning, data cleansing, and data transformation. Sometimes your data is "dirty" and needs to be categorized in a different way or you need to "extract" a piece of text from a another piece of text. In this example, we use a combination of the FIND, RIGHT, and LEN functions to extract the domain name from an email address (e.g. the "tesla.com" from "[email protected]"). Here's the Google Sheet if you want to make a copy for yourself to follow along.

Start with finding the @

The first step is to use the FIND function to find the location of the "@" symbol in the email address. The FIND function takes two required arguments and one optional argument. You're basically find the index location of where that characters or string exists within the cell:

In the case of "[email protected]," the FIND function would return 11 since the "@" symbol starts at the 11th position within the email address. Pretty simple right?

Nesting LEN inside the RIGHT function

The next part is a little trickier. Now that we know the position of the "@" symbol, we want all the characters after the "@" symbol to get the domain of the email address. There are multiple ways of doing this, but I chose to use the RIGHT and LEN functions. To make this more clear, I could have put the LEN function in its own column, but decided to next it within the RIGHT function:

The RIGHT function takes two arguments and simply returns the number of characters from the "right" of the text you give it (in this case the email address). Since we don't know how many characters to pull from each e-mail address, we use the result of the LEN(A2) - B2 formula which tells us how many characters to pull from the right of the email address.

LEN(A2) gives us the length of the entire text (for "[email protected]" it's 21). If we subtract the index position of the "@" symbol from that length, we'll get the exact number of characters to pull for each unique email address. Pretty nifty.

Note: The "Position of @" column also could've been nested in the 3rd column (and replaced the current cell reference of B2).

I typically use a combination of FIND, LEN, and MID to extract the text I need from a longer piece of text. Once you master these few functions, you'll be able to to pull anything you want out of a long piece of text to get "clean" data.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

* The Tim Ferriss Show #444: Hugh Jackman on Best Decisions, Daily Routines, The 85% Rule, Favorite Exercises, Mind Training, and Much More* EconTalk: Robert Lerman on Apprenticeships

On a recent episode of The Intelligence, The data editor at The Economist spoke about a U.S. presidential election forecast their publication is working on. I looked more into their model and discuss some of the features and parameters of their model and what makes their forecast unique. Some of the techniques used in The Economist's model can be used with your own forecasting use cases. To see a summary of The Economist's model, see this page. Learn more about how the model works on this page.

Source: The Economist

Key takeaways and a caveat

The model utilizes machine learning and multiple data sources and it's easy to get caught up in the details. Here are the key takeaways as described by Dan Rosenhack, the data editor at The Economist:

* Machine learning is used to create equations to predict the 2020 presidential outcome* Early polls are not as reliable early on in the election cycle* Partisan non-response bias can result in a supporter being more likely or unlikely to respond to a pollster when there is extremely good or bad news about that supporter's party or candidate

A caveat: The Economist's model and the various forecasting techniques they use are definitely outside of my knowledge and skillset. Most of this episode is me learning more about the model and interpreting some of the results. You don't have to be a statistics programmer or data science professional to appreciate what the data team has done at The Economist. If you are working with data in any capacity, pushing yourself to learn about subjects that push your comfort zone will only make you more knowledgable about the data analysis process.

Fundamentals vs. early polling

One key finding from the model is that polls conducted in the first half of the year during the election cycle are a pretty weak predictor of results. On the other hand, fundamental measures like the president's approval rating, GDP growth, and whether there is an incumbent running for re-election are much better predictors. This chart shows the difference between poll results and fundamentals for predicting the outcome in 1992:

Source: The Economist

The model primarily relies on these fundamental indicators, but over time the polls become a better indicator for predicting the outcome. In the last week leading up the election in November, more weight is applied to the polls than the fundamentals.

This visualization below shows that early polls tend to overestimate a party's share of the vote (in this case the Democratic share) compared to fundamental indicators. As you get closer to election day, however, the polls start to become a better predictor:

Source: The Economist

Overfitting data

One downside The Economist points out with other models that try to forecast the presidential election is that equations are created that overfit to historical data points. Think about it: if you tried to create an equation to predict who would win the NBA championship in 2020 based on 1990s data, you may create an equation that leans heavily to the Bulls. Unfortunately, Michael Jordan isn't playing anymore and the 2020 NBA season is now being played in a bubble in Orlando.

Had to mention Jordan somewhere in this post :)

Have you ever wondered what the underlying meaning of your dreams are? Chances are you may have tried Googling something like "What does it mean to dream about [INSERT DREAM]." In The Shape of Dreams, Federica Fragapane answers this very question of what people around the world dream about by using Google Search queries from 2009 to 2019. Federica uses a mix of data storytelling and data visualizations to show what we collectively dream about based on what we search for in Google. The key takeaway: someone on the opposite side of the world probably has similar dreams as you showing that we are more connected than we think.

Shape of Dreams

Importance of data visualization

Data visualizations are just as important (if not more important) than the number crunching and analysis of the data itself. While Excel and Google Sheets are the standard tools for analyzing data, there are a variety of tools for creating charts and visualizations such as Tableau, Google's Data Studio, and Microsoft's own Power BI.

Source: Melting Asphalt

I've posted about the power of data visualizations in the past including New York Times' data bootcamp (that teaches data visualization), data visualizations to model COVID-19, and my own class on creating a data-driven presentation. Creating meaningful data visualizations requires you to understand the technical aspects of aggregating data and actually creating the visualization itself. It also requires the creative side of telling a story around the visualization. Federica does an amazing job of telling a story about the Google Search queries about what we collectively dream about as a society.

Structure of Shape of Dreams

I really like how Federica gives the reader two options: read the story about the data where she takes you through the visualizations with key takeaways and also gives you the ability to explore the data yourself. In the first chapter, she simply shows the most common types of dreams by keyword across different languages:

Who doesn't dream about their teeth falling off?

When you explore the data, you can use the arrow keys to see the dreams people search for by language and by year which leads to some interesting results:

Varying the types of visualizations

As you go through chapter 2 and chapter 3, you see Federica utilizing different types of visualizations to better tell the story behind the dream Google Searches. A motif she uses across the visualizations is a flower's pedals, and you're able to interact with the pedals in chapter 2. To summarize what I imagine to be a extremely large dataset, we see some general categories of dreams in chapter 2:

Federica discovers that searches in English, Portuguese, and Spanish aggregate up to dreams about animals, family, and relationships.

You'll see a more traditional time-series chart in chapter 3 showing the popularity...