WATERFALL

Excel Usage

=WATERFALL(data, title, xlabel, ylabel, color_up, color_down, color_total, legend)

• data (list[list], required): Input data (Labels, Changes).
• title (str, optional, default: null): Chart title.
• xlabel (str, optional, default: null): Label for X-axis.
• ylabel (str, optional, default: null): Label for Y-axis.
• color_up (str, optional, default: “green”): Positive color.
• color_down (str, optional, default: “red”): Negative color.
• color_total (str, optional, default: “blue”): Total color.
• legend (str, optional, default: “false”): Show legend.

Returns (object): Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).

Examples

Example 1: Simple waterfall chart with mixed changes

Inputs:

data
Start	100
Increase A	50
Decrease B	-30
Increase C	20

Excel formula:

=WATERFALL({"Start",100;"Increase A",50;"Decrease B",-30;"Increase C",20})

Expected output:

"chart"

Example 2: Waterfall chart with labels

Inputs:

data		title	xlabel	ylabel
Q1	1000	Quarterly Performance	Quarter	Revenue
Q2	200
Q3	-150
Q4	300

Excel formula:

=WATERFALL({"Q1",1000;"Q2",200;"Q3",-150;"Q4",300}, "Quarterly Performance", "Quarter", "Revenue")

Expected output:

"chart"

Example 3: Waterfall with custom colors

Inputs:

data		color_up	color_down	color_total
Initial	500	blue	orange	black
Add	100
Subtract	-50

Excel formula:

=WATERFALL({"Initial",500;"Add",100;"Subtract",-50}, "blue", "orange", "black")

Expected output:

"chart"

Example 4: Waterfall chart with legend

Inputs:

data		legend
Base	1000	true
Gain 1	250
Loss 1	-100
Gain 2	150

Excel formula:

=WATERFALL({"Base",1000;"Gain 1",250;"Loss 1",-100;"Gain 2",150}, "true")

Expected output:

"chart"

Python Code

import sys
import matplotlib
IS_PYODIDE = sys.platform == "emscripten"
if IS_PYODIDE:
    matplotlib.use('Agg')
import matplotlib.pyplot as plt
import io
import base64
import numpy as np

def waterfall(data, title=None, xlabel=None, ylabel=None, color_up='green', color_down='red', color_total='blue', legend='false'):
    """
    Create a waterfall chart (change analysis) from data.

    See: https://matplotlib.org/stable/gallery/lines_bars_and_markers/bar_label_demo.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Input data (Labels, Changes).
        title (str, optional): Chart title. Default is None.
        xlabel (str, optional): Label for X-axis. Default is None.
        ylabel (str, optional): Label for Y-axis. Default is None.
        color_up (str, optional): Positive color. Valid options: Green, Blue. Default is 'green'.
        color_down (str, optional): Negative color. Valid options: Red, Orange. Default is 'red'.
        color_total (str, optional): Total color. Valid options: Blue, Black. Default is 'blue'.
        legend (str, optional): Show legend. Valid options: True, False. Default is 'false'.

    Returns:
        object: Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
    """
    def to2d(x):
        return [[x]] if not isinstance(x, list) else x

    try:
        data = to2d(data)

        if not isinstance(data, list) or len(data) < 1:
            return "Error: Data must be a non-empty list"

        # Extract labels and values
        labels = []
        values = []
        for row in data:
            if not isinstance(row, list) or len(row) < 2:
                continue
            try:
                labels.append(str(row[0]))
                values.append(float(row[1]))
            except (ValueError, TypeError):
                continue

        if len(labels) == 0 or len(values) == 0:
            return "Error: No valid data rows found"

        # Calculate cumulative values and positions
        cumulative = [0]
        for val in values:
            cumulative.append(cumulative[-1] + val)

        # Create figure
        fig, ax = plt.subplots(figsize=(10, 6))

        # Prepare bars
        x_pos = np.arange(len(labels) + 1)
        colors = []
        bottoms = []
        heights = []

        for i, val in enumerate(values):
            bottoms.append(cumulative[i])
            heights.append(val)
            if val > 0:
                colors.append(color_up)
            elif val < 0:
                colors.append(color_down)
            else:
                colors.append(color_total)

        # Add total bar
        labels.append('Total')
        bottoms.append(0)
        heights.append(cumulative[-1])
        colors.append(color_total)

        # Create bars
        bars = ax.bar(x_pos, heights, bottom=bottoms, color=colors, alpha=0.7, edgecolor='black')

        # Add connecting lines
        for i in range(len(cumulative) - 1):
            ax.plot([i, i+1], [cumulative[i+1], cumulative[i+1]], 'k--', linewidth=0.5)

        # Set labels
        ax.set_xticks(x_pos)
        ax.set_xticklabels(labels, rotation=45, ha='right')

        if title:
            ax.set_title(title)
        if xlabel:
            ax.set_xlabel(xlabel)
        if ylabel:
            ax.set_ylabel(ylabel)

        # Handle legend
        if legend == "true":
            from matplotlib.patches import Patch
            legend_elements = [
                Patch(facecolor=color_up, label='Increase'),
                Patch(facecolor=color_down, label='Decrease'),
                Patch(facecolor=color_total, label='Total')
            ]
            ax.legend(handles=legend_elements, loc="best")

        ax.axhline(y=0, color='black', linewidth=0.8)
        plt.tight_layout()

        if IS_PYODIDE:
            buf = io.BytesIO()
            plt.savefig(buf, format='png', bbox_inches='tight')
            plt.close(fig)
            buf.seek(0)
            img_base64 = base64.b64encode(buf.read()).decode('utf-8')
            return f"data:image/png;base64,{img_base64}"
        else:
            return fig
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator