1. Introduction and first metrics
Hi, my name is David Ardia, and I am a professor in Quantitative Methods for Finance.
In this course, you'll learn how to build an interface with spreadsheets to monitor financial investments. Tracking their performance makes sure your investments meet your expectations on reward and risk.
2. Market value of a company
Banks, asset managers, and individuals can invest money in publicly traded firms, by buying stocks. You can think of the market value of a company as a cake.
3. Stock
This cake is divided equally into slices,
4. Stock
and each slice is a stock traded on the exchange.
5. Stock price
The market price of a stock is not constant. It fluctuates over time depending on the law of supply and demand.
6. Dividends
As a reward for holding the stock, the company may pay a pre-specified amount of money, called a dividend, to its shareholders. Dividends can be paid at various time periods.
7. Monitoring dashboard
In this course, you'll monitor the hypothetical stock ABC. You'll build a dashboard with spreadsheets composed of four panels. In each chapter, you'll build a different part of the dashboard.
8. Prices panel
The first panel summarizes past prices;
9. Returns panel
the second measures reward and risk indicators;
10. Distribution panel
the third displays the probability distribution of historical returns,
11. Benchmarking panel
and the last panel compares the stock ABC with a benchmark.
12. Historical prices and dividends
Performance monitoring starts with a dataset from which you can extract relevant metrics. In your case, you have a time series of monthly historical prices going from December 2012 to December 2017, together with the dividends paid out during that period. A time series is a series of values indexed by the time.
13. Number of prices
First, you can look at the number of prices included in the series. The number of past observations on which you are doing your computations is important, as more data leads to more accurate results.
14. Minimum and maximum prices
Next, you can focus on the extreme values reached by the price during that period; this means finding the minimum and maximum values. These indicators give you an idea about the price variation during the period under analysis.
15. Functions COUNT(), MIN() and MAX()
The spreadsheet function to count the number of observations is COUNT(),
16. Functions COUNT(), MIN() and MAX()
while the ones to compute the minimum and the maximum prices are MIN() and MAX().
17. Functions COUNT(), MIN() and MAX()
They work the same way: The input is the range of cells from which you want to extract the information,
18. Functions COUNT(), MIN() and MAX()
and the output is the metric of interest.
19. Functions COUNTIFS(), MINIFS(), and MAXIFS()
You can also condition the former calculations with given criteria. For instance, you can count only positive values. This is achieved with the COUNTIFS() function in which you specify the condition as a second argument.
20. Functions COUNTIFS(), MINIFS(), and MAXIFS()
In this case, you can count the number of prices which are greater than $48.
21. Functions COUNTIFS(), MINIFS(), and MAXIFS()
Also, you can use MINIFS() and MAXIFS() to compute the minimum and maximum under certain conditions.
22. Functions COUNTIFS(), MINIFS(), and MAXIFS()
For MINIFS() and MAXIFS(), the arguments are a bit different than for COUNTIFS(). You need to specify the range of cells from which the minimum or maximum will be computed and the range of cells over which to evaluate the criterion. If you want to compute the minimum or the maximum over the range of cells on which the criterion applies, simply use two times the range of cells in the inputs.
23. It's time to practice!
Now that you know how to deal with time series of prices, it's time to practice!