## Python for Excel Use xlwings for Data Science and Finance

### 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

### Requirements

A desktop computer (Windows or Mac) capable of storing and running Python/Anaconda. The course will walk you through installing the necessary free software.

An internet connection capable of streaming HD Videos.

A working installation of Microsoft Excel.

Mac users are welcome. Please note that 10%-15% of the course content (UDFs) work on Windows only!

Willingness to code and work with Python.

### Description

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 useExcel 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 scratchFor 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 webIt 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 Bloomberg-like Stock 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) standardizedintuitive to usemost users are well-trained it requires low/zero set upit requires low/zero maintenance and it´s still the best choice for financial models & spreadsheet calculationsWhy 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 toAutomate 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.As always, there is no risk for you as I offer a 30-Days-Money-Back-Guarantee. Looking forward to seeing you in the course!(Mac users are welcome! However, please note that 10%-15% of the course content (UDFs) is currently not available on Mac!)

### Overview

Section 1: Getting Started

Lecture 1 Introduction (don´t skip!)

Lecture 2 Course Overview (don´t skip!)

Lecture 3 Tips: How to get the most out of this Course (don´t skip!)

Lecture 4 FAQ / Your Questions answered

Lecture 5 How to download and install Anaconda for Python coding

Lecture 6 Jupyter Notebooks – let´s get started

Lecture 7 How to work with Jupyter Notebooks

Section 2: First Steps with xlwings (Reading and Writing Elements)

Lecture 8 Introduction and Downloads

Lecture 9 How to install xlwings

Lecture 10 How to use xlwings as a Data Viewer

Lecture 11 Data Viewer – Update

Lecture 12 How to connect to an Excel Workbook

Lecture 13 How to read and write single Values

Lecture 14 How to assign a name

Lecture 15 How to write Excel Functions with Python

Lecture 16 Range Shortcuts

Lecture 17 Case Study – Bringing it all together

Lecture 18 Homework

Section 3: Reading and writing many Values

Lecture 19 Section Downloads

Lecture 20 One-dimensional Data Structures

Lecture 21 How to write Values vertically

Lecture 22 Rows and Columns (1dim vs. 2dim)

Lecture 23 How to read two-dimensional Data Structures

Lecture 24 Advanced Reading with expand

Lecture 25 How to write two-dimensional Data Structures

Lecture 26 Range Indexing and Slicing

Lecture 27 Efficiency

Lecture 28 Homework

Section 4: Project 1: Monte Carlo Simulations in Excel with Python (Part 1)

Lecture 29 Introduction

Lecture 30 Section Downloads

Lecture 31 The Excel Model explained (Part 1)

Lecture 32 The Excel Model explained (Part 2)

Lecture 33 Running a simple Monte Carlo Simulation

Lecture 34 A more advanced and realistic Monte Carlo Simulation

Lecture 35 Final Considerations

Section 5: Running Python Scripts in Excel – RunPython

Lecture 36 Introduction and Downloads

Lecture 37 Installing the xlwings add-in and other preparations

Lecture 38 Running Python Scripts with “Run main”

Lecture 39 Troubleshooting (Part 1)

Lecture 40 All you need to know about VBA Macros

Lecture 41 Running Python Scripts with “RunPython”

Lecture 42 Troubleshooting (Part 2)

Lecture 43 Run main vs RunPython

Lecture 44 Excursus: Converting Jupyter Notebooks to .py

Lecture 45 Homework

Section 6: Project 1: Monte Carlo Simulations in Excel with Python (Part 2)

Lecture 46 Introduction and Downloads

Lecture 47 Monte Carlo Simulation with RunPython (Part 1)

Lecture 48 Monte Carlo Simulation with RunPython (Part 2)

Section 7: Using Matplotlib and Seaborn in Excel with xlwings

Lecture 49 Introduction and Downloads

Lecture 50 How to write a Matplotlib Plot into Excel

Lecture 51 How to update the Plot

Lecture 52 How to change Size and Position (Part 1)

Lecture 53 How to change Size and Position (Part 2)

Lecture 54 How write a Seaborn Plot into Excel

Lecture 55 How to create Excel Charts with Python

Lecture 56 Homework: Adding a Plot to the Monte Carlo Simulation (Project 1)

Section 8: Project 2: Build Dashboard Apps with Excel (GUI) and Python (analytical backend)

Lecture 57 Introduction and Downloads

Lecture 58 IMPORTANT NOTICE (Update January 23)

Lecture 59 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 1)

Lecture 60 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 2)

Lecture 61 Stock Performance Analysis during COVID-19 with Python & Pandas (Part 3)

Lecture 62 Building a Stock Performance Dashboard App (Part 1)

Lecture 63 Building a Stock Performance Dashboard App (Part 2)

Lecture 64 Improving the Source Code and Errors

Section 9: Reading and Writing Data Structures (Numpy, Pandas) & Converters

Lecture 65 Section Downloads

Lecture 66 (Default) Converters

Lecture 67 The Numpy Converter

Lecture 68 The Dictionary Converter

Lecture 69 The DataFrame Converter (Part 1)

Lecture 70 The DataFrame Converter (Part 2)

Lecture 71 Data Science Application: Inspecting and Manipulating DataFrames in Excel

Lecture 72 The Pandas Series Converter

Lecture 73 Excursus: How to load Data from Excel into Pandas with pd.read_excel()

Lecture 74 Excursus: Advanced import with pd.read_excel()

Lecture 75 Excursus: How to load Financial Data / Time Series with pd.read_excel()

Section 10: User-defined Functions (UDF) and Dynamic Arrays with xlwings (Windows only)

Lecture 76 Introduction and Downloads

Lecture 77 Preparations and your first UDF

Lecture 78 How to change the Name and Location of the Python Module

Lecture 79 Troubleshooting (UDF)

Lecture 80 UDFs – Behind the Scenes

Lecture 81 More complex UDFs and the @xw.arg Decorator

Lecture 82 How to create Numpy UDFs

Lecture 83 UDFs and Array Formulas

Lecture 84 How to create Dynamic Arrays with xlwings UDFs

Lecture 85 How to create Pandas UDFs

Lecture 86 How to add Docstrings

Lecture 87 Homework

Section 11: Project 3: Use Pandas UDFs in Excel for Data Science and Finance (Windows only)

Lecture 88 Introduction and Downloads

Lecture 89 IMPORTANT NOTICE (Update January 23)

Lecture 90 How to load Financial Data from the Web into Excel with the DataReader UDF

Lecture 91 How to resample Time Series in Excel with the resample UDF

Lecture 92 How to calculate Financial Returns with a Pandas/Numpy UDF

Lecture 93 How to get Summary Statistics of a Dataset with the describe UDF

Lecture 94 How to create a Dataset´s Correlation Matrix with the corr UDF

Lecture 95 Taken all together – the Super UDF

Lecture 96 How to perform inner/outer/left/right joins with the merge UDF

Section 12: APPENDIX 1: Python Crash Course for Excel Users

Lecture 97 Introduction and Overview

Lecture 98 Section Downloads

Lecture 99 Intro to the Time Value of Money (TVM) Concept (Theory)

Lecture 100 Calculate Future Values (FV) with Python / Compounding

Lecture 101 Calculate Present Values (FV) with Python / Discounting

Lecture 102 Interest Rates and Returns (Theory)

Lecture 103 Calculate Interest Rates and Returns with Python

Lecture 104 Introduction to Variables

Lecture 105 Excursus: How to add inline comments

Lecture 106 Variables and Memory (Theory)

Lecture 107 More on Variables and Memory

Lecture 108 Variables – Dos, Don´ts and Conventions

Lecture 109 The print() Function

Lecture 110 Coding Exercise 1

Lecture 111 TVM Problems with many Cashflows

Lecture 112 Intro to Python Lists

Lecture 113 Zero-based Indexing and negative Indexing in Python (Theory)

Lecture 114 Indexing Lists

Lecture 115 For Loops – Iterating over Lists

Lecture 116 The range Object – another Iterable

Lecture 117 Calculate FV and PV for many Cashflows

Lecture 118 The Net Present Value – NPV (Theory)

Lecture 119 Calculate an Investment Project´s NPV

Lecture 120 Coding Exercise 2

Lecture 121 Data Types in Action

Lecture 122 The Data Type Hierarchy (Theory)

Lecture 123 Excursus: Dynamic Typing in Python

Lecture 124 Build-in Functions

Lecture 125 Integers

Lecture 126 Floats

Lecture 127 How to round Floats (and Integers) with round()

Lecture 128 More on Lists

Lecture 129 Lists and Element-wise Operations

Lecture 130 Slicing Lists

Lecture 131 Slicing Cheat Sheet

Lecture 132 Changing Elements in Lists

Lecture 133 Sorting and Reversing Lists

Lecture 134 Adding and removing Elements from/to Lists

Lecture 135 Mutable vs. immutable Objects (Part 1)

Lecture 136 Mutable vs. immutable Objects (Part 2)

Lecture 137 Coding Exercise 3

Lecture 138 Tuples

Lecture 139 Dictionaries

Lecture 140 Intro to Strings

Lecture 141 String Replacement

Lecture 142 Booleans

Lecture 143 Operators (Theory)

Lecture 144 Comparison, Logical and Membership Operators in Action

Lecture 145 Coding Exercise 4

Lecture 146 Conditional Statements

Lecture 147 Keywords pass, continue and break

Lecture 148 Calculate a Project´s Payback Period

Lecture 149 Defining your first user-defined Function

Lecture 150 What´s the difference between Positional Arguments vs. Keyword Arguments?

Lecture 151 How to work with Default Arguments

Lecture 152 Coding Exercise 5

Section 13: APPENDIX 2: Matplotlib, Numpy, Pandas and Seaborn Crash Course

Lecture 153 Downloads for this Section

Lecture 154 Matplotlib Introduction

Lecture 155 Line Plots

Lecture 156 Scatter Plots

Lecture 157 Customizing Plots (Part 1)

Lecture 158 Customizing Plots (Part 2)

Lecture 159 Coding Exercise 6

Lecture 160 Modules, Packages and Libraries – No need to reinvent the Wheel

Lecture 161 Numpy Arrays

Lecture 162 Indexing and Slicing Numpy Arrays

Lecture 163 Vectorized Operations with Numpy Arrays

Lecture 164 Changing Elements in Numpy Arrays & Mutability

Lecture 165 View vs. copy – potential Pitfalls when slicing Numpy Arrays

Lecture 166 Numpy Array Methods and Attributes

Lecture 167 Numpy Universal Functions

Lecture 168 Boolean Arrays and Conditional Filtering

Lecture 169 Coding Exercise 7

Lecture 170 How to work with nested Lists

Lecture 171 2-dimensional Numpy Arrays

Lecture 172 How to slice 2-dim Numpy Arrays (Part 1)

Lecture 173 How to slice 2-dim Numpy Arrays (Part 2)

Lecture 174 Recap: Changing Elements in a Numpy Array / slice

Lecture 175 How to perform row-wise and column-wise Operations

Lecture 176 Coding Exercise 8

Lecture 177 Intro to Tabular Data / Pandas

Lecture 178 Create your very first Pandas DataFrame (from csv)

Lecture 179 Pandas Display Options and the methods head() & tail()

Lecture 180 First Data Inspection

Lecture 181 Coding Exercise 9

Lecture 182 Selecting Columns

Lecture 183 Selecting one Column with the “dot notation”

Lecture 184 Zero-based Indexing and Negative Indexing

Lecture 185 Selecting Rows with iloc (position-based indexing)

Lecture 186 Slicing Rows and Columns with iloc (position-based indexing)

Lecture 187 Position-based Indexing Cheat Sheets

Lecture 188 Selecting Rows with loc (label-based indexing)

Lecture 189 Slicing Rows and Columns with loc (label-based indexing)

Lecture 190 Label-based Indexing Cheat Sheets

Lecture 191 Summary, Best Practices and Outlook

Lecture 192 Coding Exercise 10

Lecture 193 First Steps with Pandas Series

Lecture 194 First Steps with Pandas Index Objects

Lecture 195 Importing Time Series Data from csv-files

Lecture 196 Initial Analysis / Visualization of Time Series

Lecture 197 Seaborn Introduction

Section 14: What´s next? (outlook and additional resources)

Lecture 198 Bonus Lecture

Data Scientist and Finance Professionals seeking to use Excel as Frontend and Python as analytical Backend in their Projects.,Excel professionals seeking to write Excel tools with clean Python code instead of VBA/Marcos.,Python Beginners are welcome as the course includes a Python Crash Course designed for Excel Professionals.,Python Developers seeking to work with Excel as GUI (Graphical User Interface).

#### Course Information:

Udemy | English | 16h 15m | 5.52 GB

Created by: Alexander Hagmann

You Can See More Courses in the Office Productivity >> Greetings from CourseDown.com