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

    We respect your privacy. Unsubscribe at anytime.

    Pandas: Calculate a Heatmap to Visualize Historical CAGR Sector Performance

    What is CACR and why not use AAGR?

    Often when you see financial advisors have statements with awesome returns. These returns might be what is called Annual Average Growth Rates (AAGR). Why should you be skeptical with AAGR?

    Simple example will show you.

    • You start by investing 10.000$.
    • First year you get 100% in return, resulting in 20.000$.
    • The year after you have a fall of 50%, which makes your value back to 10.000$

    Using AAGR, your investor will tell you you have (100% – 50%)/2 = 25% AAGR or calls it average annual return.

    But wait a minute? You have the same amount of money after two years, so how can that be 25%?

    With Compound Annual Growth Rate the story is different as it only considers the start and end value. Here the difference is a big 0$, resulting in a 0% CAGR.

    The formula for calculating CAGR is.

    ((end value)/(start value))^(1/years) – 1

    As the above example: (10.000/10.000)^(1/2) – 1 = 0

    Step 1: Getting access to financial sector data

    In this tutorial we will use the Alpha Vantage. To connect to them you need to register to get a API_KEY.

    To claim your key go to: https://www.alphavantage.co/support/#api-key

    Where you will select Software Developer in the drop-down Which of the following best describes you? Write your organization of choice. Then write your email address and click that you are not a robot. Or are you?

    Then it will give you hare API_KEY on the screen (not in a email). The key is probably a 16 upper case character and integer string.

    Step 2: Get the sector data to play with

    Looking at Pandas-datareaders API you will see you can use the get_sector_performance_av() function.

    import pandas_datareader.data as web
    API_KEY = "INSERT YOUR KEY HERE"
    data = web.get_sector_performance_av(api_key=API_KEY)
    print(data)
    

    Remember to change API_KEY to the key you got from Step 1.

    You should get an output similar to this one (not showing all columns).

                                RT      1D      5D  ...       3Y       5Y      1
    0Y
    Communication Services   0.38%   0.38%  -0.20%  ...   24.04%   29.92%   74.7
    8%
    Information Technology   0.04%   0.04%  -1.36%  ...  104.45%  183.51%  487.3
    3%
    Consumer Discretionary  -0.06%  -0.06%   1.36%  ...   66.06%   92.37%  384.7
    1%
    Materials               -0.07%  -0.07%   1.75%  ...   17.50%   37.64%  106.9
    0%
    Health Care             -0.16%  -0.17%   0.90%  ...   37.21%   43.20%  268.5
    8%
    Consumer Staples        -0.19%  -0.19%   1.42%  ...   15.96%   27.65%  137.66%
    Utilities               -0.38%  -0.38%   0.60%  ...   13.39%   34.79%   99.63%
    Financials              -0.61%  -0.61%   3.23%  ...    1.67%   23.89%  119.46%
    Industrials             -0.65%  -0.65%   4.45%  ...   12.57%   40.05%  155.56%
    Real Estate             -1.23%  -1.23%  -0.63%  ...   12.51%      NaN      NaN
    Energy                  -1.99%  -1.99%   1.38%  ...  -39.45%  -44.69%  -29.07%
    

    The columns we are interested in are the 1Y, 3Y, 5Y, and 10Y.

    Step 3: Convert columns to floats

    As you saw in the previous Step that the columns all contain in %-sign, which tells you that the entries are strings and not floats and need to be converted.

    This can be done by some string magic. First we need to remove the %-sign before we convert it to a float.

    import pandas_datareader.data as web
    API_KEY = "INSERT YOUR KEY HERE"
    data = web.get_sector_performance_av(api_key=API_KEY)
    for column in data.columns:
      data[column] = data[column].str.rstrip('%').astype('float') / 100.0
    print(data[['1Y', '3Y', '5Y' , '10Y']])
    

    Where we convert all columns in the for-loop. Then we print only the columns we need.

                                1Y      3Y      5Y     10Y
    Communication Services  0.1999  0.2404  0.2992  0.7478
    Information Technology  0.4757  1.0445  1.8351  4.8733
    Consumer Discretionary  0.2904  0.6606  0.9237  3.8471
    Materials               0.1051  0.1750  0.3764  1.0690
    Health Care             0.1908  0.3721  0.4320  2.6858
    Consumer Staples        0.0858  0.1596  0.2765  1.3766
    Utilities               0.0034  0.1339  0.3479  0.9963
    Financials             -0.0566  0.0167  0.2389  1.1946
    Industrials             0.0413  0.1257  0.4005  1.5556
    Real Estate            -0.0658  0.1251     NaN     NaN
    Energy                 -0.3383 -0.3945 -0.4469 -0.2907
    

    All looking nice. Also, notice that we converted them to float values and not in %-values by dividing by 100.

    Step 4: Calculate the CAGR

    Now we need to use the formula on the columns.

    import pandas_datareader.data as web
    API_KEY = "INSERT YOUR KEY HERE"
    data = web.get_sector_performance_av(api_key=API_KEY)
    for column in data.columns:
      data[column] = data[column].str.rstrip('%').astype('float') / 100.0
    data['1Y-CAGR'] = data['1Y']*100
    data['3Y-CAGR'] = ((1 + data['3Y']) ** (1/3) - 1) * 100
    data['5Y-CAGR'] = ((1 + data['5Y']) ** (1/5) - 1) * 100
    data['10Y-CAGR'] = ((1 + data['10Y']) ** (1/10) - 1) * 100
    cols = ['1Y-CAGR','3Y-CAGR', '5Y-CAGR', '10Y-CAGR']
    print(data[cols])
    

    This should result in something similar.

                            1Y-CAGR    3Y-CAGR    5Y-CAGR   10Y-CAGR
    Communication Services    19.99   7.445258   5.374421   5.742403
    Information Technology    47.57  26.919700  23.172477  19.368083
    Consumer Discretionary    29.04  18.419079  13.979689  17.097655
    Materials                 10.51   5.522715   6.597970   7.541490
    Health Care               19.08  11.120773   7.445592  13.933956
    Consumer Staples           8.58   5.059679   5.003594   9.042452
    Utilities                  0.34   4.277734   6.152820   7.157502
    Financials                -5.66   0.553596   4.377587   8.177151
    Industrials                4.13   4.025758   6.968677   9.837158
    Real Estate               -6.58   4.007273        NaN        NaN
    Energy                   -33.83 -15.399801 -11.169781  -3.376449
    

    Looks like the Information Technology sector is very lucrative.

    But to make it more digestible we should visualize it.

    Step 5: Create a heatmap

    We will use the seaborn library to create it, which is a statistical data visualizing library.

    The heatmap endpoint is defined to simply take the DataFrame to visualize. It could not be easier.

    import pandas_datareader.data as web
    import seaborn as sns
    import matplotlib.pyplot as plt
    API_KEY = "INSERT YOUR KEY HERE"
    data = web.get_sector_performance_av(api_key=API_KEY)
    for column in data.columns:
      data[column] = data[column].str.rstrip('%').astype('float') / 100.0
    data['1Y-CAGR'] = data['1Y']*100
    data['3Y-CAGR'] = ((1 + data['3Y']) ** (1/3) - 1) * 100
    data['5Y-CAGR'] = ((1 + data['5Y']) ** (1/5) - 1) * 100
    data['10Y-CAGR'] = ((1 + data['10Y']) ** (1/10) - 1) * 100
    cols = ['1Y-CAGR','3Y-CAGR', '5Y-CAGR', '10Y-CAGR']
    sns.heatmap(data[cols], annot=True, cmap="YlGnBu")
    plt.show()
    

    Resulting in the following output.

    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