Project-Excel-2
Excel Project 2: Efficient Frontier
A skills showcase project for Excel
Project description
In this project, I construct many efficient frontiers from various stocks in the DAX30 index to showcase my familiarity with Excel. Spreadsheet is an intuitive way to work with data due to its tabular form of presentation and working with spreadsheet does not require as much knowledge as other analytical tools do. However, the latest version of Excel included in the Microsoft 365 subscription is very sophisticated with many cool features that greatly accelerates the analyzing process. In this project, I will present the implementation of such features in the optimization of a stock portfolio.
Moreover, a short but explanatory introduction to portfolio optimization is also given as the foundation for all the steps taken in this project. The approach presented in this project might not be the best way to optimize a portfolio or construct an efficient frontier, because the implemented method focuses on finding the analytical solution to the efficient frontier, and it is more efficient to use the numerical approach to achieve the same result. In this project, two approaches are discussed and presented in two separate Excel files. Besides the technical details, some aesthetic elements are also taken into consideration because Excel also provides many features for customizing the spreadsheet to make it more user friendly.
Executive summary
For me, Excel is like an inclusive set of tools with a canvas and lots of other packed instruments ready for the analyst to paint a picture or articulate a comprehensive story from raw data, a manifestation of the chaotic real world. Yet Excel does not do that automatically following its user’s will, just like all the other tools, but it requires the user to master certain skills to be able to control these tools efficiently. The purpose of this project is to demonstrate some of these skills and my knowledge of them.
In details, these skills include
- Downloading data of chosen stocks from Yahoo! Finance
- Importing multiple data files from a folder using Power Query
- Using MATCH and INDEX functions to transform data from long to wide format
- Illustrating stock data using line chart in Excel
- Utilizing named range and table to simplify the implementation of Excel functions and make these functions more intuitive
- Summarizing information of chosen stocks with statistical functions and the Data Analysis Tools of Excel
- Calculating stock and portfolio statistics using Excel functions of matrix algebra
- Solving optimization problems numerically with Solver
- Generating customizable series with Fill tool
- Constructing complex scatter plots to illustrate different types of efficient frontier
- Generating the table of random values using Data Table tool in What-If Analysis
These are mostly the tools used for calculation and data transformation. The tools used for data aggregation are not implemented due to the nature of this project.
Detailed documentation
For further information, please refer to the project repository on Github using the following link:
Or access the documentation directly