Automation at Work
Some examples of code similar to what I have used in my previous jobs.
These are edited from the used ones to hide any personal or business information.
Python Script to run VBA Macro in Excel Workbook
I would call this script using task scheduler, power automate, or in a copilot agent to run a VBA macro in Excel
A few examples:
Load into a copilot agent so that when prompted, the agent will format data in a set format.
Run a macro that completes pricing calculations
Python Script to run Query and automatically send filtered data
Along with Task Scheduler, this script runs every morning and returns filtered results based on items that need attention
In this example, the query would pull data for left and right matching products, hydraulic motors for example. Then the python filters out items that would need attention and send them to a email box on a regular basis.
This query and code were ran through Chat GPT in order to be cleaned and have any company specific data or schema removed.
import pyodbc
import pandas as pd
conn_str = "DRIVER={SQLite3 ODBC Driver};DATABASE=sample_catalog.db"
conn = pyodbc.connect(conn_str)
sql = """
WITH BaseProducts AS (
SELECT
ProductID,
REPLACE(ProductName, VariantTag || ' - ', '') AS CleanName,
REPLACE(REPLACE(ProductName, 'Left','*'),'Right','*') AS MatchingName,
CASE
WHEN ProductName LIKE '%Left%' THEN 'Left'
WHEN ProductName LIKE '%Right%' THEN 'Right'
ELSE NULL
END AS Side,
ProductType,
ProductStatus
FROM Products
WHERE Category = 'Hydraulic Components'
),
LeftItems AS (
SELECT * FROM BaseProducts WHERE Side = 'Left'
),
RightItems AS (
SELECT * FROM BaseProducts WHERE Side = 'Right'
),
CurrentPrices AS (
SELECT
ProductID,
PriceAmount,
EffectiveDate
FROM Pricing
WHERE IsActive = 1
)
SELECT
L.ProductID AS LeftProductID,
L.CleanName AS ProductName,
L.ProductStatus AS LeftStatus,
LP.PriceAmount AS LeftPrice,
R.ProductID AS RightProductID,
R.ProductStatus AS RightStatus,
RP.PriceAmount AS RightPrice,
CASE
WHEN LP.PriceAmount = RP.PriceAmount
AND (LP.EffectiveDate > DATE('now','-30 day')
OR RP.EffectiveDate > DATE('now','-30 day'))
THEN 'Recently synchronized'
WHEN LP.PriceAmount = RP.PriceAmount
THEN 'Matching prices - old update'
WHEN L.ProductStatus = 'Discontinued'
AND R.ProductStatus = 'Discontinued'
THEN 'Ignore'
WHEN LP.PriceAmount > RP.PriceAmount
THEN 'Update Right Variant'
WHEN RP.PriceAmount > LP.PriceAmount
THEN 'Update Left Variant'
ELSE 'Manual Review'
END AS ActionRequired
FROM LeftItems L
LEFT JOIN RightItems R ON L.MatchingName = R.MatchingName
LEFT JOIN CurrentPrices LP ON L.ProductID = LP.ProductID
LEFT JOIN CurrentPrices RP ON R.ProductID = RP.ProductID
"""
actions_needed = {
"Manual Review",
"Update Left Variant",
"Update Right Variant"
}
filtered_df = df[df["ActionRequired"].isin(actions_needed)].copy()
print(filtered_df)
html_table = filtered_df.to_html(index=False)
html_content = f"""
<html>
<body>
<h2>Catalog Consistency Audit</h2>
<p>The following product variants require attention:</p>
{html_table}
</body>
</html>
"""
with open("catalog_audit_preview.html", "w") as file:
file.write(html_content)
print("Report generated: catalog_audit_preview.html")