Lecture 9 - xlwings and excel UDFs PDF

Preview

Summary

Download Lecture 9 - xlwings and excel UDFs PDF

Description

Programming with Excel -

Excel (& and other Office apps, like Word & Access) have an inbuilt programming language. o VBA (Visual Basic for Applications)  Used to automate tasks in Microsoft Office apps  Wall Street / banks are big users  the ‘applications’ (as in ‘for applications’) are the Office applications. o VBA uses the Office Object Model (for the applicable app - e.g., Excel Object Model; PowerPoint Object Model).

Visual Basic for Applications (VBA) -

“The majority of VBA users are Finance, HR, Marketing, or Project Management professionals.” Newman, 2019 Should you learn VBA? o Actually, do not learn VBA - learn Python instead!

VBA macros -

Macro Recorder o This feature allows you to click a record button and it will write code for every-singlething you physically carry out on your screen. o While it might not be the most efficient way to write the code, it’s a quick way to write simple code.

Advantages of Python over VBA -

-

My recommendation is become familiar with the Excel Object Model, but focus on Python, rather than Visual Basic. o Python has a much wider ecosystem of code  e.g., AI, graphics, linear algebra, algorithms, etc. o And still can use anything / do everything in Excel! Can also integrate R with Excel (but I cannot find a similar program to xlwings for Excel/R integration) MS Power BI and Tableau Prep can also run python and R scripts:

Excel Object Model -

-

Each Excel object has its own predefined methods and properties (attributes) that can be manipulated by VBA (& also Python) code. Extract of Excel Object Model showing part with focus on the Chart Object o The Chart Object is a container of other objects – e.g., Series, Axes o So, lots of objects! Our focus will be Excel’s Range object o A square or rectangle collection of worksheet cells. o While there is a Cell object in the object model, xlwings allows you to define a range as a single cell, thereby making the cell concept redundant.

o

Examples will show you how to access each cell as you interrogate a range (which is a collection of cells).

xlwings object model -

xlwings is a python representation (API) for the Excel Object Model. Sort of a python wrapper to the underlying COM code Using the value property of the xlwings range object to access (or set) the contents of cells in python

Iterate through an Excel range -

Using the value property of the xlwings range object to access the contents of cells in python

Slicing -

As range is clearly an ordered collection, it must support an index. Therefore, can support slicing operations.

.offset method -

Returns a Range object that represents a Range that’s offset (in terms of Excel rows/cols.) from the specified range.

.api attribute -

xlwings is just a smart wrapper around pywin32 on Windows and appscript on Mac. You can access the underlying objects by calling the api property.

Excel’s Lambda Functions -

Excel’s Lambda functions will still not be as potentially powerful as functions written in VBA or Python. But the introduction of Excel’s lambda functions addresses a clear omission from Excel (although, one way of getting around this limitation in the past was through add-ins – but developing an Excel add-in is not trivial – written in C#, VB.NET, C++).

Excel UDFs – -

A UDF (User-Defined Function) in Excel is a function registered in Excel but it not native (i.e., is not one of the functions that all Excel implementations support). For example (and detailed in a later slide), I’ve created a UDF to return the value for Black Scholes Option Pricing (which is not something Excel knows how to do out-of-the-box) xlwings provides the UDF interface (i.e., manages the interface between Excel & Python environments) Optional – may not actually use (but will impress!) Use if you wish to extend Excel’s functionality using python for Excel end-users Excel UDFs will have arguments that need to be passed to the python script python script must be a function Arguments can be stored in Excel cells, or selected during UDF use (e.g., selection of a range of cells as a range argument).

Variable-length argument lists (in Python) -

-

Sometimes you might wish to write a Python function in which you don’t initially know how many arguments the user will pass. Python has *args (short for arguments) which allow us to pass the variable number of nonkeyword arguments to a function. In the function header, an asterisk * is placed before the variable name that holds the values of all non-keyword variable arguments.  This is called *args  *args is a collection (has parts) o So, the number of values in the function call could vary with each program run! When a user using a UDF specifies (chooses) a range (block) of Excel cells as an argument value, they are therefore passing a variable set of argument values (i.e., an array of cell objects that can be different with each execution) from Excel to the python script.

Complex Arguments -

When the user in Excel selects a range of cells as an argument (e.g., D3:F6) ) xlwings returns to the python interpreter a range object. Being an object, one can access the properties of the cells – e.g., refer to the range.value property to access cell contents. As range is a collection, we need to loop through the elements if we wish to complete a full pass (this is sort of the xlwings version of unpacking).

Optional arguments -

As you know, some arguments in Excel are mandatory (bold), while others are optional. Optional arguments may reflect specific use cases (not the usual stuff) or may simply default to the usual value (you override only now-and-then)

Excel UDFs -

Python script must be a function Must include the @xw.func example: @xw.func def CONVERT_FtoC (FValue): CValue = (FValue -32) *5/9

return CValue SPILL: return a value to excel and put it into the cell. If the value is a list it will SPILL out of the cells

Argument Passing arguments: a set of values entered by the user variable-length argument lists -

when a use using a UDF specifies (chooses) a range (i.e., block of Excel cells) as an argument value, they are therefore passing a variable set of argument values from Excel to the python environment...