Python for Finance — Analysing Account Receivables
Analysing account receivables is important to identify potential firm collection problems.
Account receivables are amounts owed to a company by its customers. In this story, we are going to use Python to analyse receivables for companies in the technology sector. The story will be split into two parts. First, we will start analysing account receivables for Apple. Then, we will move to analyse the receivables to sales ratio for a group of comparable companies in the Technological sector.
How to Read Account Receivables
Account receivables are financial assets from a company since they represent amounts owned to the company for selling goods or services.
One of the ratios that investors often use to compare receivables across companies is the receivable to sales ratio. A high ratio of account receivables in terms of sales indicates that a firm has a big percentage of sales in credit. A high ratio poses certain risk in companies since debtors may not be able to pay their obligations.
Every company needs to estimate how much of the outstanding receivables will not be collected. These are called bad debt receivables and reduce the profits of a company and the gross receivable amounts. Therefore, keeping low receivable levels may be seen as a good sign since the bad debt provision made by companies will therefore be smaller.
On the other hand, having very low receivables to sales ratio may indicate that a firm is too aggressive with its customers. Consequently, an aggressive collection approach, may affect firms relationships with customers impacting sales.
Account Receivables Trend Analysis with Python
First of all, we are going to perform a receivables trend analysis with Python for Apple. This will show us how account receivables have evolved during the last quarters in absolute terms.
We will use financialmodelingprep, a free financial API, to retrieve financial data. If you have followed along with my previous stories, the code below will result familiar to you. Therefore, I will not go into much details on what it does.
Basically, we are making a get request to the API endpoint to retrieve Balance Sheet data. Then, we parse the response in order to extract receivables data and add it to a Python dictionary.
Finally, we convert the dictionary into a Pandas DataFrame.
import requests
import pandas as pd
all_Receivables = {}
stock = 'AAPL'
balanceSheet = requests.get(f'https://financialmodelingprep.com/api/v3/financials/balance-sheet-statement/{stock}?period=quarter')
balanceSheet = balanceSheet.json()
all_Receivables[stock] = {}
for item in balanceSheet['financials']:
receivables = item['Receivables']
period = item['date']
all_Receivables[stock][period] = receivables
all_Receivables
receivables_apple = pd.DataFrame.from_dict(all_Receivables, orient='index')
receivables_apple = receivables_apple.T
Once we have our Pandas DataFrame, we use the library Plotly in order to plot the receivables in a bar format:
import plotly.express as px
fig = px.bar(receivables_apple, x=receivables_apple.index,y =receivables_apple[stock],title='Account Receivables: ' + stock)
fig.show()Comparing Account Receivables Across Companies with Python
In the previous section, we plotted the account receivables trend for Apple. We can see that receivables are increasing over time. This may be due to the fact that sales are increasing which is translated into a higher level of receivables.
For this reason, looking only to Account Receivables may not be enough to help investors. In this section, we are going to go one step further to analyse both:
- Account Receivables to sales ratio. This will indicate the level of receivables in terms of sales. This will be very helpful in order to compare a company with comparable companies.
- Account Receivables comparison across companies operating in the same sector.
First of all, as you see below in the code part, we have a list of companies operating in the Technological sector. In one of my previous post, I showed you how to programatically extract all tickers of companies operating in a sector.
Next, we loop through each of the companies in the list in order to extract receivables and revenues to compute the receivable to sales ratio. Next, we add these three variables into a Python dictionary. Finally, as we did in the previous section, we convert the dictionary into a Pandas DataFrame.
technological_companies = ['MSFT','AAPL','AMZN','GOOG','IBM','CSCO','ORCL','SAP','IBM']
all_Receivables = {}
for company in technological_companies:
try:
#Retrieve Balance Sheet Data for each of the companies in the list
balanceSheet = requests.get(f'https://financialmodelingprep.com/api/v3/financials/balance-sheet-statement/{company}?period=quarter')
balanceSheet = balanceSheet.json()
all_Receivables[company] = {}
for item in balanceSheet['financials']:
receivables = item['Receivables']
all_Receivables[company]['receivables'] = receivables
IS = requests.get(f'https://financialmodelingprep.com/api/v3/financials/income-statement/{company}?period=quarter')
IS = IS.json()
for item in IS['financials']:
revenues = item['Revenue']
all_Receivables[company]['Revenue'] = revenues
all_Receivables[company]['receivables_to_sales'] = float(receivables)/float(revenues)
except:
pass
receivables_companies = pd.DataFrame.from_dict(all_Receivables, orient='index')
receivables_companies = receivables_companies.T
print(receivables_companies)Then, we perform some data manipulation to keep only the row receivables_to_sales since it is the information that we want to plot.
#keep only receivables to sales
receivables_companies = receivables_companies[receivables_companies.index =='receivables_to_sales']
receivables_companies = receivables_companies.T.reset_index()Finally, after cleaning up the DataFrame, we can plot the receivables to sales ratio for different companies using Plotly:
import plotly.express as px
fig = px.bar(receivables_companies, x=receivables_companies['index'],y =receivables_companies['receivables_to_sales'])
fig.show()
Wrapping Up
We have seen two easy ways to analyze account receivables for individual companies as well as for a group of comparable companies.
In our example, we can see that IBM has an extremely high receivables to sales ratio compare to peer companies. As next step to complete our analysis, we would need to go to the financial notes of the company financial report to understand the reason for the high ratio. Is it due to IBM offering long term payments to customers? Or maybe the firm is not good enough managing the collection of receivables?
On the other extreme, we have Amazon with a very low ratio. This may be due to the fact that a part of Amazon business is directly selling to customers where payments are instantaneous.
Follow me on Twitter to learn more about Python for Finance.
Originally published at https://codingandfun.com on April 16, 2020.
