What will we cover in this tutorial?
We will get financial data from FPM cloud (Free stock API) for the last years and generate a 3 sheet Excel sheet with charts similar to this. All done from Python.
Step 1: Collect the data
In this tutorial we are only going to use the example data on Apple, that is free available without registering on FMP Cloud. If you want to do it on another stock yo will need to register on their site.
What we need is the income statement and cash flow statement. They are available as JSON on their page (income statement and cash flow statement).
As our main library we will use Pandas.
The data can be collected with the following code (NOTICE: The code syntax highlighter changes & to & in the URLs => remove the amp;).
import pandas as pd
income_statement = pd.read_json("https://fmpcloud.io/api/v3/income-statement/AAPL?limit=120&apikey=demo")
income_statement.set_index('date', inplace=True)
cash_flow = pd.read_json("https://fmpcloud.io/api/v3/cash-flow-statement/AAPL?limit=120&apikey=demo")
cash_flow.set_index('date', inplace=True)
Notice that we set the index to be the date column. This makes the further work easier.
Step 2: Prepare the data
The next step we need to do is to make the necessary calculations and prepare the data.
We are only interested Revenue, Earnings Per Share (EPS) and Free Cash Flow (FCF). So let’s take that data and keep it in a DataFrame (The Pandas main data structure).
data = income_statement[['revenue', 'eps']].copy()
data['fcf'] = cash_flow['freeCashFlow']
Now the data should look something like this.
revenue eps fcf
date
2020-09-26 274515000000 3.3600 7.336500e+10
2019-09-28 260174000000 2.9925 5.889600e+10
2018-09-29 265595000000 3.0025 6.412100e+10
2017-09-30 229234000000 2.3175 5.080300e+10
2016-09-24 215639000000 2.0875 5.349700e+10
Step 3: Calculate Percentage change
We are actually not interested in the actual values. The only thing we care about is the percentage growth from year to year.
As the data is in reverse order, and we need to calculate from the beginning to the end. We first need to order the dates from old to newest.
Then it is straight forward to calculate using pct_change() method.
data = data.sort_index()
data['rev %-chg'] = data['revenue'].pct_change()
data['eps %-chg'] = data['eps'].pct_change()
data['fcf %-chg'] = data['fcf'].pct_change()
Now the data should look similar to this (or the tail of it).
revenue eps fcf rev %-chg eps %-chg fcf %-chg
date
2016-09-24 215639000000 2.0875 5.349700e+10 -0.077342 -0.100216 -0.233326
2017-09-30 229234000000 2.3175 5.080300e+10 0.063045 0.110180 -0.050358
2018-09-29 265595000000 3.0025 6.412100e+10 0.158620 0.295577 0.262150
2019-09-28 260174000000 2.9925 5.889600e+10 -0.020411 -0.003331 -0.081487
2020-09-26 274515000000 3.3600 7.336500e+10 0.055121 0.122807 0.245670
Step 5: Preparing data used in each Excel sheet
The next step is to prepare the data for each sheet in the Excel document.
We only need the last 10 years of data and will use loc to get that. Then we create a DataFrame for each sheet.
data_set = data.loc['2010-01-01':]
data_set.index = data_set.index.year
rev = data_set[['revenue', 'rev %-chg']].copy()
eps = data_set[['eps', 'eps %-chg']].copy()
fcf = data_set[['fcf', 'fcf %-chg']].copy()
Notice that we set the index only to be the year.
Step 6: Creating the Excel sheet
To create the Excel sheet we will use Pandas with the XlsxWriter.
This can be done as follows.
def create_sheet(writer, df, sheet_name):
df.to_excel(writer, sheet_name=sheet_name)
writer = pd.ExcelWriter("financials.xlsx", engine='xlsxwriter')
workbook = writer.book
create_sheet(writer, rev, 'Revenue')
create_sheet(writer, eps, 'EPS')
create_sheet(writer, fcf, 'FCF')
workbook.close()
This will create a 3-sheet Excel document. One sheet for each DataFrame (rev, eps, fcf).
The function create_sheet(…) is practical, as each DataFrame is similar and we need to create the same sheet for each DataFrame.
The result will be in the Excel document financial.xlsx.
Step 7: Creating the charts
Now it pays to have the function create_sheet(…).
The full Excel document can be created by the following code.
def create_sheet(writer, df, sheet_name):
df.to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
workbook = writer.book
chart = workbook.add_chart({'type': 'line'})
chart.add_series({
'name': sheet_name,
'categories': [sheet_name, 1, 0, 11, 0],
'values': [sheet_name, 1, 1, 11, 1],
})
column_chart = workbook.add_chart({'type': 'column'})
column_chart.add_series({
'name': sheet_name,
'categories': [sheet_name, 1, 0, 11, 0],
'values': [sheet_name, 1, 2, 11, 2],
'y2_axis': True,
})
chart.combine(column_chart)
chart.set_title({'name': sheet_name})
chart.set_x_axis({'name': 'Date'})
chart.set_y_axis({'name': 'Value'})
column_chart.set_y2_axis({'name': '%-chg'})
worksheet.insert_chart("E1", chart)
writer = pd.ExcelWriter("financials.xlsx", engine='xlsxwriter')
workbook = writer.book
create_sheet(writer, rev, 'Revenue')
create_sheet(writer, eps, 'EPS')
create_sheet(writer, fcf, 'FCF')
workbook.close()writer = pd.ExcelWriter("financials.xlsx", engine='xlsxwriter')
workbook = writer.book
create_sheet(writer, rev, 'Revenue')
create_sheet(writer, eps, 'EPS')
create_sheet(writer, fcf, 'FCF')
workbook.close()
The details of creating the charts in Excel can be found on XlsxWriter. Basically, it is sending informations to the XlsxWriter engine through dictionaries. You need to send the values that you would set in Excel, if you were working inside there.
Again, the result will be in the Excel document financial.xlsx.
Want to learn more?
If you are serious about learning Python for Finance check out this course.
- Learn Python for Finance with pandas and NumPy.
- 21 hours of video in over 180 lectures.
- “Excellent course for anyone trying to learn to code and invest.” – Lorenzo B.
Learn Python
Learn Python A BEGINNERS GUIDE TO PYTHON
- 70 pages to get you started on your journey to master Python.
- How to install your setup with Anaconda.
- Written description and introduction to all concepts.
- Jupyter Notebooks prepared for 17 projects.
Python 101: A CRASH COURSE
- How to get started with this 8 hours Python 101: A CRASH COURSE.
- Best practices for learning Python.
- How to download the material to follow along and create projects.
- A chapter for each lesson with a description, code snippets for easy reference, and links to a lesson video.
Expert Data Science Blueprint
Expert Data Science Blueprint
- Master the Data Science Workflow for actionable data insights.
- How to download the material to follow along and create projects.
- A chapter to each lesson with a Description, Learning Objective, and link to the lesson video.
Machine Learning
Machine Learning – The Simple Path to Mastery
- How to get started with Machine Learning.
- How to download the material to follow along and make the projects.
- One chapter for each lesson with a Description, Learning Objectives, and link to the lesson video.
