python. finance. excel. - the thalesians
Post on 11-Apr-2017
651 Views
Preview:
TRANSCRIPT
Python. Finance. Excel.• The Thalesians, Zürich
June 9th, 2016 Felix Zumstein, CFA
About me
• Consultancy:
– Analytical apps for Excel & web
– Professional support/training for xlwings
• Previously:
– 9yrs in Banking /Asset Management
– Background: Finance & Economics
2
About this talk
3
This talk is NOT about sophisticated models that make you rich.
This talk is about HOW to implement classic financial models using Python and Excel.
Download the material from: https://github.com/ZoomerAnalytics/talks
Agenda
4
1) Portfolio Optimization• Intro to fundamental packages for finance• xlwings: Interactive use
2) Monte Carlo Simulation• Simulation with NumPy• xlwings: From Excel macros to web apps
3) Option Pricing and Implied Volatility• Introduction to QuantLib• xlwings: User Defined Functions (UDFs)
4) Save the Spreadsheet
1 Portfolio Optimizationxlwings: Interactive use
Jupyter Notebook
6
This demo gives an introduction to:
• Jupyter notebook• Pandas• xlwings• Matplotlib/Seaborn• Plotly• SciPy• cvxopt
xlwings: Basic Syntax
7
>>> from xlwings import Workbook, Range
>>> wb = Workbook(…)>>> Range("A1").value = my_variable>>> Range("A1").options(pd.DataFrame,
expand="table").value
my_variable: Strings, numbers, DateTime, lists (nested), NumPy arrays, Pandas DataFrames, …
cvxopt
8
• GPL license (SciPy: BSD)
• Installation:- Mac/Linux: conda install cvxopt- Windows: Download wheel from
http://www.lfd.uci.edu/~gohlke/pythonlibs/#cvxoptthen pip install it
• CVXPY: Python-embedded modeling language for convex optimization problems. It wraps the solvers cvxopt, ECOS and SCS (cvxpy.org).
2 Monte Carlo Simulationxlwings: Excel Macros
10
Random walk
𝑑𝑆# = 𝜇𝑆#𝑑𝑡 + 𝜎𝑆#𝑑𝑊#
In the Black-Scholes model, asset prices 𝑆# are assumed to follow a geometric Brownian motion (GBM), as defined by this stochastic differential equation (SDE):
𝜇: drift, 𝜎: volatility, 𝑊#: brownian motion
Discrete time version:
𝑆# = 𝑆#+,# exp 𝜇 −𝜎1
2Δ𝑡 + 𝜎𝜀# Δ𝑡
S[0, :] = starting_pricefor t in range(n_timesteps):
rand_nums = np.random.randn(n_simulations)S[t + 1, :] = S[t, :] * np.exp((mu -‐ 0.5 * vol ** 2) * dt +
vol * rand_nums * np.sqrt(dt))
Python: (for full context look at the repo as mentioned on slide 13)
Same code, different front end
11
simulation.py
xlwings_app.py web_app.py
Prototype analytical web apps
12
vs.
Source code and hosted app
13
Hosted Sample: www.zoomeranalytics.com/simulation-demo
Source Code: https://github.com/ZoomerAnalytics/simulation-demo
xlwings: Call Python from VBA
14
Sub MyFunction()RunPython ("import module;module.func()")
End Sub
def func():wb = xw.Workbook.caller()
module.py
VBA:
3 Option Pricing & Implied Volatility
xlwings: UDFs
QuantLib
16
• Open-source framework for quant finance. Released in 2000 and written in C++ with bindings for many languages.
• ”Offers […] features such as market conventions, yield curve models, solvers, PDEs, Monte Carlo (low-discrepancy included), exotic options, VAR, and so on.” (quantlib.org)
• Installation- Windows: Download the Python wheel from
http://www.lfd.uci.edu/~gohlke/pythonlibs/#quantliband pip-install it
- Linux: apt-‐get install quantlib-‐python quantlib-‐swig- Mac: Err…Right now, no binaries (wheels or conda
package) and building it is not exactly fun
PyQL
17
• The official Python bindings for QuantLib (“quantlib-python”) are created with SWIG (Simplified Wrapper and Interface Generator)
• Enthought started the PyQL project that uses Cython instead of SWIG. PyQL offers (from the PyQL docs):
- Integration with standard datatypes (like datetime objects) and numpy arrays;
- Simplified API on the Python side (e.g. usage of Handles completely hidden from the user);
- Support full docstring and expose detailed function signatures to Python;
- Code organised in subpackages to provide a clean namespace, very close to the C++ code organisation;
- Easy extendibility thanks to Cython and shorter build time when adding new functionalities;
- Sphinx documentation.
• However, PyQL covers only a fraction of the official bindings.
Options: Implied Volatility
18
Implied volatility is the volatility that makes the option price calculated by a model equal to its observed market price.
𝐶 𝐹, 𝜏 = 𝐷(𝑁 𝑑< 𝐹 − 𝑁 𝑑+ 𝐾)
𝑑± = 1
𝜎 𝜏 log
𝐹𝐾 ±
12𝜎
1𝜏
Black-Scholes formula (alternative formulation):
𝑃 𝐹, 𝜏 = 𝐷(𝑁 −𝑑+ 𝐾 −𝑁 −𝑑< 𝐹)
𝐶,𝑃: Call/Put Option, 𝐷: discount factor, 𝐹 = 𝑆E𝑒 G+H I: forward price,𝑁(J): cumulative distribution function of std normal distribution,𝜎: volatility of returns of underlying asset, 𝐾: Strike, 𝜏: time to maturity, 𝑟: cont. comp. risk-free rate, 𝑞: dividend yield
xlwings: UDFs
19
@xw.func@xw.arg("x", pd.DataFrame)def myfunction(x):
return x
4 Save the Spreadsheetxlwings: VBA unit tests
Tough times for spreadsheet models
21
"Where a bank relies on manual processes and desktop applications (e.g. spreadsheets, databases) and has specific risk units that use these applications for software development, it should have effective mitigants in place (e.g. end-user computing policies and procedures) and other effective controls that are consistently applied across the bank's processes. "
– Basel Committee on Banking Supervision, Principle 3
Unit Tests
22
• Automated unit tests are a cornerstone of modern software development (”test-driven development”)
• Excel: doesn’t offer any means to create unit tests (there’s Debug.Assert, but that doesn’t really help)
• Python: has a built-in unit test module
• Unit testing with xlwings is unintrusive:
- No add-in to be installed
- No changes to the original VBA code base
- Python standard lib + xlwings is all you need
• It’s a great way to verify & debug legacy VBA code
Running VBA code with xlwings
23
xlwings has a beauuuutiful interface to access VBA Subs and Functions:
VBA:
Function MySum(x, y)MySum = x + y
End Function
Python:
>>> wb = xw.Workbook.active()>>> my_sum = wb.macro('MySum')>>> my_sum(1, 2)3
Testing with xlwings
24
xlwings allows you to:
• Unit test spreadsheets with or without VBA code
• Test VBA code against an alternative implementation in Python (incl. any third party packages like SciPy or QuantLib that are usually heavily tested)
Demo: Prime numbers
Questions?
top related