How to generate Business Intelligence from a planning sheet in Excel with Python

It is important to have information about your hotel or camping. Normally you can use (expensive) planning software, but imagine you use a simple planning sheet in Excel. Unfortunately that doesn’t give you useful statistics. We can do something about it!

I wrote a script with Python and the module openpyxl

The sheet

The Excelsheet we use looks like this:

the planning sheet

We see three colors:

• green : arrival
• blue : back-to-back (family A check out in the morning and family B will check in on the same day in the afternoon)
• red : departure
• yellow : the guest just stays in the accommodation, which is occupied at that moment.

Also we have a google sheet with the (average) price for each month, for each accomodation type

the prices

Writing the script

There were some challenges like reading the fill color of the cell in the Excel file. The complete script is linked in the bottom

Reading the excel file

First we have to read the excel file. We use urllib to save the online file. With openpyxl we read it.

Find the color codes

Then we have to find the color codes of the various colors. We used an function for it

Make the dataframe

Now we can make the dataframe with the number of arrivals, departures and back-to-backs for each day, for each accomodation type

This will make the dataframe with all the info we need. Later I used a function to merge the prices with this table and thus calculate the turnover.

First result: dataframe with the checkins/-outs, back2backs per day per accommodation type

Booking table

With the same method, we can also make a booking table

A booking table with all the bookings

After I used standard code to calculate the various things and make the analyses.

Results

We can now calculate the turnover

Or plot the occupation

Or the frequency table of the duration of stays

Business intelligence

With these statistics I can answer questions like

• Which months give the best turn-over
• Does it make sense to open a month earlier or later
• Which accommodations are the most popular and yield the most money
• Which year was best, what are differences in the year
• Which days have the most checkins or checkouts
• etc. etc

Links

• Online interactive version: https://rcsmit-streamlit-scripts-menu-streamlit-fiaxhp.streamlitapp.com/?choice=11
• Excel file: https://github.com/rcsmit/streamlit_scripts/blob/main/input/dummy_occupation.xlsx
• Dummy prices: https://docs.google.com/spreadsheets/d/1eNdn6mAglADaqOuRyQAHkx-yFgd-2mlK_fACLIHoJNk/edit#gid=0
• Code: https://github.com/rcsmit/streamlit_scripts/blob/main/read_bezetting_test.py