Excel is cool, but we can do better.

Automating repetitive work with Python versus VBA. Use excel as a 'front-end' and Python (or your language of choice) to build consistent, efficient analysis.

In my previous role, excel was pervasive. Some of you are in the financial services industry (investment bankers, private equity / hedge fund analysts, etc.) and likely live in excel, and for those in other disciplines, I’d be willing to bet you’ve used excel or some of Microsoft Office’s other applications.

Excel is a valuable tool - we can build analysis like valuation models, construct databases like a CRM, and output summaries of data with charts / tables. In my opinion, the greatest values of excel are its:

1. Ubiquity - in corporate work, you can be confident that your colleagues / counter parts at other businesses can work their way around a sheet.

2. User friendliness - (I know many of you might be shaking your head saying “Leon, I’m not an excel expert in the slightest”); but overall, as opposed to building SQL queries, excel is a really user-friendly way to explore and format data.

This is an important point because I think it demonstrates that excel is a great front-end tool.

To use a software term here, front-end work (versus back-end work), is focused on the way we present data to users. In a strictly software sense, front-end developers design the way a user sees and interacts with a particular product / application. Front-end web developers for example are using languages / tools like HTML, CSS, JavaScript & React to present information to users in the most user-friendly and pleasant way possible.

So in an analogy to this concept, I think excel makes for an awesome front-end corporate tool. Most people know how to use it, and it allows us to explore data with ease.

So what is excel on its own not good for? I argue:

1. Large complex projects: In my investing role I often was working with excel documents with literally millions of line items representing each individual transaction a business invoiced customers for. Running an INDEX-MATCH pattern on massive datasets like this is an expensive task for your CPU.

2. Statistical modeling: In particular, Monte Carlo simulations that rely on the generation of a significant amount of random numbers. Excel is not nearly as efficient as Python in generating random numbers & assigning them to variables.

3. Repetitive tasks: Anything you’re doing again and again in excel that can be automated with a few lines of code.

In other words, for large, statistically heavy projects, excel is not the best back-end tool. Backend development is all about constructing the interaction between an application and its dataset as well as the process of architecting datasets and working with data to build desired functions / results.

Microsoft Office has an answer to some of these problems: Visual Basic Applications (VBA). VBA is a programming language provided specifically for the Microsoft Office suite. For the programmers in the chat, VBA can just about fully support Object-Oriented Programming, so you could do just about anything imaginable with it. However, syntactically coding in VBA is like walking through a sea of legos and debugging VBA code is about as bad as it gets.

VBA lets you build awesome automations for Excel, PowerPoint, and Word (also the other Microsoft Office products), but this article provides a much less painful solution: XLWings.

XLWings is a free (a pro-version exists) open source Python library that lets you pass data between excel and a Python script. If you’ve been putting off learning how to code and your job involves a lot of excel, let this be your wakeup call. XLWings lets you seamlessly pass data between your excel workbooks and Python scripts.

Supercharge your workbooks by interacting with Python through xlWings

For those who have the programming basics down and are set up with their own IDE, the documentation to get started with XLWings is here.

For those who are shaking their head at ‘IDE’ and Python, I’ll be sharing lots of resources to getting started with programming. Over the coming weeks I plan on writing an article on how I approached self-teaching myself Python / JavaScript, some of the cool resources I’ve come across so far in my Masters program at UChicago, and like this article, the tools I’m really excited about using in my day to day.

Calling all developers, some project ideas that I’ve been thinking about using XLWings and the like:

1. In combination with market data APIs like Polygon.io or AlphaVantage, calling these data providers from Python and outputting historical financials & inputs required for high-level financial models. Using these historicals along side industry average projections to run ‘instant-models’ on a large set of tickers effectively as an ‘in the weeds’ screening tool for value investments.

2. Something I’ve already spent some time on: using OpenAI’s GPT-3 to craft unique sourcing emails for Venture Capital / Private Equity funds. Technically Excel isn’t a necessary tool for this process - I set things up in a SQL database, but in practice a product like this would likely be used in conjunction with investment analysts who will manually source businesses, upload sourcing / firmographic data to a spreadsheet, which can then be pulled into an outreach program via XLWings.

3. Monte Carlo / Heavy Stats based financial modeling: I recently worked on a really cool statistical modeling exercise for one of the companies I do contract software development for. For those who haven’t worked with Monte Carlo simulations before, this is simply the process of building simulations by generating random numbers (usually lots of them). You could for example say, I think there’s a 20% chance drug A makes it to stage I clinical trials, 5% to stage II, 1% to stage III, some assumptions about market size, etc., etc. and use random numbers to generate a hypothetical outcome for a new pharmaceutical coming to market.

If any of these projects interest you, shoot me an email: Leon.Vortmeyer@gmail.com. I’m always excited to chat / kick the proverbial can.

To all my readers, thank you for subscribing! To many many more posts and collaborative thinking! If you’re excited about this series, please share with your friends!

Until the next,

Leon