Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Examen de VBA au secours...

lomybor

XLDnaute Nouveau
Bonjour à tous,

voila la situation, j'ai un examen de VBA à rendre pour demain (en anglais)
et autant vous dire que je suis aussi bon en VBA que Domenech en sélectionneur...

J'imagine que personne ne va me répondre mais bon je suis obligé de tenter... Alors voila mon énoncé, si jamais quelqu'un est assez fou pour le faire, tant mieux !!!!! (pj: tableau excel fourni...)

Using the stock return data in the Excel file “Final Assignment 3 (Option 1) – Data.xlsx”, write a VBA function to estimate and test the CAPM using the methodology illustrated in the course presentation. The spreadsheet contains monthly excess stock (R_i) and market returns (R_M) above the risk free rate.

The following steps need to be performed:

Regress the time series of excess stock returns on the market excess returns labeled “FTSE All Share” using a rolling window of 60 months, i.e. using months 1-60 in the first regression, then months 2-61 in the second regression and so on for each stock. Make sure to include an intercept term (α_i) in the time series regressions as well. So the regression equation should look as follows:

R_i=α_i+β_(i,M) R_M+ε_i (1)
After that, collect the times series of slope coefficients (β_(i,M)) for each stock and regress stock excess returns (R_(i,t+1)) on the slope coefficients (β_(i,M,t)) in monthly cross-sectional regressions. The regression equation should look as follows

R_(i,t+1)=a_t+b_t β_(i,M,t)+ε_(t ) (2)
Note that equation (2) is a cross-sectional regression as opposed to a time series regression in equation (1), i.e. every month, month t+1 stock returns are regressed on month t betas of all stocks.

Equation (2) results in two time series of regression coefficients, a and b. Compute and interpret t -statistics on each of these time series and conclude what the results mean regarding the validity of the CAPM. Compute t-statistics as follows:

t=(mean(x))/((std(x))/(N-1))

where x is the variable examined (the regression coefficients a and b in our case) and N is the number of observations in each time series.

Perform all computations in a VBA function that takes the stock excess returns array and market excess return array as inputs and computes a 2x2 output array containing the mean regression coefficients a and b as well as their associated t-statistics. Verify your function’s results by performing the above steps manually in Excel.

Note: As the returns shown in the spreadsheet are over a period of rising markets, you can assume that stock returns are positively related to their betas if the CAPM holds.
 

Pièces jointes

  • Final Assignment 3 (Option 1) - Data.xlsx
    24 KB · Affichages: 121

James007

XLDnaute Barbatruc
Re : Examen de VBA au secours...

Bonsoir,

Pour demain matin ...

Si tu en as le courage, CAPM et MPT, avec le travail de Markowitz et de Sharpe ... cela a été codé et recodé en VBA par beaucoup de gens ...

Il te faut "google-iser" un max ... tu trouveras sans doute ... your total happiness ...

Bon Courage
 

bouvbouv

XLDnaute Nouveau
Re : Examen de VBA au secours...

Bonjour Lomybor,

figure toi que mon professeur de VBA vient de me donner à faire le même examen que tu as eu en 2009 !

Aurais-tu par le plus heureux des hasards conservé la solution ?

je te remercie de ton aide,

bon week end,

Nicolas
 

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…