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...

xlwings Newsletter

Everything about xlwings and Python in Excel