This is the analysis we did for a story on the expedited Board of Revisions foreclosure process in Ohio.

This specifically walks you through how we looked at the data in Cuyahoga County. For transparency purposes, and so everyone can reproduce our results, the files we FOIA’d will also be posted on Github. Information here is also available online on the County Auditor and County Clerk’s websites.

Through a public records act request, we received from the Cuyahoga County Auditor every transfer in the past several years:

TransferHistory <- rio::import("Cuyahoga_TRANSFER_HISTORY_ENTIRE_COUNTY.xlsx") # Warning, after you start this, you whould probably go to lunch. This takes a ridiculously long time to load. 

As this is a ridiculously big file, the county history all the way back to 1975, we pare it down to the last ten years. (Eleven by the time we finished this!)

Verifying Felter’s info

Feltner_Info <- filter(Transfer_History_Post_April09, str_detect(GRANTEE1, "Feltner|FELTNER") & str_detect(GRANTOR1, "Feltner|FELTNER"))

Examining all land bank transfers.

Land_Bank_Transfers <- Transfer_History_Post_April09 %>% 
  filter(str_detect(GRANTEE1, "land bank|LAND BANK|REUTILIZATION|Reutilization"))

Bringing in Tax and value information

Cuyahoga_Tax <- rio::import("Cuyahoga Tax Info Combined.csv")
Cuyahoga_Value_Info <- read.csv("~/Code/Blue/property_project_ohio/PropertyData/WHS_CAMA_CERT_VALUES.csv")

Looking just at Land Bank Transfers

Land_Bank_Transfers$Market_value = Cuyahoga_Tax$TAX_MARKET_TOTAL[match(Land_Bank_Transfers$PROPERTY_NUMBER, Cuyahoga_Tax$PROPERTY_NUMBER)]

Just the sheriff’s deeds

Land_Bank_Transfers <- Land_Bank_Transfers %>% 
  filter(DEED_TYPE=="Sheriffs Deed Ex", !str_detect(GRANTOR1, "LAND REUTILIZATION"))

We got this information from The Cuyahoga County Clerk of Courts for all Board of Revisions cases from 2018 through 2019.

cuyahoga_court_results <- read.csv("~/Code/Blue/property_project_ohio/PropertyData/cuyahoga_court_results.txt", header=FALSE, sep=";")

Putting our data into the right format: Now we want to get headers and the dollar data to a point that we can add with it.

colnames(cuyahoga_court_results) <- c("Parcel_Number","Case_Number","Filing_Date","Prayer_Amount")
cuyahoga_court_results$Prayer_Amount <- number_with_commas(cuyahoga_court_results$Prayer_Amount)
cuyahoga_court_results$Filing_Date <- as.Date(cuyahoga_court_results$Filing_Date, "%m/%d/%Y")

Data Verification: Making sure each case is unique

Duplicates <- cuyahoga_court_results %>% 
  group_by(Case_Number) %>% 
  summarize(Total=n())

And they are. Yay.

Now we want to match the valuations. Over the past ten years the value of homes has fluctuated wildly, so it’s not accurate to take the current valuation and say that was the valuation in say, 2009. And a good follow up story would be all but that would take forever so we’re concentrating on the past two years.

Cases_2018_and_2019 <- read.csv("~/Code/Blue/property_project_ohio/PropertyData/all_bor_cases_results.txt", header=FALSE, sep=";")

colnames(Cases_2018_and_2019) <- c("Case_Number","Prayer_Amount","Filing_Date","Parcel_Number","Caption")
Cases_2018_and_2019$Prayer_Amount <- number_with_commas(Cases_2018_and_2019$Prayer_Amount)
Cases_2018_and_2019$Filing_Date <- as.Date(Cases_2018_and_2019$Filing_Date, "%m/%d/%Y")

Adding the value from the Auditor’s office

First, taking out the slash which is in the parcel ID, but not the parcel number. But they are otherwise the same.

Cases_2018_and_2019$PARCEL_ID <-gsub("-", "", Cases_2018_and_2019$Parcel_Number)

Data Verification: 10 of these have data errors which are fixed here.

#typos
Cases_2018_and_2019["454","PARCEL_ID"] = "01820101"
Cases_2018_and_2019["3856","PARCEL_ID"] = "13201042"
Cases_2018_and_2019["862","PARCEL_ID"] = "11226008"
Cases_2018_and_2019["1237","PARCEL_ID"] = "73103809C"
Cases_2018_and_2019["1897","PARCEL_ID"] = "13603136"
Cases_2018_and_2019["2344","PARCEL_ID"] = "11107083"
Cases_2018_and_2019["2369","PARCEL_ID"] = "11531142"
Cases_2018_and_2019["3385","PARCEL_ID"] = "73103893C"
Cases_2018_and_2019["3375","PARCEL_ID"] = "13505062"

#Case Dismissed
Cases_2018_and_2019 =Cases_2018_and_2019[-3361,]

Then joining them together:

Cases_2018_and_2019 <-left_join(Cases_2018_and_2019, Cuyahoga_Value_Info, by="PARCEL_ID")
## Warning: Column `PARCEL_ID` joining character vector and factor, coercing
## into character vector

How much tax money is lost to taxpayers?

Cases_2018_and_2019 <- mutate(Cases_2018_and_2019, 
                              Do_Taxes_Exceed_Value=case_when(
  Prayer_Amount > CERT_TOT ~ 1,
  Prayer_Amount < CERT_TOT ~ 0,
  Prayer_Amount == CERT_TOT ~ 0),
  Recoverable_Amount=case_when(
    Prayer_Amount > CERT_TOT ~ CERT_TOT,
  Prayer_Amount < CERT_TOT ~ Prayer_Amount,
  Prayer_Amount == CERT_TOT ~ 0),
  Lost_Equity=CERT_TOT-Recoverable_Amount
  )

How much do homeowners and banks miss out on?

scales::dollar(sum(Cases_2018_and_2019$Lost_Equity))
## [1] "$77,075,749"

How much money do taxpayers lose out on?

scales::dollar(sum(Cases_2018_and_2019$Recoverable_Amount))
## [1] "$9,092,751"

What’s the median amount of taxes owed?

dollar(median(Cases_2018_and_2019$Prayer_Amount))
## [1] "$1,249.10"

In how many cases was the amount asked for less than $500?

Cases_2018_and_2019 %>% 
  filter(Prayer_Amount<500) %>% 
  summarize(Total=n())
##   Total
## 1   855