Ateneo - UPO

The course introduces the advanced use of Microsoft Excel 365 for financial modelling, data analysis and the automation of calculation procedures. Its aim is to develop the ability to design dynamic, transparent, efficient, reliable and easily updatable models.

Particular attention will be devoted to the recent functionalities of Excel 365: Excel Tables and structured references, dynamic arrays and advanced functions for filtering, sorting, aggregating and transforming data, including FILTER, GROUPBY, PIVOTBY, MAKEARRAY and STOCKHISTORY.

A substantial part of the course is devoted to functional programming in Excel: the use of LET to improve the readability and efficiency of formulas, the construction of custom, reusable functions with LAMBDA, and the use of iteration and array-manipulation functions. The course will also cover the use of Power Query for importing, cleaning, transforming and automatically refreshing data from different sources.

The tools presented will be applied to concrete financial problems: modelling of mortgages and amortisation schedules, construction of payment schedules and cash flows, bond valuation, importing and analysing market prices and returns, portfolio optimisation and scenario analysis.

Subject to the time available, the course finally introduces automation through macro recording and an outline of VBA programming.

The teaching material prepared by the lecturer is made available on the course platform. This material is complemented by the official Microsoft documentation on Excel 365, on arrays and dynamic functions, on functional programming with LET and LAMBDA, and on importing, cleaning and transforming data with Power Query.

Tutorials and further online resources, mainly in English, are also used in support of the applied topics covered during the course.

General and comprehensive reference text for Microsoft Excel 365:

Michael Alexander, Richard Kusleika and John Walkenbach, Microsoft Excel 365 Bible, Wiley, 2022.

Introduction to VBA programming:

https://www.excel-pratique.com/en/vba/introduction

Introduction to the design and development of UserForms in VBA:

https://www.excel-easy.com/vba/userform.html

Further references, tutorials and technical resources concerning the financial applications developed during the course are made available on the course platform.

For the project on which assessment is based, the main reference is the course material published on the platform; the textbook and the online resources listed above are intended for individual study and for looking up the functions used.

The course aims to provide advanced skills in the use of Microsoft Excel 365 for the design and development of dynamic, transparent, efficient, reliable and easily updatable financial models.

The course aims to develop the ability to organise, import, transform and analyse data, to use the modern array-calculation features and dynamic functions of Excel, to create custom functions by means of functional programming tools and to automate calculation and analysis procedures. Subject to the time available, the course also introduces automation through macro recording and the first elements of VBA programming.

Particular attention will be devoted to building modular and reusable models, to checking the correctness and efficiency of the solutions developed and to interpreting the results. The skills acquired will be applied to concrete financial problems concerning, among others, mortgages and amortisation schedules, bonds and cash flows, market data analysis and portfolio optimisation.

The course makes use of interactive teaching activities (2 ECTS credits).

Required background knowledge:

A good working knowledge of Microsoft Excel is required: creating, editing, formatting and printing worksheets, building simple charts, using formulas, cell ranges, relative and absolute references and copy and fill procedures, and using the main mathematical and financial functions and the basic tools for organising and managing simple datasets.

Basic knowledge of financial mathematics, statistics, optimisation and matrix algebra is also required.

No previous programming experience is required: the functional programming tools in Excel and the outline of VBA are introduced during the course starting from the fundamental concepts.

The course consists of interactive classes held in person, in which theoretical presentations are accompanied by demonstrations in Excel and guided exercises. Topics are developed mainly through financial applications and case studies: Excel is used in class to build, check and modify the models presented.

Students are invited to replicate the procedures illustrated by the lecturer, to apply the tools presented to concrete cases and to discuss the solutions adopted. Discussing modelling choices is an integral part of the classes and prepares students for the oral discussion required to obtain the pass certification.

Part of the learning path is carried out through self-study: students are required to consult independently the tutorials and online resources indicated, mainly in English, and to develop the applied project on which assessment is based.

The course is worth 2 ECTS credits, delivered as interactive teaching.

A laptop with an up-to-date version of Microsoft Excel 365 for Windows is required. Instructions, exercises and teaching materials are prepared specifically for Excel in a Windows environment: the availability and behaviour of some of the functionalities used during the course may differ in earlier versions of Excel and in the versions available for macOS. Full compatibility of the materials and of the applications developed during the course with versions other than Excel 365 for Windows is therefore not guaranteed.

Attendance of at least 80% of the classes is compulsory.

Attendance at a minimum of 80% of the classes is compulsory.Course activities require an up to date version of Microsoft Excel 365. Instructions, practical exercises, and teaching materials will be specifically developed for Excel in a Windows environment. The availability and functionality of some of the features used during the course may differ in earlier versions of Excel and in the versions available for macOS. Full compatibility of the course materials and applications with versions other than Excel 365 for Windows cannot therefore be guaranteed.Students with physical disabilities, Learning Disabilities or Special Education Needs can request specific services and tools via the Staff Sviluppo e Coordinamento Carriere e Servizi alle Studentesse e agli Studenti, consulting the University webpage: https://www.uniupo.it/en/services/servicesstudents-physical-or-learning-disabilities Students with disabilities, learning disabilities or special education needs, once they have contacted the University Staff, can refer to the tutor in charge of the course to define the examination modalities.

The course provides a pass/fail assessment (pass or fail judgement) and not a mark out of thirty; the correlation between the level of competence achieved and the assessment is therefore expressed in binary terms, not through bands of numerical scores.

The pass is awarded on the basis of an applied project, carried out individually or in groups, developed with Microsoft Excel 365 and concerning a financial problem. The project is accompanied by a short report and is presented and discussed orally.

The project must demonstrate the ability to organise, import, clean and transform data, to use the advanced functionalities of Excel, dynamic arrays and functional programming tools, to build efficient and modular formulas with LET and LAMBDA and to automate calculation and analysis procedures, applying the tools used correctly, interpreting the results critically and setting out their financial implications.

Criteria for obtaining a pass (minimum threshold): the project must use the required Excel tools correctly, including LET and LAMBDA functions and dynamic arrays where relevant, and produce a model free from substantial methodological errors; in the oral discussion, students must demonstrate that they understand how the model works, that they can justify the methodologies used and that they can discuss the solutions implemented. Failure to meet even one of these requirements results in a fail judgement.

Features of an excellent project: while they do not change the judgement, which remains binary, a high-level project stands out for the efficiency, transparency and readability of its formulas, for an advanced degree of automation of the calculation and analysis procedures, for the depth of the analysis carried out and for the ability to interpret the results critically and to communicate their financial implications clearly during the discussion.

Attendance of at least 80% of the classes is compulsory.

Part I - Excel 365 and dynamic modelling

Principles for building clear, reliable and easily updatable financial models. Excel Tables, structured references, dynamic arrays and the main functions for filtering, sorting, selecting and transforming data.

Part II - Functional programming in Excel

Use of LET to improve the readability and efficiency of formulas. LAMBDA functions and the construction of custom functions. Use of MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY. Function composition, iterative calculations and the development of modular formulas.

Part III - Dynamic data analysis and aggregation

Use of GROUPBY and PIVOTBY to aggregate, summarise and analyse datasets. Building dynamic reports. Importing and analysing financial data with STOCKHISTORY.

Part IV - Power Query

Importing, cleaning and transforming data from Excel files, CSV files and other sources. Handling missing data, duplicates and errors. Merging, appending and aggregating tables. Automating import and refresh procedures.

Part V - Financial applications

Modelling of mortgages and amortisation schedules. Dynamic construction of payment schedules and cash flows. Building bond cash-flow matrices. Bond valuation and analysis of the term structure. Importing and analysing financial prices and returns. Portfolio optimisation and scenario analysis.

Part VI - Outline of automation with VBA, subject to the time available

Recording, running and editing simple macros. Introduction to the VBA environment and to the structure of the code. Use of simple procedures to automate repetitive tasks.

Knowledge and understanding
By the end of the course, the student will be able to:

1. understand the principles for designing dynamic, transparent, efficient, reliable and easily updatable financial models;

2. understand the logic of dynamic array calculation in Excel 365 and of the functions derived from it;

3. understand the principles of functional programming in Excel and the role of LET and LAMBDA in building readable, efficient and reusable formulas;

4. understand the logic of importing, cleaning, transforming and refreshing data with Power Query;

5. understand the structure of the main financial models covered in the course, concerning mortgages and amortisation schedules, bonds and cash flows, market data and portfolio optimisation;

6. understand the criteria for assessing the correctness, efficiency, transparency and maintainability of a model;

7. understand, subject to the time available, the principles of automation through macro recording and the basic structure of VBA code.

Applying knowledge and understanding

By the end of the course, the student will be able to:

1. design and develop dynamic, reliable, transparent, efficient and easily updatable Excel models;

2. organise and manage datasets using Excel Tables, structured references, dynamic arrays and advanced functions;

3. use the modern functionalities of Excel 365 to filter, sort, aggregate, transform and analyse data;

4. use functions such as STOCKHISTORY, GROUPBY, PIVOTBY and MAKEARRAY for the import, processing and dynamic analysis of data;

5. use LET to improve the efficiency, readability and maintainability of formulas;

6. create custom, reusable functions with LAMBDA and apply functional programming tools such as MAP, REDUCE, SCAN, BYROW and BYCOL;

7. import, clean, transform, integrate and refresh data from different sources with Power Query;

8. apply the advanced functionalities of Excel to build models concerning mortgages and amortisation schedules, bonds and cash flows, market data and portfolio optimisation problems;

9. check the correctness and robustness of the models developed and assess their efficiency, transparency, readability and level of automation;

10. critically interpret the results of the analyses and communicate them clearly and effectively;

11. record, run and edit simple macros and use simple procedures to automate repetitive tasks, subject to the time available.