[SOLVED] vba statistic 2019

2019

VBA ExcelStOK TRACK PROJECT

First Part : Stock Track with VBA
You have to create a workbook with these sheets:
Equity portfolio which contains some stocktransactions with their critical information purchase value, purchase date, quantity, last price and total value for the line
You have to select your stocks from the list below :
Automobile :
PEUGEOT
MICHELIN
RENAULT
VALEO
FAURECIA

Btiments et matriaux de construction :
VINCI
LAFARGEHOLCIM LTD
SAINTGOBAIN
BOUYGUES
EIFFAGE

To help you, I will send you the historical data prices for one year period :
With these data you have to calculate daily, monthly and yearly return for each stock in your portfolio.

Diversification effect Measure Computing:
From some data you can measure the evolution of your portfolio like you buy it one year before and you compare this evolution to each stock in the portfolio.
You have to calculate some statistical indicators for each stock and for your portfolio like: Mean, Variance, Standard deviation, Covariance and Correlation.

You have to present your result in the dashboard and use vba to resolve this project.

In the second Part : you can use your old VBA project of the last year and you must manage it to be more efficient with more features.

Equity analysis: which contains a dash board for a select equity:

You must create a sub in VBA to compute a historical volatility, average, skewness, kurtosis and to produce some charts to present this asset.
You have to create a chart for historical return rates of a select equity.
Create a specific sheet to download data from Bloomberg excel addin you can use the data prepared by me if you dont have Bloomberg access

Efficient Frontier from our equity assets use a solver to find a efficient points, the program is already created you just have to prepare historical return rates to use it.

Option portfolio: which contains some option transactions call and put with their information spot price, exercise price, maturity date, volatility, riskfree rate, quantity and total value for the line:
Create functions to compute a price and Greek letters for each line.

Option Analysis: which contains a report sheet with price and sensitivities analysis for a select option for example you can create different charts to present price variations due to input parameters variation, you can also draw the payoff profile of your option

Bond Portfolio: which contains bond investment transactions with their critical information identifier, coupon rate, nominal, maturity date, issue date, coupon frequency, unit price, quantity and total price for the line:
Create function to compute a Price for each line and for the portfolio.
Create function to compute a Duration for each line and for the portfolio.
Create function to compute a Convexity for each line and for the portfolio.
Create a specific sheet to download data from Bloomberg excel addin

Bond Analysis: which contains some analysis and chart to present:
A relationship between bond price and yield to maturity for a selected bond
A cash flow projection for a select bond
A comparison between two bonds in the same chart.

Market Value structure of your portfolio and the weighting for each asset category:
We have to present the data and the chart of your portfolios market value.
We have to present the data and the chart of your portfolio unrealized gains and losses value.
Remarks:
The VBA project is an individual homework assignment.
You have to add comments to explain your codes.
Be careful about your workbook presentation.
Write a users guide document word document to explain how we can use your workbook and what the assumptions are and the formulae you took into consideration to produce your results.
You can use the entire VBA lab code and documentation that you made before to build your VBA project.
You have to send me your VBA project package VBA project workbook and you user guide document before 12 pm on 22122019.