Scribd
Upload
3 views4 pages

Python

This document is a Python script that creates an Excel workbook for budgeting using the openpyxl library. It sets up a main worksheet titled 'Budget' with headers, dropdown lists for days and stores, and pre-fills suggested items with quantities. Additionally, it creates separate sheets for each store with auto-populated shopping lists and calculates total costs.

Uploaded by

tandersonj1122
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views4 pages

Python

This document is a Python script that creates an Excel workbook for budgeting using the openpyxl library. It sets up a main worksheet titled 'Budget' with headers, dropdown lists for days and stores, and pre-fills suggested items with quantities. Additionally, it creates separate sheets for each store with auto-populated shopping lists and calculates total costs.

Uploaded by

tandersonj1122
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

from openpyxl import Workbook

from [Link] import DataValidation

from [Link] import PatternFill

wb = Workbook()

ws = [Link]

[Link] = "Budget"

# Headers

headers = ["Day","Store","Item","Suggested Qty (100 ppl)","Qty","Unit Cost","Total"]

[Link](headers)

# Colors for days

colors = {

"Day 1": "FFF2CC", # light yellow

"Day 2": "D9EAD3", # light green

"Day 3": "CFE2F3", # light blue

"Drinks (Bulk)": "EAD1DC", # light purple

"Supplies": "F4CCCC" # light red

# Dropdowns

day_list = '"Day 1,Day 2,Day 3,Drinks (Bulk),Supplies"'

store_list = '"Sams,Walmart,Kroger"'

dv_day = DataValidation(type="list", formula1=day_list)


dv_store = DataValidation(type="list", formula1=store_list)

ws.add_data_validation(dv_day)

ws.add_data_validation(dv_store)

# Pre-fill suggested items

items = [

["Day 1","Sams","Hot Dogs",5],

["Day 1","Sams","Buns",7],

["Day 1","Sams","Baked Beans",6],

["Day 1","Sams","Cookies",2],

["Day 2","Walmart","Ground Turkey",20],

["Day 2","Walmart","Spaghetti Noodles",13],

["Day 2","Sams","Sauce",9],

["Day 2","Sams","Garlic Bread",4],

["Day 3","Kroger","Ground Beef",15],

["Day 3","Kroger","Ground Turkey",10],

["Day 3","Sams","Shells",6],

["Day 3","Sams","Toppings",1],

["Drinks (Bulk)","Sams","Capri Sun",3],

["Drinks (Bulk)","Sams","Water Cases",2],

["Drinks (Bulk)","Sams","Tea",2],
["Supplies","Sams","Plates",3],

["Supplies","Sams","Utensils",1],

["Supplies","Walmart","Paper Towels",3],

row = 2

for d, s, item, qty in items:

ws[f"A{row}"] = d

ws[f"B{row}"] = s

ws[f"C{row}"] = item

ws[f"D{row}"] = qty

ws[f"E{row}"] = "" # user enters actual qty

ws[f"G{row}"] = f"=E{row}*F{row}"

dv_day.add(ws[f"A{row}"])

dv_store.add(ws[f"B{row}"])

# Apply color

fill = PatternFill(start_color=colors[d], end_color=colors[d], fill_type="solid")

for col in ["A","B","C","D","E","F","G"]:

ws[f"{col}{row}"].fill = fill

row += 1

# Totals

ws["F82"] = "Grand Total"


ws["G82"] = "=SUM(G2:G80)"

ws["F83"] = "Total People"

ws["G83"] = 100

ws["F84"] = "Cost Per Person"

ws["G84"] = "=G82/G83"

# STORE SHEETS WITH AUTO LISTS

stores = ["Sams","Walmart","Kroger"]

for store in stores:

s = wb.create_sheet(title=store)

s["A1"] = f"{store} Auto Shopping List"

s["A3"] = "Total Cost:"

s["B3"] = f'=SUMIF(Budget!B2:B80,"{store}",Budget!G2:G80)'

[Link](["Item","Qty","Unit Cost","Total"])

# Auto-populated list using FILTER (Excel 365+)

s["A6"] = f'=FILTER(Budget!C2:G80,Budget!B2:B80="{store}")'

# Save

[Link]("VBS_Full_Automation.xlsx")

You might also like