6/21/2023 0 Comments Master key spreadsheet![]() PCW: What are some tips for someone who wants to get into your line of work as far as education or skills to acquire? Security concerns are the only reason I’m now using Microsoft 365. 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). Jason Moore: I’ve worked in finance 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. 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. 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. 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). The data is messy! You’d be surprised how many ways Best Buy is spelled, or how often shopping centers change their name. It’s at this stage that Excel becomes a critical differentiator. 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. The goal is to understand which retailers are expanding/contracting and which landlords are doing better/worse. ![]() ![]() Jason Moore: Let’s say you wanted to track the tenants at 3,000 major shopping centers across the U.S. PCW: Can you describe the kinds of things you can use Excel to analyze? Tableau comes close in certain situations, but Excel wins 9 of 10 times. We use Python and Alteryx to prep massive datasets, but nothing beats Excel for that “last mile” of delivery. 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. PCW: Do you use a lot of VBA other scripting or programming languages in Excel? Q: Is it fast enough for you? A: Never, which is why I use five PCs. 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: Would you say there’s any particular functions that are the most popular tools you use in Excel more often? 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. 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. ![]() 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. 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. The $50 entry fee has been temporarily reduced to $25 to celebrate the sport’s ESPN 8: The Ocho moment. If you’re interested in testing your mettle against Excel masters, you can register for the 2022 Financial Modelling World Cup Open, taking place beginning October 8. Editor’s note: This story first ran on February 4, 2022, but with ESPN airing an Excel esports: All-Star Battle on ESPN 8: The Ocho over the weekend of August 5, we’re republishing it for spreadsheet enthusiasts who missed the fascinating discussion the first time.
0 Comments
Leave a Reply. |