Creating an analytics-powered attendance tracking app using VBA & Python
Combining the use of VBA and Python, an interesting synergy to create an analytics powered attendance tracking for large-scale corporate events.
Introduction
The hype of newer, more powerful and easy to code programming languages such as Python, R, etc makes older programming languages such as Microsoft Visual Basic for Applications (VBA) relatively less popular to pick up in comparison. However, this does not mean that they are any less effective or useful, especially when work processes are still heavily dependent on Microsoft applications. In fact, existing work processes that use VBA scripts can be augmented using python libraries to increase code efficiency or add additional features. Hence, the inspiration for this application stems from augmenting a VBA script with the capabilities of python libraries by integrating them together. The objective of this application will be to build an automated attendance tracking & registration system for corporate events that are powered by a VBA script while at the same time, leverage on Python’s data analysis & visualisation libraries in integrating analytics and data visualisation capabilities into this application. The following codes can be found in my github repo.
VBA & Excel component for this application
Firstly, the U.I has to be created for the application. The interface will be created using excel as it has to be integrated with the backend VBA script. Moreover, it will make the application easy to deploy and user-friendly. For this application, there will be 4 U.Is which will be split into 4 excel sheets. The 4 excel sheets are the registration sheet, the database sheet as well as the application’s master control sheets for the VBA script and Python respectively.
Registration Sheet
The registration sheet will be used to register attendants. It will use a unique identification number that will be used to query the attendant’s information and register them from the database application. This number is customisable and can be pre-set by the user to suit the event’s needs. In addition, we created the registration U.I such that it can query up to 20 types of attendant’s information in order to app customisation flexible. The querying process and registration of the attendant will be handled automatically using a VBA script.
Database Sheet
The database sheet will be used to store the event’s attendant database. This sheet will also be used as a query source for the VBA script that will be created later as well as the main database for the python script to use for analytics and data visualisation functions. The above data being used is mock data for demonstration purposes only.
Master Control — VBA Script
This master control sheet can be used by the user to pre-set variables to customise the app to the event’s needs. The first variable that can be configured is the column name and cell location containing the variables that the user wants the VBA to query from. The second variable is the column containing the value that the user wants to change as well as the value that the user wants to change into when the query is a success. This variable is aimed at the attendance column where the attendant’s attendance status will be changed upon registration.
Master Control — Python
This master control sheet can be used by the user to pre-set features of the python data visualisation U.I. The U.I will be powered by Streamlit and it will consist of 2 components. The first component will consist of general information on attendance data and the second would be 5 customisable data visualisation graphs that will be set using this master control. The customisable features include the type of graphs used and which column data does the user want to show in the U.I. This master control can also be used to activate the Streamlit, hence, this allows the user the option to use the application either with or without the Streamlit application.
VBA Script
The following VBA script is used to automate the query registration process when the user uses the registration sheet. The script below is placed directly into the registration sheet visual basic editor such that it will run automatically upon receiving an input identification number to query.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range(“B1”)) Is Nothing Then
If Not IsEmpty(Range(“B1”).Value) Then
Dim IC_confirm As Variant
Dim ICsearch As Variant
Dim IC_value As Variant
Dim Input_confirm As Variant
Dim Input_value As Variant
Dim Input_col As Variant
IC_value = Range(“B1”).Value
Input_value = Sheets(“Master_Control”).Range(“B8”).Value
Input_col = Sheets(“Master_Control”).Range(“E7”).Value
IC_confirm = Sheets(“Master_Control”).Range(“B4”).Value & “:” & Sheets(“Master_Control”).Range(“B4”).Value
Input_confirm = Sheets(“Master_Control”).Range(“B7”).Value & “:” & Sheets(“Master_Control”).Range(“B7”).Value
Set ICsearch = Sheets(“Attendance_Sheet”).Range(IC_confirm).Find(IC_value)
If ICsearch Is Nothing Then
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Interior.Color = RGB(255, 0, 0)
MsgBox (“IC not found”)
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Value = “”
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Interior.ColorIndex = 0
Else
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Interior.Color = RGB(0, 255, 0)
ActiveWorkbook.Worksheets(“Registration”).Range(“B5”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 1).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B6”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 2).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B7”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 3).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B8”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 4).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B9”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 5).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B10”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 6).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B11”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 7).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B12”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 8).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B13”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 9).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“B14”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 10).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K5”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 11).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K6”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 12).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K7”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 13).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K8”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 14).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K9”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 15).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K10”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 16).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K11”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 17).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K12”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 18).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K13”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 19).Value
ActiveWorkbook.Worksheets(“Registration”).Range(“K14”).Value = ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, 20).Value
ActiveWorkbook.Worksheets(“Attendance_Sheet”).Cells(ICsearch.Row, Input_col).Value = Input_value
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Value = “”
Application.Wait (Now + TimeValue(“0:00:03”))
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Value = “”
ActiveWorkbook.Worksheets(“Registration”).Range(“B1”).Interior.ColorIndex = 0
End If
End If
End If
End SubThe VBA script above is basically an if function which querries the inputted identification number (IC_value) from the database sheet. This function returns up to 20 of the attendant’s information to the registration page if the attendant’s identification number is found. The function returns a message box notifying the user that the identification number is not found otherwise. The function will also update the attendance status of the attendant in the database sheet.
Python Component for this application
As mention previously, the python component for this application will be a data visualisation tool that can be activated in the Python master control sheet. This tool will be powered by Streamlit and will have 2 components. The first component will display generalised information on attendance data. The second component will be a section that displays up to 5 customisable graphs based on the database columns configured in the master control.
First Component
This component will display generalised information such as the proportion of attendants compared to non-attendants in a pie chart.
This component will also display information relating to attendants that require personalised tracking. This function is useful for event coordinators that need to track key attendants such as guest-of-honours, management officials, etc
Second Component
The second component consists of up to 5 customisable graphs that can display any of the column information in the database. The column options have to configure in the master control first. The choice of graphs is limited to vertical bar plots and box plots.
Streamlit Python Script
The following python script will be used to create the Streamlit U.I above. Please take note that the following code segments have to be run as a whole, segmenting the code makes the explanation of each function easier
Importing modules & creating functions
Apart from importing the relevant modules, 3 functions will be created to plot the necessary graphs when needed. This will reduce the overall code length later on.
import streamlit as st
import pandas as pd
import os
import openpyxl
from math import pi
import plotly.express as px
#Open attendance xl filedef createbar(df,X, y, title):
fig = px.bar(df, x=X, y=y)
fig.update_layout(title_text=title)
st.plotly_chart(fig, use_container_width=True)def createdistplot(df, X, title):
fig = px.histogram(df, x=X, marginal=”box”, hover_data=df.columns)
fig.update_layout(title_text=title)
st.plotly_chart(fig, use_container_width=True)def createpie(df,X, y, title):
fig =px.pie(df,values=y,names=X)
fig.update_layout(title_text=title)
st.plotly_chart(fig, use_container_width=True)
Opening the Database Sheet
This code segment will extract information from both the master control and transform the attendant’s database into a DataFrame
ospath = os.path.dirname(os.path.abspath(__file__))
print(ospath)streamlit_as = pd.read_excel(open(r”{}\Revised_attendance_sheet.xlsm”.format(ospath),’rb’),sheet_name=”Attendance_Sheet”)
streamlit_ms = pd.read_excel(open(r”{}\Revised_attendance_sheet.xlsm”.format(ospath),’rb’),sheet_name=”streamlit_reference_sheet”)book = openpyxl.load_workbook(r”{}\Revised_attendance_sheet.xlsm”.format(ospath))
Input_label = book[“Master_Control”][“B6”].value
Input_value = book[“Master_Control”][“B8”].value
First Component
This code segment will create the attendance database in the Streamlit U.I as well as plot out the pie chart on the proportion of attendees vs non-attendees. This code segment will also filter out special guests in the list and update the U.I on their status.
st.header(“Attendance tracking App”)
st.subheader(“Attendance Breakdown”)
st.write(streamlit_as)streamlit_as_YN_y = streamlit_as[Input_label].value_counts().tolist()
streamlit_as_YN_x = streamlit_as[Input_label].value_counts().keys().tolist()streamlit_as_YN_df = pd.DataFrame({“Input_Label”:streamlit_as_YN_x,”Proportion”:streamlit_as_YN_y})createpie(df=streamlit_as_YN_df,X=”Input_Label”, y=”Proportion”, title=””)field_1 = streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 1”, “Cols”].iloc[0]
field_2 = streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 2”, “Cols”].iloc[0]
field_3 = streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 3”, “Cols”].iloc[0]
field_4 = streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 4”, “Cols”].iloc[0]
field_5 = streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 5”, “Cols”].iloc[0]streamlit_as_Y = streamlit_as[streamlit_as[Input_label] == Input_value]Special_field_col = book[“streamlit_reference_sheet”][“B9”].value
Special_field_value = book[“streamlit_reference_sheet”][“B10”].valueSpecial_cond = streamlit_as[streamlit_as[Special_field_col] == Special_field_value]n = 0
for i in Special_cond[Input_label]:
if i == Input_value:
n =+ 1st.subheader(“Special Guest Tracking”)
if n == 0:
st.info(“No Special Guests have arrived yet”)
else:
st.warning(“Take note, certain Special Guests have arrived”)
Special_cond_have = Special_cond[Special_cond[Input_label] == Input_value]
st.write(Special_cond_have)
Second Component
The code for the second component is large nested if statement which first checks if the user wants something to be plotted based on the configuration of the master control. Afterwhich, the statement will check the type of graph to plot and calls the appropriate graph plotting function created earlier on when importing the modules.
st.subheader(“Graphical representation for Field 1 — {}”.format(field_1))
if not field_1 == “None”:
if streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 1”, “Choice of graph”].iloc[0] == “displot”:
createdistplot(df=streamlit_as_Y, X=field_1, title=”breakdown”)
elif streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 1”, “Choice of graph”].iloc[0] == “V_bar”:
print(“bar code — graph 1”)
y = streamlit_as_Y[field_1].value_counts().tolist()
X = streamlit_as_Y[field_1].value_counts().keys().tolist()data = pd.DataFrame({“Categories”:X,”Count”:y})
createbar(df=data,X=”Categories”, y=”Count”, title=”breakdown”)else:
st.write(“No Data field selected”)st.subheader(“Graphical representation for Field 2 — {}”.format(field_2))
if not field_2 == “None”:
if streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 2”, “Choice of graph”].iloc[0] == “displot”:
createdistplot(df=streamlit_as_Y, X=field_2, title=”breakdown”)
elif streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 2”, “Choice of graph”].iloc[0] == “V_bar”:
print(“bar code — graph 2”)y = streamlit_as_Y[field_2].value_counts().tolist()
X = streamlit_as_Y[field_2].value_counts().keys().tolist()data = pd.DataFrame({“Categories”:X,”Count”:y})
createbar(df=data,X=”Categories”, y=”Count”, title=”breakdown”)
else:
st.write(“No Data field selected”)st.subheader(“Graphical representation for Field 3 — {}”.format(field_3))
if not field_3 == “None”:
print(streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 3”, “Choice of graph”].iloc[0])
if streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 3”, “Choice of graph”].iloc[0] == “displot”:
print(“Pie code — graph 3”)
createdistplot(df=streamlit_as_Y, X=field_3, title=”breakdown”)elif streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 3”, “Choice of graph”].iloc[0] == “V_bar”:
print(“bar code — graph 3”)y = streamlit_as_Y[field_3].value_counts().tolist()
X = streamlit_as_Y[field_3].value_counts().keys().tolist()data = pd.DataFrame({“Categories”:X,”Count”:y})
createbar(df=data,X=”Categories”, y=”Count”, title=”breakdown”)
else:
st.write(“No Data field selected”)st.subheader(“Graphical representation for Field 4 — {}”.format(field_4))
if not field_4 == “None”:
if streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 4”, “Choice of graph”].iloc[0] == “displot”:
print(“Pie code — graph 4”)
createdistplot(df=streamlit_as_Y, X=field_4, title=”breakdown”)elif streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 4”, “Choice of graph”].iloc[0] == “V_bar”:
print(“bar code — graph 4”)y = streamlit_as_Y[field_4].value_counts().tolist()
X = streamlit_as_Y[field_4].value_counts().keys().tolist()data = pd.DataFrame({“Categories”:X,”Count”:y})
createbar(df=data,X=”Categories”, y=”Count”, title=”breakdown”)
else:
st.write(“No Data field selected”)st.subheader(“Graphical representation for Field 5 — {}”.format(field_5))
if not field_5 == “None”:
if streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 5”, “Choice of graph”].iloc[0] == “displot”:
createdistplot(df=streamlit_as_Y, X=field_5, title=”breakdown”)
elif streamlit_ms.loc[streamlit_ms[“Data_col”] == “Data 5”, “Choice of graph”].iloc[0] == “V_bar”:
print(“bar code — graph 5”)y = streamlit_as_Y[field_5].value_counts().tolist()
X = streamlit_as_Y[field_5].value_counts().keys().tolist()data = pd.DataFrame({“Categories”:X,”Count”:y})
createbar(df=data,X=”Categories”, y=”Count”, title=”breakdown”)
else:
st.write(“No Data field selected”)
Potential Improvements
There are 3 possible improvements that can be explored to make the application more effective. First, increasing the number of graphs that can be used in the second component. Second, considering importing the database into an SQL table compared to having it being loaded in excel instead. Finally, improve the master control sheet for VBA to allow more customisation of column actions and changes when a query is successful.
Conclusion
In conclusion, the code segments and explanations above only outline a feasible and basic approach one can take to building an analytics powered automated attendance taking & registration application using VBA and Python. This application has definitely been an interesting project to explore as the experience has proven how Python scripts can be creatively integrated to work with VBA scripts, enhancing the potential for customised Microsoft Macro-powered applications to perform more complex tasks.
