Learn how you can become a Python programmer in just 12 weeks.

    We respect your privacy. Unsubscribe at anytime.

    Create Financial Analysis and With Multiple Sheets and Charts in Excel from Python

    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.

    Watch tutorial

    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.

    12% Investment Solution

    Would you like to get 12% in return of your investments?

    D. A. Carter promises and shows how his simple investment strategy will deliver that in the book The 12% Solution. The book shows how to test this statement by using backtesting.

    Did Carter find a strategy that will consistently beat the market?

    Actually, it is not that hard to use Python to validate his calculations. But we can do better than that. If you want to work smarter than traditional investors then continue to read here.

    Python for Finance: Unlock Financial Freedom and Build Your Dream Life

    Discover the key to financial freedom and secure your dream life with Python for Finance!

    Say goodbye to financial anxiety and embrace a future filled with confidence and success. If you’re tired of struggling to pay bills and longing for a life of leisure, it’s time to take action.

    Imagine breaking free from that dead-end job and opening doors to endless opportunities. With Python for Finance, you can acquire the invaluable skill of financial analysis that will revolutionize your life.

    Make informed investment decisions, unlock the secrets of business financial performance, and maximize your money like never before. Gain the knowledge sought after by companies worldwide and become an indispensable asset in today’s competitive market.

    Don’t let your dreams slip away. Master Python for Finance and pave your way to a profitable and fulfilling career. Start building the future you deserve today!

    Python for Finance a 21 hours course that teaches investing with Python.

    Learn pandas, NumPy, Matplotlib for Financial Analysis & learn how to Automate Value Investing.

    “Excellent course for anyone trying to learn coding and investing.” – Lorenzo B.

    Leave a Comment