Are you overwhelmed with all the deals and liens available in the market?
I know that I was when I started looking at deals and lien lists.
The sheer amount of data can be daunting.
I came up with a quick way to filter data to help decide if any of the deals/liens are investable!
I am going to write this article from the perspective of analyzing tax lien lists but the steps and tricks are applicable to filtering any major data set.
Tax lien auctions are a time sensitive and intense process given that some counties and municipalities having lists of liens available are in the thousands or tens of thousands of liens available. You need a quick way to analyze these liens and filter down to manageable investment data set.
Here are three easy steps to help you wade through a list of thousands of liens:
Step 1: Know Your Investment Blocks/PIN (Parcel Identification Number)
I have a master list of Blocks within key investment markets that interest me for lien investing. I utilize this list and the Sort Function in Excel to narrow down the list to only include the blocks that are within my investment zones.
A similar approach to this idea is something that Jerry K. of Aristo Associates, Inc. does for his online tax lien investing business model that I ran across in my readings and I will use his analysis as my example template for this article given that he has done a great job showing the use of the Auto Filter tool:
“Jerry goes to the the county GIS map website and determine the PIN (Parcel Identification Number) for the areas he likes. That creates a PIN list that he ranks in the order of his investment preference.”
Step 2: Inputting Data and Applying the Automatic Filter Function
Input all of your data, or open the spreadsheet that contains all of your data. It is best that your data have column heading such as categories to specify the data below it.
Select all the data you wish to filter because the AutoFilter option is an automatic process that does not receive any specific inputs on how to filter, it is recommended that you choose ‘’all’’ of the data you have. This will avoid the possibility that you lose data associations across rows and/or columns.
Click “Data”, then select “Filter”.
So going back to Jerry’s analysis example:
“By applying the Filters to each column at the top and then clicking on the filter icon (red arrow at top of column) the spreadsheet then opens a pop up to allow how you want to filter your list:”
Step 3: Filtering the Data
Once you click the Filter button you will notice that the categories have drop-down buttons. Using these buttons, you can set your filter options.
- Sort Ascending: sorts data in ascending order based on the data in that column
- Sort Descending: sorts data in descending order based on the data in that column; numbers are sorted in reverse order 5, 4, 3, 2, 1, etc. and words are sorted in reverse alphabetical order, e, d, c, b, a, etc.
- Custom: You may customize how Excel sorts the data based on data ranges and information.
So going back to see how Jerry completed his sort analysis.
“Jerry uses “Text Filters” (in yellow highlight) which then opens the next box and he choose “Begins With… (red arrow on right side above).This opens another box
wherein he fills in the beginning of the PIN number so that the spreadsheet filters the list that only contain the PIN that begin with the number for the area he likes:
That results in a much narrower list that he can start to analyze:”
I do pretty much the same thing as Jerry except I use Block data instead of PIN. Jerry is fellow Biggerpockets member as well and I want to thank him for sharing this information.