March 2021
Intermediate to advanced
335 pages
8h 42m
English
Content preview from Python for Excel
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
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.1
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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access
More than 5,000 organizations count on O’Reilly
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.Julian F.
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.Addison B.
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.Amir M.
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.