Thursday, January 17, 2008

The Weekly Screen Scrape - How its Done

Wow, I can't believe it. There are 14 homes over 2000sqft and under $400,000 in Folsom and El Dorado Hills now (okay 4 are pending sale, but that's still 10 homes).

As promised, below is a description of the process....I'm sure it's way more detail than desired....and a firsthand look at how crazy obsessive I am.

Each week, around Thursday, I enter my criteria into I actually have to do two queries since I am looking at two different counties. I use Metrolist, because they show homes that are Pending Sale (a key leading indicator if you ask me). I also use my true price range, not my browsing range (I browse higher to see what is in the pipeline =)

I copy the tables, and paste them into a new Excel file. Then reformat listings into single rows (price, address, city, beds, bath, sqft, and status). If you don’t know Excel very well, this could be really difficult.

I open my Excel screen scrape data file in a separate sheet and paste in the results from above. I compare the new week’s listings to the previous week’s listings using the “match” function on the address field. Once I have old and new listings lined up I can tell what the delta is. Homes that were PS but were not in the new list go onto my “escrow” sheet. Homes that just disappeared go onto my “unsold” sheet. And I also have a “stats” sheet to keep track of weekly totals; since my main list gets updated. I also do a comparison of the prices (using the “if” function), to see if there were price changes (increases are more common than you think).

At first it took quite a while to do this, but I now have it down to 20-30 minutes or so, and that’s only because the listings have increased so much. To look for trends you have to keep the same exact search criteria from week to week.

Basically I look at inventory and Pending Sales ratio. When inventory consistently starts going down and Pending Sales steadily going up….to me that’s a sign the market is finding its equilibrium. Note: This may not be a good indicator if you have a lower limit to your price range, because homes may fall off the bottom.

One additional thing to look for, people withdrawing their listings. To me that’s a sign that there is pent up demand to sell (which no one really talks about, although some become rentals).

Nothing fancy….just very tedious….but it’s not that different from my day job =)


rainman said...

When will the 400K become 300K?

a. By summer
b. By end of year
b. Never

Buying Time said...

If the past is any indicator (when I started the screen scrape), last year at this time, I was lucky to find homes under 500k.

I think things have accelerated a bit since then, but as I have always said, I don't think Folsom and El Dorado Hills will fall as hard and fast as the rest of fact they may lag a bit as demand eventually gets siphoned off to other areas that are now more affordable.

Sippn said...

Yes, there was some accerlation 2x this fall while financial markets were in dissarray, but you're looking at a 2.5 month inventory for what you want (2000 sf under $400K).

Increasing foreclosures vs lower interest rates - it could go either way.

Observant One said...

It depends on the area. For Natomas, (formerly) the biggest growth area in Sacramento, things just became a whole lot worse due to the FEMA/Flood matter. And that's not an issue that will go away, ever. Demand will be siphoned from there, not to there.

On the other hand, areas outside of the flood zone just became a whole lot more attractive and will be in more demand as this plays out over time. The winners are the foothill communities and the communities in between Hwy 50 and I-80, such as Fair Oaks, Carmichael and Gold River.

Rates now have dropped to the point where all the hullabaloo about ARMs isn't going to matter much. LIBOR, which many of the ARMS are tied to, has dropped below 4%. Even for people who don't refi or get a loan mod, the adjustment isn't going to be a death knell.

Anonymous said...

The formatting on that data source is horrible. Do you really edit each line manually to get the information you use? I'm talking about the copy/paste into excel - it is full of mashed data, multiple records per line, pictures and hyperlinks. Yuck!

sacramentia said...

I wouldn't expect much more or a drop out of Edh:

- 110k median income
- migration from elk grove bad schools to edy.
- smaller migration from folsom upgrades.
- new town center which adds to the attractiveness of the area.
- highest concentration of tech jobs in sac msa - tech is doing ok.
- interest rates are falling.

Husmanen said...

Thanks for the detail and insight. I do something similar regarding the screen scrape but I am not as consistent. I look at MLS, get auto-email from Zip and a few agents, and check out Help-U-Sell and Craigslist as often as I can (at least 5 times a week). When I find a home that is of interest I bounce it off my initial criteria which is saved in an Excel workbook, on a worksheet called, of all things, “criteria”. I then take a look at it on Zillow to check for power lines, major roads, future development potential etc.

If the home still passes the test I put it on my short list of “homes of interest”, then I check my worksheet titled “rentals of interest” to get a good feel for what a comparable home rents for. BTW, my “rentals of interest” list is ever growing and derived from Craiglist and a handful rental house rental sites. Then I do a drive by and check out the home (many homes have staged pictures on the MLS but are actually vacant). Many homes are also fixers and I have another worksheet with estimated costs, some are must do now (e.g. roof, HVAC etc) and others can be phased in (e.g. landscaping, tile replacement etc).

If the home still passes the test, I then bounce some numbers off an “investment” check using the Gross Rent Multiplier and Net Operating Income rules of thumb, comparing a potential price to a potential bid. NOTE, not a specuvestor, minimum requirement is zero cash flow.

Probably the most important part of my work is the non technical/non analytical portion. Discussions with my wife. She always brings in elements that I had not considered, some houses have fallen others not. Those that are the fallen are not forgotten, they go into the closed category, but not forgotten.

Buying Time said...

Anon - you underestimate my Excel prowess. It takes me longer to cut and paste from metrolist than to reformat (but that is why I use a new file).

Sactia - Funny..I don't see it that way...last I checked EDH had almost 12 months inventory. A big part of the problem around here is that most of the homes require Jumbo Loans.

G Spot1 said...

BT, thanks for the post. I'm a little sorry I asked...Excel is not my strong suit....

You are right about jumbo - I'm watching Roseville/Granite Bay and things really crashed up here in August and September. I'm also starting to see increased default/foreclosure activity in the newer neighborhoods. Option ARMs finally catching up with people, although I think this is going to be a long process.

Oz said...

EDH inventory seems relatively low and is lower than it was at this time last year...can't say that about many places in the Sacto area, lol.

What are you using as the absorption rate? Saying that it's whatever months of inventory doesn't mean a whole lot with the number of sales recently being at record lows as they now are. How many months of inventory exist at a normal rate of absorption, or is that what you were using?

I like EDH, even more so now that the high ground is reiterated as being key.

Sippn said...

Blah , blah, blah you bunch of bean counters (I was one!)

husmanen had it right - the "non technical discussions" with his wife

Actually, you do enough analysis and your "gut feel" does start kicking in.

There are thousands of minute variables about a house....a home that cannot easily be put into a spreadsheet. You have to get out there and "feel" it.

Hey, BT - do I need to provide any supporting data on the "high ground" :)

Sippn said...

Reminds me, I sent a friend to see a 4000 sf home for about $600K - a screaming deal in the place it was. I said, "its a total rehab/expansion/divorce thats a few thousand short of being done. Worth over $1 mil complete." (per the pictures I saw)

She said "yes I walked it on an open house, the seller moved a wall into the master bedroom when building the bath, and the room is substandard - ruined the home.

The numbers are only a place to start. Got to visit - rarely is it too good to be true.

Oh yeah, and use professionals!

Buying Time said...

Sippn - Sometimes I wonder if I should be offended that you doubt my capabilities so much. I use the screen scrape to figure out when to start seriously looking (like buy within a couple months). It is not part of my actual home search.

OZ - You are relatively new, so I will try and cut you some slack. But if you stick around, you need to start paying better attention to what is going on as opposed to the "I love EDH" speach (we all do...that's why we are here). I post month's inventory every month. And the figures are actually low cause they only account for MLS inventory.

Buying Time said...

And yes...I have been very grouchy lately.

Sippn said...

No don't doubt your abilities, in fact I'm quite impressed.

The more experience and knowledge I get, the more I consult with others.

But if you loose your sense of humor, I might have to stop poking fun at you. I did just point out a mistake I made.

Taking the teens to the slopes this am, will wave.

Cmyst said...

EDH is ok, and I suspect that I would like it a lot more were I 20 years younger OR older. Despite my evil smugness, I do realize that it is much safer and cleaner up here.
However, and I'm just blindly accepting Mentia's stats, that still indicates that the MEDIAN house in EDH is overpriced for the MEDIAN income. Our household made just under 100k this year and about 108K last year, so that puts us pretty close to that median income.
IMHO, this is the bottom margin of moving into "professional working class" income, which means that you feel you're making enough and have enough potential future earning power to get in some serious debt trying to impress people. Toss in the expenses associated with raising a couple of kids, and that 110K median is looking kind of skimp if you're trying to live that EDH lifestyle.
To be fair, if I had kids, I'd prefer an environment like this IF I could afford it. It would allow me to get out of the house with them, or send them out on their own with friends, and feel pretty safe and secure.
But I honestly don't see how any household that contains children could afford these prices unless their income is closer to 200K. I base that on my own income, not having any kids at home, and having only one car payment on a small economy "entry level" car and NO other consumer debt, RENTING (therefore paying about 1/3 less)a 30 year old house in Governor Village.

Oz said...

Hey, don't cut me any slack. Reading some comments in one of the other threads, you don't have as good a handle on things as you think you do. Lambasting someone for "data" on whether the hills are more desired than flatlands in the Sacramento area? Are you kidding?? That right there told me you're not all that and a bag of chips!

Now, I'll say it again. What are you using for your absorption rate, hmm?

Buying Time said...

OZ - I compare active SFH resale listings in the MLS via ZipRealty with DQs via SacBee resale SFHs. This underestimates the ratio, since DQ includes all sales, while the MLS only accounts for about 2/3 of listing/sales based on previous analysis. DQ also overinflates the sales since they seem to include title transfers back to the bank.

As for the Hills issue...I was getting to that...I was planning to do a separate post on it as opposed to a lengthy comment.

sacramentia said...

cmyst - since you keep throwing your income out there I'm going to comment on it. What are you doing with all that money !?! With your income you can afford a home in el dorado hills, you just choose to spend it in other ways. Maybe you have a really low tolerance for debt/risk - I don't know...but 35% of 100k is about 3000/mo that could go towards a home. With 20% down that leaves you with about a 500k home - which is a nice house. And most of it is tax deductable.

So it is like saying - if I give you free rent, can you live off of 65k per year? What am I missing?

Cmyst said...

It's my understanding that "median household income" means "gross median household income", and of course my net income is much less than 100K.

A house is one facet of a lifestyle, and a 500K house is setting the bar kind of high on expectations. So, yeah, I guess I choose not to give up everything else just so I can say I own a house.
"500k home - which is a nice house. And most of it is tax deductable."
You're kidding, right?
Plus, while I have a great deal of admiration for BT and Gwynster for having saved sizable down payments, I don't have one. If I DID, I'd be even less likely to buy now, 'cause it would really sting to see that money dissolve away. I think there are more people like me, who don't have 20% down payments, than there are people who do have them.

sacramentia said...

"500k home - which is a nice house. And most of it is tax deduct able."
You're kidding, right?

Which part you referring too - the nice house or tax deductible comment?