Automatisation sur Excel (VBA)

Automatiser une fichier excel afin de réaliser des simulations.

Un portefeuille est créé a partir de 10 à 15 composants. Nous disposons de l'historique d'évolution de ces composants (indice).
Le poids de chaque composant est calculé à la date T0 pour correspondre au poids cible (ou allocation). Ainsi en T0 pour chaque indice un nombre d'unités de cet indice est "acheté".
(et on a donc (nombre d'unité *valeur de l'indice en T0) / valeur du portefeuille= poids cible)
Chaque jour la valeur du portefeuille est calculée ainsi que le poids de chaque indice. Si le poids d'un seul indice est supérieur ou inférieur de X% par rapport au poids cible, alors on recalcule pour tous les indices le nombre d'unités nécessaire pour revenir au poids cible.

Procédure automatisation simulation de portefeuille.
(voir fichier excel joint)
Principe :
A partir de données historiques de 10 à 15 indices (cf feuille « histo » mais qui peut –etre différente, plus longue ou plus courte), calculer la valeur quotidienne d’un portefeuille composé de ces indices et effectuer des rebalancements de ce portefeuille dès que le poids d’un indice dans le portefeuille dépasse de X% (à la hausse ou à la baisse) une allocation cible fixée (jusqu’à la date de fin)
Comparer ce portefeuille avec le même portefeuille de départ qui n’aurait pas subi de rebalancement.
Faire cela pour une multitude de date de départ.

Pour chaque simulation, la date de départ représente le T0 de l’investissement pour lequel la répartition du portefeuille est égale à l’allocation cible (cf paramètres variables), T+1 le jour suivant (1).
Chaque jour, la valeur du portefeuille global est calculée, ainsi que le poids de chaque indice dans ce portefeuille.
Dès que l’allocation varie de +/- X% (cf paramètres variables), alors il faut réallouer (rebalancer) pour revenir à l’allocation cible.
Et cela jusqu’à la Date de Fin
Résultat : Valeur à la « date de Fin » du portefeuille ainsi que la valeur de ce portefeuille à la « date de Fin » sans le rebalancement.

Puis, T0=T1 et le process reprend.

Il y a autant de simulations que de dates (1) entre la « Date de Départ » et la « Date de Fin »

Résultat global :
Un tableau avec en face de chaque date comprise entre « Date de Départ » et « Date de Fin » (1) la valeur à la « Date de Fin » du portefeuille « rebalancé » et du portefeuille sans rebalancement.

(1) jours pour lesquelles les données sont disponibles : dates utilisées dans les « historiques des indices »

 

Détails :

Données d’entrée :
Historique de 10 à 15 indices (I1, I2, ..I15)
Valeur portefeuille en T(0): 1 000 000

Paramètres variables :
Date de Départ : Dd
Date de Fin : Df
Allocation cible (tableaux de 10 à 15 données) (A1, A2, …..A15)
X% de variation de l’allocation qui déclenche un rebalancement

Options :
Integrer des « Frais de Gestion » de Y% au portefeuille rebalancé : chaque jour, le portefeuille perd (1/365)*Y%
(en fait chaque allocation perd (1/365)*Y% d’unités)
(en fait 1/250 si l’on ne considère que les jours ouvrés)

Process :
Prendre la date de départ
T0= Dd
Aller chercher dans le tableau d’historique cette date et calculer le nombre d’unité de chaque indice tel que :
nbre d’unités de Indice I1= (Valo (t0) portefeuille * alloc cible(I1)) / Valeur de (I1) en t0

idem pour tous les indices

Aller chercher la valeur du portefeuille à la date de fin.
Dans la feuille de résultat, afficher cette valeur en face de la date de départ.

Date suivante : T(1) =T(0)+1 (verifier dans le tableau d’historique que cette date existe, sinon passer à celle d’après) (ou sinon T(1) = la date sous T(0) dans le tableau d’historique)

Nombre d’unités de chaque indice = nombre d’unités en T(0) *(1-y%/250)

Valeur portefeuille (T1)= Somme (i=1 à i=15)(Valo (Ii) *Nbre d’unités(Ii))

Pour chaque indice Ii, poids dans le portefeuille en T1:
P(Ii) = (Valeur (Ii) *Nbre d’unités)/ Valeur portfeuille

Si, au moins un des indices à un poids > à allocation cible +X% ou inférieur à allocation cible -X% , alors : On fige la valeur du portefeuille pour cette date et on calcul le nombre d’unités de chaque indice pour que son poids soit égal à l’allocation cible.
(en fait c’est le meme process qu’en T0, mais avec une valo de portefeuille différente de la valeur de départ)

puis on réitère jusqu’à la date de fin.

Afficher la valeur du portefeuille à la date de fin dans la feuille de resultat en face de la date de départ, à coté de la valeur du portefeuille sans réallocation.

Ensuite refaire la simul avec comme date de départ, le jour suivant.

Et ce jusqu’à la date de fin.

Exemple :

Données d’entrée :
Historique de 2 indices A et B entre le 03 janvier 2000 et le 24 Janvier 2000.

Paramètres variables :
Date de Départ : 03 janvier 2000
Date de Fin :24 Janvier 2000
Allocation cible :
• Indice A : 60%
• Indice B : 40%
2% de variation de l’allocation déclenche un rebalancement vers l’allocation cible.

Process :
Date 1 : 03/01/2000
1/ Déterminer le nombre d’unités de départ de chaque indice pour composer le portefeuille qui respecte l’allocation cible
(ici : 600 unités de A et 80 Unités de B)

2 / Valeur du portefeuille à la « date de fin » si l’on garde cette allocation
(Ici : 111600)
Ce résultat est un paramètre de résultat global (pour date de départ du 03/01/00)

3/ process de réllocation/rebalancement :
Le nombre d’unités en J0 est conservé en J1 : calcul de la valeur du portefeuille et du poids de chaque indice.
Itération les jours suivants, jusqu’à ce que le poids d’un des Indices soit supérieur ou inférieur de 2% à l’allocation cible.
(Ici : le 5/01/00 puisque A comme B depasse ce critère, cf tableau 1)

⇨ réallocation (= calcul du nouveau nombre d’unité) pour atteindre l’allocation cible de 60% - 40% (attention, on conserve la valeur du portefeuille avant reallocation, ici 105 200)
⇨ A l’issue de cette réallocation, on obtient le tableau 2

On réitère le process : dès que le poids d’un indice est supérieur ou inférieur de 2% à l’allocation cible, on rebalance.
Donc, le 10/01/2000, il faut rebalancer pour obtenir le tableau 3

Ainsi de suite jusqu’à obtenir le tableau 4.
Valeur final de portfeuille « rebalancé » : 110 468

4/ Première ligne du tableau de résultat :

Valeur à la date de fin du portef
Résultat Static Rebalancé
03/01/00 111 600 110 468
04/01/00 XXX YYY
05/01/00 ZZZ TTT

Puis le process recommence avec comme date de départ le jour suivant (donc ici 04/01/00) pour lequel est calculé le nombre d’unités d’indice A et B pour avoir une allocation de 60% A et 40% B.