To complete this exercise, you will need to use the Pandas library to perform a series of data manipulation and analysis tasks on valuation
of Indonesia banking institutions. This exercise is worth 15 points.
Pre-Lab Preparation & Prerequisites
Environment Setup and API Keys
You will need to have a proper Python environment setup, with
.env
file
containing the necessary API keys.Data Analysis w/ Pandas
Review the Pandas tutorial to familiarize yourself with the library and its
capabilities.
Submission Template (Google Colab)
Use the provided Google Colab template to complete the exercises and submit
them for grading (a total of 15 points).
Google Classroom
Head over to Google Classroom to submit your assignments and receive feedback.
Financial Ratios for Value Investing
When it comes to investing, the specific style of “value investing” popularized by Benjamin Graham and Warren Buffet is based on the idea of buying stocks that are undervalued relative to their intrinsic value. One way to determine if a stock is undervalued is to look at a company’s financial ratios. These ratios can help investors understand how a company is performing financially and whether its stock is a good investment. In particular, We’ll be looking at the Price-to-Earnings ratio, the Price-to-Book ratio, and the Price-to-Sales ratio.- Where the Price per share is the current stock price,
- and Earnings per share is the company’s net income divided by the number of shares outstanding.
- Where the Price per share is the current stock price,
- and the Book value per share is the company’s total assets minus its total liabilities, divided by the number of shares outstanding.
- Where the Price per share is the current stock price,
- and the Sales per share is the company’s total revenue divided by the number of shares outstanding.
Margin of Safety
In value investing, a lower ratio is generally considered better, as it indicates that the stock is undervalued relative to its financial performance. When a stock is trading at a higher P/E or P/B ratio relative to its peers, value investors may typically consider it overvalued — this is where the concept of “margin of safety” comes into play. Magin of safety is simply the difference between the intrinsic value of a stock and its market price, and a stock with a higher P/E value is considered to have a lower margin of safety, unless its growth rate is significantly higher than its peers. In the following exercise, you will be working with data directly from a financial data API to extract the historical financial ratios of Indonesia’s leading banking institutions. The data would look like this:Benchmarking Current Valuation
Benchmarking Current Valuation
Historical P/E ratios provide a benchmark for evaluating a stock’s current valuation. By comparing the current P/E ratio to its historical averages, investors can gauge whether the stock is trading above or below its historical norm.
- High Current P/E Relative to Historical P/E: If the current P/E ratio is significantly higher than its historical average, it may indicate that the stock is overvalued relative to its past valuation. This could suggest a reduced margin of safety, as the stock price may be less protected against future declines.
- Low Current P/E Relative to Historical P/E: Conversely, a lower current P/E ratio compared to historical averages may suggest undervaluation. This can indicate a higher margin of safety, as the stock is trading at a lower multiple of earnings than in the past, potentially providing a buffer against adverse developments.
Assessing Growth Expectations
Assessing Growth Expectations
Historical P/E ratios reflect market expectations and sentiment over time. By analyzing historical trends, investors can understand whether the current P/E ratio aligns with growth expectations.
- Consistent Historical P/E: If the historical P/E ratio has been consistent and the current P/E is significantly higher, it might imply that market expectations for growth are unusually high. If these expectations are not met, the stock price could suffer, reducing the margin of safety.
- Changing Historical P/E: If the historical P/E ratio has fluctuated widely, it may reflect the company’s volatility or changes in its growth prospects. Comparing the current P/E ratio to this range helps investors assess whether the stock’s current valuation is reasonable given its historical performance and market conditions.
Evaluating Market Cycles
Evaluating Market Cycles
Historical P/E ratios can provide insights into market cycles and valuation extremes. By understanding where the current P/E ratio falls within these cycles, investors can better evaluate the margin of safety.
- High Market Cycles: During market bubbles or periods of irrational exuberance, P/E ratios tend to be elevated. A current P/E ratio at the high end of historical extremes might signal that the stock is overvalued, indicating a lower margin of safety.
- Low Market Cycles: Conversely, during market downturns or periods of pessimism, P/E ratios may be depressed. A current P/E ratio at the low end of historical ranges could suggest undervaluation, providing a higher margin of safety.
Banking Institutions’ historical valuation
For your convenience, a copy of the dataset has been provided in thedatasets
folder. Using
what you’ve learned in the Pandas tutorial, you should know how to read this data into a DataFrame:
1. Basic Data Exploration
- Objective: Learn to explore the basic structure of a DataFrame
- Task: Write Python code to import the dataset and display the first 5 rows of the DataFrame. Then, report its shape (number of rows and columns).
- Hint: Use the
head()
method and theshape
attribute.
2. Filtering Data
- Objective: Learn to filter data based on specific conditions
- Task: Write Python code to filter and display rows where the
pe
is greater than 15 and whensymbol
is equal toBRIS.JK
. Name this new DataFramebris_high
. - Hint: Use boolean indexing
3. Calculate Average Values
- Objective: Learn to compute statistical summaries on a DataFrame
- Task: Write Python code to calculate the median
pe
,pb
, andps
values for the year 2022. - Hint: Use the
median()
method after filtering the DataFrame for the year 2022.
4. Sort Data, Reorder and Drop Columns
- Objective: Learn to sort data and reorder columns
- Task: Write Python code to sort the DataFrame by
ps
in descending order, then reorder the columns so thatsymbol
is the first column,year
,ps
,pe
, andpb
. Drop thecompany_name
column. - Hint: Use the
sort_values()
method, and optionallydrop()
method to remove thecompany_name
column. Recall that insort_values
, you can specify theascending
parameter toFalse
to sort in descending order. You may also choose thecolumns
parameter in thereindex()
method to reorder the columns.
5. Grouping and Aggregating Data
- Objective: Learn to group data and perform aggregate operations
- Task: Write Python code to group the bank valuation data by
symbol
and calculate the averagepe
,pb
, andps
values for each group. Display the results. Now, do the same but grouped byyear
and calculate the averagepe
,pb
, andps
values for each year. - Hint: Use the
groupby()
method followed by themean(numeric_only=True)
method.
In past versions of
pandas
, the mean()
method would include all numeric columns by default.
There is now a FutureWarning
that suggests numeric_only
defaulting to False
in the future.We can either specify numeric_only=True
to avoid the warning, or use the select_dtypes()
method to filter
out only the numeric columns before calling the mean()
method.Dive Deeper
The data you’ve been provided with is not hand curated, but rather sourced from Sectors Financial Data API. If you’re feeling adventurous, you can proceed along with this section that explains how the data is being fetched from the API and subsequently processed into a csv file. These topics may feel a bit advanced, and you are not required to complete them for the lab. They are included here for those who have a personal interest in learning more about how the “data pipeline” works in the real world.Nested Data Structures into DataFrames
The data is fetched from the API using therequests
library, as you have seen in your Quick start guide.
fetch_data
we need to specify a url. This can be constructed from
reading the correponding API documentation. For example, to fetch the historical P/E ratios of BBRI, one could do:
pd.json_normalize
to convert the JSON response into a DataFrame.
Here’s the output:
historical_valuation
key contains the data we’re interested in, and we can extract it using pd.json_normalize
once we’ve used .explode()
to expand out the list of dictionaries into separate rows.
pd.json_normalize
to expand the dictionary into separate columns:
json_normalize
: Converts JSON data into a DataFrameexplode
: Expands out a list of dictionaries into separate rows
banks
list, and then concatenate the DataFrames together to form the final dataset.
Show full code
Show full code