Fan Chart: Python in Excel


Introduction

I have used fan charts before, a long time ago. This week one of our Excel famous, Mark Proctor from Excel off the Grid, posted a video showing how to create a fan chart. I watched the video and decided to try to create a fan chart. using Python in Excel. And I did it!

A Fan Chart

This is a fan chart, as created by the Bank of England: it seems that the Bank of England first invented the fan chart:


A fan chart shows the middle line, the average or maybe the median value and then plots a series of variations of plus and minus a standard deviation or a percentile or a quartile or whatever it is you want to include.

My Fan Chart


I used my initial Python script to create a simpler version of the BoE fan chart: simpler because I included fewer scenario values, otherwise, a very similar chart.



As you can see, I have shown the actual data as a single line and then the forecast is the black line that continues on from there and then I have shown what I have called the lower and upper X%: at 50%, 75% and 90%. These are equivalent to 0.675, 1.15 and 1.96 standard deviations, respectively.

My Colourful Fan Chart


The Python script shown below is an updated version of the fan chart, which has two improvements over the one above:

  • the legend no longer masks part of the fan
  • the elements of the fan are in colour now: this is not vital, just more appealing



The Python Code

Reminder, I am using Python in Excel, the new add in that has now come with Excel 365: the ability to code in Python directly into the cells in Excel. Here is my code that you can just copy and paste into a Python enabled cell in an Excel worksheet or you can download my file from the link at the end of this post.:

import pandas as pd
import matplotlib.pyplot as plt

try:
    # Load Excel data
    df = xl("A1:J25", headers=True)

    # Merge Actual and Forecast into a single series
    df['Values'] = df['Actual'].combine_first(df['Forecast'])
    df['Date'] = pd.to_datetime(df['Date'])

    # Create the fan chart
    plt.figure(figsize=(10, 6))

    # Use a vibrant colour scheme
    plt.fill_between(df['Date'], df['Lower 90%'], df['Upper 90%'], color='#FF9999', label="90% CI") 
    plt.fill_between(df['Date'], df['Lower 75%'], df['Upper 75%'], color='#FFCC99', label="75% CI")  
    plt.fill_between(df['Date'], df['Lower 50%'], df['Upper 50%'], color='#FFFF99', label="50% CI")  

    # Plot Actual and Forecast values
    plt.plot(df['Date'], df['Values'], color='#0066CC', linewidth=2, label="Actual/Forecast")  # Blue line

    # Customize the chart
    plt.title("Fan Chart with Vibrant Colours")
    plt.xlabel("Date")
    plt.ylabel("Forecast Value")

    # Move the legend to the top-right corner
    plt.legend(loc="upper left")

    plt.grid(True, linestyle='--', alpha=0.6)
    plt.xticks(rotation=45)

    # Show the chart
    plt.show()

except NameError:
    print("Data not defined. Please load the data first.")

The code that will create the fan chart refers to lines 1 - 13 and the code on lines 37 - 38 print out the chart on your Excel worksheet.

All the rest of the code is concerned with formatting.

The Data

The data I used are shown below and you could easily add to or remove the data to make your data frame larger or small, as you wish.



Finally, the formulas I used to dynamically calculate the Lower and Upper values are in the range E17:J28. The formula in cell E17 and F17, for Lower 50% and Upper 50%, respectively, are::

  • E17 =C17 - (ROW(C17)-ROW($C$17)+1)*0.675*STDEV.P($C$17:$C$28)
  • F17 =C17 + (ROW(C17)-ROW($C$17)+1)*0.675*STDEV.P($C$17:$C$28)

Reminder, the value of 0.675 refers to the number of standard deviations for the 50% cut off, it is 1.15 for 75% and 1.96 for 95%.

Enhancing the data

I trust you find the above to be a useful addition to your Python and charting armoury. However, I felt that the chart looked a bit spartan so I enhanced the data and therefore the fan chart by creating these scenarios:

Lower and Upper Boundaries

  • 50%
  • 60%
  • 70%
  • 80%
  • 90%

I also added a bit of code to soften the fans a little bit as I felt their edges were a bit hard. Here is the full code for all of that: again, copy and paste the code into your Excel file or just download my file by clicking on the link at the end of this post.

import pandas as pd 

import matplotlib.pyplot as plt


try:

    # Load Excel data

    df = xl("A4:N28", headers=True)


    # Merge Actual and Forecast into a single series

    df['Values'] = df['Actual'].combine_first(df['Forecast'])

    df['Date'] = pd.to_datetime(df['Date'])


    # Create the fan chart

    plt.figure(figsize=(10, 6))


    # Use a vibrant color scheme

    plt.fill_between(df['Date'], df['Lower 90%'], df['Upper 90%'], color='#FF9999', alpha=0.6, label="90% CI")

    plt.fill_between(df['Date'], df['Lower 80%'], df['Upper 80%'], color='#FFCC99', alpha=0.4, label="80% CI")

    plt.fill_between(df['Date'], df['Lower 70%'], df['Upper 70%'], color='#FFD699', alpha=0.3, label="70% CI")

    plt.fill_between(df['Date'], df['Lower 60%'], df['Upper 60%'], color='#FFFF99', alpha=0.4, label="60% CI")

    plt.fill_between(df['Date'], df['Lower 50%'], df['Upper 50%'], color='#FFFFCC', alpha=0.6, label="50% CI")


    # Plot Actual and Forecast values

    plt.plot(df['Date'], df['Values'], color='#0066CC', linewidth=2, label="Actual/Forecast")  # Blue line


    # Customize the chart

    plt.title("Fan Chart with alpha Controlled Colours")

    plt.xlabel("Date")

    plt.ylabel("Forecast Value")


    # Move the legend to the top left corner

    plt.legend(loc="upper left")


    plt.grid(True, linestyle='--', alpha=0.6)

    plt.xticks(rotation=45)


    # Show the chart

    plt.show()


except NameError:

    print("Data not defined. Please load the data first.")

Conclusion

There you are, enough to get you started with a basic fan chart that looks a lot like the fan charts that the Bank of England might produce. Download my file to see some of the developments that I built into the fan chart as I created the example and as I sought to improve what I had done.


Download my Excel file

Duncan Williamson

11 December 2024



No comments: