PROC HTTP: Using the Web to Combat Fraud in Auto Finance During a Global Pandemic
Abstract
Most car dealerships do not buy cars outright; they finance them until they sell to a consumer. While the vast majority of dealerships conduct themselves respectably, there are occasional cases of dealership fraud. Typically, auditors are sent to dealerships to monitor vehicle inventory and verify cars are on the lot. In the midst of a global pandemic, this has been difficult to do effectively. The practice of selling vehicles without repaying the lender allows a dealership to float cash since it typically only has to pay a monthly interest amount as opposed to the full amount for the vehicle when it sells. Compounded, this practice can set a dealer back beyond the point of return, potentially resulting in hundreds of millions of dollars in loss. Time to get creative! Using PROC HTTP, its possible to grab HTML code from the dealer inventory web pages, identify VIN numbers from the HTML, and compare the list of VINs to the lender's active loans. As a result, PROC HTTP empowered the lender to monitor dealership inventories in a new way to help overcome the challenges presented by the pandemic.
Watch the presentation
Watch PROC HTTP: Using the Web to Combat Fraud in Auto Finance During a Global Pandemic on the SAS Users YouTube channel.
Introduction
Pre-pandemic lenders regularly utilized contracted in-person inventory monitoring services on the dealerships physical lots. Teams would go out onto the lot and perform an audit by manually inputting all VINs physically present at the dealership. If a loan existed with the lender for the dealership on a specific VIN, and that VIN was not present at the time of audit, the lender would require confirmation of the VIN's actual physical location.
The pandemic complicated this process. Initially a large amount of dealers temporarily shut down operations. Other dealerships limited the amount of people allowed on the premises. There were also instances where dealerships had infections among staff members and were forced to quarantine. All of these factors contributed to not being able to audit dealerships consistently in the traditional sense.
"Selling out of Trust" ("SOT" for short) is a practice where a dealership has a floorplan loan for a vehicle, then sells that vehicle to a consumer, but does not report the vehicle as sold to the lender. Typically, the dealership is required to report the vehicle as sold to the lender within a couple of days of the sale. This practice allows the dealership to continue paying a smaller monthly payment for the vehicle to the lender, while using the dollars provided by the consumer for the vehicle for other purposes (potentially paying bills, buying more inventory, advertising, etc.). Typically, a dealership that engages in an SOT does so because resources are limited. If these conditions at the dealership continue and there are more SOT situations, the dealership can dig itself into a hole it can't escape. This results in large losses for the lender. It is in the best interest of both the dealerships and the lender for consistent accountability practices in inventory monitoring and control.
The internet has changed a lot of things in the way dealerships do business. Modern consumers are well informed and arrive at dealerships having done their research online. Not only that, but a lot of consumers are now opting to cut out the trip to the dealership all together and purchase directly online. These new consumer practices require dealerships to keep an up to date inventory list on their websites at all times in order to stay competitive.
proc http
The pandemic prompted us as a lender to find new and creative ways to continue monitoring dealership inventories, since couldn't continue to do so in the traditional ways. I was introduced to PROC HTTP at SAS Global Forum 2019 in Dallas at a breakout session. I made a mental note that it seemed like a really cool tool, but at the time I couldn't think of any practical applications for the business. The pandemic changed that. I started exploring whether we could identify VINs from a dealerships advertised website inventory and compare this advertised list to the list of the lender's active floorplan VINs. In every case I researched, VIN data was stored in the html code. In a lot of cases the VIN was advertised directly on the site, but in some cases it existed only on the back end as a unique identifier.
If you are attempting to do your own research, this simplest way I have found to do so is in Chrome. On the main inventory list page you will want to identify a vehicle, right-click and select "Inspect", and then search through all of the html from that section of the page until you find a VIN number. I did run into an instance where the HTML referenced a widget and did not contain a VIN number, but when I navigated to the widget that was referenced I discovered all of the VIN numbers in the widget data.
Once you confirm that the VIN numbers exist and identify their location, you can start pulling them into SAS using PROC HTTP. In the below image, there are a couple of things to note:
- Most lenders will have security setup that makes it difficult to use PROC HTTP. In my case, I needed to use a service account (below where you see proxyusername and proxypassword). I also needed to submit each specific dealership's website to our cyber security team so the service account could grab the data and pull it into SAS.
- Please note the two different URLs below. Most dealer websites have a maximum display number for inventory pages. I've seen some that go up to 200, and some that only go to 16. The case below allowed 100 per page. You will need to run PROC HTTP for each inventory page until all available pages have been captured. In the case below I manually added them one by one, but you could potentially run a loop where you grab these pages until there's no more data to import.
- Each PROC HTTP statement send the data to a unique txt file. You will also need to create a txt file for each individual inventory page.
- It's also important to note that a lot of dealerships keep their used inventory and new inventory in separate lists. Used inventory is the hardest for a lender to monitor and represents the greatest risk in most cases.
Now that you've grabbed your data from the web it's time to import it into SAS. I did not have any prior experience importing text files into SAS before working on this project. The code in the image below was built out using the "Import Data" function in SAS. Since the HTML code varies so much from site to site, and in a lot of cases there are not great delimiters, I found it most helpful to import everything as a single column and cleanup from there. You will need to complete this step for each of the .txt files you created using PROC HTTP. Note the character limit below. You will want to validate that nothing got cut off when you import your code.
Once imported, you should end up with tables that look like this:
From here you will want to start cleaning up your data. The first thing to do is identify where your VINs are stored and eliminate any non-VIN values. In the image below I do this using the CONTAINS function in PROC SQL and add "USED" as an identifier for inventory type. Once you do this for each of the tables you created in the previous step, you can use PROC APPEND to append them all together.
Once you append everything together, you should end up with something like this:
From here I used a combination of the PRXCHANGE and the STRIP/TRANWRD functions in a data step to isolate my VIN numbers. If you go this route pay attention to the character lengths I set at the beginning of the data step. You will need to adjust these as needed as well as the specified language in quotes below as needed. Another great option that's not pictured is using a function like PRXNEXT to try to identify 16 character strings.
The end result leaves you with a clean VIN number:
Once you have a clean VIN number you can take the VIN list you created from the dealer's website and compare it to a list of the lender's active floorplan loans. Any VINs on the active floorplan list that are not found advertised on the dealership's website provides the lender the ability to ask specific questions about specific vehicles. Most dealerships will keep their website inventory up to date for the sake of their customers. There is a bit of work on the front end to get everything setup for each dealer, but (barring any major website overhauls) the lender can run this code daily to monitor any changes in dealership inventory.
Conclusion
PROC HTTP provides invaluable insight for lenders to dealership inventories and helps overcome obstacles presented to traditional inventory motoring and control in the auto finance industry.
