Blog
May 20, 2022
A New Way to Automate Google Sheets with Python
Posted by Felix Zumstein
It’s been possible to automate Google Sheets via the Google Sheets API for many years and there are multiple Python packages available that wrap this API: there’s gspread, pygsheets, ezsheets, and Google’s own google-api-python-client. All these packages talk from Python to Google Sheets and require you to create a Google Cloud Platform project, download a credentials file, and potentially share your spreadsheet with the user from the credentials file. This can be a relatively painful... Read More...February 4, 2021
What Makes Excel's Lambda Functions so Awesome (and what doesn't)?
Posted by Felix Zumstein
In December 2020, Microsoft announced Lambda Functions as a new Excel feature. Lambda functions are an exciting way to define custom functions (a.k.a. user-defined functions) by only relying on Excel formulas, without having to use VBA or writing an add-in. In this blog post, we’re going to see how Lambda Functions work and why they are so awesome. It’s not all roses though: we’ll also look at the current limitations of Lambda Functions and how... Read More...June 3, 2020
5 Python Libraries for Reporting and Factsheets
Posted by Felix Zumstein
Python is a popular tool for all kind of automation needs and therefore a great candidate for your reporting tasks. There is a wealth of techniques and libraries available and we’re going to introduce five popular options here. After reading this blog post, you should be able to pick the right library for your next reporting project according to your needs and skill set. Table of Contents Overview Pandas xlwings Plotly Dash Datapane ReportLab Conclusion... Read More...July 5, 2019
Dynamic arrays in Excel with Python and xlwings
Posted by Felix Zumstein
The days of Ctrl-Shift-Enter and You can't change part of an array are finally over! Say hello to the new dynamic arrays that Microsoft has now (July 2019) rolled out to a broader user base: their Office 365 subscribers who are on the monthly channel. To check if you are on the monthly channel, go to File > Account and see if it mentions Monthly Channel (Targeted) under About Excel. If you are not, then... Read More...July 15, 2017
Tired of Excel's VLOOKUP? Use SQL queries on Excel ranges!
Posted by Felix Zumstein
If you have the free xlwings Excel add-in installed, you can query your data using standard SQL statements, like this: Overview What is xlwings? The sql formula Behind the scenes What is xlwings? xlwings is a Python package that allows us to write Python functions instead of VBA code. It recently added a full blown Excel add-in that brought us “xlwings extensions”: These are Excel functions that are available as soon as we install the... Read More...October 17, 2016
Unit Tests for Microsoft Excel
Posted by Felix Zumstein
Many (business) users use Excel to create full blown applications. However, unlike applications developed by professional software developers, Excel tools fall short of most software development practices that are considered minimal standards. This introduces risks that can lead to reputational damages and substantial financial losses (remember the «London Whale»). This blog post introduces the concept of test-driven development before it shows you how to write an automated unit test for a VBA function. All we... Read More...September 23, 2016
How to fetch Market Data in Excel like a Pro
Posted by Felix Zumstein
Get market data from Quandl and other data providers directly via cell formulas: Overview Bloomberg’s infamous BDH formula Enter xlwings Building QDH Bloomberg’s infamous BDH formula Tell a financial analyst to download market data into Excel, and the odds are that they will start typing the following formula in an Excel spreadsheet: =BDH("TICKER", "PX_LAST",...). This formula stands for “Bloomberg Data History” and will download the end-of-period prices of the indicated ticker directly into your spreadsheet... Read More...