← Back to all projects

New Hotel Collection

Nightly Pace Report (NPU) Automation

Automated nightly hospitality revenue reporting across multiple hotel properties.

Role
Sole Developer (Freelance)
Timeframe
Jun 2025 — Present
Status
In Production
Team Size
1
Domain
Hospitality revenue reporting / business process automation

New Hotel Collection

Nightly Pace Report (NPU) Automation

Overview

The Nightly Pace Report automation compiles a nightly revenue report for a multi-property hotel group, replacing a manual, multi-step spreadsheet process that staff previously performed by hand. Running unattended on a Windows server, it pulls together data from every relevant source each night.

It authenticates to Microsoft Graph to fetch report emails, queries the Square Orders API for point-of-sale revenue, and ingests Shiji PMS exports — then drives a macro-enabled Excel workbook via Windows COM automation to populate room totals, refund tables, and monthly summaries before emailing the finished report and archiving artifacts to AWS S3. The pipeline saves staff 3+ hours of work every night across three properties.

My Role

Sole Developer (Freelance)

  • Designed and built the end-to-end nightly reporting pipeline in Python
  • Integrated Microsoft Graph API to fetch and send report emails via Outlook
  • Integrated the Square Orders API to pull and summarize daily point-of-sale revenue
  • Automated Excel (.xlsm) workbook generation through Windows COM (pywin32)
  • Implemented AWS S3 upload of generated report artifacts
  • Handled ingestion and normalization of Shiji PMS export files
  • Set up scheduled nightly execution on a Windows server

Key Features

  • Unattended nightly execution across three hotel properties
  • Automated retrieval of report emails and CSV attachments via Microsoft Graph
  • Square POS order aggregation with timezone-correct daily windows and pagination
  • Programmatic Excel (.xlsm) workbook generation with monthly tables and totals
  • Automated report distribution by email and archival to AWS S3
  • Ingestion and normalization of Shiji PMS exports (business-on-the-books, journals/refunds)
  • End-of-month preparation and rolling monthly table management

Challenges & Solutions

Problem

Reconciling daily revenue across disparate sources (Outlook report emails, Square POS, and Shiji PMS exports) into one consistent report

Solution

Built a pipeline that pulls each source independently, normalizes it, and merges results into a single Excel workbook per day

Problem

Accurately bounding 'daily' Square orders despite timezone and UTC conversion requirements

Solution

Converted Eastern Time day windows to UTC and handled cursor-based API pagination

Problem

Programmatically updating a complex macro-enabled Excel report without manual intervention

Solution

Automated Excel via Windows COM (pywin32) to copy templates, locate date cells, and write totals/monthly tables

Architecture & Tech

Languages

Python

Frameworks

PydanticMSAL

Tools

uvpywin32boto3requestspytzExcel (xlsm macros)Windows Task Scheduler / batch script

Infrastructure

Deployment
Windows server (scheduled batch script via uv)
Cloud
AWS
Other
AWS S3 (artifact storage)

Integrations

Microsoft Graph API (Outlook)

Fetch daily report emails/attachments and send the compiled report

Square Orders API

Pull and summarize daily point-of-sale orders (gross/net sales, taxes, tips, discounts, returns)

AWS S3

Store and archive generated report artifacts

Shiji PMS

Ingest property management system export files (business-on-the-books, journal/refunds)

Microsoft Excel

Generate and update the macro-enabled (.xlsm) report workbook via COM automation

Impact & Highlights

Replaced a manual nightly Excel reporting process with an unattended automation running in production across three hotel properties.

3+ hours per night

Staff time saved

3 (Harbourside, Cordova, Central)

Properties automated

  • Built an end-to-end nightly reporting automation that replaced a manual Excel process and saved hotel staff 3+ hours of work per night
  • Integrated Microsoft Graph, the Square Orders API, AWS S3, and Shiji PMS exports into a single unattended pipeline
  • Automated complex Excel workbook generation via Windows COM (pywin32), including monthly tables, refund reconciliation, and revenue summaries
  • Delivered a production system running nightly across three hotel properties

Previous project

Mirrosa Halo

Silver Mirror

View case study

Next project

Scribe

CoffieldWeb

View case study

Have a project in mind?

Let’s talk about what you’re building and how Montgomery Labs can help ship it.

Start a project