Overview

In the following exercise, we will practice writing an automation script to calculate liquidity ratios. This script will also create an MS Excel file containing the results and three graphs so that we can visualize the liquidity ratios.

The objective of this exercise is to practice working with real-world data and to see how automation can help you be more productive.

You can also read the article "How to use a python custom module to simplify your work" to learn more about the python module used in this exercise.

Task Requirement

We want to automate the creation of a worksheet that contains a table with three liquidity ratios for Apple Inc. for the last 10 years. We also want to visualize the liquidity ratios while comparing them with industry ratios when possible.

The liquidity ratios that we want to calculate:

• Current Ratio
• Quick Ratio or Acid Test
• Cash Ratios

According to Guru Focus, as of 8-10-2020, the industry liquidity ratios are 1.96 for the Current Ratio and 1.58 for the Quick Ratio. There is no industry ratio for the Cash Ratio, so we will need to compare it with the results from previous years.

Resources

The following resources are made available to us for this exercise:

• A file named apple.csv, which contains the last ten years of Apple Inc.'s annual accounting data for the following balance sheet accounts:
  • Cash & Cash Equivalent
  • Inventories
  • Total Current Assets
  • Total Current Liabilities
• A file named financialratios.py. This file is the custom python module that stores the functions needed to calculate each ratio.

Both files and a copy of the final python script are available from the project repository. You can also get the complete financial information of Apple Inc. here.

Additionally, you will also need to have installed Python 3.8 on your computer with the following libraries:

• Pandas library
• Matplotlib

Finally, you will need a code editor. I will be using Microsoft Visual Code as my code editor.

Instructions

The instructions for this exercise are as follows:

1. Read and learn about liquidity ratios.
2. Download the apple.csv and financialratios.py files from the project repository.
3. Save both files to the project's folder.
4. On your project's folder, create a python script that does the following:
   1. Imports the necessary libraries and module
   2. Imports the dataset into a pandas DataFrame and name it df.
   3. Creates an empty DataFrame called df_ratios to store Apple Inc.'s liquidity ratios
   4. Use the required functions to calculate each ratio and save the results into the df_ratios DataFrame.
   5. Export the results to an MS Excel file.
   6. Create a graph for each of the liquidity ratios

What are Liquidity Ratios?

Liquidity ratios are metrics used to measure a company's ability to meet its short-term obligation or liabilities. The two most common liquidity ratios used in financial analysis are the current ratio and the quick ratio. You can learn more about financial ratios by reading the article "What are Financial Ratios?" by the Corporate Finance Institute or any Financial Management textbook.

Solution

The following steps can be followed to writing the script about liquidity ratios:

Step 1: Setting the project's folder

Before we begin to write the Python script, download the exercise files apple.csv and the python module financialratios.py. Save both files to the project's folder.

Next, open your code editor of choice and create the file for the script in the project's folder where you have stored the apple.csv and the financialratios.py module.

With everything setup, we can now start writing the script.

Step 2: Importing the required libraries and module

The first step is to import the necessary python module and libraries.

from matplotlib.pyplot import colorbar
import financialratios as fr
import pandas as pd 
import matplotlib.pyplot as plt

Step 3: Importing the apple.csv file

Our third step is to import the data stores in the file apple.csv. We will import the data into a panda's DataFrame object called df.

df = pd.read_csv('apple.csv', index_col='Date', parse_dates=True)

Because the apple.csv the file also contains dates; we can also set the column "Date" as our index. We can parse the content in the "Date" column as a datetime datatype.

Step 4: Creating an empty DataFrame

Now that we have imported our data and saved it to the DataFrame df, we can create the empty DataFrame where we want to store the results. We can call this DataFrame df_ratios by writing the following code:

# Create the column names
column_names = ["Current Ratio", "Quick Ratio", "Cash Ratio"]
# Create the empty dataframe
df_ratios = pd.DataFrame(columns=column_names)

Step 5: Calculating the Current Ratios

Our fifth step is to use the custom module financialratios to call the function get_current_ratio and calculate the current ratio. We will store the results into the new DataFrame df_ratios under the column named "Current Ratio". Here is the line of code we need to write:

df_ratios["Current Ratio"] = fr.get_current_ratio(df['current assets'], df['current liabilities'])

Let us dig down to see what we just wrote:

1. We specify that we want to save the results of our calculation in the column Current Ratio of the DataFrame df_ratios .
2. We call the function get_current_ratio from the custom module that we imported and named fr.
3. The function accepts two parameters. The first one is for the values in the "Current Assets" column in the DataFrame df.
4. The second parameter that we pass to the functions is the values in the "Current Liabilities" column of the DataFrame df.

We can check that the calculation has been done correctly by calling the .head method on df_ratios by printing df_ratios.head()

Step 6: Calculating the Quick and Cash Ratio

Now that we know our script is working, we can continue calculating the other two ratios in the same way we did with the current ratio.

df_ratios["Quick Ratio"] = fr.get_quick_ratio(df['current assets'], df['inventories'], df['current liabilities'])
df_ratios["Cash Ratio"] =  fr.get_cash_ratio(df['cash & cash equivalent'], df['current liabilities'])

We can check that the calculations have been done correctly by calling the .head method on df_ratios.

Step 7: Exporting the DataFrame to an MS Excel File

Now that we have calculated all the liquidity ratios for the last ten years, we can export the table to a Microsoft Excel file. We can export the results by writing the following line of code.

df_ratios.to_excel('liquidity-ratios.xlsx')

As we can see in our folder, the liquidity-ratios.xlsx file has been created and can now be open using Microsoft Excel.

Step 8: Visualizing the Current Ratio

In this step, we will use the library matplotlib to plot the current ratios for the past 10 years. Remember that we will set the industry average at 1.96.

plt.plot(df_ratios['Current Ratio'])
plt.axhline(y=1.96, color='r')
plt.xticks(rotation=45)
plt.title('Current Ratio')
plt.xlabel('Years')
plt.ylabel('Ratio')
plt.show()

If we run the script we get the following graph:

Step 9: Visualizing the Quick Ratio

Next, we will visualize the quick ratio, setting the industry ratio to 1.58.

plt.plot(df_ratios['Quick Ratio'])
plt.axhline(y=1.58, color='g')
plt.axhline(y=1, color='r')
plt.xticks(rotation=45)
plt.title('Quick Ratio')
plt.xlabel('Years')
plt.ylabel('Ratio')
plt.show()

If we run the script we get the following graph:

Step 10: Visualizing the Cash Ratio

The last step to be included in the script is the lines of code that will allow us to visualize Apple's cash ratio for the last 10 years.

plt.plot(df_ratios['Cash Ratio'])
plt.xticks(rotation=45)
plt.title('Cash Ratio')
plt.xlabel('Years')
plt.ylabel('Ratio')
plt.show()

If we run the script we get the following graph:

Final Thoughts

I hope you found this project useful and that you now have a better understanding of how python can be of great tool to use for automating repetitive tasks like calculating financial ratios. Moreover, I hope you can apply what you have learned in your job. Remember that you can extend the script you just wrote to calculate other ratios. You can also modify the python module to included different ratios too.

Please feel free to share this article with your coworkers, friends, and family through social media. You can also subscribe to my mailing list to receive information when I publishing new content.

Again thank you for taking the time to read this article.