Most people view Microsoft’s Excel as a simple “spreadsheet program” that’s rapidly being eclipsed by free web-based apps that run on any cheap laptop.
Well think again. Modern-day Excel is an advanced analytics tool that can push today’s fastest hardware to their limits, and it’s sexy enough that it rates its own eSports coverage. No joke — thousands tuned in live to watch the Financial Modeling World Cup that was broadcast by ESPN.
Intrigued by the interest of “gamers” and curious to see just how hard advanced Excel users are pushing the application, PCWorld decided to interview spreadsheet superstar Jason Moore. Moore is the head of analytics and data at an investment firm, and semi-finalist who won cold, hard cash in December’s Financial Modeling World Cup. Moore is also the same Excel expert who provided PCWorld with a workbook that we used to test the the latest and greatest laptop CPUs.
To find out how much hardware you need to push spreadsheets to their limits, what it’s like to be an Excel esports player, and just why Excel is so powerful, read on.
PCW: A lot of people have a hard time believing that Excel is an esport — is this for real?
Jason Moore: I still can’t believe it, but the comments from the> 300,000 YouTube viewers that watched the FMWC finals don’t lie. The challenges were engaging and well designed; and everyone can appreciate a well-designed Excel formula.
PCW: How did you get into Excel as an esport?
Jason Moore: LinkedIn advertisement that asked “Are you the go-to Excel person at work?” Uh ah yeah, sign me up!
PCW: What do you do for a day job?
Jason Moore: The company I work for invests in commercial real estate stocks (apartments, hotels, shopping centers, etc.). As the Head of Data / Analytics, I build processes that analyze and present data to the team. For example, each night we track nightly rates at most US hotels for the upcoming six months. Excel is the final stop on that data’s journey to becoming actionable intelligence (ie, charts that help us understand which companies or markets are doing better or worse than expected).
PCW: So you actually drive Excel for a living. Can you describe what Excel is for people who still think of it as a simple spreadsheet application?
Jason Moore: My Excel skills are the differentiator between myself and others in finance, thus it absolutely pays the bills. Excel combines the basic functions of a database (eg, SQL), a programming language (eg, Python), and visual analytics (eg, Tableau) into one powerful platform. Don’t get me wrong, we utilize all those other tools and more, but Excel is the most productive.
PCW: Would you say there’s any particular functions that are the most popular tools you use in Excel more often?
Jason Moore: Besides the obvious basic functions (SUM, AVERAGE, etc.), my go-to functions are AVERAGEIFS, IFERROR, IF, FIND, OFFSET, INDEX-MATCH, and even old school “ctrl-shift” arrays to find weighted averages based on multiple parameters. Although I’d say VBA is the most powerful “tool” in Excel.
PCW: Do you use a lot of VBA other scripting or programming languages in Excel?
Jason Moore: In my opinion, VBA is what makes Excel the best programming tool in the world. Using the record macro tool, VBA allows anyone to become a programmer; it’s how I learned to code. From simple task automation, to complex IF-THEN process trees, interacting with external databases, and navigating websites before grabbing their underlying data. VBA can do almost anything while the familiar Excel grid is right there to display your data at each step. We use Python and Alteryx to prep massive datasets, but nothing beats Excel for that “last mile” of delivery. Tableau comes close in certain situations, but Excel wins 9 of 10 times.
PCW: There seems to be this tension between people who think a database is a better use than Excel for very large data sets. Do you agree?
Jason Moore: Define very large. To some a 500,000-record table (20 columns wide) is very large, but Excel crushes a database for extracting insight from that size table. Formatting, searching, sorting, filtering, and charting are all easier in Excel. We have a billion records in a SQL warehouse, but most of those records originate and / or terminate in a spreadsheet (eg, charts). Obviously databases are better for storing data, and vaults are better for storing money, but not when you actually need to use it. Excel makes it easy enough to pull just the data you need from almost any warehouse.
PCW: You provided PCWorld with a real-world Excel Workbook that we recently used to review laptop CPUs. Can you tell us a little about it?
Jason Moore: That workbook was built in March 2020 to track COVID cases, hospitalizations, deaths, and vaccination rates from thousands of jurisdictions around the world. It also tracks reopening indicators from Google, OpenTable, the TSA, etc. and sends everyone summary charts each morning. It’s half work, half personal to be honest. Investment professionals are trained not to trust anyone, so analyzing real-time data from various unrelated parties helped us filter out the noise fairly quickly, spotting trends and inflections before most others. Although helpful, it’s the only major spreadsheet I don’t want to survive forever; hoping to retire it soon.
PCW: Can you describe the kinds of things you can use Excel to analyze?
Jason Moore: Let’s say you wanted to track the tenants at 3,000 major shopping centers across the US. The goal is to understand which retailers are expanding / contracting and which landlords are doing better.
You write numerous Python scripts that scrape tenant lists from the property websites each night (property, tenant, unit, square feet), uploading them to a SQL database. It’s at this stage that Excel becomes a critical differentiator. The data is messy! You’d be surprised how many ways Best Buy is spelled, or how often shopping centers change their name. Sure, Python can attempt to handle it with some fuzzy math algos, or a developer can build a GUI in C ++… but I’m not a coder and good luck making either of those transparent to the end user (a financial analyst who works in Excel all day).
So we built spreadsheets that interact with the database (using macros), allowing a person to spot-check and correct various abnormalities once per week in just 10 minutes for all 3,000 shopping centers. Another macro assembles the cleansed data for each landlord’s portfolio, quantifying various metrics over time, compares it to company-reported data and our internal models, then sends an email with summary charts & tables and a link to the raw data should the analyst wish to dig deeper.
PS, Excel can also scrape the tenant lists from the websites.
The hardware you need to drive Excel hard
PCW: There’s a belief that Excel doesn’t need much hardware to run, can you describe the computer, or PCs you use for Excel? Such as what kind of CPU and how many RAM and storage?
Jason Moore: The latest build (June 2020) is a Core i9-10900X with 256GB of Samsung DDR4-2666 RAM and a 2TB Samsung 983 Enterprise SSD. Some spreadsheets run the CPU at 100 percent for hours, so it has an Asetek 650LS 120mm CPU Cooler. Having as many cores as possible and at least 64GB of RAM is key.
PCW:How large do your spreadsheets get?
Jason Moore: My current record is a 2.3GB stock-picking algorithm. It’s 95 percent calculations, not just a giant warehouse. I’d say my average size is 50 MBs. We had to upgrade everyone’s office PCs so my colleagues could open my spreadsheets; 16GB of RAM isn’t enough if you’re a heavy Excel user (or know someone who is).
PCW: Since you’re a “gamer,” do you run RGB on your builds?
Jason Moore: Haha, no, I had to Google what you meant by that. I’ll talk to Puget Systems, perhaps we can build one that lights up green for “buy” and red for “sell.”
Jason Moore: Never, which is why I have five PCs. I use LogMeIn to access four of them each day, along with dozens of scheduled tasks to fire programs at certain times.
PCW: What kind of monitor do you run and what resolution? Do you run multiple monitors?
Jason Moore: I’ve learned with Excel you want to be consistent with monitor resolution. If you set row height on a 2k resolution monitor it will look squished on a 1080p monitor. Given my numerous PCs I’m still running 1080p until we can switch everything at once.
PCW: What about keyboard and mouse? Mechanical switches or dome switches? Is it a full keyboard or compressed without 10-key?
Jason Moore: Your standard Logitech keyboard with 10-key and normal wheel mouse. You gotta have a 10-key.
Microsoft Excel versus Google Sheets, and what skills you need
PCW: Excel Vs. Google Sheets: Which wins, and will Sheets ever compete with Excel?
Jason Moore: I’ve worked in finance for almost 18 years and have never seen a Google Sheet. My kids use Sheets (middle and high school), but anyone serious about working in finance will want to move on sooner than later. There were moments when I thought Microsoft was dumbing down Excel and considered trying Sheets, but I ended up just using Excel 2010 for about 10 years (it remains their most stable version IMO). Security concerns are the only reason I’m now using Microsoft 365.
PCW: What are some tips for someone who wants to get into your line of work as far as education or skills to acquire?
Jason Moore: Build a spreadsheet to analyze your monthly spending, your daily electrical usage and / or solar production, local crime statistics, government budgets, inflation metrics, home sales, your investment portfolio, sports stats… you get the picture. Make a simple spreadsheet about a topic that interests you, then tweak it, add more data, record a macro, then more data. There are terabytes of free data available to everyone (eg, data.gov, NYC Open Data, BLS, BEA, Census Bureau, etc.). OzGrid.com is my go-to Excel resource.
PCW: What’s your best advice for someone getting into advanced Excel techniques?
Jason Moore: Purchase some case studies from Financial Modeling World Cup; solve them yourself then do it again six months later. Watch the YouTube videos from FMWC champ Diarmuid Early, or MrExcel’s Bill Jelen. Join a community of like-minded people on LinkedIn and share your modeling approaches.
PCW: Is there anything you think we should cover that we didn’t cover or — that you want people to know about potential Excel esports players or heavy Excel users?
Jason Moore: Excel doesn’t get enough credit because most people have never actually seen what it’s capable of, even those who have used it for decades. In a messy world it enables an ideal blend of man and machine, hard-coded history and an unscripted future, data and insight…
One of founding fathers of hardcore tech reporting, Gordon has been covering PCs and components since 1998.