Applications (Page en phase de rédaction)





On choisit d’associer VBA et Python principalement quand cela simplifie le code, permet d’utiliser des bibliothèques de fonctions sophistiquées, ou bien utilise des bases de données tierces plus robustes pour traiter des fichiers volumineux

 
Par exemple, à supposer qu’une plage de données dans Excel, à partir desquelles nous souhaitons calculer des statistiques, comporte beaucoup de cellules non renseignées (des trous), il n’est pas justifié de passer directement cette plage à une fonction développée en python, car la validation de cette plage (tests sur les valeurs, remplissage des vides) se fait plus simplement en VBA

Exemple pratique: Soit une zone de 3 colonnes de données à partir de [F3] (c’est à dire une plage de données à partir de [F3:H3]). Pour identifier cette région et afficher des 0 dans les cellules non renseignées, le code VBA correspondant est
 

 
Une fois ces instructions exécutées, la plage de cellules u est passée à la fonction python
 
Les exemples applicatifs qui vont suivre ne sont qu’un échantillon de situations-type, s’adaptant ensuite à des contextes variés. Pour décider rapidement ce qui doit être laissé à VBA et ce qui revient à Python, on peut poser que

  • la récupération et la validation des données sont laissés à VBA
  • les variables intermédiaires jugées utiles de conserver pour d’autres parties de programme, le sont en VBA (sauf cas particulier). Rappelons qu’une fonction python peut renvoyer une valeur de type hybride (par ex. une liste + un float), mais que VBA ne peut pas la récupérer.
  • dès qu’il y a des calculs en boucle ou des traitements avancés sur des ensembles itérables, ainsi que des opérations sur des nombres complexes, c’est python.

 

Aller à Calculs de rendements, Matrice de corrélations, Génération de scénarios aléatoires,Interpolations

Les premiers exemples sont élémentaires, histoire d’acquérir des automatismes.
Le nom donné au fichier Excel est appVBApython. Le fichier python qui contient le code des fonctions appelées est appPythonVBA.py: il se trouve dans le répertoire C:\Users\Utilisateur. Dans la fonction Settings (à l’intérieur du module xlwings), on mettra à jour les paramètres PYTHONPATH et UDF_MODULES
PYTHONPATH = « C:/Users/Utilisateur »
UDF_MODULES = « appPythonVBA »
 

Calculs de rendements à partir d’un historique de prix

 
Les calculs de rendements sont indispensables en gestion d’actifs et calculs de risques. A partir de ces rendements, on peut calculer des moyennes, des écart-types (volatilités), des coefficients de corrélation entre titres etc. Toutes ces statistiques ont déjà leurs fonctions en VBA et en Python, mais les fonctions python ont l’avantage de convertir les différents types VBA – tableaux, plages de cellules ou Variant – en objet list, ce qui évite des étapes de conversion entre type Range ou Variant, à Single() ou Double().
Par convention, pour de faibles évolutions d’un actif X entre t et t+Δt le taux de variation, ou rendement est


La fonction python effectuant le calcul sur une séquence de prix, appelée rend s’écrit simplement
 

 
Une fois cette fonction sauvegardée, on lance ImportPythonUDFs et rend est alors intégrée aux fonctions personnalisées d’Excel.
A partir de là, on peut calculer directement le rendement journalier moyen et la volatilité du titre dans VBA. On pourrait le faire dans la fonction python, mais le résultat renvoyé serait de type liste hétérogène, ce que VBA n’accepte pas.
En supposant que les cours se trouvent affichés à partir de A3 dans la feuille « Feuil3 » du classeur, le code VBA correspondant est
 

 

Matrice de corrélations entre actifs

 
Ici, le but est d’exploiter l’avantage que procure la fonction corrcoef de numpy. L’alternative est la suivante:

  • soit parcourir les colonnes de la plage de cellules concernée, appeler pour chacune d’elles la fonction rend, puis réassembler les collections de rendements obtenus (de type Variant) avant d’appeler corrcoeff
  • soit saisir la plage de cellules en entier, la passer à la fonction python qui va, pour chaque colonne, calculer les rendements, réunir toutes les listes obtenues en un objet ndarray et lancer corrcoef

 
Ici, compte tenu de la facilité avec laquelle on peut concaténer des listes en python, on choisit la deuxième solution.
Il faut noter que la plage de cellules passée en argument contient des prix affichés en colonne et que corrcoef calcule des coefficients de corrélation 2 à 2 entre les lignes qui composent l’objet ndarray: il faut donc appeler la fonction transpose de numpy avant d’effectuer le calcul des rendements.
 

 

Génération de scénarios – exemple d’application financière

 
L’utilisation de la méthode dite de Monte-Carlo est très courante en Finance, dans le calcul des risques. Elle consiste, à partir de scénarios impliquant des variables aléatoires satisfaisant une distribution donnée, de calculer toutes les évolutions possibles de valeurs d’un portefeuille et d’en déduire les perspectives de gains ou de pertes et leurs probabilités attachées.
Dans la section § Package numpy, il a été vu comment générer un scénario aléatoire portant sur l’évolution de quatre actifs corrélés dont l’historique de prix est affiché sur une feuille Excel. (§ numpy.linag > cholesky)
Dans l’exemple ci-dessous, nous allons calculer le prix théorique d’un placement qui offre au souscripteur de toucher, au bout d’un an, la moyenne, si elle est positive, des rendements constatés sur 4 actifs: ces rendements sont calculés par le rapport de la variation sur le prix initial, c’est à dire ΔX/X
Pour chaque scénario, cette moyenne (actualisée) se calcule par:


 

Le modèle choisi pour l’évolution de chaque actif est le modèle de base (Black-Scholes)

Les valeurs Zi gaussiennes sont obtenues par multiplication de la matrice de Cholesky calculée à partir de la matrice de corrélations (voir § Package numpy)avec un vecteur de 4 valeurs normales centrées zi.



Pour les calculs,

  • la récupération des données de base, i.e. l’historique de cours, se fait en VBA (forcément)
  • Les valeurs zi sont générées par la fonction random.gauss(0,1)
  • Le taux d’intérêt r est une donnée, les volatilités σi sont calculées en VBA, à partir des historiques de cours
  • la matrice de corrélation ainsi que la décomposition de Cholesky sont en python
  • la boucle permettant d’évaluer l’expression donnée dans (1) pour un nombre N de scénarios sera programmée en python

 
On pourrait inclure dans une seule fonction python le programme allant du calcul de la matrice de corrélations jusqu’au calcul du prix. Ici, on a choisi de séparer chaque étape de calcul, car seule la boucle finale se rapporte au cas précis de la formule donnant le prix. Les étapes précédentes(corrélations et Cholesky), sont d’usage général: on peut affecter une fonction « utilitaire » à chacune de ces étapes.
Donc, le code python pourra être décomposé en 3 fonctions
matcorr (calcul des corrélations), trigonal (Cholesky), algo (boucle pour le calcul du prix)
 

 
Le programme VBA appelant est
 

 

Les interpolations

 
Dans l’exemple précédent, pour simplifier, nous avons utilisé un taux d’intérêt r qui était une donnée de l’exercice et calculé des volatilités à partir d’historique de cours: dans la vie réelle, tous les paramètres sont récupérés de courbes (valeur=f(temps)) données par le marché. Ces courbes déterminent, pour une liste d’échéances standards (1 mois, 2 mois,…, 1an, 2 ans…), la valeur (taux, volatilité, coefficient de corrélation…) qui s’applique.
Il est peu fréquent que l’échéance d’un actif financier à évaluer tombe sur une échéance standard, donc il faut calculer des interpolations.
 
Le package scipy.interpolate offre des fonctions d’interpolation pour des courbes à une dimension (interp1d) voire plus (griddata)
Supposons que la courbe des volatilités, de 1 à 10 ans, se trouve sur la feuille « vols » du classeur Excel, les intitulés des actions se trouvant sur la 1ère ligne.
 


Le programme VBA va parcourir un à un les intitulés (Soc A, Soc B,…) et, pour chacun, calculer la volatilité correspondant à la date de maturité de l’actif financier à évaluer.
La fonction interp1d prend en argument les données de la courbe standard – abscisses X/ordonnées Y – et renvoie un objet qui contient tous les paramètres nécessaires à l’interpolation pour une abscisse quelconque p. La syntaxe est la suivante
 

 
L’argument kind est facultatif. kind peut être égal à ‘cubic‘ (pour une interpolation cubic spline): par défaut, l’interpolation est linéaire.
Le code suivant décrit une fonction interpol destinée à être utilisée dans Excel
 

 
Notez le test if st==(‘cubic’,): et non if st==’cubic’:
En effet, rappelons que l’argument facultatif *st est de type tuplet: un tuplet contenant un seul élément s’écrit toujours entre parenthèses avec une virgule de séparation.
Le 1er argument de la fonction interp1d est une séquence de valeurs numériques. Dans notre exemple, les abscisses sont des dates. Cela pose un problème car le format des dates sous Excel n’est pas reconnu par Python. Il faut transformer cette date en chaîne de caractères (objet str) dans la fonction python avant de pouvoir en extraire les données AAAA/MM/JJ.
Par exemple, la conversion de la date 13/10/2022 en texte (fonction str) donne 2022-10-13 00:00:00
Pour pouvoir utiliser interp1d, nous choisissons de transformer toutes les dates en valeurs entières égales au nombre de jours entre aujourd’hui et les dates en question, à l’aide de la fonction conv_date
 

 
Ce qui donne la fonction d’interpolation modifiée pour traiter les abscisses de type date
 

 
La récupération des volatilités se ferait via la portion de code VBA suivante
 

 
On notera encore que toute valeur renvoyée par la fonction python est de type Variant, ce qui nécessite de récupérer la valeur de chaque volatilité à partir d’un objet de type Variant (ici, x), à l’aide de vol(i) = x(0, 0)