VBA voor smarties: Excel & Python

Excel & Python

Excel is onderdeel van MS Office.
VBA is de programmeertaal van MS Office.
Python is een programmeertaal die begin jaren 90 is ontwikkeld.
In tegenstelling tot VBA is Python een Open Source programmeertaal.
Het aantal ontwikkelaars van Python is daardoor vele malen groter dan dat van VBA.
In Python zijn veel sub-bibliotheken ontwikkeld die een aanvulling kunnen zijn op de mogelijkheden van de MS-Office programma's.
Een evident voordeel van Python is bijvoorbeeld de verwerking van oneindig grote getallen.

Zoals blijkt uit het bij MS ingediende wensenlijstje voor de ontwikkeling van VBA zien veel Python-programmeurs graag een nauwe samenwerking tussen Python en MS-Office.
Daarmee komen bijv. de draaitabellen, draaitabelgrafieken in Excel ook voor Python programmeurs beschikbaar.
In het Python platform zijn diverse pogingen gedaan Python en bijv. Excel aan elkaar te koppelen.
De belangrijkste representant daarvan is XLwings.
XLwings maakt deel uit van de Anaconda distributie van Python: https://www.anaconda.com/download/

In mijn zoektocht naar de koppeling van Excel en Python heb ik me in XLwings verdiept.
XLwings probeert in een eigen Python sub-bibliotheek (xlwings.py) alle faciliteiten van Excel onder te brengen.
Dat lijkt mij geen handige weg.

Ik heb gezocht naar een alternatief waarbij:
- Python vanuit Excel kan worden gestart
- argumenten vanuit Excel kunnen worden meegegeven aan de Python code
- de resultaten van Python in een door Excel leesbare vorm worden opgeslagen: een csv-bestand of een afbeelding
- het Excelbestand automatische koppelingen heeft met de Python output via queries
- de gebruiker de Pythoncode ook in het Excelbestand kan bewerken.

Een manier om XLwings en mijn benadering te vergelijken leek mij het gebruik van de XLwings voorbeelden.
Die vind je hier: https://www.xlwings.org/examples

Voordelen van mijn aanpak:
- er is geen aparte Python bibliotheek nodig
- de bewerking van de Python output vindt in Excel plaats en niet in de Python code
- Python wordt alleen ingezet voor wat Excel niet kan
- het gebruik van Python-gegevens in Excel is daardoor flexibeler
- het gebruik van Excel-events (gebeurtenissen) maakt knoppen in het werkblad overbodig
- de Python tab in het lint bevat de knoppen voor herberekening/updating/refreshing
- er is geen speciale Add-In vereist
- het Pythonbestand kan in een userform in Excel bekeken en bewerkt worden: opening van een aparte Python editor is niet nodig
- alle voorzieningen van Excel (nu en in de toekomst) staan ter beschikking; er is geen interface-ontwikkelaar nodig om de Python bibliotheek te actualiseren
- in feite gebruik ik Excel als 'front-end' en Python als 'back-end'

De voorbeelden:

- een Fibonaccireeks
- een MonteCarlo-simulatie
- het gebruik van een database
- het binnenhalen en verwerken van gegevens van een website
- de grafische weergave van berekeningen die Excel niet kent
- het gebruik van Python als UDF

Bij ieder voorbeeld staat een groene knop waarmee een ZIP-bestand met Excel- en Python-bestand gedownload kan worden.

Excel als Python GUI

Met behulp van de VBA-mogelijkheden in Excel kun je een VBA_Userform gebruiken als Grafische User Interface voor Python.
Python heeft zelf geen ingebouwde GUI.
De Python-bibliotheek waarmee je wel zelf een GUI kunt maken is op zijn zachtst gezegd moeizaam.
Dus waarom dan niet gebruik gemaakt van de ingebakken grafische VBA-voorzieningen.

Vooraf

Installeer Python om de voorbeelden te kunnen gebruiken/testen.
Python kent op dit moment 2 'smaken': Python 2.x en 3.x.
De x staat voor een versienummer.
De voorbeelden in deze webpagina draaien onder beide versies, met uitzondering van de database.
Voor de database is Python 3.x vereist.
Als Python goed is geïnstalleerd volstaat "py -2" om Python 2.x en "py -3" om Python 3.x te starten.
Daar gaat de code in de voorbeeldbestanden vanuit.

De voorbeelden

De voorbeelden zitten in een Zip-bestand.
Ieder Zip-bestand bevat een
- Excel *.xlsb bestand
- Python *.py bestand
- een *.csv bestand
Ieder Excel-bestand bevat
een Python-tab in het Lint
met een knop Py edit om het Python bestand te bekijken/bewerken
met knop(pen) om macro's te starten
NB. Houd er rekening mee: een Userform toont het Pythonbestand als platte tekst
Het Userform is geen Python-editor met hulpmiddelen.

Werkwijze

Pak het Zip-bestand uit in een aparte, eigen directory.
Open het Excelbestand.
De knop(pen) in de Python-tab starten de macro's die de Python-code laten lopen.
Het resultaat komt in het werkblad terecht.
De eerste keer dat de code draait kan die trager zijn, omdat er bibliotheken in het geheugen geladen moeten worden.

Excel als Python GUI Python GUI

De voorbeelden 'Fibonacci', 'simulatie', 'plot' en 'database' heb ik geïntegreerd in het werkboek 'Python GUI.xlsb'.
Het website-voorbeeld zit er niet in omdat daarvoor de draaitabel en de slicers in Excel de grafische interface zijn.
Het UDF-voorbeeld zit er niet in, omdat dat juist gaat over wat er in een werkblad gebeurt.

Bij het openen van het bestand wordt meteen het Userform getoond.
Het Userform bevat 1 Multipage met 5 tabs.
Alle Pythoncode zit nu in 1 Python-file: 'Excel.py'.
Excel roept via de knoppen in het Userform de afzonderlijke funkties in het Pythonbestand op.
Als Python extra bibliotheken nodig heeft –zoals bijv. numpy- roept de code die pas op als de funktie start.
Er worden zo geen overbodige bibliotheken in het geheugen geladen.

De Pythoncode schrijft resultaten naar een 'csv'-bestand of een grafisch bestand.
Excel kan een koppeling bevatten met een bestand via een Querytable of met de eigenschap van een object (Shape of Picture). Met VBA kun je het csv-bestand rechtstreeks uitlezen en de resultaten in een Excel werkblad plaatsen.
In het voorbeeldbestand staat iedere gekoppelde Querytable in een eigen, verborgen werkblad.
Dat geldt voor de Querytables 'database' en 'simulation'.

Fibonacci Fibonacci

Geef in cel A2 aan hoeveel Fibonaccigetallen je wil hebben.
De VBA code in de werkblad_change gebeurtenis aktiveert de Pythoncode.
De Fibonaccigetallen komen vanaf cel B1 tevoorschijn.

Geef in cel C2 en D2 aan van welke getal je de dubbele som wil hebben: 2*(C2+D2)
De VBA code in de worksheet_change gebeurtenis aktiveert de Pythoncode.
Het resultaat komt in cel B1.

In de Python-tab in het lint kun je tussen beide berekeningen schakelen met de knoppen 'Double' en 'Fibonacci'.

Dit voorbeeldbestand bevat 1 Querytable (in cel B1) voor verschillende Pythonberekeningen.
De inschakeling van Python voor de berekening van de dubbele som is alleen maar interessant als het om grote getallen gaat die Excel niet kan verwerken.

Kijk bijvoorbeeld ook eens naar het resultaat als je 600 Fibonacci-getallen wilt hebben.

Python-bestand

def main(n,n_csv):

c_00 = ''
a, b = 0, 1
for it in range(n):
a, b = b, a + b
c_00 += str(a) +'\n'
open(n_csv,'w').write(c_00)

def d_sum(x, y, n_csv):

open(n_csv,'w').write(str(2 * (x + y))+'\n \n ')

Simulatie Simulatie

Geef in de cellen B1:B7 de parameters in voor de simulatie.
Klik op de knop 'Refresh' in de Python tab van het lint.
De webquery in het verborgen werkblad 'data' leest resultaten van de Pythonberekening.
De grafiek in werkblad 'simulation' heeft de Querytable als DataSource.
Zou gauw de Querytable ververst is wordt de tabel geactualiseerd.

Met de knop 'Py edit' in de Python tab kun je de Pythoncode oproepen, bekijken, eventueel wijzigen en opslaan.

Python-bestand

import numpy as np

def main(n_0,n_1,n_2,n_3,n_4,n_5,n_csv):

n_3=np.log(1 + float(n_3))
n_4=float(n_4)
n_6=float(float(n_1)/n_2)

perc_selection = [5, 50, 95] # hardcoded

price = np.zeros((n_2 + 1, n_0))
percentiles = np.zeros((n_2 + 1, 3))

price[0,:] = n_5
percentiles[0,:] = n_5

for t in range(1, n_2 + 1):

rand_nums = np.random.randn(n_0)
price[t,:] = price[t-1,:] * np.exp((n_3 - 0.5 * n_4**2) * n_6 + n_4 * rand_nums * np.sqrt(n_6))
percentiles[t, :] = np.percentile(price[t, :], perc_selection)

sy=[t* n_6 for t in range(0,n_2+1)]

open(n_csv,'w').write('')
for (it,it1,it2) in zip(percentiles,price,sy):

open(n_csv,'a').write(str(it[0])+"," +str(it[1])+"," + str(it[2]) +','+str(it1[0]) +','+str(it2) +'\n')

Bij opening van het bestand wordt de verbinding van de Querytable aangepast aan de lokatie van het CSV-bestand.

De code voor het starten van Python staat in de codemodule van Sheet1.
De tabel kun je verplaatsen naar iedere gewenste plek in het werkblad.

Database Database

Het Zip bestand bevat:
- het Excelbestand database.xlsb
- het Pythonbestand database.py
- de csv-bestanden combo.csv en list.csv
- een Python databasebestand: chinook.sqlite
Het Pythonprogramma maakt gebruik van de Python bibliotheek 'sqlite'.

Het Excelbestand

bevat 2 querytables:

-

De lijst van alle in de database beschikbare muziekgenres.
Deze Querytable bevindt zich in het verborgen werkblad 'source'.
De combobox 'C_00' in werkblad 'playlist' wordt gevuld met de gegevens van de Querytable in werkblad 'source'.
Deze Querytable is gekoppeld aan het 'combo.csv' bestand. Daarin staat het resultaat van de selektie van muziekgenres in de database 'chinook.sqlite'.
De funktie 'combo' in 'database.py' maakt deze muziekgenreselektie.

Het werkblad 'playlist' bevat de querytabel 'playlist'.
Deze querytabel is gekoppeld aan het list.csv bestand.
Daarin staat het resultaat van de filtering van de database 'chinook.sqlite' op basis van de selektie in combobox 'C_00'.
De funktie 'list' in 'database.py' maakt de selektie van muziektracks.

Verversen van de lijst met muziekgenres kan met de knop 'combo' in de Python tab in het Lint.

De selektie van tracks kan met de knop 'Playlist' in de Python tab in het lint.
De selektie kan ook plaatsvinden als gevolg van de keuze in combobox 'C_00'.
Dat is een veel voor-de-hand-liggender aanpak.
Dat is geïmplementeerd in de 'C_00_change'-gebeurtenis.

Met de knop 'Py edit' in de Python tab kun je de Pythoncode oproepen, bekijken, eventueel wijzigen en opslaan.

De code in de gebeurtenis ThisWorkbook_Open past de koppelingen van de Querytables aan aan de lokatie van de CSV-bestanden.

Het Python-bestand

De code voor het starten van Python staat in de codemodule van Sheet1.
De Python code illustreert 2 methoden om een SQL-string in Python uit te laten voeren:
1. de SQL-string staat in de Pythoncode hard gecodeerd: de funktie 'list'
2. de SQL-string wordt als argument vanuit Excel aan de Pythoncode doorgegeven: de funktie 'combo'

import sqlite3

def list(n_db,n_sql,n_csv):

cursor = sqlite3.connect(n_db, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES).cursor()

cursor.execute(

"""
SELECT
t.Name AS Track, alb.Title AS Album, art.Name AS Artist, t.Composer
FROM PlaylistTrack pt
INNER JOIN Track t ON pt.TrackId = t.TrackId
INNER JOIN Album alb ON t.AlbumId = alb.AlbumId
INNER JOIN Artist art ON alb.ArtistId = art.ArtistId
WHERE PlaylistId = ?
""", (n_sql,))

open(n_csv,'w').write(','.join(it[0] for it in cursor.description)+'\n')
open(n_csv,'a').write('\n'.join(','.join(str(it1).replace(',',' ') for it1 in it) for it in cursor))

def combo(n_db,n_sql,n_csv):

cursor = sqlite3.connect(n_db, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES).cursor()
open(n_csv,'w').write('\n'.join(it[1] for it in cursor.execute(n_sql)))

Web-gegevens Web

Dit bestand is geen voorbeeldbestand van XLwings.
Het is ontwikkeld op verzoek van een klant.
De URL met gegevens bevat ten tijde van de ontwikkeling van de code ca. 417000 records.
Als de URL niet meer bestaat kun je die eenvoudig vervangen in de code van macro 'M_refresh'.

Het Pythonprogramma maakt gebruik van de Python programmabibliotheek 'requests'.

Het werkblad 'data' bevat een querytable 'data'.
De querytabel is gekoppeld aan het bestand 'data.csv'.
Daarin schrijft de Pythoncode het resultaat van de 'webscraping'.
Het werkblad 'data' kan verborgen worden.

Het werkblad 'pivottable' bevat een draaitabel.
De 'Data Source' van de draaitabel is de querytable in werkblad 'data'.
Iedere keer als de webgegevens worden geaktualiseerd wordt de draaitabel bijgewerkt.

De verversing van Web-gegevens kan met de knop 'Refresh' in de Python tab in het lint.
De code voor het starten van Python staat in de codemodule van Sheet1.
Met de knop 'Py edit' in de Python tab kun je de Pythoncode oproepen, bekijken, eventueel wijzigen en opslaan.

Python-bestand

import requests

def load_data(n_url,n_csv):

open(n_csv, 'w', newline="").write(requests.get(n_url).text)

De code in de gebeurtenis ThisWorkbook_Open past de koppeling van de Querytable aan aan de lokatie van het CSV-bestand 'data.csv'.

Plotting results Plot

Het pythonprogramma maakt gebruik van 2 speciale Python bibliotheken:
- 'Numpy' voor mathematische berekeningen en
- 'Matplotlib' voor de grafische weergave van mathematische berekeningen.

Voer in werkblad 'streamplot' in cel B1 de parameterwaarde (geheel getal) in voor de berekening in Numpy.
Start het Pythonprogramma met de knop 'Draw' in de Python-tab van het Lint.

De uitvoer van het Pythonprogramma is een grafisch bestand 'graph.png'.
De aanroepende Excelmacro 'M_plot' laadt het grafische bestand in het werkblad.
De Excelmacro bevindt zich in de macromodule van werkblad 'streamplot'.

Met de knop 'Py edit' in de Python tab kun je de Pythoncode oproepen, bekijken, eventueel wijzigen en opslaan.

Python-bestand

import numpy as np
import matplotlib.pyplot as plt

def main(n,n_png):

# Based on: http://matplotlib.org/users/screenshots.html#streamplot
Y, X = np.mgrid[-3:3:100j, -3:3:100j]
U = -1 + n * X**2 + Y
V = 1 - n * X - Y**2

fig, ax = plt.subplots(figsize=(6, 4))
strm = ax.streamplot(X, Y, U, V, color=U, linewidth=2, cmap=plt.cm.autumn)
fig.colorbar(strm.lines)

fig.savefig(n_png)

UDF UDF

XLwings heeft als voorbeeld een Excelblad met UDF's gemaakt.
De combinatie van UDF's met Python is een ongelukkige.
UDF's worden in veel gevallen veel vaker herberekend dan strikt genomen nodig is.
Dat geldt zelfs als in een ander openstaand bestand een verandering optreedt.
Je kunt beter de methode gebruiken zoals beschreven is in het bestand Fibonacci.xlsb.
Die methode is veel robuuster: er wordt alleen iets herberekend als een van de elementen waarop de berekening berust gewijzigd is.
Alle andere berekeningen worden dan met rust gelaten.
Dat bespaart een hoop overbodige rekentijd en vertraging.
Mijn advies is dus om voor UDF's en Python niet te combineren.

Om te illustreren dat het kan zonder XLwings en hoe dat dan gaat heb ik het voorbeeld van XLwings omgezet.

Het ZIP-bestand bevat:
- Excel-bestand UDF.xlsb
- 4 csv bestanden
- Python-bestand udf.py

Het voorbeeldbestand 'UDF.xlsb' bevat 4 Querytables: 'data_001 ...data_004'.
Deze queries zijn gekoppeld aan een 4-tal csv-bestanden 'data_001.csv ....data_004.csv'
Drie van de Querytables staan in werkblad 'UDF', Querytable 'data_001' staat in het verborgen werkblad 'data'.
UDF's staan in Excel altijd in een macromodule; in dit geval module 'python_udf'.

De 'workbook_Open' gebeurtenis bevat code die de koppelingen van de Querytables bijwerkt.
De koppeling verwijst naar de directory van het Excelbestand 'UDF.xlsb'.

Omdat het gebruik van UDF's veroorzaakt dat iedere wijziging in het werkblad de verversing van het werkblad bewerkstelligt, bevat de Python tab van het lint geen knop om macro's te aktiveren.

Met de knop 'Py edit' in de Python tab kun je de Pythoncode oproepen, bekijken, eventueel wijzigen en opslaan.

In het voorbeeld van XLwings staat als eerste UDF een UDF die via Python de naam van het werkboek achterhaalt.
Dat is vergezocht: de Excel funktie =cell("filename") geeft hetzelfde resultaat.
Als je al een UDF wil gebruiken is dit voor-de-hand-liggender (en veel sneller):

Function F_snb()

F_snb=thisworkbook.fullname

End Function

Het Python bestand udf.py bevat 4 funkties: 'double_sum, add_one, matrix_mult en correl'.
De funktie 'correl' maakt gebruik van de Python bibliotheek Pandas (onderdeel van de Anaconda distributie).
Het is mij niet gelukt deze code aan het praten te krijgen.

De overige funkties worden in standaard Python afgehandeld.

Python-bestand

import pandas as pd

def double_sum(x, y, n_csv):

open(n_csv,'w').write(str(2 * (x + y)) + '\n \n')

def add_one(sn, n_csv):

open(n_csv,'w').write('\n'.join(','.join(str(it1+1) for it1 in it) for it in sn))

def matrix_mult(sn, sp,n_csv):

open(n_csv,'w').write('\n'.join(','.join(str(sn[x][y]*sp[x][y]) for y in range(len(sn))) for x in range(len(sn))))

def correl(sn,n_csv):

st=pd.dataframe(sn).corr()
open(n_csv,'w').write('\n'.join(','.join(str(it1+1) for it1 in it) for it in st))