1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
Sub voirunclo()
Dim Db1 As Database
Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim Rs3 As DAO.Recordset
Set Db1 = DBEngine.OpenDatabase("Q:\Portefeuille\CDO\Suivi Portefeuilles\Suivi portefeuille.mdb")
Application.ScreenUpdating = False
fin = Range("IU2").End(xlDown).Row
For I = 2 To fin
CLOVisu.ComboBox1.AddItem Range("IU" & I)
Next I
Sheets("Menu").Range("N13") = fin - 1
CLOVisu.Show
Nomchoisi = CLOVisu.ComboBox1
If Nomchoisi = "" Then
GoTo lafin
End If
Sheets("CLO").Activate
Cells(2, 2) = "Fiche CLO: " & Nomchoisi
STRSQL2 = "SELECT CLO.NomCLO, CLO.Manager, CLO.Vintage, CLO.SettlementDate, CLO.SizeDeal, CLO.[Payment frequency], CLO.[Reinvestment Period], CLO.[non call Period], CLO.[ramp up period], CLO.[call date], CLO.LeadManager, " & _
"CLO.SeniorMontant , CLO.ClassAAA1Montant, CLO.ClassAAA2Montant, CLO.ClassAA1Montant, CLO.ClassAA2Montant, CLO.ClassA1Montant, CLO.ClassA2Montant, CLO.ClassBBB1Montant, CLO.ClassBBB2Montant, CLO.ClassBB1Montant, " & _
"CLO.ClassBB2Montant, CLO.ClassBMontant, CLO.EquityMontant, CLO.SeniorCoupon, CLO.ClassAAA1Coupon, CLO.ClassAAA2Coupon, CLO.ClassAA1Coupon, CLO.ClassAA2Coupon, CLO.ClassA1Coupon, CLO.ClassA2Coupon, " & _
"CLO.ClassBBB1Coupon, CLO.ClassBBB2Coupon, CLO.ClassBB1Coupon, CLO.ClassBB2Coupon, CLO.ClassBCoupon, CLO.SeniorSpread , CLO.ClassAAA1Spread, CLO.ClassAAA2Spread, CLO.ClassAA1Spread, CLO.ClassAA2Spread, CLO.ClassA1Spread, CLO.ClassA2Spread, " & _
"CLO.ClassBBB1Spread, CLO.ClassBBB2Spread, CLO.ClassBB1Spread, CLO.ClassBB2Spread, CLO.ClassBSpread, CLO.SeniorRatingMoodys, CLO.ClassAAA1RatingMoodys, CLO.ClassAAA2RatingMoodys , CLO.ClassAA1RatingMoodys, " & _
"CLO.ClassAA2RatingMoodys, CLO.ClassA1RatingMoodys, CLO.ClassA2RatingMoodys, CLO.ClassBBB1RatingMoodys, CLO.ClassBBB2RatingMoodys, CLO.ClassBB1RatingMoodys, CLO.ClassBB2RatingMoodys, CLO.ClassBRatingMoodys, " & _
"CLO.SeniorRatingSP, CLO.ClassAAA1RatingSP, CLO.ClassAAA2RatingSP, CLO.ClassAA1RatingSP, CLO.ClassAA2RatingSP, CLO.ClassA1RatingSP, CLO.ClassA2RatingSP, CLO.ClassBBB1RatingSP, CLO.ClassBBB2RatingSP, " & _
"CLO.ClassBB1RatingSP, CLO.ClassBB2RatingSP, CLO.ClassBRatingSP, CLO.SeniorRatingFitch, CLO.ClassAAA1FratingFitch, CLO.ClassAAA2RatingFitch, CLO.ClassAA1RatingFitch, CLO.ClassAA2RatingFitch, CLO.ClassA1RatingFitch," & _
"CLO.ClassA2RatingFitch, CLO.ClassBBB1RatingFitch, CLO.ClassBBB2RatingFitch, CLO.ClassBB1RatingFitch, CLO.ClassBB2RatingFitch , CLO.ClassBRatingFitch, CLO.SeniorIsin, CLO.ClassAAA1Isin, CLO.ClassAAA2Isin, " & _
"CLO.ClassAA1ISin, CLO.ClassAA2Isin, CLO.ClassA1Isin, CLO.ClassA2Isin, CLO.ClassBBB1Isin, CLO.ClassBBB2Isin, CLO.ClassBB1Isin, CLO.ClassBB2Isin, CLO.ClassBIsin, CLO.EquityIsin, CLO.SeniorWAL , CLO.ClassAAA1WAL, " & _
"CLO.ClassAAA2WAL, CLO.ClassAA1WAL, CLO.ClassAA2WAL, CLO.ClassA1WAL, CLO.ClassA2WAL, CLO.ClassBBB1WAL, CLO.ClassBBB2WAL, CLO.ClassBB1WAL, CLO.ClassBB2WAL, CLO.ClassBWAL, CLO.EquityWAL, CLO.FeePrimary, " & _
"CLO.feeseccondary, CLO.[Incentive fee 1], CLO.[Incentive fee 2], CLO.maxratfactor, CLO.MinWAspread, CLO.minMoodysDS, CLO.minRecoveryRate, CLO.maxNonEuro, CLO.MaxWal, CLO.MaxFixedRate, CLO.SStop5perIssuer, " & _
"CLO.SSOtherperIssuer, CLO.Mezzperissuer, CLO.MinSS, CLO.MaxMezz, CLO.MaxHY, CLO.MaxCLO, CLO.MaxsyntheticSec, CLO.MaxshortSyntheticSec, CLO.MinRatingPurchase, CLO.MaxPIK " & _
"FROM CLO " & _
"WHERE (((CLO.NomCLO)= '" & _
Nomchoisi & _
"')) "
Set Rs2 = Db1.OpenRecordset(STRSQL2, dbOpenSnapshot)
Range("C6:D16").ClearContents
Range("C20:D32").ClearContents
Range("G20:H32").ClearContents
Range("C69:D86").ClearContents
Range("C37:E65").ClearContents
Range("C59:E60").ClearContents
Range("B95:N5000").ClearContents
Range("C66").ClearContents
Range("E66").ClearContents
Sheets("CLO").Range("M6").CopyFromRecordset Rs2 |
Partager