Financial Analytics in Spreadsheets
Learn how to build a graphical dashboard with spreadsheets to track the performance of financial securities.
Start Course for Free4 Hours15 Videos56 Exercises
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.Loved by learners at thousands of companies
Course Description
Monitoring the evolution of traded assets is key in finance. In this course, you will learn how to build a graphical dashboard with spreadsheets to track the performance of financial securities. You will focus on historical prices and dividends of the hypothetical stock ABC. You will learn how to visualize its prices, how to measure essential reward and risk indicators, and see if your investment in ABC outperformed a benchmark index. At the end of the course, you should be able to use spreadsheets to build great monitoring tools used by traders and financial analysts in their day-to-day business life!
- 1
Monitoring historical prices
FreeIn the first chapter, you’ll be introduced to the problem: you have a time series of monthly (historical) prices for the hypothetical stock ABC from which you have to extract some meaningful information. You’ll be given some definitions (what is a stock? what are dividends?), and at the end of the chapter, you’ll be able to graphically represent the evolution of a stock price over a specific period.
Introduction and first metrics50 xpWhat is a stock?50 xpCount prices and dividends100 xpFind minimum and maximum prices100 xpFind minimum and maximum dividends100 xpIdentifying dates with unusual prices50 xpFind price at a given date100 xpFind dates with minimum and maximum prices100 xpVisualizing the price evolution50 xpPlot a line chart of historical prices100 xpCustomize the chart100 xpHighlight minimum and maximum prices100 xp - 2
Monitoring historical returns
In this chapter, the core of the analysis will switch from historical prices to historical returns. You’ll learn (and compute) the main performance indicators of past returns, both in terms of reward and risk. Finally, you’ll be introduced to risk-adjusted performance measures: indicators that take into account both reward and risk.
From prices to returns50 xpWhat are dollar and percentage returns?50 xpCompute historical returns100 xpCount positive and negative returns100 xpReward metrics50 xpCompute the average return100 xpCompute the effective rate of return using PRODUCT() and COUNT()100 xpCompute the effective rate of return using ARRAYFORMULA()100 xpRisk metrics50 xpCompute the volatility100 xpCompute the volatility with STDEV()100 xpCompute the historical value-at-risk100 xpRisk-adjusted metrics50 xpCompute the Sharpe ratio100 xpCompute the semideviation100 xpCompute the Sortino ratio100 xp - 3
Monitoring the distribution of returns
In this chapter, you'll look at the full distribution of historical returns. First, you’ll learn how to build a histogram to describe the distribution of historical returns. Second, you’ll be introduced to the Gaussian distribution, a commonly used model for stock returns. You'll visually inspect if the Gaussian model is reasonable for the ABC stock returns. Finally, you'll understand potential flaws with the Gaussian model.
Histogram of stock returns50 xpDefine bins100 xpFind the frequency of each bin100 xpConvert frequencies into relative frequencies100 xpBuild the histogram100 xpThe Gaussian model50 xpPlot the standard Gaussian model100 xpChange the location of the Gaussian model100 xpChange the dispersion of the Gaussian model100 xpCalibrating the Gaussian model50 xpCalibrate the Gaussian model on historical returns100 xpOverlay the Gaussian model to the empirical histogram100 xpCompute the 5% value-at-risk from the Gaussian model100 xpLimitations of the Gaussian model50 xpWhat are the limitations of the Gaussian model?50 xpCompute skewness and kurtosis of the historical returns100 xp - 4
Benchmarking performance
In this final chapter, you’ll benchmark ABC stock against a market index and verify whether ABC outperformed the benchmark or not. The comparison process will be done through several steps/metrics. First, you’ll analyze the cumulative wealth. Next, you’ll extend the comparison using different indicators such as Sharpe Ratio and Drawdown. Finally, you’ll examine the linear relation between ABC stock and the benchmark through the correlation coefficient. At the end of the chapter, you’ll be introduced to more powerful and advanced spreadsheet features that introduce interactivity in your analysis.
Benchmarking50 xpHow to choose a good benchmark?50 xpCompare the final wealth100 xpPlot the cumulative wealth100 xpPerformance metrics comparison50 xpCompare the Sharpe ratios100 xpCompare the drawdowns100 xpCompare the maximum drawdowns100 xpCorrelation analysis50 xpCompute the correlation coefficient100 xpCompute the rolling-window correlation100 xpCreating the dashboard50 xp
In the following tracks
Finance Fundamentals in SpreadsheetsDatasets
Stock ABCCollaborators

Prerequisites
Data Visualization in SpreadsheetsDavid Ardia
See MoreProfessor of Quantitative Methods for Finance
David is professor of quantitative methods for finance. His research lies in the areas of econometrics, quantitative finance, and risk management. In 2018, he was elected “Swiss risk manager of the year” by the Swiss Risk Association. He is a big fan of open-source initiatives and is the co-author of several R packages such as "DEoptim", "GAS", "MSGARCH", and "sentometrics". He is a member of the Sentometrics organization. When he does not code, he plays a foodie in downtown Montréal. For more details go here.
Riccardo Mancini
See MoreFreelance Quantitative Analyst
Riccardo works as a Market Risk Manager for a financial services firm. He is passionate about coding in R, quantitative models applied to finance, and… soccer!
What do other learners have to say?
Join over 12 million learners and start Financial Analytics in Spreadsheets today!
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.