Tax Lien Aggregator

Executive Summary

Final Project Description

Investors that seek substantial returns by acquiring properties that are distressed usually pay a subscription fee to local firms that provide listings of these properties. These firms typically include title companies, data aggregators, or others that specialize in gathering such information for local real estate professionals.

There are a lot of publicly available websites that provide various types of information regarding distressed properties. These include sites such as www.ksl.com, Zillow.com, realtytrac.com, etc. Most sites with reliable information require a subscription fee. However, most properties that are distressed have property taxes that have not been paid. County websites list those properties that have tax liens against them for back taxes that have not been paid. In Salt Lake County, this website is: http://www.treasurer.slco.org/delqTax/cfml/delinqall.cfm.

The problem with this website is that is provides limited information that in and of itself is not very useful because it doesn’t include the address information for the property, only the parcel number. A potential homeowners or investors looking for distressed properties to purchase in Salt Lake County have to access two separate websites in order to determine the address of properties that are subject to a tax sale due to delinquent property taxes. Another website, http://assessor.slco.org/cfml/query/query2.cfm, can be used to find a property’s address by entering the parcel number into a search query. This makes it virtually impossible to look for distressed assets in specific geographic areas.

This final project is a web query that aggregates the tax lien information from the first website and address information from the second website. In addition, the second website has an interface with Google Maps that provides coordinate information. This coordinate information includes longitude and latitude that can be used to plot the location of the property in Google Earth Pro.

The query will be limited to those properties that are subject to a tax sale. This will allow a homeowner or investor to visually see the location of all properties in Salt Lake County that will be sold at auction by the taxing authority if the back taxes are not paid in full within five years.

Code Elements

The code that was written for the final project contains three parts: the webQuery sub-procedure to query the county website, a startingRow function that determines where data should start being entered on the query spreadsheet, and the obtainAddress sub-procedure to query the assessor website.

When the county website is queried it lists only 200 properties on each page, and the user has to navigate to the next page to see the next 200 properties. The webQuery sub gathers parcel #, owner name, category code, category description, status, balance due, first delinquent tax year, and tax sale year for each property on each page. It starts on the first page and extracts all the information from that page and stores it on sheet1. It then moves on to the next page and loops to each subsequent page. Each time the query is run, it can download information on 200 properties.

The startingRow function evaluates the row where the last entry was stored from the page last queried and indicates where the first entry of the current page being queried should be entered. The startingRow function allows the webQuery sub-procedure to loop its query on each page of the county website without overwriting information that was already saved on sheet1.

Whereas the county website provides information on 200 properties at a time, the assessor’s website provides information on only one property at a time. As such, it takes a significantly longer amount of time to gather address information. Whereas, the county website can be queried in only a few minutes, the assessor’s website may take hours. For this reason, the query for the assessor’s website is broken out into a different sub-procedure, allowing the user to run each query at different times.

Due to differences in the way the two websites are designed, the obtainAddress sub-procedure needed to make significant adjustments to html text in order to make it useable. That is one reason why the code for this sub-procedure is so long.

Learning Outcomes

I didn’t realize when I started this project, that I would have to learn a substantial amount of additional VBA functionalities and HTML properties that were not covered in class. To begin with, the county website presents the results of its query in an html table. I had to learn how to extract information from an html table. I found, http://www.vbaexpress.com/forum/showthread.php?t=31831, which discusses how to do this and provided some code that I used in the final project. I still had to manipulate this code so that it would work on the county website. Figuring this out took the majority of my time on the project.

For the obtainAddress query, I was able to use the code that was written by Professor Allen in the agent.xlsm file for querying websites. I still had to manipulate the code in order to get it to work for the assessor website.

In total, I would estimate that I spent fifty hours on this project. It was very challenging, but also very rewarding.

Results and Next Steps

The queries work beautifully and I was able to download information for 525 properties. Obtaining the addresses took almost two hours, but because it worked so well, I was able to let it run by itself. If I had $300 to purchase Google Earth Pro, the next step would be to import the excel file into Google Earth Pro to see the location of each property within Salt Lake County.

Additional functionality, which I may add in the future, could include removing all parcels with no address from the file and cleaning up the address lines for street types that do not appear in the correct column. Additional query information could also be gathered from the assessor’s website including obtaining land record, residence record, tax valuation, adjoining values, neighborhood values, and parcel characteristic information.

Here are the links for my files:

http://files.gove.net/shares/files/12w/ajh99/Final_Project_Write-up.pdf

http://files.gove.net/shares/files/12w/ajh99/Final_Project.xlsm