How did you do that?

Or: How a Non-developer Snuck Python Into a Large Organization

Transcript

1. How did you do that? Or: How a Non-developer Snuck Python Into a Large Organization
2. Me?
3. Economist
4. Hobby developer With 7 years professional experience... in PHP!
5. My job
6. Business Developer
7. Business Developer Analyst Business Representative Tester Number Cruncher Geek Translator Excel Ninja
8. Python Smuggler
9. This Talk
10. No code Well, no python anyway
11. “It’s better to beg for forgiveness than to ask for permission”
12. “It’s better to beg for forgiveness than to ask for permission”
13. “Don’t ask for forgiveness. Ask for a raise” - Drunk EuroPython Attendee
14. Disclaimer THE AUTHOR MAKES NO REPRESENTATIONS OR WARRANTIES AS TO THE TIMELINESS, AVAILABILITY, ACCURACY OR COMPLETENESS OF ANY INFORMATION CONTAINED IN THIS PRESENTATION. LIKE ANY PRINTED MATERIALS, THE INFORMATION PROVIDED IN THIS PRESENTATION MAY BECOME OUTDATED OVER TIME. THE AUTHOR UNDERTAKES NO OBLIGATION TO CORRECT OR UPDATE ANY CONTENT OR INFORMATION ON THIS PRESENTATION. THE AUTHOR MAY MAKE IMPROVEMENTS AND/OR CHANGES IN THE CONTENT AND INFORMATION AT ANY TIME WITHOUT NOTICE, AND RESERVES THE RIGHT TO ALTER OR DELETE CONTENT AND INFORMATION IN THE PRESENTATION AT ANY TIME. ALL INFORMATION IN THIS PRESENTATION IS PROVIDED "AS IS," WITHOUT ANY WARRANTIES OF ANY SORT WHATSOEVER, ALL OF WHICH ARE EXPRESSLY DISCLAIMED. NEITHER THE AUTHOR, HIS FIRM, NOR ANYONE ELSE WILL BE LIABLE FOR YOUR USE OF OR RELIANCE ON INFORMATION CONTAINED ON THIS SITE UNDER ANY CIRCUMSTANCES, INCLUDING NEGLIGENCE. NEITHER SHALL THE AUTHOR, HIS FIRM, OR ANYONE ELSE BE LIABLE FOR ANY INJURY OR ANY DIRECT, INDIRECT, SPECIAL, EXEMPLARY, INCIDENTAL, CONSEQUENTIAL, PUNITIVE OR OTHER DAMAGES ARISING OUT OF ACCESS TO, USE OF OR INABILITY TO USE THE CONTENT OR MATERIALS ON THIS WEBSITE, EVEN IF THE AUTHOR IS INFORMED OF THE POSSIBILITY OF SUCH DAMAGES. APPLICABLE LAW MAY NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY OR CONSEQUENTIAL DAMAGES, SO THE ABOVE LIMITATION MAY NOT APPLY TO YOU, BUT IN NO EVENT SHALL ANY LIABILITY TO YOU FOR ALL DAMAGES, LOSSES AND CAUSES OF ACTION ARISING FROM OR RELATED TO THIS WEBSITE (WHETHER IN CONTRACT, TORT OR OTHERWISE) EXCEED THE AMOUNT PAID BY YOU, IF ANY, FOR ACCESSING THIS WEBSITE.
15. Disclaimer THE AUTHOR MAKES NO REPRESENTATIONS OR WARRANTIES AS TO THE TIMELINESS, AVAILABILITY, ACCURACY OR COMPLETENESS OF ANY INFORMATION CONTAINED IN THIS PRESENTATION. LIKE ANY PRINTED MATERIALS, THE INFORMATION PROVIDED IN THIS PRESENTATION MAY BECOME OUTDATED OVER TIME. THE AUTHOR UNDERTAKES NO OBLIGATION TO CORRECT OR UPDATE ANY CONTENT OR INFORMATION ON THIS PRESENTATION. THE AUTHOR MAY MAKE IMPROVEMENTS AND/OR CHANGES IN THE CONTENT AND INFORMATION AT ANY TIME WITHOUT NOTICE, AND RESERVES THE RIGHT TO ALTER OR DELETE CONTENT AND INFORMATION IN THE PRESENTATION AT ANY TIME. ALL INFORMATION IN THIS PRESENTATION IS PROVIDED "AS IS," WITHOUT ANY WARRANTIES OF ANY SORT WHATSOEVER, ALL OF WHICH ARE EXPRESSLY DISCLAIMED. NEITHER THE AUTHOR, HIS FIRM, NOR ANYONE ELSE WILL BE LIABLE FOR YOUR USE OF OR RELIANCE ON INFORMATION CONTAINED ON THIS SITE UNDER ANY CIRCUMSTANCES, INCLUDING NEGLIGENCE. NEITHER SHALL THE AUTHOR, HIS FIRM, OR ANYONE ELSE BE LIABLE FOR ANY INJURY OR ANY DIRECT, INDIRECT, SPECIAL, EXEMPLARY, INCIDENTAL, CONSEQUENTIAL, PUNITIVE OR OTHER DAMAGES ARISING OUT OF ACCESS TO, USE OF OR INABILITY TO USE THE CONTENT OR MATERIALS ON THIS WEBSITE, EVEN IF THE AUTHOR IS INFORMED OF THE POSSIBILITY OF SUCH DAMAGES. APPLICABLE LAW MAY NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY OR CONSEQUENTIAL DAMAGES, SO THE ABOVE LIMITATION MAY NOT APPLY TO YOU, BUT IN NO EVENT SHALL ANY LIABILITY TO YOU FOR ALL DAMAGES, LOSSES AND CAUSES OF ACTION ARISING FROM OR RELATED TO THIS WEBSITE (WHETHER IN CONTRACT, TORT OR OTHERWISE) EXCEED THE AMOUNT PAID BY YOU, IF ANY, FOR ACCESSING THIS WEBSITE. If you follow my advice and get fired, don’t blame me!
16. Banking IT
18. “Limit all lines to a maximum of 79 characters.” - pep8
21. COBOL ~1959
22. PL/I ~1965
23. Millions LOC?
24. Other Goodies • DB2 • SQL Server • C#/.NET • (+ custom framework) • SAS • JAVA • Lotus Notes (Eclipse based e- mail client) • Windows XP • IE7 (migrated from IE6 ~1 year ago)
25. Smuggling Python A case study
26. Assignment: Test cash flows, bookkeepings, interest calculation and risk reporting for 200k+ loans
27. ALMOST NO AUTOMATIC TESTING Oh, by the way:
28. Get some data Step 1:
29. 15 different tables Don’t blame me... I didn’t design the indexing 20 SQL queries
30. SELECT count(id) FROM loans.cashflows The query has been cancelled due to resource limit (96 CPU seconds)
31. Old workflow SQL Save SQL Save SQL Save Excel
32. Old workflow SQL Save SQL Save SQL Save Excel x20 per loan
33. Format data Step 2:
35. ~10 pivot tables 4-7 fields in each
36. Test Step 3:
37. Test Step 3: Hint: This is the only part that actually adds business value
38. Downsides • Really slow • Mind-numbingly boring • Really, really slow • Inconsistent • Ridiculously slow • Lots of room for error
39. Did I mention it was SLOW?
40. So... What does a lazy python programmer do?
42. No administrator rights on work pc Problem #1
43. Portable Python! Solution: http://www.portablepython.com/ Hint: Install it while your manager isn’t looking
44. DB2? Is there even a driver for that? Problem #2
45. ibm-db Solution: https://code.google.com/p/ibm-db/ Hint: Eggs are zip files. Just extract and install manually
46. Getting data into .xlsx format Problem #3
47. openpyxl Solution: https://bitbucket.org/ericgazoni/openpyxl Hint: Forget about pywin32. It’s more trouble than it’s worth
48. Make pivot tables Problem #4
49. Excel VBA Solution: Hint: Make an auto-installing add-on. And stop crying, it’s still better than doing it by hand!
50. Input Problem #5
51. .txt file Solution: Hint: Make a simple syntax. [filename1:]account1\n [filename2:]account2\n [filename3:]account3\n
52. Distribution (and avoiding detection) Problem #6
53. py2exe Solution: Hint: Put the output in a dist/ folder and include a bat file for easy launching. Distribute as a zip file
54. Problems solved Time to get coding
55. Low hanging fruit Step 1 and Step 2
56. New workflow Select Accounts Python Magic Excel Double Click
57. Python magic Excel Input SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL Split
58. Python magic • Queries are hugely expensive • Run as few as possible • Hit the right indexes (this gets nasty) • Use threading to run queries in parallel • Multiprocessing + py2exe is a no-go • Schedule expensive queries first
59. New workflow VBA Magic Test Run Macro Excel
60. VBA magic • Make required pivots • Add some metadata • e.g. account names • Do a little formatting • Add thousand separators • Color specific lines and panes
61. Result • Time/test case cut from 2+ hours to ~45 minutes • From 2-3 test cases/day to 6+ • Errors in data non-existent • Excel files have a standardized format • Flatter learning curve
62. Using Python increased our efficiency by ~300% Breakdown for managers:
63. Shh... Don’t tell them that Java/C#/VBA could have done exactly the same
64. Other examples
65. Conversion Tools Made conversions from old to new system a one man job. Used to take 2-3 people. On weekend overtime pay.
66. Reporting Checker From 4 hours/day to 30 min/day. Catch errors before our stakeholders report them.
67. Routing Checker Easy DSL for complicated payment routing rules. Reduces errors which would need manual correction.
68. Approval Listings We actually sent this to your stakeholders!
69. Takeaways Why did it work?
70. Do it for yourself Suggestion #1 And don’t tell anyone
71. Do it on your own time Suggestion #2
72. Go for the low-hanging fruit Suggestion #3 And don’t over-reach
73. Measure the value Suggestion #4
74. No really. Measure! Suggestion #4b
75. No really. Measure! Suggestion #4b Cost to the company if you did it on your own time: Nothing
76. No really. Measure! Suggestion #4b Cost to the company if you did it on your own time: Nothing Value for the company if it saves you 2 hours a day: 20 days/month at 2 hours/day = 40 hours/month
77. No really. Measure! Suggestion #4b 1 person = 40 hours 2 people = 80 hours 3 people = 120 hours 4 people = 160 hours That’s a man-month!
78. Get grass-level support Suggestion #5 Involve your colleagues
79. Be patient Suggestion #6 ... and honest
80. You won’t have to beg for forgiveness If you do this right:
82. Thank You @mipeddk