Excel Template To Create Interactive Waterfall Charts | Combines VBA + Python
Language:
Create stunning and interactive Waterfall charts with this Excel template with just ONE click.
This excel template combines VBA + Python, which does all the hard work for you in the backend to create interactive waterfall charts.
The waterfall charts can be saved as HTML, JPEG, and PDF.
import time import ctypes try: # Imports import plotly.graph_objects as go import plotly import pandas as pd import sys import os # Set Path to current directory os.chdir(sys.path[0]) # Get Full Path of Excel file (passed over from VBA) excel_file_path = sys.argv[1] # Get Worksheet Name (passed over from VBA) worksheet_name = sys.argv[2] # Read Excel Data and store it in a variable: df [dataframe] df = pd.read_excel(excel_file_path, sheet_name=worksheet_name) # Store values of columns in a seperate variable x = df['Category'] y = df['Value'] measure = df['Measure'] text = df['Text [optional]'] group = df['Group'] hovertext = df['Hovertext [optional]'] # // Settings # Titles title = df['Settings'][1] title_alignment = float(df['Settings'][2]) x_anchor_title = df['Settings'][3] yaxis_title = df['Settings'][4] # Font title_font_size = df['Settings'][7] font_family_excel = df['Settings'][8] title_font_color = df['Settings'][9] font_size = df['Settings'][10] title_font_family_excel = df['Settings'][11] # Bars Formatting decreasing_color = df['Settings'][14] increasing_color = df['Settings'][15] totals_color = df['Settings'][16] textposition = df['Settings'][17] insidetextanchor = df['Settings'][18] insidetextfontcolor = df['Settings'][19] insidetextfontsize = int(df['Settings'][20]) # Horizontal line [x-axis] show_xaxis_line = df['Settings'][23] xaxis_line_style = df['Settings'][24] xaxis_line_width = df['Settings'][25] xaxis_line_color = df['Settings'][26] # Additional Settings: base = df['Settings'][29] waterfallgap = df['Settings'][30] # Connector Styling connector_line_visible = bool(df['Settings'][33]) connector_style = df['Settings'][34] connector_width = float(df['Settings'][35]) connector_color = df['Settings'][36] # Output filename = df['Settings'][39] export_html = df['Settings'][41] export_jpeg = df['Settings'][42] export_pdf = df['Settings'][43] # Create Waterfall Chart fig = go.Figure(go.Waterfall( measure = measure, x = [group,x], y = y, base = base, text = text, textposition = textposition, insidetextanchor = insidetextanchor, insidetextfont = {'size':insidetextfontsize, 'color':insidetextfontcolor}, decreasing = {"marker":{"color":decreasing_color}}, increasing = {"marker":{"color":increasing_color}}, totals = {"marker":{"color":totals_color}}, hovertext = hovertext, connector = {'line':{'dash':connector_style,'width':connector_width,'color':connector_color},'visible':connector_line_visible, 'mode':'between'} )) # Update Layout [OPTIONAL] fig.update_layout( title = {'text':title, 'x': title_alignment, 'xanchor':x_anchor_title, 'font':{'color':title_font_color}}, yaxis_title= yaxis_title, title_font_size = title_font_size, font_size = font_size, plot_bgcolor = 'rgba(0,0,0,0)', # Transparent Background showlegend = False, font_family=font_family_excel, title_font_family=title_font_family_excel, waterfallgap = waterfallgap ) # Dispaly Horizontal Line [CONDITION] if show_xaxis_line == True: #Remove NaN Values from List cleaned_list = [value for value in x if str(value) != 'nan'] fig.add_shape( # Line Horizontal type="line", x0=-0.5, y0=0, x1=len(cleaned_list)-0.5, y1=0, line=dict( color=xaxis_line_color, width=xaxis_line_width, dash=xaxis_line_style, )) print('Waterfall is being generated ...') # Exports if export_html == "Yes": plotly.offline.plot(fig,filename=f'{filename}.html') print('HTML generated: OK') if export_jpeg == "Yes": fig.write_image(f'{filename}.jpeg') print('JPEG generated: OK') if export_pdf == "Yes": fig.write_image(f'{filename}.pdf') print('JPEG generated: OK') except Exception as e: t = time.localtime() timestamp = time.strftime('%b-%d-%Y_%H%M', t) with open("log.txt", mode="w") as f: f.write(f'{timestamp} // Error: {e}') ctypes.windll.user32.MessageBoxW(0, f'Error: {e}', "Oops, something went wrong...", 1)