Hertz Global Holdings (HTZ) has had significant media coverage due to a recent bankruptcy filing as well as an attempt to raise up to an additional $500 million in equity after the bankruptcy declaration. Hertz has filed for Chapter 11 Bankruptcy anticipating a reorganization. The move to raise equity has been stopped by the as noted by the most recent Hertz 8Q filing as of June 18th, 2020. As of June 12, 2020.
Summary
This article shows analysis using the Python programming language to both download this data from the Hertz website as well as save it into “DataFrames” that we exported to “.csv” files that were then manipulated using Excel. The code used is in the appendix to this article.
To download the data a search was performed on the Hertz website for all vehicles for sale within 10,000 miles of St. Louis, MO. The same 10,000 radius download was performed for New York City, NY and San Francisco, CA. Unless otherwise stated statistics relate to the “St. Louis” search. This search yielded some descriptive information, but not a comprehensive view of the size of the fleet portfolio that is for sale.
Search results initially showed a total of 26,054 vehicles for the St. Louis search but after scraping the relevant search only 11,994 vehicles were found before the website search results began showing up blank. This brings into question the accuracy of their search option. Various sources online state that Hertz originally put up over 50,000 vehicles for sale, however, without a comprehensive universe that contains unique Vehicle Identification Numbers (VINSs), coupled with a systematic scraping approach we cannot corroborate with certainty the size of the fleet for sale. In our case this meant that while a search garnered 26,054 results, thumbing through the results shows that individual results ended after 11,994 vehicles. Stated in another manner, the total suggested search results did not match the available displayed results. This is most likely related to the programming structure and/or display structure of their used car sale website.
Data
The data below briefly shows some of the data from the St. Louis search. Some descriptions are longer than others. For example, we have two entries in our St. Louis data that list ‘1992’ as the full description with mileage of ‘1 mile’ and no other information. This is common with large datasets and not a concern for us because it is not material based on the total size of the data from the search. In addition, there are 215 entries for vehicles that require a manual call to the location for a price quote. These 215 items have been removed from our St. Louis Data. sample statistics for St. Louis data are listed below.
Example (St. Louis Zip Code Search):
Vehicle Title / Description: “2019 CADILLAC XT5 Premium Luxury SUV”
Price Listed: $33,279
Mileage: 24,664
Descriptive Statistics Overview:
Total Vehicles: 11,994
Total Sum of Sale Value: $217,583,305
Average Price: $18,472
Max Price: 2019 Chevrolet Corvette Z06 3LZ Coupe: $67,995
Min Price: 2016 Kia Rio LX Sedan: $6,877
Average Mileage: 33,350
Average Year: 2018.70
Figure 1: Portfolio Sale Value by Make
Figure 2: Vehicle Data Buckets Summary Statistics by Search Location
Closing
The data above shows us that the average price, mileage, and age across our samples is similar. On average a car that Hertz is selling is worth somewhere around $18,500. In addition, average mileage is approximately around 33,000 – 34,000 miles for cars put up for sale and most cars are 2018-2019 models. We also see that Hertz substantially favors Chevrolet models for its fleet. However, without having a listing of all the unique vehicles listed for sale we cannot go much further.
Next Steps:
The next step is to try to get an idea of how much money Hertz can raise by selling their portfolio and how that could impact them going forward. To do this the following steps will be attempted.
- Assemble a complete universe of available for sale cars
- Based on the universe calculate an estimated value of the entire sale portfolio
- Use financial statements to determine what this impact could have for holders of Hertz debt for recoveries as Hertz continues bankruptcy proceedings
In a bankruptcy reorganization many factors can impact recoveries based on where your securities sit in the firm’s capital structure. Usually, debt holders will receive some portion of their total principal in a recovery or equity in the newly reorganized company. That perceived/estimated amount has a significant impact on the market value of the current securities. Modeling potential recoveries can give insight into what current outstanding securities are worth.
Appendix
Figure 3: Sliced File Format Example
Figure 4: Python Code
from requests import get
from bs4 import BeautifulSoup
import pandas as pd
url_insert = 0 # a variable to increment by 35 results per page
count_one = 0
count_two = 0
#create an empty dataframe, the "Split" columns are placeholders for when we split our multi-word title into
#components so in Excel we can analyze by make, model, etc.
df_middle = pd.DataFrame(columns = ['Description', 'Price', 'Mileage', 'Split 1', "Split 2", "Split 3", "Split 4",
"Split 5", "Split 6", "Split 7", "Split 8", "Split 9", "Split 10"])
#at 35 results per page using a p of 800 means we can search up to 28,000 (35 * 800) results. The most results we got after searching
#was just over 26,000 even though we did not download all of them because blank pages started just before 12,000 at the time
# of our St. Louis 10,000 mile radius search
for p in range(0,800):
url = 'https://www.hertzcarsales.com/used-cars-for-sale.htm?start=' + str(url_insert) + '&geoRadius=10000&geoZip=10007'
response = get(url)
html_soup = BeautifulSoup(response.text, 'html.parser')
type(html_soup)
li_class = html_soup.find_all('li', class_ = 'item hproduct clearfix closed certified primary')
count_one = count_one + 1
print(count_one)
print(url)
url_insert = url_insert + 35
for i in li_class:
title_long = i.find('a', class_ ='url')
title_long = title_long.text
title_long = str(title_long)
title_long = title_long.rstrip("\n")
title_long = title_long.lstrip("\n")
title_split = title_long.split()
#our pre-split placeholder list, when split up to 14 columns are available, one for each word based on the number of
#words in the title
blank_list = ["none", "none", "none", "none", "none", "none", "none", "none", "none", "none", "none", "none", "none", "none"]
for a in range(len(title_split)):
blank_list[a]=title_split[a]
price = i.find('span', class_='value')
price = price.text
price = str(price)
price = price.rstrip("\n")
price = price.lstrip("\n")
#print(price)
data = i.find('div', class_='gv-description')
mileage = data.span.span
mileage = mileage.text
mileage = mileage.rstrip("\n")
qmileage = mileage.lstrip("\n")
#the line below removes the text "miles" from our mileage column
mileage_clean = ''.join([i for i in mileage if i.isdigit()])
#print(mileage.text)
#print("----------------------------------------------------")
list_current = {'Description':[title_long], 'Price':[price], 'Mileage':[mileage_clean], 'Split 1':blank_list[0], 'Split 2':blank_list[1], 'Split 3':blank_list[2],
'Split 4': blank_list[3], 'Split 5':blank_list[4], 'Split 6':blank_list[5], 'Split 7':blank_list[6], 'Split 8':blank_list[7], 'Split 9':blank_list[8],
'Split 10':blank_list[9], 'Split 11':blank_list[10], 'Split 12':blank_list[11], 'Split 13':blank_list[12], 'Split 14':blank_list[13]}
df_current = pd.DataFrame(data = list_current)
count_two = count_two + 1
print(count_two)
#print(df_current)
df_middle = df_middle.append(df_current)
df_middle.to_csv('C:\\Users\\james\\PycharmProjects\\workingfiles\\Webscraping\\Hertz\\output_NewYorkCity.csv')