VBA Portofolio

Public Function Portofolio(Data As Variant, StartDate As Date, EndDate As Date, priceType As StockPriceType) As Variant

  ' Initialisation
    Dim Variances As Collection   ' Vecteur des Variances pondéré au carré
    Dim Rendements As Collection  ' Vecteur des rendements pondéré
    Dim Covariances As Collection ' Matrice Covariances Pondéré par leur poids

    Dim Portofolio As Collection  ' Structure de donnée en sortie, au lieu d'un collection, peut être un objet = dict

    Dim i As Integer
    Dim j As Integer

  ' Calcul des rendements et variances de chaque action du portefeuille
    Dim Stock As Collection

    For i = 1 To Data("Stocks").Item.Count
        Set Stock = GetStockData(Data("Stocks").Item(i), StartDate, EndDate)
        Rendements.Add CalcMean(Stock, priceType) * Data("Weight").Item(i)            ' Le rendement pondéré par son poids
        Variances.Add CalcVolatilite(Stock, priceType) * (Data("Weight").Item(i) ^ 2) ' La variance pondéré par son poids
    Next

  ' Calcul des differentes paires de Covariances
    Dim Stock_i As Collection
    Dim Stock_j As Collection
    Dim Covariance As Collection

    For i = 1 To Data("Stocks").Item.Count - 1                                         ' On itère de 1 à N-1
        Set Stock_i = GetStockData(Data("Stocks").Item(i), StartDate, EndDate)
        For j = 2 To Data("Stocks").Item.Count                                         ' On itère de 2 à N
            Set Stock_j = GetStockData(Data("Stocks").Item(i), StartDate, EndDate)
            Covariance.Add CalcCovariance(Stock_i, Stock_j, priceType) * Data("Weight").Item(i) * Data("Weight").Item(j)
        Next j
        Covariances.Add Covariance
        Set Covariance = New Collection                                                ' Reinitialisation Covariance
    Next i

  ' Rendement et Variance du portefeuille
    Dim Rendement As Collection
    Dim Variance As Collection

    Dim R As Double
    Dim V As Double
    Dim CV As Double
    Dim CVs As Double

    ' Somme des Rendements pondéres des actions du portefeuille
    For i = 1 To Rendements.Item.Count
     R = R + Rendements.Item(i)
     V = V + Variances.Item(i)
    Next i

    ' Calcul de la Variance du portefeuille
    For i = 1 To Data("Stocks").Item.Count - 1
        For j = 1 To Covariances(i).Item.Count
            CVs = CVs + Covariances(i).Item(j)
        Next j
        CV = CV + CVs
        CVs = 0
    Next i

    Rendement.Add R
    Variance.Add V + 2 * CV

    Portofolio.Add Rendement
    Portofolio.Add Variance


End Function

Sub ShowPortofolio()
 Dim Data As Dictionary 'J'aimerais que data soit un dictionnaire
 Dim Portofolio As Dictionary 'Le dictionaire qui contient le rendement et variance de notre portefeuille
 Dim StartDate As Date
 Dim EndDate As Date
 Dim P As Variant

 Set Data("Stocks") = GetDictonnary 'GetDictonnary serait une fonction qui renvoie une liste de valeurs, ici on voudrait les symboles de nos actions du portefeuille
 Set Data("Weight") = GetDictonnary 'Ici on recupere les ponderations pour les actions de notre portefeuille
 Set StartDate = DateSerial(2007, 1, 1)
 Set EndDate = DateSerial(2007, 12, 31)

 P = Portofolio(Data, StartDate, EndDate, StockPriceType.closePrice)
 Set Portofolio("Rendement") = P.Item(1)
 Set Portofolio("Variance") = P.Item(2)

' Print the average return
    Debug.Print "Portofolio's return from " & StartDate & " To " & EndDate & ": " & Portofolio("Rendement").Item
    Debug.Print "Portofolio's variance from " & StartDate & " To " & EndDate & ": " & Portofolio("Variance").Item

    Debug.Print "Average Return for " & StockSymbol & ": " & meanReturn
End Sub