Wednesday, February 20, 2013

Mining the First 3.5 Million California Unclaimed Property Records

As I mentioned in my previous article the state of California has over $6 billion in assets listed in its unclaimed property databaseThe search interface that California provides is really too simplistic for this type of search, as misspelled names and addresses are both common and no doubt responsible for some of these assets going unclaimed. There is an alternative, however - scrape the entire database and mine it at your leisure using any tools you want.

Here's a basic little scraper written in Ruby.

It's a slow process, but I've managed to pull about 10% of the full database in the past 24 hours (3.5 million out of about 36 million).

What does the distribution of these unclaimed assets look like? 

Among those with non-zero cash reported amounts:
  • Total value - $511 million
  • Median value - $15
  • Mean value - $157
  • 90th percentile - $182
  • 95th percentile - $398
  • 98th percentile - $1,000
  • 99th percentile - $1,937
  • 99.9th percentile - $14,203
  • 99.99th percentile - $96,478
Visually, it looks like this:

  • 548309 have value >= $100
  • 67452 have value >= $1,000
  • 4954 have value >= $10,000
  • 304 have have value >= $100,000
  • 4 have value >= $1,000,000
  • The largest value was $8,050,000
The top 10 by value:
  1. $8,050,000 to Jasmine Holdco. Entered as Hold Co. Sent CEO Alexander Slusky a note on LinkedIn.
  2. $2,183,062 to someone associated with Procket Networks. Procket was bought by Cisco. Sent former Procket CFO Curtis Mason a note on LinkedIn.
  3. $1,669,561 to Wyle Systems. Address and city misspelled. Sent email to Wyle, was told this money belongs to Wyle Electronics, which in turn was purchased by Arrow Electronics.
  4. $1,419,929 to Anne Baronia. Appears to have moved. Sent her notes on Facebook and LinkedIn.
  5. $777,856 to Citi Residential Lending. Premium refund.
  6. $639,000 to Martin Peter Wright. Funds for liquidation.
  7. $611,460 to Jeanne and Melvin Hing. Mature CD or savings certificate.
  8. $520,761 to Loretta Nisewander. Checking account.
  9. $507,077 to Payroll (no address). Now you know what to name your next kid. "This here's my son Payroll, and this one's my daughter Unknown".
  10. $450,000 Joseph Mallon. Funds for liquidation.
People who should be easy to contact:
  1. $58,946 to Ehren Maedge. COO of Scale Computing, Foundation Capital.
  2. $408,105 and $94,001 to Dane Prenovitz. Director of the Dani Investment Collection, appears to go by DJ Dane Mitchell now.
  3. $322,826 to the Hearts Afire Foundation.
  4. $160,825 to the Vernon Otto Wahrenbrock Trust. Wahrenbrock's was the biggest used bookstore here in San Diego - it was very sad when Vernon passed away and Wahrenbrock's closed. His grandson, Craig Maxwell, owns a bookstore in La Mesa.
  5. $10 to OJ Simpson from NetZero.

Monday, February 18, 2013

Sergey Brin, Please Pick up your Paychecks

The state of California is currently holding over $6 billion in unclaimed property belonging to millions of people. What type of property and who are the rightful owners? According to California's official unclaimed property website, these assets fall into the following categories:
  1. Bank accounts and safe deposit box contents
  2. Stocks, mutual funds, bonds, and dividends
  3. Uncashed cashier's checks or money orders
  4. Certificates of deposit
  5. Matured or terminated insurance policies
  6. Estates
  7. Mineral interests and royalty payments, trust funds, and escrow accounts
People forget, people die, people move around. But $6 billion is a staggering amount of money; some of these amounts have to be really large. Let's try to find some interesting examples.

This is official California UCP search form. Programmer and database types will notice one problem immediately - no fuzzy string matching. If your name or address was misspelled on the assets, or munged in the recording process, tracking down any assets belonging to you could become a difficult to impossible process. Given that this database has at most 18 million rows, there's no excuse for such a basic (and important) feature to be missing.

Leaving aside the flaws and the apparent lack of due diligence on the part of the state of California, let's try to find some interesting examples of owed property.

Strategy - celebrities and the super wealthy with either unusual names and/or known cities (through Forbes or others).
On a personal note, I've found about $40K for friends plus businesses they own (over $20K in one case).