Automated School Budget Tracker using Google Form, Excel & VBA

Managing a school budget manually can be time-consuming and prone to errors. But with the power of Google Forms, Excel, Pivot Tables, and VBA automation, you can create a smart and dynamic Automated School Budget tracker that’s easy to maintain and insightful to use.

In this blog I will explain how I created a fully automated School Budget Tracking System, from data entry through google form to real-time dashboards and even automatic email alerts for low budget warning if budget is low as criteria.

Step 1: First of all open excel workbook and create following no of sheet

• Admin(Date, Department, Expense Category, Expense Amount, Description)
• Sports (Date, Department, Expense Category, Expense Amount, Description)
• Teaching(Date, Department, Expense Category, Expense Amount, Description)
• Event (Date, Department, Expense Category, Expense Amount, Description)

Step 2: Create Google Form

Open google form and create blank form in this form use following field

• Date Field
• Department Field
• Expense Category

You can add Following Expense category

Office Supplies,Maintenance,Electricity,Staff Salary,Teacher Salary,Studey Meterial,Classroom                                        Equipment,Decoration,Food,Guest Arrangement,Sports Equipments,Coach Fees,Event Transport

• Expense Amount
• Description

Click on Submit

 Click on  Responses and link a google sheet with this form to record responses.

Step 3: Open Linked Google sheet and Generate .csv url to link Excel Sheet

• Go to File Menu
• Click on Share- Select Publish to Web
•  Select .csv format in embed (Comma-Seperated Value).

It will create a URL Like (https://docs.google.com/spreadsheets/d/e/2PACX-1vSXPkWBo34qvA9JmqieiDO9r0Cesk5CaxCYwVmWuav1qEkoTLp1BQVJLOABVnFa_WTx0_-ykztuBPdt/pub?output=csv)    Automated School Budget

• Copy this URL for Paste in Excel Workbook.

Step 3: Open Excel Sheet

• Go to Data Menu
• Click on From Web
• It will open a window, paste copy URL here
• Click on Ok

These link create connection with google sheet and display google sheet data here click on Load option. after that it will create new link sheet. rename as MasterSummary.

Step 4: Integrate Department Sheet for Filter Department wise record

• Click on Admin Sheet and goto Above Date Column (A2)  Paste below formula

=IFERROR(INDEX(MasterSummary!B$2:B$1004, SMALL(IF(MasterSummary!$C$2:$C$1004="Admin", ROW(MasterSummary!B$2:B$1004)-ROW(MasterSummary!B$2)+1), ROWS($B$2:B2))), "")

Note :-After paste formula it apply as array format otherwise it will not work  for that use press this key combination CTRL+SHIFT-ENTER  Automated School Budget

Above formula paste in all department sheet in A2 cell and only change department name Admin to as your sheet name like Sports,Teaching,Event etc.

after apply these formula now your master summary sheet linked with all department sheet as response is recorded it will auto filter departmentwise sheet and related record will be show.

Step 5: Integrated Refresh Button using VBA code

•  Insert any shape from insert menu. these button use on all sheet to refresh record.
•  Write Button name Refresh
• Go to developer tab and click on Visual Basic
• Click on insert Menu and Insert a Module
• Paste Below code

Sub RefreshAllSheetsAndPivots()
Dim ws As Worksheet
Dim pt As PivotTable
Dim lo As ListObject
On Error Resume Next ' Avoid interruption from sheets with no query/table
' Refresh all QueryTables and Tables in each sheet
For Each ws In ThisWorkbook.Worksheets
' Refresh all QueryTables (like linked data from Google Sheets)
If ws.QueryTables.Count > 0 Then
Dim qt As QueryTable
For Each qt In ws.QueryTables
qt.Refresh BackgroundQuery:=False
Next qt
End If
' Refresh all Tables (ListObjects)
If ws.ListObjects.Count > 0 Then
For Each lo In ws.ListObjects
lo.Refresh
Next lo
End If
' Recalculate all formulas in each sheet
ws.Calculate
Next ws
' Refresh all PivotTables in all sheets
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
' Trigger workbook-level refresh as a backup
ThisWorkbook.RefreshAll
On Error GoTo 0 ' Reset error handler
End Sub

• Save this file as Excel Macro-Enabled Workbook

Step 6: Integrate VBA code for Automated Email Notification

Sub SendLowBudgetReportByEmail()    'Automated School Budget
Dim totalBudget As Double
Dim remainingBudget As Double
Dim ws As Worksheet
Dim filePath As String
Dim fileName As String
Dim outlookApp As Object
Dim outlookMail As Object

Set ws = ThisWorkbook.Sheets("MasterSummary") ' Change if needed
totalBudget = ws.Range("I3").Value
remainingBudget = ws.Range("I5").Value

' Check if remaining budget is below 20%
If remainingBudget < 0.2 * totalBudget Then

' Set page setup to fit to 1 page wide
With ws.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

' Export as PDF
fileName = "LowBudgetReport_" & Format(Now, "yyyymmdd_hhmmss") & ".pdf"
filePath = Environ("TEMP") & "\" & fileName

On Error GoTo PDFErr
ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath
On Error GoTo 0

‘ Send email
Set outlookApp = CreateObject(“Outlook.Application”)
Set outlookMail = outlookApp.CreateItem(0)

With outlookMail
.To = “……………..@gmail.com”  ‘ email your email id here
.Subject = “?? Low Budget Alert”
.Body = “Please find the attached report. The remaining budget is critically low.”
.Attachments.Add filePath
.Send
End With

Exit Sub

PDFErr:
MsgBox “Failed to export PDF. Please check sheet formatting.”, vbCritical
Else
MsgBox “Remaining budget is above threshold. No email sent.”, vbInformation
End If
End Sub

Note: above code if budget is 20% below it will send email you can change according your condition

Step 7: Create Pivot Table

• Select MasterSummary Table
• Go to Insert, Click on Pivot Table create table as below

[Department–Expense Amount],[Expense Category- Expense Amount][Total Budget-Expense Budget]

• Insert Chart for above table
• Create Slicer for Department and Expense Category and link with chart

Step 8: Create Deshboard

• Take New Excel Sheet
• Copy  Chart and Slicer from Pivot Sheet and Paste here.
• Design as your choice.

Conclusion:

With this setup, your School Budget Tracker will have:

• Easy data entry via Google Form

• Centralized budget tracking in Excel

• Live  Dashboard

• Automatic refresh and email notifications using VBA

This system not only saves time but ensures transparency and accountability in school finances.

Please Watch This Class Online Click Here

For Online Technical Classes

Easy way to create Sales Deshboard