Professor David Ardilla builds a spreadsheet dashboard to track a hypothetical stock ABC from 2012 to 2017, monitoring reward and risk across four panels. The lecture covers basic functions like COUNT, MIN, MAX, and conditional calculations such as COUNTIFS and MINIFS to extract metrics from monthly price and dividend data.
Chapters
Hi, my name is David Ardilla and I'm professor in Quantitative Methods for Finance. In this course you 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. 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. This cake is divided equally into slices and each slice is a stock traded on the exchange. The market price of a stock is not constant. It fluctuates over time depending on the law of supply and demand. As a reward for holding a 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. 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. The first panel summarizes past prices. The second measures reward and risk indicators. The third displays the probability distribution of historical returns. And the last panel compares the stock ABC with a benchmark. Performance monitoring starts with a data set 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 in indexed by the time. 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. 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. The spreadsheet function to count the number of observations is count, while the ones to compute the minimum and maximum prices are min and max. They work the same way. The input is the range of cells from which you want to extract the information and the output is the metric of interest. 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. In this case, you can count the number of prices which are greater than $48.
Also, you can use minifs and maxifs to compute a minimum and maximum under certain cond. For minifs and maxifs, the arguments are a bit different than for cantifs. 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 2 times the range of cells in the input. Now that you know how to deal with time series of prices, it's time to practice.
