Dear Analyst

With the last episode for 2020, I wanted to take a look back and pull out some of the main themes and topics from the podcast. As people rushed to pick up new data skills to adapt to our changing environment this year, I think the precedent has been set for how one can learn almost anything from home. You may have picked up Excel and data analysis skills this year. What are some skills you should think about for 2021? This episode provides five skills for data analysts to consider adding to their toolbet in 2021.

First, a look back at some of the main topics from 2020:

1. Excel errors will still happen

Aside from the latest features Microsoft or Google releases for Excel and Google Sheets, the only spreadsheet news that makes it to mainstream media are mistakes that lead to a financial loss.

It all started with episode 38 where I spoke about the JPMorgan Chase trader that caused a $6.2B loss. Given the popularity of this episode, I did a few follow up episodes including episode 40 on the two Harvard professors who created a report that may have led to incorrect austerity policies. There was also episode 49 all about how Tesla might have underpaid by $400M for the acquisition of SolarCity. Many of these stories can be found on the EuSpRIG website (which I covered in episode 47).

These stories go back to the early 90s. After 30 years, I expect these stories to continue making headlines. When analysts are lazy, put under time constraints, and are hastily putting together spreadsheets, these errors will undoubtably occur.

2. Excel's custom data types, what's the hype?

Excel's custom data types came onto the scene a few months ago. The Excel community raved about the new feature, but I think the jury is still out on real use cases and audience for this feature. Touted as going "beyond text and numbers," the features is still in active development and is still missing some crucial features. I did deeper into this feature in episode 51. A more recent feature announcement called LAMBDA may give analysts the ability to extend Excel beyond what it's meant to do. Just like the custom data types feature, we'll wait to see how this feature actually gets used in the workplace.

It's rare to see new features for Excel completely change the way analysts do their work. This video from MAKRO makes a good point about new features kind of dumbing down the existing features that power users have come to love (and master).

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

3. Google Apps Script for the win

This year was all about Google Apps Script for me. From doing some heavy VBA scripting during my financ...

When I worked in FP&A, I discovered that VBA could automate a lot of tedious tasks I was doing in Excel. From creating charts to formatting data, I realized that there possibilities with VBA were endless. As I started using Google Sheets more, I found that Google Apps Script offers similar functionality to extend what your Google Sheets can do. The specific use case I wanted to solve was syncing data to and from my Google Sheets from other workplace tools. This episode talks about how I picked up Google Apps Script, and how you can level-up your skills to be more data driven in your job. Original slides for this episode are here.

This episode was adapted from a talk I gave for Promotable.io's "Breaking into data" series. The original presentation I gave is here.

Starting with the macro

The way I started with VBA was simply recording a macro. You hit record, do a bunch of stuff in Excel, and then see what code is outputted from those actions you took. For example, this little script selects the range A1:A6 in your spreadsheet and applies a right-align formatting to the cells (among other things):

What's nice about these macros is that you don't have to know how to write code. At least initially. Just by doing stuff in Excel, you can see how VBA interprets those actions in the VBA editor (as shown above).

The first thing I tried to do with VBA back in the day was simply select some cells. This is the Range("A1:A6").Select portion of the script above. Then you can hit "play" in the macro, and Excel will select these cells for you without you touching your mouse or keyboard! The first time I saw this happen in my Excel file was a mind-blowing event. I realized I could control my spreadsheet just from pushing play.

Doing more with Google Sheets with Google Apps Script

Google Apps Script is the VBA of Google Sheets. Since Google Sheets has an extensive API, you can access pretty much any part of the Google Sheets UI. The reason I like using Google Apps Script include:

* It's free* The language looks and feels like Javascript* Lots of built-in services to access not only Google Sheets, but also Gmail, Google Calendar, and other products in Google Workspace (formerly G Suite)

I was worried that learning Google Apps Script would be difficult since it's different from VBA. I started with simple tutorials like this one (teaches you how to programmatically create a Google Doc file and send you the link via Gmail). Google is clearly trying to target "citizen developers" like myself who don't really have any forma programming experience but know just enough to be dangerous. Tutorials like the one below make it seem like anyone can use Google Apps Script and take advantage of its robustness:

https://www.youtube.com/watch?v=JE4pF40ujh8&feature=emb_logo

Just like I first selected a range of cells with VBA, I did super simple tasks and workflows with Google Apps Script like selecting some cells or applying some formatting to numbers.

The Google Apps Script editor

Data transformation and munging

The first time I heard the term "munging," I thought it was some kind of disease. This is all data munging is:

Sometimes you have a long list of data in a column and you want to pull out a specific string of characters in the cell and put the output in another column. I've done this countless times and was reminded of this task after watching this meetup on Power Query vs. Excel formulas from Danielle Stein Fairhurst (I talked at length about Power Query vs. formulas in the previous episode about custom data types). This episode dives into the nested formula and other solutions to extract text from the middle of a cell. You can copy the Google Sheets from this episode or watch the tutorial below.

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

World Happiness Report data

Before I get into the solution, wanted to talk a bit about the data set used in this episode: The World Happiness Report.

Source: Action For Happiness

This report aims to measure the state of happiness around the world. The first report was published in 2012. Five years later, the report ranked 155 countries by their happiness levels and was released at an event celebrating the International Day of Happiness (March 20th) at the United Nations.

What I find interesting about the actual poll is that the "main life evaluation" question asked to respondents uses a mechanism called the Cantril Ladder:

Please imagine a ladder with steps numbered from zero at the bottom to 10 at the top. The top of the ladder represents the best possible life for you and the bottom of the ladder represents the worst possible life for you. On which step of the ladder would you say you personally feel you stand at this time? (ladder-present) On which step do you think you will stand about five years from now? (ladder-future)

I think it's a report with an audacious goal of quantifying and standardizing happiness levels around the world. The Sustainable Development Solutions Network has published the raw CSV data from 2015-2019 on Kaggle.

The Problem

I edited the data set from 2019 a little to demonstrate the problem we need to solve. In the screenshot below, you'll notice in the Data column the Overall Rank, Country, and Score values are all combined into one value:

Our goal is to extract "Finland" from cell B2, "Denmark" from cell B3, etc. We want to put the country into an empty column, and then we can hide the Data column or completely delete it assuming we copy and pasted the values in our final output column.

Detecting the pattern

You'll notice the pattern with how the data is formatted. The country is contained within opening and closing parentheses. The rank of the country precedes the country, and the overall happiness score is at the end of the value preceded by a dash. We can combine the MID() and FIND() functions to get just the country in between the two parentheses.

Using MID() and FIND() to extract text from a cell

I created a new column next to column B called Extract Country and put this formula in this column to extract the country:

=mid(B2,find("(",B2)+1,find(")",B2)-find("(",

To much fanfare, custom data types in Excel were released late last month (October 2020). This feature started off as a way to see "rich data" relating to stocks and geography in a cell, and now Microsoft is letting you define your own data types. Perhaps you want to see all attributes for a customer such as the location, region, and account rep in a cell without seeing these columns in your worksheet (see screenshot below). In this episode, I want to dig deeper into how this feature fits into existing workflows. More importantly, I want to know who is the audience for Excel custom data types?

Source: The Verge

Redefining the data engineer role

I can't tell if data engineer who are working in SQL are rejoicing or rolling their eyes at this new feature. One one hand, a data engineer might be happy because if a business user in their organization needs to see additional data about a customer, they don't need to contact the engineer to add this to the SQL database. In theory, the business user can add this column to the "Customers" data type in Power Query and that column is now available for anyone to use.

On the other hand, perhaps the data engineer needs to learn how to use Power Query and Power BI now because no one in the organization knows how to do the right types of joins in Power Query. Granted, setting up joins and cleaning up data in Power Query is probably easier than writing an Airflow DAG. If you're an analyst who is excited about using this feature, perhaps you'll need to up-level your skills to become a proficient user of Power Query first and learn how to use joins:

https://www.youtube.com/watch?v=-kle5a7vbRA

Who maintains these data types and who uses them?

My skepticism for this feature started as I saw people doing tutorials on how this feature works. The end product is quite tantalizing. But there are two separate audiences Microsoft needs to convince to use this feature: the admins (most likely the data engineers) and the business user (anyone with "analyst" in their job titles).

Microsoft has made it clear that this feature is still in development and the feature has only rolled out to a subset of Office 365 subscribers. Microsoft most likely released this feature because the most ardent users of the stocks and geography data types probably sent feedback saying they want to create their own data types. If you are one of those users, I'm super curious about what your use case is and how you are using the feature. I see two camps of people:

The admin

If your organization deploys this feature, undoubtably there will be someone who has to maintain all the data types. Here is the workflow for how the admin might maintain a "Product" data type:

* Set up the data connection with a database table that contains all your company's products* Do some data cleanup in Power Query to ensure the right columns show up in the data type* Merge with other tables as necessary to get all properties a business user may want to see* When a new property is needed, decide whether this change should be made in the underlying database or done through another merge in Power Query* Set up a cross-functional meeting so other business users know that a change may be coming to the organization's data types* Start at step 2 again and rinse and repeat

This little podcast/newsletter started as a little experiment last year. I never thought I would make it to episode number 50, but here we are! Thank you to the few of you out there who listen/read my ramblings about spreadsheets.

I decided to give you all a break and invite my first guest to the podcast: Shawn Wang (aka @swyx). Shawn currently works in developer experience at AWS, but has a really diverse background (check out his site to learn more). I've mentioned Shawn in previous episodes (25 and 49) and was honored he agreed to be the first guest on Dear Analyst. We dig into a variety of topics including negotiating your salary, Javascript frameworks, creating, and whatever else tickled my fancy.

Shawn Wang (source: swyx.io)

Becoming a Jedi

I was particularly interested in a 4,000-line Excel VBA script he wrote while working as a trader in a previous job. You can learn a lot about someone from looking at their code, and that's exactly what we did during this episode. Shawn was kind enough to share a VBA script he built back in 2012 for his team to price billions dollars worth of derivatives. I honestly don't understand 90% of this script, but Shawn walked through a lot of the derivative concepts he had to translate into this VBA script. You can see some of his thoughts about this script in the Tweet thread below:

https://twitter.com/swyx/status/1327041894853922816

I think it's amazing that his bank relied on traders using this homegrown script to price everything from interest rates to mortgages.

One of the main takeaways from our walkthrough of this script is that the code isn't pretty. Shawn had a problem that he needed to solve, picked up the tool that could solve that problem, and started hacking away at the solution. Shawn shared a story from his senior trader at the time on building tools for yourself:

One of the rights of passage for becoming a Jedi is building a light saber. Once you have the light saber, you just use it, and stop building it.—Shawn Wang

For the benefit of other traders out there, Shawn also believes in learning in public. Releasing this script is just one example of that. By producing content and acknowledging gaps in your knowledge, you'll learn faster than being a "lurker," as Shawn puts it.

No-code is a lie

We talked a bit about an article he wrote called No code is a lie, and how programmers sometimes need to get over themselves. Programmers may get caught up in the style of their code, but the end-user just cares about whether the thing works and solves their problem.

After finance, Shawn moved from Excel and VBA scripts to Haskell, Python, and Javascript. He still has a soft spot for Excel, however. With Excel, you have your database and user interface right in front of you. This not only gives people an easy way to create, but makes creation more inclusive.

Excel is creation over code. I don't define myself as coding, I define myself creating.—Shawn Wang

Taming the Javascript community

Shawn got really involved with the ReactJS community and eventually became one of the moderators of the subreddit after

I've been posting quite a few episodes about spreadsheet errors as of late. The two Harvard professors and their faulty economic policies, the JPMorgan whale trader, and the EuSpRIG community who tries to catch all these errors. This is a quick story about how Tesla's acquisition of SolarCity in 2016 was botched due to a spreadsheet error involving double-counting SolarCity's debt. You can read more about how this "computational error" was communicated to Tesla and SolarCity in the S-4 here. Elon must've been a happy man after the deal.

Two friendly CEOs

It's 2016, and Tesla is in talks to buy SolarCity, a solar panel company. The deal is complicated because of two factors:

* Elon is the largest shareholder in both Tesla and SolarCity* Lyndon Rive, SolarCity's CEO, is Elon's first cousin

To make the process transparent and not make it seem like Elon was forcing the deal to happen between the two parties, SolarCity set up a special committee of outside advisors to help with the deal. One of the advisors was Lazard, the investment bank.

Lazard gets a bunch of spreadsheets from SolarCity and starts doing what the typical discounted cash flow analysis for the deal. SolarCity has to provide Lazard with some of their spreadsheets, but it turns out their spreadsheets contained one major flaw:

The spreadsheet double-counted some of SolarCity's projected indebtedness.

As such, Lazard valued SolarCity's equity between $14.75-$30.00 per share instead of $18.75-$37.75 per share. This resulted in SolarCity being discounted by $400M in the deal.

Source: ValueScope

Now Lazard is a big investment bank, and they said that after they discovered the "computational error," it wouldn't affect the terms of the deal. If you're a shareholder in SolarCity, you would probably think that a 16% discount on the original deal terms is a big deal (the final purchase price was $2.6B).

Whose spreadsheet is it anyway?

Was Lazard responsible for detecting this double-counting error in SolarCity's spreadsheets before building their discounted cash flow analysis? Or was SolarCity responsible for this garbage-in-garbage-out scenario?

Given that SolarCity set up this special committee of 3rd-party advisors just to make sure the deal was valued correctly and transparently, I can see the argument for Lazard being responsible for auditing SolarCity's spreadsheets. As with many of the other spreadsheet errors mentioned previously, time constraints, laziness, and human error ultimately are responsible for these errors.

Other Podcasts & Blog Posts

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

* ShopTalk Show #432: SWYX

For spreadsheet newbies, number formatting may seem like a pretty innocuous matter. As you become more familiar with Excel or Google Sheets, you'll find that improper number formats will lead to formulas that don't output what you expect or formulas that straight up don't work. This episode explores what happens when you unknowingly have numbers formatted as text. I also explore the various ways you can try to debug the errors that come from numbers formatted as text. You can copy the Google Sheet here for this episode.

AutoSums and avocados

The topic for this episode came from a question about AutoSum in the Microsoft Excel community forum which has close to 40,000 views and 25 replies (some of them are quite spicy, I might add). I'll be referencing this thread quite a bit in this episode since some of the best ideas come from--you guessed it--the comments. Props to Excel MVP Sergei Baklan for jumping into this thread and trying to help answer a somewhat ambiguous question.

No one: Spending Friday nights perusing the Microsoft Excel forums for interesting questions :)

The data set for this episode is a fun one: avocado sales across different cities (learn more about the dataset on Kaggle here). The prologue for this data set is amazing and will ring true for all millennials out there:

It is a well known fact that Millenials LOVE Avocado Toast. It's also a well known fact that all Millenials live in their parents basements. Clearly, they aren't buying homes because they are buying too much Avocado Toast! But maybe there's hope… if a Millenial could find a city with cheap avocados, they could live out the Millenial American Dream.-Justin Kiggins, Product Manager, Chan Zuckerberg Initiative

One can only make an episode about formatting numbers in Excel/Google Sheets so interesting, so this was my best attempt. And we go on!

Numbers formatted as text mess up formulas

The first thing you'll notice with numbers formatted as text is that they will mess up formulas by giving you an output you would not expect. For instance, in our avocado dataset, cell C53 is simply a sum of all the "numbers" in column C, but the result of the SUM formula is 0:

How is this possible? If you click on the column C header and go to Format->Number, you'll notice that all the cells in this column are formatted as "Plain text."

This means any numbers you type into the cells in this column will be treated as text. Since these cells aren't formatted as numbers, Google Sheets doesn't know how to treat these values which means the SUM formula is trying to sum up a bunch of text values. In Excel, this is similar to the cells being formatted as "Text":

Detecting cells that are formatted as text

If you are really diligent, you can use the ISTEXT formula to test whether a cell is indeed formatted as text. However, when you inherit an Excel model, you are just hoping that the previous modeler did things correctly and you can just plug in numbers and move on with your life. How can you figure out if some cells are improperly formatted before pulling your hair out?

Unfortunately, no answer exists. If you know of one, please comment below. For new spreadsheet users,

The episode about how a rogue trader cost JPMorgan Chase $6.2B due to an Excel error struck a chord with folks. This episode explores three horror stories (and a recent one related to COVID) where people made simple spreadsheet errors and cost their companies and organizations millions of dollars. I don't get too in-depth with the actual spreadsheet error in each story like I did with the JPMorgan Chase story, but do provide a quick analysis and lessons to be learned from each story. At the end of the day, these stories are not about the deficiencies of Excel itself, but rather human error and oversight.

EuSpRIG for the win

All the stories below come from the EuSpRIG's website where some of the stories go back to the mid-90s. I came across the European Spreadsheet Risks Interest Group (EuSpRIG for short) watching a webinar about auditing Excel workbooks by Paula Guilfoyle. During the webinar, she mentioned these Excel "horror stories" on the EuSpRIG website, and low and behold there's a rich archive of all these horror stories which the team has consistently been updating for what looks like to be over 20 years. You know the group and the content must be legit since the website still looks like a site from the late 90s:

EuSpRIG website

In all seriousness, Patrick O'Beirne (chair of EuSpRIG) has created an amazing community and resource all spreadsheet users should peruse to learn from past spreadsheet mistakes. Nicole Kobie at Wired recently wrote a great story about these "Excel warriors," and I think this quote from the story highlights the main issues all you analysts out there should heed:

Part of the challenge of this work is that spreadsheet defenders must not only be Excel experts but know the industry that they’re working in.-Nicole Cobie, Wired

From what I can tell, all the horror stories on the EuSpRIG website (and really any time you see a story in the media about a spreadsheet error) highlight something negative that happened to the company. These type of stories are the only ones that reporters pick up and lead to clicks, ad dollars, and that whole thing. Rarely do you see a story of masterfully crafting a spreadsheet formula that leads to a positive result for the company.

Story #1: $2.6B erased from Fidelity's Magellan fund

This story originated from a thread in an e-mail listserv from 1995 called The Risks Digest. Stepping back for a bit, it's amazing that these spreadsheet mishaps were "documented" this far back on Microsoft Excel '95 (I learned Excel on version 2003 which seems lightyears ahead of 1995):

Excel 95, Source: Version Museum

Story goes like this: In November 1994, Fidelity was planning on making a distribution from their fund in the amount of $4.32/share. Fidelity cancelled the distribution because a tax accountant forgot to put a minus sign in front of a $1.3B net capital loss, which resulted in a positive dividend estimate that was off by $2.6B.

Analysis & lessons learned

Can you imagine being the tax accountant having to tell your boss that you forgot to put a minus sign in a cell?

I wrote a lengthy blog post comparing using Google Sheets as a project management "platform" relative to other dedicated project management software on the market. This post explores how to build a project management platform in Google Sheets. The functionality in this Google Sheets tool rivals that of some of the more popular project management platforms on the market (e.g. Microsoft Project). Here's the Google Sheet with the workflow fully built out.

Source: TaskRay

An unconventional tool for project management

Google Sheets is not the "platform" you might think of when it comes to typical project management workflows. In episode 43, I talked about how spreadsheets can be "extended" beyond its core use case (e.g. accounting, financial analysis) to tools and application. This project management "tool" is a perfect example of this concept.

Google Sheets might be the first thing your team reaches for because it's free and allows real-time collaboration. With some basic formulas, you can build some pretty advanced functionalities and workflows similar to other SaaS tools and software. Here are the main features of the Google Sheet and how it works.

Step 1: Creating a task dependency column

Most projects have tasks with dependencies. In column A, we have all our tasks. In Column B, we have the name of the task that the current task depends on. Instead of copying and pasting a task from column A into column B every time we want to change what the dependent task is, we can use a data validation to get a dropdown of all the tasks in column A:

Now, any task that shows in column A shows up in the dropdown in in column B:

The issue is that if you change the spelling or name of a task in column A, it won't carry through to the dropdown to the data validation cells in column B. So if "Instructor Shoot" changes to "Instructor Shooting," cell B3 will still say "Instructor Shoot." You have to manually click the dropdown again and select the new "Instructor Shooting" dependency. Not the end of the world assuming your dependencies don't change often, but could be annoying if you have projects with tasks and dependencies that change often.

Step 2: Calculating task end dates

Column G, or the Task End Date, is simply the Task Start Date plus the Duration (Days) column. While this seems like a trivial formula, it's actually not that "easy" in other project management platforms. The reason is that you are mixing a date format with a number format. In other platforms, you have to create a special formula column that uses some special function like DATEADD(). In Google Sheets, it just kind of works with adding a number (representing the number of days the task takes) to the Task Start Date:

Step 3: Calculating task dependency start and end dates

Moving over to columns H and I, we want to calculate the start and end dates of each task's dependency task. The reason it's important to calculate these dates is so that we know when the current task can start. We are assuming a basic Finish-to-Start dependency type where the current task cannot start until it's d...

When you need to capture some data in a structured way, you'll open up an Excel file or Google Sheet and just start throwing data into the spreadsheet. Not much thinking; just copy and paste. As that dataset grows, the original structure you had set up for that spreadsheet may not be ideal. Specifically, the dataset is not ideal for putting into a PivotTable. Long-term, I'd argue that all your spreadsheets should be structured in a way that's suitable for a PivotTable (which makes it ready for storing in a traditional database). This post explores how you can structure a dataset that looks like 99% of data out there into a structure you can analyze in a PivotTable. Link to the Google Sheet is here.

Video walkthrough of Google Sheet:

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

Why this is important

Telling someone that their data should be structured is a platitude like "such is life" and "forgive and forget." Let's be more specific in how this statement can impact your work.

To be specific: 9 times out of 10, structure your data so that it can always be analyzed in a PivotTable.

Consider this scenario:

* Your accounting team needs your group to start forecasting expense for next month's budget* You start gathering the data and throw it into a spreadsheet* Every month new data gets added to the spreadsheet, and perhaps the CFO wants to get more granular analyses on the forecast* You start adding additional columns to the spreadsheet and perhaps summary tables in other sheets in the file* Other teams now need to see your data to understand how your team's decisions will impact their decisions* This spreadsheet ends up being too hard to maintain, so there's an internal project to put this data into a real database (some ERP solution)* One quarter of planning goes by, and another quarter for implementation* 6 months later, the business has changed, the structure of the database needs to be adjusted, and the data engineer role still needs to be filled

This concocted scenario is quite extreme, but the key lesson is this:

Focusing on the schema and structure of your spreadsheet today takes time and requires you to think about how your data will be used and maintained in the future.

U.S. public food assistance dataset

I've started browsing Kaggle's to find interesting datasets recently, and this one caught my attention since it looks at spending and household participation related to a public food assistance program called SNAP. As the creator of the dataset discusses, there are many issues with collecting government datasets. Data is spread out across different agencies, there are multiple formats, and data is sometimes aggregated. This makes consolidating the data a pain. These problems may sound familiar if you're working at a large organization.

The "Raw" sheet in the Google Sheet simply shows the cost, households participating, and total people associated with the SNAP program for the 2019 fiscal year across four states (CA, IL, LA, NY):

In your organization, this could be sales data, headcount data, COGS, whatever. The key thing about this dataset is that you have all the numbers organized by month across the top.