Python for Excel

Chapter 12. User-Defined Functions (UDFs)

The previous three chapters showed you how to automate Excel with a Python script and how to run such a script from Excel at the click of a button. This chapter introduces user-defined functions (UDFs) as another option to call Python code from Excel with xlwings. UDFs are Python functions that you use in Excel cells in the same way as you use built-in functions like SUM or AVERAGE. As in the previous chapter, we will start with the quickstart command that allows us to try out a first UDF in no time. We then move on to a case study about fetching and processing data from Google Trends as an excuse to work with more complex UDFs: we’ll learn how to work with pandas DataFrames and plots as well as how to debug UDFs. To conclude this chapter, we’ll dig into a few advanced topics with a focus on performance. Unfortunately, xlwings doesn’t support UDFs on macOS, which makes this chapter the only chapter requiring you to run the samples on Windows.¹

A Note for macOS and Linux Users

Even if you are not on Windows, you may still want to have a look at the Google Trends case study as you could easily adapt it to work with a RunPython call on macOS. You could also produce a report by using one of the writer libraries from Chapter 8, which even works on Linux.

Getting Started with UDFs

This section starts with the prerequisites for writing UDFs before we can use the quickstart command to run our first UDF. To follow along with the examples in this ...