本次作业主要为excel相关的question金融代写
考虑包含在文件“考试数据 2021.xls”中的数据集中的数据。该文件分为 4 个工作表：i) 每日库存； ii) 每月库存； iii) 每日资金； iv) 每月资金。包含股票的工作表，包括从 2015 年 1 月 1 日到 2021 年 6 月 14 日的意大利股票市场的精选股票。每日工作表包括根据股息调整的每日价格，而月度工作表包括相同证券样本的月度价格，根据股息进行调整，从 2015 年 1 月 1 日开始，到 2021 年 6 月 1 日结束。
以基金标识的工作表包含在欧元区出售并由一些最著名和最受尊敬的投资公司管理的投资基金样本：每日和每月频率，对于股票也考虑的相同样本大小。
首先关注关于股票的两个工作表。计算每日和每月的回报 计算每日和每月频率的股票的均值、标准差、方差、偏度和峰度。在表格中显示结果并进行评论。
计算方差-协方差矩阵和相关性
选择一个由 10-12 种证券组成的样本。你应该激励你选择证券。例如，可以根据方差-协方差的相关结构做出选择，解释并证明您的选择。
绘制您选择的证券价格在整个样本长度内的每日和每月频率的行为
计算您在每日和每月讨论中选择的证券样本的平均方差最优投资组合分配。
对投资组合施加非负约束后计算相同的资产配置
给定先前的结果，计算每日和每月频率的最佳均值方差投资组合的均值、标准差、方差、偏度和峰度。通过广泛地提供必要的直觉
绘制每日和每月讨论的有效边界。
考虑一个代表意大利股票市场的指数，例如 FTSE Italia All Market，在两个不同的工作表中给出，一个用于每日版本，一个用于月度版本。该指数在其总回报版本中收集。计算与该指数相关的所有统计数据（均值、标准差、方差、峰态和偏度）。讨论该指数的此类统计数据与您为自己找到的统计数据之间的差异
计算您投资组合中包含的每种证券的 beta 和您的投资组合的 beta 为
假设无风险证券的回报等于 0.5% (0.005)，请计算您的投资组合中两 (2) 种证券以及您的投资组合的证券市场线 (SML)。如果 SML 被验证，则验证两个选定的证券，每日和每月的频率。
Consider data in the dataset included in the file ’data for exam 2021.xls’. This file is divided up into 4 worksheet: i) stocks daily; ii) stocks monthly; iii) funds daily; iv) funds monthly. The worksheet with stocks, include a selection of stocks from the Italian Stock Market starting from January 1st, 2015 until June 14th, 2021. The daily worksheet includes daily prices adjusted for dividends, while the monthly worksheet includes monthly prices for the same sample of securities, adjusted for dividends, starting from Jan 1st, 2015 and ending on June 1st 2021.
The worksheet identified with funds, contains a sample of investment funds sold in the Euro Area and managed by some of the most famous and respected investment firms: both in daily and in monthly frequency, for the same sample size considered also for stocks.
- Focus first on the two worksheet on stocks. Compute returns for both daily and monthly Compute mean, standard deviation, variance, skewness and kurtosis for stocks at daily and monthly frequency. Show the results in a table and comment.
- Compute the variance-covariance matrix and the correlation
- Select a sample made of 10-12 securities. You should motivate your choice of securities. The choice can be made, for example, on the basis of the correlation structure of the variance- covariance Explain and justify your choices.
- Plot the behavior of the security prices you have chosen, both in daily and monthly frequency during the entire lenght of the sample
- Compute the Mean Variance optimal portfolio allocation for the sample of securities chosen by you both in daily and monthly Discuss.
- Compute the same asset allocation after imposing non-negativity constraint on portfolio
- Given previous results, compute mean, standard deviations, variance, skewness and kurtosis of your optimal mean-variance portfolios, both for daily and monthly frequency. Provide the necessary intuition by making extensive
- Plot the efficient frontier for both daily and monthly Discuss.
- Consider an index representative of the Italian stock market, such as FTSE Italia All Market, given in the two distinct worksheets, one for the daily and one for monthly version. The index is here collected in its Total Return version. Compute all the statistics relative to that index (mean, standard deviation, variance, kurtosis and skewness). Discuss the differences between such statistics for the index and those you found for your
- Compute the beta for each security included in your portfolio and the beta for your portfolio as
- Given a return for a Risk-Free security equal to 0.5 per cent (0.005), compute the Security Market Line (SML)for two (2) securities of your portfolio and for your portfolio as well. Verify for the two chosen securities if the SML is verified., for both daily and monthly frequency.