Module 2: Importing, Segmenting, and Analyzing Data
In this module, we will learn essential skills in processing, segmenting, and transforming data in order to extract useful information and get answers to your targeted questions.
If you have any questions or comments, please enter your comments in the "Discussion Area" below the videos. We learn better through discussion and mutual support!
Topic: Importing Data from Bloomberg
You are interested in the high-yield bond market. As an initial probe, you select a few bonds issued by different companies, and would like to import its price data on the secondary market for the past 2 years.
Topic: Use IF Statement
In studying financial markets, it is helpful to identify periods when the stock markets move in a certain fashion. Take the Nasdaq composite index, for example. Suppose we download its monthly data for the last twenty year. We can compute its monthly changes during each month. If the combined change of the current month and its previous two months (i.e. three-month total) exceed 300 points, then we call the market is “upbeat”. If the three-month total drops more than 300 points, then we call the market is “downbeat”. Identify those upbeat and downbeat months.
Topic: Find exact match with VLOOKUP
In a small company with six employees, everyone’s phone extension is stored in a table, but everyone’s job function is in another table. Combine this two table together using VLOOKUP.
Topic: Categorize data with VLOOKUP
Use VLOOKUP to visualize the monthly changes of Nasdaq index over the past twenty years, which range widely between -700 to more than 700.
Topic: Automatically reference a cell with MATCH and INDEX
As a stock analyst, Ava is following the stock of Google (GOOG). She has in front of her the weekly price history for the stock. She wants to find out when the stock experienced the largest price jump.
Topic: SUMPRODUCT and SUMIF
A travel guidebook is updating its restaurant ratings in a town, which has 10 restaurants. It has collected numeric data, on a 0-100 scale on food quality, service quality, and dining environment of each of the dining places. Moreover, the weights for each of the dimensions are 40%, 30%, and 30%. In addition, some restaurants have explicit agreed to be included in the guidebook. Set up a spreadsheet to calculate their weighted scores and count, among those who have opted in the book, how many restaurants have a combined score of 80 or more.
Topic: COUNT, COUNTA and COUNTIF
A marketing firm has sent out a survey to potential customers to gauge the purchase intention of a product., which is scored between 0 (no interest) to 100 (definitely buy). All copies of the surveys are tracked with a unique ID. As the table below indicates, some of the surveys are not returned and are marked as “n/a”. Use COUNT and COUNTA to compare the number of surveys received against the total numbers recorded. Then use COUNTIF to calculate how many customers gave out scores higher than 80.
Topic: Uncover correlation in data
Do the stocks of Google (GOOG) and McDonald’s (MCD) tend to move in the same direction?
Topic: Identify high performers with RANK
In launching a new hand-held device, a company has collected estimated market demand information from a number of potential markets (see table in Excel file; all numbers in thousands). The company decides to enter each market according to their estimated demand size. Use Excel to compute the ranking of each market in terms of demand.
Topic: Set cutoffs with QUARTILE/PERCENTILE
In the previous example analyzing market demand, the company also want to divide the 20 markets into four tiers, according to the quartiles in market demand. Calculate the four quartiles of market demand.