How to Use Python in an M&A Deal: Gas Station Locations

Article Overview

With gas prices sky-rocketing and recent speculation of merger and acquisition deals happening in the gas stations space, I figured it would be interesting to showcase how Python could be used to augment an M&A deal by analyzing gas station locations of Couche-Tard and Shell Canada.

You can try out the app mentioned in this article here: https://bit.ly/locationsDemo

The full Python code can be found here and can also be accessed from the Streamlit app, from the top right menu:

Figure 1: Viewing app source from Streamlit app’s menu

Importance of Store Locations in an M&A Deal

One of the typical analysis an investment banking team will do for a merger or acquisition deal is calculate cost synergies — cost synergies are the reduction of costs due to increased efficiencies following a merger of two companies. For a merger of two retail companies, a common source of cost synergies is the closure of store locations within a certain proximity of each other. For example, if two grocery store companies were to merge, they probably don’t need two stores right across the street from each other. By closing down one of the two stores, the merged company can save on rent, and also prevent cannibalization of sales from the two stores competing against each other.

In the example dashboard I put together, we will pretend Couche-Tard will be merging its gas stations with Shell Canada (although the rumours in the news as of July 2022 are that Couche-Tard will be buying out Petro-Canada gas stations).

Overview of Dashboard

The dashboard app was created with Python and Streamlit and has two main uses:

• It allows the user to pick one Couche-Tard and one Shell Canada gas station; the app then calculates the distance in KM between the two locations
• The user can then pick one of these two locations and a radius in metres to find all the gas stations within that proximity

These gas stations that fall under the radius chosen are potential candidates of locations to close down. The app provides a summary of the found locations in both table format and also plotted on a map with a radius drawn around the analyzed main gas station:

Figure 2: Locations within picked radius

Background on Python Code

To create the dashboard five main Python packages were used:

1. Requests library to scrape the gas station locations (not covered in this article)
2. Math library to calculate the distances (using simple sin/cos of latitudes and longitudes)
3. Pandas for data manipulation (importing the excel files, filtering, sorting, etc.)
4. Folium to plot the radius and markers of locations on a map
5. Streamlit to create the dashboard

Scraping Gas Stations Locations

One of the reasons why Python is great for doing this type of analysis, is that it’s fairly easy to scrape store locations from a website. Although this article is not meant to teach how to web-scrape, below are the general steps on how the gas stations were found:

1. On the store location website, right click inspect
2. Go to the Network tab, and filter for XHR
3. Refresh the site, to reload all the server requests
4. Find the JSON file that has all the info on gas stations
5. Right click, Copy à Copy as cURL (bash)
6. Paste the cURL text into a cURL converter such as: https://curlconverter.com/
7. Copy the Python code from cURL converter into your preferred Python program and you might need to modify the code a bit to grab all the locations and save them as a Pandas dataframe

For example, below is the code that was added to step 7 after finding the JSON file for the Couche-Tard locations and grabbing the Python code from https://curlconverter.com/:

Sources for locations:

Couche-Tard Locations: https://www.couche-tard.com/store-locator

Shell Locations: https://shellgsllocator.geoapp.me/?locale=en_CA

Calculating Distances between Locations

In order to find the distance between two locations, the latitude and longitude of each location are needed. Most JSON files that contain retail locations also provide latitude/longitude coordinates. However, in the off-chance that the scraped data doesn’t provide the coordinates, there are Python packages out there that help find latitude/longitude coordinates by providing a street address. One such package is geopy and the lat/long of an address can be found using the code below:

In our case, the scraped locations of Couche-Tard and Shell had latitude/longitude information. Geopy also has functions that can be used to calculate distances between two sets of coordinates more accurately. However, to keep things simple and avoid installing geopy on your local device (as it’s not one of the standard packages that comes with Python package managers such as Anaconda), I created two python functions to calculate the distance in both nautical miles and also km. These functions were converted from the excel functions found here: https://blog.batchgeo.com/manipulating-coordinates-in-excel/

Pandas for Data Manipulation

Once the excel files were created for all the store locations, there was very minimum work to be done in terms of data manipulation. One advantage of using the Pandas package in Python is the ease of applying functions on an entire data set without having to create complicated loops.

Once the user picks a location to analyze in the dashboard, the latitude and longitude of that main location are extract and Pandas then applies the distance calculator function from the previous section on the entire table to create a new “Distance KM” column for each location in the data set.

Below is the code used to create this column in the Couche-Tard table (df2) and the Shell table (df1). Pandas was also used later in the dashboard to quickly sort the tables by distance and also filter out only the locations that were within the picked radius.

Folium to Plot Locations and Radius

The package doing all the heavy lifting in the dashboard is the Folium library. The Folium documentation has many great demo codes to get started with plotting locations and shapes on a map. Within less than 30 lines of code, I was able to plot all the Couche-Tard and Shell gas station locations as well as get the map to zoom in on the picked location and also draw a circle with a chosen radius distance around the analyzed station.

Below is the drawMap function I used to create the map, followed by an explanation of the code.

The function has 4 arguments as inputs:

• df1 and df2 are the two Pandas dataframes containing all the locations of Couche-Tard and Shell gas stations
• lat and long are the latitude and longitude of the main gas station being analyzed
• radiusM is the radius in metres provided by the user in the dashboard

The drawMap function then has 4 main sections:

• The folium.Map function creates a zoomed in version of the map centered on the latitude/longitude of the chosen gas station
• The following two for loops cycle through all the locations in the two tables and plots individual markers on the map: red markers for Shell locations and blue markers for Couche-Tard locations
• The folium.Circle function then creates the circle around the chosen location with a radius in metres provided by the user
• The function then returns the map as an output, which will then be plotted in the dashboard with streamlit

Streamlit to Glue it All Together

The rest of the code are just streamlit functions and components to create the dashboard. Streamlit remains my favourite dashboarding app in Python because it is very easy to use and in just a few minutes you can have web app up and running and hosted on the streamlit cloud service for free.

Below are some snippets of the streamlit functions to give an idea of how easy it is to create some of the dropdowns and input boxes on the dashboard:

The following st.selectbox codes create two dropdowns of all the locations in the two tables:

Figure 3: Streamlit dropdowns with st.selectbox

The following code creates a numerical input box for the user to enter a radius. This radius is then later used to filter the tables and draw the circle on the map around the picked gas station:

radius = st.number_input(‘Insert a radius (metres)’, value=2500)

Figure 4: Streamlit numerical input with st.number_input

The following code creates an interactive table output which allows the user to sort by any of the columns, search within the table with CTRL+F, and also quickly highlight and copy parts of the table directly into Excel:

st.dataframe(df1)

Figure 5: Streamlit interactive dataframe with search feature

Taking the App to the Next Level

I hope this app and article gave you an idea of how Python could be used to augment the analysis of an M&A deal and also save valuable time. When I worked in investment banking in M&A, I used to see analysts spend hours upon hours doing an analysis like this by hand and fully in Excel. They would get interns to help “download” the store locations manually from the store locator websites and then manually through trial and error and google maps they would find all the store locations that were within close proximity of each other to create a short list of potential closures.

This app was put together within a couple hours and can be replicated for other store locations and brands with very minimal changes to the code. One feature that an analyst could add to the code to make it even more practical, is to run a quick for loop that finds all the gas stations within close proximity of others (vs. analyzing just one location at a time). With Python and the Pandas package this functionality can easily be added and the code will run relatively fast (a few seconds) vs. trying to run different permutations manually in Excel or with VBA.

Many of the concepts mentioned in this article are also taught in more detail in our Python 3 class at Marquee, where I cover web-scraping and creating dashboards with Streamlit. If you are interested in running a private session for your team or have any questions about the code, don’t hesitate to reach out. My contact info can be found on my github: https://github.com/dbogt/.