Create Gauge & Bullet Charts In 3 Seconds Using Excel and Python 🐍 | Free Excel Template
Language:
In this tutorial, I will show you the Gauge/Bullet Chart Generator I have created in Excel. With this template, you can easily create Indicator Charts directly in Excel using VBA & Python.
📝 Resources:
Download the Gauge/Bullet Chart Generator for free here:
Download Here
👩💻 Source Code:
import time, sys, os, ctypes # Libraries already included in Python # Set Path to current directory os.chdir(sys.path[0]) # If this fails, error log will be created try: # Imports import plotly.graph_objects as go # pip install plotly import plotly # pip install plotly import pandas as pd # pip install pandas # Get Workbook-& Worksheetname by system arguments passed over by VBA workbook = sys.argv[1] worksheet = sys.argv[2] # Create dataframe with Pandas df = pd.read_excel(workbook, sheet_name=worksheet) # // Shape shape = df['Settings'][1] mode = df['Settings'][2] # // Title title = df['Settings'][5] title_alignment = df['Settings'][6] title_font_size = df['Settings'][7] title_font_family = df['Settings'][8] # // Values value = df['Settings'][11] threshold = df['Settings'][12] reference = df['Settings'][13] max_value = df['Settings'][14] first_range_visible = bool(df['Settings'][15]) if first_range_visible == False: first_range_min = 0 first_range_max = 0 else: first_range_min = df['Settings'][16] first_range_max = df['Settings'][17] second_range_visible = bool(df['Settings'][18]) if second_range_visible == False: second_range_min = 0 second_range_max = 0 else: second_range_min = df['Settings'][19] second_range_max = df['Settings'][20] # // Colors title_color = df['Settings'][23] background_color = df['Settings'][24] bar_color = df['Settings'][25] threshold_color = df['Settings'][26] first_range_color = df['Settings'][27] second_range_color = df['Settings'][28] increasing_color = df['Settings'][29] decreasing_color = df['Settings'][30] tick_color = df['Settings'][31] # // Axis show_axis = bool(df['Settings'][34]) tickmode = df['Settings'][35] number_ticks = int(df['Settings'][36]) position_ticks = df['Settings'][37] len_ticks = float(df['Settings'][38]) width_ticks = float(df['Settings'][39]) size_ticks = float(df['Settings'][40]) # // Additional Settings font_size = float(df['Settings'][43]) number_font_size = float(df['Settings'][44]) delta_position = df['Settings'][45] show_relative_change = bool(df['Settings'][46]) height = df['Settings'][47] threshold_width = df['Settings'][48] threshold_thickness = float(df['Settings'][49]) # // Exports filename = df['Settings'][52] export_html = df['Settings'][54] export_jpeg = df['Settings'][55] export_pdf = df['Settings'][56] # // Create Figure fig = go.Figure(go.Indicator( visible = True, mode = mode, value = float(value), domain = {'x': [0.1, 1], 'y': [0, 1]}, delta = {'reference': reference, 'position': delta_position, 'relative': show_relative_change, 'increasing':{"color":increasing_color}, 'decreasing':{"color": decreasing_color}, 'font': {"size": font_size} }, number = {'font':{"size":number_font_size}}, title = {'text':title, 'font': {"size": int(title_font_size), "family":title_font_family, "color":title_color}, 'align':title_alignment}, gauge = { 'shape': shape, 'axis': { 'range': [None, float(max_value)], 'tickmode':tickmode, 'visible':show_axis, 'dtick':number_ticks, 'tickcolor':tick_color, 'tickwidth':width_ticks, 'ticklen':len_ticks, 'ticks':position_ticks, 'tickfont':{'size':size_ticks}}, 'threshold': { 'line': {'color': threshold_color, 'width': float(threshold_width)}, 'thickness': float(threshold_thickness), 'value': float(threshold)}, 'bgcolor': background_color, 'steps': [ {'range': [float(first_range_min), float(first_range_max)], 'color': first_range_color}, {'range': [float(second_range_min),float(second_range_max)], 'color': second_range_color}], 'bar':{"color":bar_color}, })) fig.update_layout(height = height) # Print to screen print('Indicator is being generated ...') # Exports to different file types if export_html == "Yes": plotly.offline.plot(fig,filename=f'{filename}.html') if export_jpeg == "Yes": fig.write_image(f'{filename}.jpeg') if export_pdf == "Yes": fig.write_image(f'{filename}.pdf') # Error log except Exception as e: # Create Timestamp t = time.localtime() timestamp = time.strftime('%b-%d-%Y_%H%M', t) # Write to Log.txt file with open("log.txt", mode="w") as f: f.write(f'{timestamp} // Error: {e}') # Display Message Box to User ctypes.windll.user32.MessageBoxW(0, f'Error: {e}', "Oops, something went wrong...", 1)