Creating the dashboard
1. Creating the dashboard
Congratulations! You have completed this course. Well done! You can now merge the interfaces developed in the previous chapters into a great dashboard to monitor the past performance of stock ABC.2. Dashboard
The dashboard is composed of four panels.3. Dashboard: "Prices panel"
The first panel is the "Prices panel".4. Dashboard: "Prices panel"
It allows you to track the dynamics of the stock price over the period under analysis,5. Dashboard: "Prices panel"
and provides you with information on the extreme values reached by the stock in the past.6. Dashboard: "Returns panel"
The second panel is the "Returns panel", which provides you with the major metrics of reward, risk, and risk-adjusted performance.7. Dashboard: "Distribution panel"
The third panel is the "Distribution panel".8. Dashboard: "Distribution panel"
It displays the histogram of the past returns,9. Dashboard: "Distribution panel"
together with parameters such as skewness and excess kurtosis.10. Dashboard: "Benchmarking panel"
And finally the last panel is the "Benchmarking panel".11. Dashboard: "Benchmarking panel"
It provides comparisons with a benchmark through various tools like the cumulative wealth,12. Dashboard: "Benchmarking panel"
the maximum drawdown,13. Dashboard: "Benchmarking panel"
and the correlation coefficient.14. Dashboard
You can now replace past returns of stock ABC with your own investment and monitor its past performance! But this is not all.15. Make it interactive!
If you have several investments, you can make your dashboard interactive! Let's go a bit beyond the scope of the course and illustrate how you can have further fun with spreadsheets!16. Make it interactive!
Here is how to proceed. Collect the historical returns of the various investments in a separate section of your spreadsheet.17. Make it interactive!
Then, go on the sheet with the interface and create a list of securities using the "Data validation" tool.18. Make it interactive!
From there, you can select the name of the financial assets to include in the analysis.19. Make it interactive!
You can click on the title of the dashboard and see the list of assets already created.20. Make it interactive!
What remains to do is to change the input returns used for the analysis such that they match with the value chosen in the list before. Here, use VLOOKUP() with the MATCH() function. This allows you to replace the return of ABC stock by the return of the selected stock at a specific date.21. Make it interactive!
And you're done!22. Make it interactive!
Here's an animation that shows how the final outcome should look like.23. Thank you!
Hope you enjoyed the course! Thanks for taking this course and have fun with spreadsheets!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.