Identifying dates with unusual prices
1. Identifying dates with unusual prices
The first analysis you made on stock ABC allowed you to find the minimum and maximum historical values. Another question of interest about these extreme prices is "When did they occur?".2. Context affects prices
This information is crucial for a more in-depth analysis. For example, if you observe the minimum value in a period of a market downturn, like during the financial crisis of 2008, you can associate it with the state of the economy.3. Context affects prices
If the minimum occurred right after the company announced benefits below the market's expectation, then the drop in price reflects a company-specific event.4. Function VLOOKUP()
The spreadsheet function VLOOKUP() can be used to find the date associated with a specific historical price. VLOOKUP() stands for vertical lookup. It has three mandatory arguments (a "search key", a "range of cells", and an "index number"), and one optional argument ("is_sorted").5. Function VLOOKUP()
The function searches the row corresponding to the "search key" in the first column of the matrix of input cells. Once found, it returns the element in the row corresponding to the "index number" specified as the third input argument. The search key can be a number, a string, or a cell. In this case, it's the price $44.96, the minimum price.6. Function VLOOKUP()
The second argument, the "range", includes all the cells for the search. The range should include the "search_key". Here, the range is A2:B10.7. Function VLOOKUP()
The third argument, "index", stands for the column index of the value to be returned, where the first column in the range is numbered 1. In this case, the index is 2 as we want the function to return the date in which the minimum price occurred.8. Function VLOOKUP()
The last argument, "is_sorted", is optional and indicates whether the first column of the specified range is sorted. In this case, it's equal to FALSE since the column with the prices is not sorted. Note that if this argument is TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.9. Function VLOOKUP()
The function outputs the value included in the cell at the intersection between row 10, the one that contains the "search_key", and column 2 of the range of cells.10. It's time to practice!
In the next exercises, you'll get more familiar with VLOOKUP() to find the dates corresponding to the minimum and maximum historical prices. Let's practice!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.