xlwings Presentation (Excel & Python) Swiss FinteCH event
-
Upload
zoomer-analytics-llc -
Category
Software
-
view
535 -
download
3
Transcript of xlwings Presentation (Excel & Python) Swiss FinteCH event
Excel & Python• Open Source Technology in Fintech, Zürich
July 14th, 2016 Felix Zumstein, CFA
Materialhttps://github.com/ZoomerAnalytics/talks/tree/master/SwissFinteCH_zrh_20160714
2
About me
• Consultancy (Zürich):
– Consulting: Web apps for Finance
– Workshops: Excel & Python
– Open-source: xlwings
• Previously:
– 9yrs in Banking /Asset Management
– Background: Finance & Economics
3
Agenda
4
1) Scripting/Interaction• Interactive data analysis• Automated Testing of Spreadsheet Models
2) Macros• Monte Carlo Simulation• From Excel to web apps
3) User Defined Functions (UDFs)• Correlation Analysis
1 Scripting/Interaction
GDP per capita
6
Source of xls file:http://data.worldbank.org/indicator/NY.GDP.PCAP.CD
+
Spreadsheet Risk: Automated Testing
7
• Audit/Regulation require effective controls
• Automated unit tests are a cornerstone of modern software development (”test-driven development”)
• Excel: doesn’t offer any means to create unit tests
• 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
2 Macros
Monte Carlo Simulation
9
simulation.py
xlwings_app.py web_app.py
Source code and hosted app
10
Hosted Sample: www.zoomeranalytics.com/simulation-demo
Source Code: https://github.com/ZoomerAnalytics/simulation-demo
Prototype analytical web apps
11
vs.
3 UDFs
DJIA: Correlation Analysis
13
=CORREL(array1, array2)
Excel’s Correlation formula accepts just 2 data sets:
Here’s how we’re going to fix this:
• Add CORREL2 to get the full correlation matrix• Visual representation: Heatmap
Questions?
twitter:@zoomeranalytics
xlwings newsletter:via xlwings.orgwww:xlwings.org, zoomeranalytics.com