Python for Excel: Use xlwings for Data Science and Finance

Python for Excel: Use xlwings for Data Science and Finance

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 16 Hours | 5.42 GB

Integrate Excel and Python and get the best of two Worlds! Python Beginners welcome. Use Pandas, Seaborn & co. in Excel

Excel vs. Python – what is the best tool for Data Science, Business and Finance?

The answer is: Use Excel and Python together and integrate both tools with xlwings. Get the best of two worlds!

With xlwings, you can use Python Data Science libraries like Numpy, Pandas, Scipy, Matplotlib, Seaborn and Scikit-learn directly in Excel! You can run Python code in Excel and boost your Excel projects! More and more Professionals and Developers use

  • Excel as Frontend
  • Python as analytical Backend.

This course is the perfect choice for

  • Experienced Python Coders: Use Excel as Graphical User Interphase (GUI) | Run your Python scripts with
  • Excel | Present your results with Excel Dashboards
  • Excel Users and complete Python Beginners: Boost your Excel projects with clean and powerful Python code!
  • Mixed Groups: Non-Coders can run and use Python code simply by clicking on buttons in Excel.

Why take this course?

  • You will learn and master the xlwings library from scratch
  • For Excel Users and complete Python Beginners: This course includes a Python Crash Course that is tailor-made for you!
  • It´s the most comprehensive and practical (hands-on) xlwings course on the web
  • It covers three comprehensive real-world projects.
  • Project 1: You will learn how to boost your financial model in Excel by adding a Python Monte Carlo Simulation – Run your Excel calculation 10,000 times with different sets of inputs and analyze the results!
  • Project 2: You will learn how to create Dashboard Apps with Excel (Graphical User Interface) and Python (analytical Backend).
  • Project 3: You will learn how to use Pandas methods and functions on your datasets directly in Excel.

Why use Excel?

There is no better Graphical User Interface (GUI) and Reporting tool than Excel. Excel is

  • widely spread (750 million users)
  • standardized
  • intuitive to use
  • most users are well-trained
  • it requires low/zero set up
  • it requires low/zero maintenance
  • and it´s still the best choice for financial models & spreadsheet calculations

Why use Python?

With hundreds of powerful Libraries, Python is the first choice for Data Science, Machine Learning and advanced analytics in Business and Finance. The Python Ecosystem is way more powerful and versatile than VBA. And it´s cleaner and easier to learn and apply!

Why learn and master xlwings?

xlwings is the perfect tool to integrate Excel and Python! xlwings allows you to

  • Automate Excel from Python e.g. to produce reports or to interact with Jupyter Notebooks.
  • Write macros in Python that you can run from buttons in Excel, e.g. to load in data from a database or an external API.
  • Write UDFs (user-defined functions) and leverage the power from NumPy, Pandas and machine learning libraries.
  • Leverage Python’s scientific stack for interactive data analysis using Jupyter Notebooks, NumPy, Pandas, scikit-learn, etc.
  • Use xlwings to automate Excel reports with Python.
  • Write Excel tools with Python instead of VBA and call your code directly from within Excel, e.g. via a button on the sheet.
  • This also works great for prototyping web apps.
  • Write (array) UDFs in a breeze by taking advantage of all the functionality already available in libraries like NumPy and Pandas.
  • Dynamic array formulas are supported.

What you’ll learn

  • Automate Excel with clean and powerful Python Code
  • Learn and master the xlwings library from 0 to 100
  • Use Excel as Graphical User Interface (GUI) and run your Python code with Excel
  • Create powerful Dashboard Apps with Excel (frontend) and Python (backend)
  • Use powerful Data Visualization Tools (Matplotlib, Seaborn) in Excel
  • Learn Python from scratch with a taylor-made Crash Course (For Python beginners)
  • Write UDFs (user defined functions) and use Numpy, Pandas and Machine Learning Libraries directly in Excel
  • Write Excel tools with Python instead of VBA and call your code directly from within Excel
  • Use xlwings to automate Excel reports with Python
  • Prototype Web apps
  • Write and use Dynamic Arrays with xlwings
  • Run your financial model 10,000 times & more with a Python Monte Carlo Simulation
  • Load (financial) data from Web APIs directly into Excel
  • Run Python Scripts from within Excel with Run main and RunPython
  • Replace VBA macros with clean and powerful Python code
Table of Contents

Getting Started
1 Introduction (don´t skip!)
2 Course Overview (don´t skip!)
3 Tips How to get the most out of this Course (don´t skip!)
4 FAQ Your Questions answered
5 How to download and install Anaconda for Python coding
6 Jupyter Notebooks – let´s get started
7 How to work with Jupyter Notebooks

First Steps with xlwings (Reading and Writing Elements)
8 Introduction and Downloads
9 How to install xlwings
10 How to use xlwings as a Data Viewer
11 How to connect to an Excel Workbook
12 How to read and write single Values
13 How to assign a name
14 How to write Excel Functions with Python
15 Range Shortcuts
16 Case Study – Bringing it all together
17 Homework

Reading and writing many Values
18 Section Downloads
19 One-dimensional Data Structures
20 How to write Values vertically
21 Rows and Columns (1dim vs. 2dim)
22 How to read two-dimensional Data Structures
23 Advanced Reading with expand
24 How to write two-dimensional Data Structures
25 Range Indexing and Slicing
26 Efficiency
27 Homework

Project 1 Monte Carlo Simulations in Excel with Python (Part 1)
28 Introduction
29 Section Downloads
30 The Excel Model explained (Part 1)
31 The Excel Model explained (Part 2)
32 Running a simple Monte Carlo Simulation
33 A more advanced and realistic Monte Carlo Simulation
34 Final Considerations

Running Python Scripts in Excel – RunPython
35 Introduction and Downloads
36 Installing the xlwings add-in and other preparations
37 Running Python Scripts with Run main
38 Troubleshooting (Part 1)
39 All you need to know about VBA Macros
40 Running Python Scripts with RunPython
41 Troubleshooting (Part 2)
42 Run main vs RunPython
43 Excursus Converting Jupyter Notebooks to .py
44 Homework

Project 1 Monte Carlo Simulations in Excel with Python (Part 2)
45 Introduction and Downloads
46 Monte Carlo Simulation with RunPython (Part 1)
47 Monte Carlo Simulation with RunPython (Part 2)

Using Matplotlib and Seaborn in Excel with xlwings
48 Introduction and Downloads
49 How to write a Matplotlib Plot into Excel
50 How to update the Plot
51 How to change Size and Position (Part 1)
52 How to change Size and Position (Part 2)
53 How write a Seaborn Plot into Excel
54 How to create Excel Charts with Python
55 Homework Adding a Plot to the Monte Carlo Simulation (Project 1)

Project 2 Build Dashboard Apps with Excel (GUI) and Python (analytical backend)
56 Introduction and Downloads
57 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 1)
58 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 2)
59 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 3)
60 Building a Stock Performance Dashboard App (Part 1)
61 Building a Stock Performance Dashboard App (Part 2)
62 Improving the Source Code and Errors

Reading and Writing Data Structures (Numpy, Pandas) & Converters
63 Section Downloads
64 (Default) Converters
65 The Numpy Converter
66 The Dictionary Converter
67 The DataFrame Converter (Part 1)
68 The DataFrame Converter (Part 2)
69 Data Science Application Inspecting and Manipulating DataFrames in Excel
70 The Pandas Series Converter
71 Excursus How to load Data from Excel into Pandas with pd.read excel()
72 Excursus Advanced import with pd.read excel()
73 Excursus How to load Financial Data Time Series with pd.read excel()

User-defined Functions (UDF) and Dynamic Arrays with xlwings (Windows only)
74 Introduction and Downloads
75 Preparations and your first UDF
76 How to change the Name and Location of the Python Module
77 Troubleshooting (UDF)
78 UDFs – Behind the Scenes
79 More complex UDFs and the @xw.arg Decorator
80 How to create Numpy UDFs
81 UDFs and Array Formulas
82 How to create Dynamic Arrays with xlwings UDFs
83 How to create Pandas UDFs
84 How to add Docstrings
85 Homework

Project 3 Use Pandas UDFs in Excel for Data Science and Finance (Windows only)
86 Introduction and Downloads
87 How to load Financial Data from the Web into Excel with the DataReader UDF
88 How to resample Time Series in Excel with the resample UDF
89 How to calculate Financial Returns with a PandasNumpy UDF
90 How to get Summary Statistics of a Dataset with the describe UDF
91 How to create a Dataset´s Correlation Matrix with the corr UDF
92 Taken all together – the Super UDF
93 How to perform innerouterleftright joins with the merge UDF

APPENDIX 1 Python Crash Course for Excel Users
94 Introduction and Overview
95 Section Downloads
96 Intro to the Time Value of Money (TVM) Concept (Theory)
97 Calculate Future Values (FV) with Python Compounding
98 Calculate Present Values (FV) with Python Discounting
99 Interest Rates and Returns (Theory)
100 Calculate Interest Rates and Returns with Python
101 Introduction to Variables
102 Excursus How to add inline comments
103 Variables and Memory (Theory)
104 More on Variables and Memory
105 Variables – Dos, Don´ts and Conventions
106 The print() Function
107 Coding Exercise 1
108 TVM Problems with many Cashflows
109 Intro to Python Lists
110 Zero-based Indexing and negative Indexing in Python (Theory)
111 Indexing Lists
112 For Loops – Iterating over Lists
113 The range Object – another Iterable
114 Calculate FV and PV for many Cashflows
115 The Net Present Value – NPV (Theory)
116 Calculate an Investment Project´s NPV
117 Coding Exercise 2
118 Data Types in Action
119 The Data Type Hierarchy (Theory)
120 Excursus Dynamic Typing in Python
121 Build-in Functions
122 Integers
123 Floats
124 How to round Floats (and Integers) with round()
125 More on Lists
126 Lists and Element-wise Operations
127 Slicing Lists
128 Slicing Cheat Sheet
129 Changing Elements in Lists
130 Sorting and Reversing Lists
131 Adding and removing Elements fromto Lists
132 Mutable vs. immutable Objects (Part 1)
133 Mutable vs. immutable Objects (Part 2)
134 Coding Exercise 3
135 Tuples
136 Dictionaries
137 Intro to Strings
138 String Replacement
139 Booleans
140 Operators (Theory)
141 Comparison, Logical and Membership Operators in Action
142 Coding Exercise 4
143 Conditional Statements
144 Keywords pass, continue and break
145 Calculate a Project´s Payback Period
146 Defining your first user-defined Function
147 What´s the difference between Positional Arguments vs. Keyword Arguments
148 How to work with Default Arguments
149 Coding Exercise 5

APPENDIX 2 Matplotlib, Numpy, Pandas and Seaborn Crash Course
150 Downloads for this Section
151 Matplotlib Introduction
152 Line Plots
153 Scatter Plots
154 Customizing Plots (Part 1)
155 Customizing Plots (Part 2)
156 Coding Exercise 6
157 Modules, Packages and Libraries – No need to reinvent the Wheel
158 Numpy Arrays
159 Indexing and Slicing Numpy Arrays
160 Vectorized Operations with Numpy Arrays
161 Changing Elements in Numpy Arrays & Mutability
162 View vs. copy – potential Pitfalls when slicing Numpy Arrays
163 Numpy Array Methods and Attributes
164 Numpy Universal Functions
165 Boolean Arrays and Conditional Filtering
166 Coding Exercise 7
167 How to work with nested Lists
168 dimensional Numpy Arrays
169 How to slice 2-dim Numpy Arrays (Part 1)
170 How to slice 2-dim Numpy Arrays (Part 2)
171 Recap Changing Elements in a Numpy Array slice
172 How to perform row-wise and column-wise Operations
173 Coding Exercise 8
174 Intro to Tabular Data Pandas
175 Create your very first Pandas DataFrame (from csv)
176 Pandas Display Options and the methods head() & tail()
177 First Data Inspection
178 Coding Exercise 9
179 Selecting Columns
180 Selecting one Column with the dot notation
181 Zero-based Indexing and Negative Indexing
182 Selecting Rows with iloc (position-based indexing)
183 Slicing Rows and Columns with iloc (position-based indexing)
184 Position-based Indexing Cheat Sheets
185 Selecting Rows with loc (label-based indexing)
186 Slicing Rows and Columns with loc (label-based indexing)
187 Label-based Indexing Cheat Sheets
188 Summary, Best Practices and Outlook
189 Coding Exercise 10
190 First Steps with Pandas Series
191 First Steps with Pandas Index Objects
192 Importing Time Series Data from csv-files
193 Initial Analysis Visualization of Time Series
194 Seaborn Introduction

What´s next
195 Get your special BONUS here!