Dauphine-M2_VBA+Python_2024-25_Introduction.pdf

24-Aug-24 Visual Basic for Applications + Python Quantitative programming Dauphine – M2 24-Aug-24 21:50:02 Moving forward in VBA VBA courses at Dauphine: ◦ L3-S1: Language bases (39h)...

24-Aug-24 Visual Basic for Applications + Python Quantitative programming Dauphine – M2 24-Aug-24 21:50:02 Moving forward in VBA VBA courses at Dauphine: ◦ L3-S1: Language bases (39h) Variables, types, instructions, loops Functions, procedures Connections with Excel, Excel objects, graphs ◦ L3-S2: Databases (18h) Access and VBA SQL bases ◦ M1 & M2: Extensions (18h & 24h) Choice among various topics More complex cases 24-Aug-24 21:50:02 1  24-Aug-24 Moving forward in Python Python courses at Dauphine: ◦ L3 & M1: Language bases (2 x 18h) Instructions, loops, functions Connections with Excel with xlwings ◦ M2: Extensions with three courses Choice among various topics More complex cases 24-Aug-24 21:50:02 Method Steps followed for each case: 1. Language concepts 2. Individual practice on simple cases 3. Case(s) to be done during the session and at home 4. Test with qualitative and quantitative questions Please… 1. come with your own computer if you can 2. ask questions during courses 1. ANY QUESTION: EXCEL, VBA or Python, no self-censorship! 3. use the Moodle Forum for questions after the course 24-Aug-24 21:50:02 2  24-Aug-24 Coding style By default, VBA uses lax rules ◦ Untyped variables and function prototypes ◦ Absolute reference to ranges ◦ No syntax quality metric But we can use VBA like structured languages (java, C++, C# …) ◦ Defensive code: less error prone, hardware independent ◦ Robust VBA/XL interface: still works after XL changes ◦ Clear and explicit code: easier and less risky to share  I will teach the second way. 24-Aug-24 21:50:02 Do short & good code The focus is on code quality, not quantity Cases notation favors short code Some topics are discussed in dedicated sheets: ◦ Why & how to avoid long methods? ◦ Why & how to avoid repeating code? ◦ Why use Option Explicit? ◦ Why avoid absolute references to Excel ranges? ◦ Why & how to avoid using Variant? ◦ Why & how avoid using global variables? ◦ How to improve long Sub performance? ◦ And more on Graphics, Worksheets, Booleans, Constants, Error management 3  24-Aug-24 Notation 1 noted case Individual grades: ◦ Quiz with qualitative questions (25%) ◦ Quiz with quantitative questions (25%) Group grades: ◦ Extent of the assignment done (25%) ◦ Code style & originality (25%) 5 points for VBA CheckStyle + 5 other criteria 2.5 points for Python CheckStyle + 7.5 other criteria 24-Aug-24 21:50:02 Distinguish learning vs. copying I often see this: 24-Aug-24 21:50:02 4  24-Aug-24 … more about copying Or also this: Distinguish learning vs. copying DO DON’T Talk to each other Copy paste code Ask questions to anyone Use code you don’t Answer questions understand Help each other Ask another student’s Compare codes whole homework Understand and use other Give your whole students' ideas homework to another Retype by yourself an idea student you have seen (without seeing the original code) 24-Aug-24 21:50:02 5  24-Aug-24 Working as a team Cases are done by groups of two It is important to be able to coordinate your contributions, work together on the challenging features and help each other to produce the best possible result If you have difficulties, please let me know before the due date and always copy your teammate 27-Nov-19 21:50:02 M2 - 8 sessions 2-4 Sep: Introduction + practice of class in VBA 9-11 Sep: Class in Python + introduction of trinomial pricing 16-18 Sep: Tree building, step by step 23-25 Sep: Practice, focus on tree 30 Sep-2 Oct: Focus on option pricing 7-9 Oct: Convergence to Black & S. 14-16 Oct: Code performance (VBA vs. Python) XX Oct: More practice 21 Oct: two quizzes 3 Nov: send memo + spreadsheet + Python code 24-Aug-24 21:50:02 6  24-Aug-24 M2 Course content Introduction Language reminders: ◦ VBA types & instructions ◦ VBA Code samples comments ◦ Python Classes in VBA & Python Case: Option pricing with a lattice 24-Aug-24 21:50:02 Types & Instructions Macro recording Functions, subroutine Types, Time Instructions ◦ Conditions ◦ Loop Button & attached Subroutine Message box 24-Aug-24 21:50:02 7  24-Aug-24 Subroutine Definition of a subroutine: Sub ResetSpot() Let Range("Spot").Value = 100 End Sub Sub SetSpot(newValue As Double) Let Range("Spot").Value = newValue End Sub Calling a subroutine: Call ResetSpot() Call SetSpot(120) 24-Aug-24 21:50:02 Function Definition of a function: Function SquareRoot(x As Double) As Double Let SquareRoot = Sqr(x) End Function Function LogBase2(x As Double) As Double Let LogBase2 = Log(x) / Log(2) End Function 24-Aug-24 21:50:02 8  24-Aug-24 Function (2) Function LogBase2Second(x As Double) As Variant If x maxLoop Loop over arrays: For Each … Next object 24-Aug-24 21:50:02 UI (1): Message box To popup a window with a message: ◦ Useful to debug ◦ or to tell the user a long macro is finished Syntax: If x Options ◦ Ribbon customization -> Developer v 24-Aug-24 21:50:02 25  24-Aug-24 Enable VBA: security level File -> Options ◦ Trust Center -> Trust Center Settings ◦ Macro parameters 24-Aug-24 21:50:02 UI (2): Play a Sub with a click To run a Subroutine: 1. add a button 2. Draw the button 3. link the button to a Subroutine 24-Aug-24 21:50:02 26  24-Aug-24 Macro recording In Developer: See the result: 24-Aug-24 21:50:02 macro example s on e! e t h i o de lik ri t e c w D on’t 24-Aug-24 21:50:02 27  24-Aug-24 Array, Object, Types Arrays Excel main objects: ◦ Application, Workbook, Worksheet, Range, Cell Custom Type 24-Aug-24 21:50:02 Arrays Declare an Array: ◦ Dim nbDays (12) As Integer 13 integers allocated, from nbDays(0) to nbDays(12) ◦ Dim myMatrix(size-1, size-1) As Double Initialize with function Array (Variant, 0 to 11): ◦ nbDays = Array(31, 28, 31, …, 31) Set dimension (with Preserve to avoid reset) ◦ ReDim Preserve nbDays(1 To 12) Use or update an Array: ◦ thisMonth = nbDays(Month(date)) ◦ matrix(i,j) = 1 24-Aug-24 21:50:02 28  24-Aug-24 Loops on Array For Each... In array: ' Calculate the average of an array of Doubles Function GetAverage(ByRef myVector() As Double) As Double Dim sum As Double, counter As Double, element As Variant Let sum = 0 Let counter = 0 For Each element In myVector Let sum = sum + element Let counter = counter + 1 Next element Let GetAverage = sum / counter End Function 24-Aug-24 21:50:02 Excel objects (1) Open Object explorer: 24-Aug-24 21:50:02 29  24-Aug-24 Excel objects (2): Workbook Application ◦ ActiveWorkbook ◦ Workbooks Open(pathAndName As String) ◦ WorksheetFunction ◦ ScreenUpdating = False ◦ Calculation = xlCalculationManual / xlCalculationAutomatic Workbook ◦ Save: Call ActiveWorkbook.Save ◦ Close: Call ActiveWorkbook.Close ◦ Worksheets: ‘collection’ of Worksheet 24-Aug-24 21:50:02 Excel objects (2): Worksheet Worksheets ◦ Count: ActiveWorkbook.Worksheets.Count ◦ For Each … In Dim element As Worksheet, report As String For Each element In Application.Worksheets Let report = report & element.name & ", " Next element Worksheet ◦ Sub: Activate, Calculate ◦ Property: Cells Warning: Avoid wkt.Cells(i + 2, j + 3). Value = … 24-Aug-24 21:50:02 30  24-Aug-24 Excel Objects: Range Range: ‘collection’ of Cell ◦ Value: Let myVal = myRange.Value ◦ Formula: Let myRange.Formula = "=Price" ◦ Coordinates: Let myRange.Cells(i, j)= 0 ◦ Clear value below starting cell: Call Range(c, c.End(xlDown).End(xlToRight)).ClearContents ◦ Rows.Count: For i=1 To myRange.Rows.Count ◦ Sort: Call myRange.Sort(…) ◦ Font.Color: Let myRange.Font.Color = RGB(…) ◦ Interior.Color: Let myRange.Interior.Color = … 24-Aug-24 21:50:02 Excel Objects: Range.Cells Range("MatrixA").Cells(i,j) ◦ To access the cell on line i and column j ◦ top left corner: Range("MatrixA").Cells(1,1) Range("FirstRate").Offset(i,j) ◦ To shift from an initial location, by i lines and j columns ◦ top left corner: Range("FirstRate").Offset(0,0) ◦ Good when the number of data may vary 24-Aug-24 21:50:02 31  24-Aug-24 Objects (3): Objects & Variables Define a class address: Set ◦ Instead of Let for variables Compare: Is ◦ Instead of = for variables Empty object: Nothing ◦ Instead of 0 for numbers, “” for strings, etc. 24-Aug-24 21:50:02 Objects (4): Example Set, Is, Nothing: Dim element As Worksheet, nextElement As Worksheet For Each element In Application.Worksheets Set nextElement = element.Next ' Add element name and punctuation If nextElement Is Nothing Then Let report = report & element.name ElseIf nextElement.Next Is Nothing Then Let report = report & element.name & " and " Else Let report = report & element.name & ", " End If Next element 24-Aug-24 21:50:02 32  24-Aug-24 New Type Imagine we want to define a type for: ◦ a real value x ◦ the real value of a function of x, y = f(x) Call this type a ‘F_Point’ Type F_Point x As Double ' real number, input of f(x) fx As Double ' real number, result of f(x) End Type 24-Aug-24 21:50:02 Instantiate a Type Example: function returns an F_Point: ' A point is returned for a given value of x Private Function ValuePoint(ByVal x As Double) As F_Point Let ValuePoint.x = x Let Range("Point_x").value = x Call Range("Point_fx").Calculate ' if manual mode Let ValuePoint.fx = Range("Point_fx").value End Function Function initializes an array of F_Point: Dim list(size - 1) As F_Point For i = 0 To size - 1 Let list(i) = ValuePoint(x_Min + i * step) Next i 24-Aug-24 21:50:02 33  24-Aug-24 Use a Type Function or Sub uses a Type: ' A point is a root of the function if the value ' of the function is below the precision threshold Private Function IsRoot(ByRef p As F_Point) As Boolean Let IsRoot = Abs(p.fx) < Fepsilon End Function ' Makes a string from an F_Point Private Function ToStringP(ByRef p As F_Point) As String Let ToStringP = "x=" & Format(p.x, "#0.000") & _ ", fx=" & Format(p.fx, "#.000") & _ ", IsRoot=" & IsRoot(p) End Function 24-Aug-24 21:50:02 Synthesis: Comparison between variables & objects: Variables Types Classes [Let] [Let] Set = Not comparable Is 0, “”, etc. No null value Nothing 24-Aug-24 21:50:02 34  24-Aug-24 Error handling By default an error stops the execution Instead we can resume execution And decide what to do: ' catch error and pop-up a message Sub ErrorWithHandling2() Dim x As Double On Error Resume Next Let x = 1 / Range("DivideByCell").value If Err.Number 0 Then ' Error pops up a message Call MsgBox("Error #" & Err.Number & ": " & _ Err.Description) Else Call MsgBox("1/cell = " & x) End If End Sub 24-Aug-24 21:50:02 Inputs & Events Input box, conversions Events: ◦ Events: Trigger subroutines with user actions ◦ Workbook events: open, close ◦ Worksheet events: calculate, change ◦ Keyboard events: short cut VBA in Powerpoint (and Word) 24-Aug-24 21:50:02 35  24-Aug-24 Input box Input Box is used to ask a question to the user: ◦ Optional: Title (here: “Question”) ◦ Optional: Default value (here: “2”) VBA gets an answer as String 24-Aug-24 21:50:02 Input Box example In this example the answer is converted to an Integer: ' Opens the example spreadsheet for a given VBA session Public Sub OpenWorkbook() ' ask question to user Dim answer As String Let answer = InputBox("Which spreadsheet..." & _ " (enter session number, 1 or 3)?", "Question", 2) ' Convert answer to integer Dim answerNumber As Integer Let answerNumber = CInt(answer) Remark: hitting Cancel in the dialog box returns the empty string “” 24-Aug-24 21:50:02 36  24-Aug-24 Conversion functions To an Integer: Cint ◦ Long: CLng To a Double: CDbl To a Boolean: CBool To a String: CStr ◦ Same as Format, but with standard format To a Date: Cdate See examples in VBA_04_Examples….xlsm 24-Aug-24 21:50:02 Events: react to user actions So far VBA codes works: ◦ After calculate for functions ◦ After user action for macros (click on a button) VBA can also trigger code when: ◦ Workbook opens / closes ◦ A cell is changed ◦ A cell is calculated ◦ The focus changes ◦ etc. 24-Aug-24 21:50:02 37  24-Aug-24 Events Excel organization Use worksheet and workbook tabs: 2 3 1 4 24-Aug-24 21:50:02 Workbook event Example 1: when Workbook opens: 24-Aug-24 21:50:02 38  24-Aug-24 Worksheet Events (1) Example 2: trigger a macro after Calculate 24-Aug-24 21:50:02 Worksheet Events (2) Example 3: move selection ◦ We can use the current selection in the macro: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' random move around selection Dim rowMove As Integer, colMove As Integer Let rowMove = WorksheetFunction.Max(2 * Rnd() - 1, _ -Target.Row + 1) Let colMove = WorksheetFunction.Max(2 * Rnd() - 1, _ -Target.Column + 1) Call Target.Offset(rowMove, colMove).Select ' Tells where we are Call MsgBox("Target: " & Target.Row & ", " & Target.Column) End Sub 24-Aug-24 21:50:02 39  24-Aug-24 Short cuts Developer -> Macros: 1 3 2 24-Aug-24 21:50:02 VBA in Word File.docm with macros instead of.docx You can record macros Movements by characters, words and lines: Sub Macro1() Selection.MoveRight Unit:=wdCharacter, Count:=2 Selection.MoveRight Unit:=wdWord, Count:=3 Selection.MoveDown Unit:=wdLine, Count:=1 End Sub We can, for instance, color words according to their frequency: 24-Aug-24 21:50:02 40  24-Aug-24 VBA in Powerpoint File.pptm with macros instead of.pptx You cannot record macros The main objects are: Application.ActivePresentation: Presentation ◦ SlideShowSettings.NamedSlideShows: custom slide shows ◦ Slides: collection of Slide Slides.Count: number of slides ◦ Slide: Design: slide master – Name: slide master name SlideShowTransition.Hidden: hidden slide (msoFalse/msoTrue) Shapes: object that includes various shapes – Item(1): first shape, etc. – Item(1).TextFrame.TextRange: string box with Text and Language ID 24-Aug-24 21:50:02 Open Excel in PowerPoint Main steps: ◦ Declare an Excel workbook: Dim myXL As Workbook ◦ Open Excel file: Const folderName As String = "C:\Users\..." Const fileName As String = "MyData.xlsx“ Set myXL = Workbooks.Open(folderName & fileName) ◦ Access a named range: Let maxI = myXL.ActiveSheet.Range(rangeName).Rows.Count ◦ Close Excel Workbook: Call myXL.Close(SaveChanges:=False) 24-Aug-24 21:50:02 41  24-Aug-24 Syntax check tool Code quality depends on syntax and semantic: ◦ Syntax: form of the text, presentation ◦ Semantic: meaning, principle Syntax can be automatically corrected There are packages (like CheckStyle) for compiled languages in development frameworks Please use CheckVBAStyle_v….xlsm to check and improve the syntax of your code 24-Aug-24 21:50:02 Supported check in v9 in VBA Check "Option Explicit" Count Private methods or variables Count Const Count untyped Functions, parameters, variables Count methods length > 30 instructions by method Count long lines Count lines with/without comments Count empty lines Count absolute references in Range() Depth of nested scopes Check indentation Find redundant code 24-Aug-24 21:50:02 42  24-Aug-24 Supported check in v9 in Python This is new in v9.0.0 and in beta testing Count private methods or variables Count untyped functions and parameters Count untyped variables (soon) Count methods length > 20 instructions by method Count long lines Count lines with/without comments Count empty lines Count absolute references in Range() (soon) Depth of nested scopes (needs improvement) Check indentation Find redundant code (soon) 24-Aug-24 21:50:02 Example 1: all Ok Select a file and scan it: 24-Aug-24 21:50:02 43  24-Aug-24 Example 2: can be improved 24-Aug-24 21:50:02 M2 Course content Introduction Language reminders: ◦ VBA types & instructions ◦ VBA Code samples comments ◦ Python Classes in VBA Classes in Python Case: Option pricing with a lattice 24-Aug-24 21:50:02 44  24-Aug-24 Code samples The samples shown on following views are extracted from works done by M1 students The purpose is to comment interactively and identify some practices that can be improved Please ask for explanations when a code sample you consider right is pointed out as being poor And feel free to mention any questions about design similar to those shown here For more details, see the frequent topics sheets in Moodle 24-Aug-24 21:50:02 Sample 1: avoid long methods Long methods are error-prone Avoid methods > 40 lines Instead, define functions and Sub 24-Aug-24 21:50:02 45  24-Aug-24 Sample 2: no code duplication Always avoid repeated code Instead, define a function or a Sub that does the same Also: avoid ”:” 24-Aug-24 21:50:02 Sample 3: use “Option Explicit” To avoid this: With Option Explicit, an error pops up: 24-Aug-24 21:50:02 46  24-Aug-24 Sample 4: absolute references Avoid all kinds of absolute references: ◦ W.Cells(9,2).Value = … ◦ Range("B9").Value = … ◦ Worksheets(2)… Instead, name ranges 24-Aug-24 21:50:02 Sample 5: avoid Variants 27-Nov-19 21:50:02 47  24-Aug-24 Sample 6: no global variables Global declarations: ◦ Good practice: Constants Class members ◦ Avoid: Global variables Local variables should be declared in each method 24-Aug-24 21:50:02 M2 Course content Introduction Language reminders: ◦ VBA types & instructions ◦ VBA Code samples comments ◦ Python Classes in VBA Classes in Python Case #2: Option pricing with a lattice 24-Aug-24 21:50:02 48  24-Aug-24 Python data types Types are not mandatory, but help understand the intention and catch errors quickly Data types in Python: Integer number int Floating number float Text str Several data tuple Boolean bool Array numpy.array Full list: www.w3schools.com/python/python_datatypes.asp Python key instructions Function, if … else: import xlwings as xw @xw.func def factorial(n: int) -> str: if n == 0 or n == 1: return 1 else: return n * factorial(n-1) @xw.func: xlwings, to call Python from Excel A subroutine is a function without "return" Python basics: www.w3schools.com/python/ 49  24-Aug-24 numpy for arrays Arrays have to be 'declared' in methods visible from Excel: import numpy as np import xlwings as xw @xw.func @xw.arg('a', np.array) # this is required def matrixTransform(a): a[0,0] += 1 # increment top left cell return a @ np.transpose(a) # @: matrix multiplication numpy: www.w3schools.com/python/numpy/ ◦ See copy, empty, shape, dot, transpose, @ For loop For loop from 0 to size-1: import xlwings as xw import numpy as np @xw.arg('a', np.array) @xw.arg('b', np.array) @xw.arg('eMatrix', np.array) def ComputeEigenvalues(a, b, eMatrix) -> np.array: […] size: int = b.shape for i in range(size): eMatrix[i, 0] = np.sign(lMat[i, i]) * (abs(lMat[i, i]) ** (1./3.)) ◦ range(start, end): from start to end-1 50  24-Aug-24 While loop and Excel sheet Example of code to find an empty cell: import xlwings as xw def world(): wb: xw.Book = xw.Book.caller() top: xw.Range = wb.sheets.range('TopCell') firstRow: int = 0 # look for the first empty cell in the column while len(str(top.offset(firstRow, 0).value)) > 5: firstRow += 1 # Fill the cell with factorial value top.offset(firstRow, 0).value = 'Factorial(' + str(firstRow) + ') = ' + str(factorial(firstRow)) xlwings doc: https://docs.xlwings.org Python in Excel Python is arriving in Excel (news)! In the meantime, you can use these tools: ◦ Install Anaconda ◦ Install xlwings Setup xlwings parameters ◦ Install PyCharm Setup PyCharm and xlwings parameters See on Moodle the document "How to install xlwings & PyCharm?" 51  24-Aug-24 M2 Course content Introduction Language reminders: ◦ VBA types & instructions ◦ VBA Code samples comments ◦ Python Classes in VBA Classes in Python Case #2: Option pricing with a lattice 24-Aug-24 21:50:02 Classes (1) A class is, like the custom Type, an object A class module has: ◦ A name: the class name ◦ Members: list of variables ◦ Access properties Get and Let ◦ Functions and subroutines 24-Aug-24 21:50:02 52  24-Aug-24 Class step by step (1) We want to create a new class for complex numbers, with such calculations as addition and multiplication Step 1: check if the name Complex is already used in VBA We will use NbComplex instead 24-Aug-24 21:50:02 Class step by step (2) Create a new class module: ◦ Insert -> class module Rename the module by the class name: NbComplex 24-Aug-24 21:50:02 53  24-Aug-24 Class step by step (3) Define the real and imaginary parts: Add get and let method to update and access the two parts: 24-Aug-24 21:50:02 Class step by step (4) Use this minimal definition to instantiate a complex number and get its values In a regular (not class) module, write: ' test of the minimal NbComplex class ' test of the minimal NbComplex class Private Sub test() Private Sub test() Dim c As New NbComplex ' def + instant. Dim c As NbComplex ' definition Let c.imagPart = 1 Set c = New NbComplex ' instantiation Call MsgBox("Real and im. parts = " & _ Let c.realPart = 0 ' initialisation c.realPart & ", " & c.imagPart) Let c.imagPart = 1 End Sub Call MsgBox("Real and im. parts = " & _ c.realPart & ", " & c.imagPart) End Sub Run sub: 24-Aug-24 21:50:02 54  24-Aug-24 Class step by step (5.1) Add constructor in a « Factory » module: 'Complex nb "constructor": part that instantiates the new Object Public Function MakeComplex(ByVal r As Double, i As Double) As NbComplex Dim c As New NbComplex Call c.FillComplex(r, i) Set MakeComplex = c End Function Private Sub test() Dim c As NbComplex Set c = MakeComplex(0, 1) Call MsgBox("Real and imaginary parts = " & _ c.realPart & ", " & c.imagPart) End Sub 24-Aug-24 21:50:02 Class step by step (5.2) Fill the object in the class module: ' Complex number "constructor": part that sets the values Public Sub FillComplex(ByVal r As Double, i As Double) Let Me.realPart = r Let Me.imagPart = i End Sub 24-Aug-24 21:50:02 55  24-Aug-24 Class step by step (6) Add the addition method in the class, and the conversion to a string: Update the test Sub: Not nice ! 24-Aug-24 21:50:02 Class step by step (7) Improve the toString function: Test: 24-Aug-24 21:50:02 56  24-Aug-24 Class step by step (8) Multiplication of two complex numbers: Test: 24-Aug-24 21:50:02 Class step by step (9) Function isReal(): ' returns True if the complex number is real Public Function isReal() As Boolean Let isReal = Me.imagPart = 0 End Function Modulus: ' Modulus of a complex number Public Function modulus() As Double Let modulus = Sqr(Me.realPart ^ 2 + Me.imagPart ^ 2) End Function 24-Aug-24 21:50:02 57  24-Aug-24 Class step by step (10) Provide Excel functions CAdd and CMult: ◦ Define complex numbers by a range 24-Aug-24 21:50:02 Class vs. Type (1) Comparison of a custom Type and a class: Class Type 24-Aug-24 21:50:02 58  24-Aug-24 Class vs. Type (2) Comparison of a custom Type and a class: Class Type Members Yes Yes Function Yes No Subroutine Yes No Let & Get Yes No 24-Aug-24 21:50:02 Comparison with other languages VBA classes is limited compared to Python and compiled OO languages: VBA Python Java C++ Heritage No Yes Yes Yes Object Me self* this this Function Yes Yes Yes Yes Subroutine Yes Yes Yes Yes Yes, convoluted Yes, mostly Yes, mostly Yes, mostly Polymorphism syntax by heritage by heritage by heritage Constructors Other module One only Yes Yes Destructors Other module Yes Yes Yes Operators No Yes No Yes *: free object name chosen in the code 24-Aug-24 21:50:02 59  24-Aug-24 Example of Polymorphism There are various financial products with common features (maturity) and specific one: We want to make high-level code agnostic of the various product types specificities 27-Nov-19 21:50:02 Polymorphism (0) Define a general purpose class, called FinancialProduct, that will be instantiated as Bond, FX or Swap: Public productType As String Public maturity As Date […] Public Function toString() As String End Function ' initialize product from range Public Sub initialize(ByRef r As Range, ByVal i As Long) End Sub The class FinancialProduct (FP) is declared as usual Bond, FX and Swap implement FinancialProduct 24-Aug-24 21:50:02 60  24-Aug-24 Polymorphism (1) Here is how to implement another class: Step 1: repeat FP members and add new ones, fixedRate Option Explicit Implements FinancialProduct ' repeated members present in all financial products Private fp_productType As String Private fp_maturity As Date […] ' member specific to the Bond Public fixedRate As Double 24-Aug-24 21:50:02 Polymorphism (2) Step 2: add access functions to FP members ' Access to product Type Private Property Let FinancialProduct_productType(ByVal pType As String) fp_productType = pType End Property Private Property Get FinancialProduct_productType() As String FinancialProduct_productType = fp_productType End Property Note the specific syntax with “_” 24-Aug-24 21:50:02 61  24-Aug-24 Polymorphism (3) Step 3: implement FP methods, same “_” syntax ' Functions defined in Financial Product Private Function FinancialProduct_isDerivative() As Boolean Let FinancialProduct_isDerivative = False End Function Private Function FinancialProduct_isMonocurrency() As Boolean Let FinancialProduct_isMonocurrency = True End Function Private Function FinancialProduct_toString() As String Let FinancialProduct_toString = "A bond that pays a rate of " & Format(Me.fixedRate, "0.000%") & " and matures on " & FinancialProduct_maturity End Function 24-Aug-24 21:50:02 Polymorphism (4) Step 4: initialize the generic fields (in Factory) ' Initialize method common to all Financial Products Public Sub FPInitialize(ByRef fp As FinancialProduct, ByRef r As Range, ByVal i As Long) Dim j As Integer For j = 1 To r.Columns.Count Select Case r.Cells(1, j).Value […] Case "Maturity" Let fp.maturity = CDate(r.Cells(i, j).Value) Case "Principal Amount" Set fp.principalAmount = New Amount Call fp.principalAmount.initialize(r.Cells(i,_ j).Value, r.Cells(i, j + 1).Value) Let j = j + 1 ' that object uses two columns End Select Next j End Sub 24-Aug-24 21:50:02 62  24-Aug-24 Polymorphism (5) Step 5: initialize fields for this specific product ' Sub defined in Financial Product, implemented for the Bond Private Sub FinancialProduct_initialize(ByRef r As Range, ByVal i As Long) Dim j As Integer ' fields common to all financial products Call FPInitialize(Me, r, i) ' bond specific fields For j = 1 To r.Columns.Count Select Case r.Cells(1, j).Value Case "Fixed rate" Let Me.fixedRate = CDbl(r.Cells(i, j).Value) […] End Select Next j End Sub 24-Aug-24 21:50:02 Polymorphism (6) Step 6: load portfolio, note the instantiations Dim aFp As FinancialProduct For i = 1 To size Select Case portfolioRange.Cells(i + 1, 1).Value Case "Bond" Set aFp = New Bond Case "Swap" Set aFp = New Swap Case "FX" Set aFp = New FX Case Else Call MsgBox("Unexpected product type: …") End Select Call aFp.initialize(portfolioRange, i + 1) Call Me.setOneFp(i, aFp) Next i 24-Aug-24 21:50:02 63  24-Aug-24 Polymorphism (7) Step 7: print report, Variant because of For Each Public Function toString() As String Dim aFp As Variant Dim report As String ' List the descriptions of all assets Let report = "The portfolio has the following assets:" & vbLf For Each aFp In Me.fpTable() Let report = report & "- " & aFp.toString() & vbLf Next aFp ' Conclude with the value of the portfolio Let report = report & "NPV: " & Format(Me.totalPV(), "#,##0.00") Let toString = report End Function See Excel sample #5 for more details 24-Aug-24 21:50:02 Memory management (1) Instantiate an object = allocate heap memory The memory is released when the object is out of scope if it is not connected to another object NbComplex objects are always freed after use because they are not connected It is not as easy with objects connected to each other. The memory is freed only if objects are disconnected (set to Nothing). Stack vs. Heap: see www.geeksforgeeks.org/stack-vs-heap-memory-allocation/ 27-Nov-19 21:50:02 64  24-Aug-24 Memory management (2) You can check that an object memory is freed: Private Sub Class_Terminate() Call MsgBox("Terminating " & Me.toString) End Sub You can also count allocated objects: Private Sub Class_Initialize() Let NodeNumber = NodeNumber + 1 End Sub Private Sub Class_Terminate() Let NodeNumber = NodeNumber - 1 End Sub 27-Nov-19 21:50:02 Practice on a Dictionary Exercise purpose: ◦ Load a series of students contact data in a dictionary ◦ Use the dictionary to get a mail or a phone number Steps: 1. Define a dictionary with just two data: name & mail 2. Get mail for a given name 3. Define a class to bundle several data for the same student: last name, first name, mail, phone number 4. Use the class in the dictionary 5. Load the whole class in the dictionary 6. Write functions to get mail & phone # from a cell Reference: https://excelmacromastery.com/vba-dictionary/ 24-Aug-24 21:50:02 65  24-Aug-24 Code samples (1) With objects, like in procedural code, avoid code duplication Solution: define methods, often class functions to factorize the code Code samples (2) Avoid the definition of the same data in two objects Data duplication is always risky Solution: define a Market member in PricerBlack 66  24-Aug-24 Code samples (3) Avoid initializing several objects in the same Sub Avoid global variable, very different from class members Solution: define an initialization method in each class Code samples (4) Choices like Call/Put are intuitive as booleans IsCall, IsPut As a string there are variations for the same choice (risk of type) Solution: store a field in lower case and make isCall and isPut functions 67  24-Aug-24 M2 Course content Introduction Language reminders: ◦ VBA types & instructions ◦ VBA Code samples comments ◦ Python Classes in VBA Classes in Python Case #2: Option pricing with a lattice 24-Aug-24 21:50:02 Python classes: Complex Constructor: __init__ import math ◦ Members can be defined in the class Complex: constructor # constructor ◦ Class name starts def __init__(self, r: float, i: float): with Upper case, aka self.real: float = r self.imaginary: float = i "Camel case". ◦ Other example: ComplexNumber 68  24-Aug-24 Python functions Syntax: def function_name ◦ Functions are names in lower case with underscore separators, aka "snake case" ◦ "self" denotes the object instance # modulus = distance from 0 to the complex number in the plane def modulus(self) -> float: return math.sqrt(self.real ** 2 + self.imaginary ** 2) # returns True if the imaginary part is equal to 0 def is_real(self) -> bool: return self.imaginary == 0 to_string function Same logic # converts the complex to a string def to_string(self) -> str: as in VBA, result: str = "" # real part a bit if self.real != 0 or self.imaginary == 0: convoluted result = Complex.my_string(self.real) # sign My_string: if self.real != 0 and self.imaginary > 0: result += "+" to remove if self.imaginary < 0: result += "-" ".0" of str # abs(imaginary part) if 1, followed by "i" if self.imaginary != 0: if abs(self.imaginary) != 1: result += Complex.my_string(abs(self.imaginary)) result += "i" return result 69  24-Aug-24 Static function A "static" method doesn't use an object instance @staticmethod ◦ my_string("1.0") → "1" def my_string(r: float) -> str: st: str(r) ◦ It is a small tool inserted if st[-2:] == '.0': in the class return st[:(len(st)-2)] else: ◦ It avoids repeating code return st Operator overloading (1) We can implement addition of two complex numbers in two ways: ◦ Either as a function that adds another complex to the instance ◦ Or as an overloading of the "+" operator # plain add function def add(self, other: Complex) -> Complex: return Complex(self.real + other.real, self.imaginary + other.imaginary) # surcharge "+" operator def __add__(self, other: Complex) -> Complex: return Complex(self.real + other.real, self.imaginary + other.imaginary) 70  24-Aug-24 Operator overloading (2) The use of the two methods in main.py is: import xlwings as xw import numpy as np from complex import Complex @xw.arg('c1', np.array) @xw.arg('c2', np.array) @xw.func(async_mode='threading') def CAddPlain(c1, c2): return Complex(c1, c1).add(Complex(c2, c2)).to_string() # Excel functions that use the Complex class @xw.arg('c1', np.array) @xw.arg('c2', np.array) @xw.func(async_mode='threading') def CAdd(c1, c2): return (Complex(c1, c1) + Complex(c2, c2)).to_string() Check overloading Use the identity 𝑎2 − 𝑏 2 = 𝑎 + 𝑏 𝑎 − 𝑏 # check that the identity works with operators overloading: a * a - b * b = (a + b) * (a - b) def check_0(self, other: Complex): return self * self - other * other - (self + other) * (self - other) # should be = 0 … 71  24-Aug-24 Use of object in same class An error pop-up when using the object Complex in the class: To avoid this, add at the beginning: from __future__ import annotations # to reference Complex objects in the class import math class Complex: […] def add(self, other: Complex) -> Complex: return Complex(self.real + other.real, self.imaginary + other.imaginary) Final tests Remove all errors: ◦ PyCharm View → Tool windows → Problems In Excel, check: 72  24-Aug-24 Memory management Python manages the object memory with a "garbage collector", that releases memory of objects that are no longer used The garbage collector keeps in memory connected objects, like in VBA When large connected objects are no longer used, it is recommended to disconnect them to help the garbage collector This means setting objects members to None Example: large trinomial tree (1) 100,000-step tree pricing without (left) and with (right) disconnection of nodes: 73  24-Aug-24 Example: large trinomial tree (2) After 10 minutes: Count allocated objects You can count the number of allocated objects in a class using static members: class Node: # count current and maximum numbers of instantiated nodes nb_nodes: int = 0 max_nb_nodes: int = 0 # WARNING: overloading __new__ and __del__ DOUBLES the pricing run time! def __new__(cls, *args, **kwargs) -> Node: instance = super().__new__(cls) # required, otherwise memory isn't allocated for the object! # added feature Node.nb_nodes += 1 Node.max_nb_nodes = max(Node.max_nb_nodes, Node.nb_nodes) return instance # decrement the object counter when an instance is deleted def __del__(self): Node.nb_nodes -= 1 74