Category Archives: Real Estate Data

Easy Fix for Rappatoni Data Export Issue

Update: Well, this helps but doesn’t completely solve the problem. Tab characters still stay in the fields not converted to numbers or dates. If this doesn’t work, I’ve added a couple of steps that will work involving using a robust text editor, Notepad++.

Oh no! What broken my 1004MC calculator?

A recent Rappatoni MLS update broke data export. It appears that a hidden tab character is placed between fields as a delimiter, changing numeric fields into character fields. This causes issues when you try to use a workbook like Don Machholz’s 1004MC because the 1004MC workbook relies on the data types to be correct. The calculator chokes whenever it finds text in a numeric field like list price (or sale price, or DOM, etc.). This only affects Rappatoni system MLSes. Metrolist was not affected (yet) because they run their own code and do not appear to have run the update that broke stuff.

Here’s a quick way to know if your MLS has been affected. Take a look at the Selling Price column. If it lines up on the left, your MLS has the issue. If it lines up on the right, you’re good to go.

Here’s an example with the problem:

Here’s how it should look:

Here’s an easy workaround for the latest version of Excel.

  • Download your data out of MLS and save it to a csv file.
  • Open up an Excel workbook.
  • Click on the cell you want the downloaded data to appear in.
  • Click on the Data Tab and select From Text/CSV
  • Select your csv file containing the data you need and hit the Import button
  • Excel will open a data import wizard. This should show your data with the correct data formats and aligned correctly (numbers aligned to the right, text to the left, dates looking like dates). Click the Transform Data button and you should be good to go.
Excel 365 Data Importer screen

Older versions may require more work to find the data import wizard. This link shows the process using a slightly older version of Excel. Brings me back to my days as an EDI analyst….

I’m learning from others that this fix doesn’t work universally. If you see this in your Excel file, you still have problems.

Here are additional steps that you can do to fix it. The simple way is to place your cursor in the edit bar to the immediate left of the data you want to keep, hit <Backspace>, then enter. This deletes the hidden Tab character but needs to be repeated throughout the file. Another option is to simply not copy text fields into the 1004MC since they are not required to run the calculator.

If you’re interested in a better solution, download a robust text editor like Notepad++ (link). Use Notepad++ to open the csv file.

csv file opened with Notepad++

Go to View, Show Symbol, Show All Characters

Notepad++ will then show the Tab characters

Select one of the now visible tab characters and type ctl + c to copy the Tab character. In Notepad++, select Search then Replace

Click on the Find what: box and paste the Tab you selected previously. Leave the Replace with: box blank. Click on the Replace All button and then save changes.

Now you should be good to go.

(There are many ways to skin a cat. Another way would be to import your files into an Access database, delete the tabs, then output as an Excel file. If you know how to do this, you don’t need my instructions)

Good luck and contact me directly at joemlynch2112@gmail.com with questions. Hopefully Rappatoni fixes this soon.

Early Pandemic Effects on Davis and Woodland, California Residential Real Estate

Monthly year-over-year change in homes sold, mean sale price, and mean psf for homes sold in Davis, CA

As I type this, the 100,000th person in the US has died from the novel coronavirus 2019. The country has shut down to bend the curve. Shelter in place started in Yolo County on March 19, forcing most people to stay home. Unemployment exploded nationwide, going from 3% to nearly 20% in a month while mortgage forbearance levels jumped to 2008 levels. How has the economic crash affected residential real estate in Davis and Woodland?

Shelter in place stopped in-person interior inspections for buyers and real estate agents while the stock market crash and jump in unemployment shook consumer confidence. However, interest rates dropped into the low 3s, increasing affordability.

Davis monthly sales 2019 to 2020

The graph above shows all single family residences sold in Davis by month for 2019 and 2020. Year-over-year change in January, February, and March was somewhat negative, indicative of a slowing market. April 2020 was lower still and probably represents sales that went into contract before or at the beginning of the shut down. May sales are probably the first period to reflect the post-shut down period. I was so surprised at how few sales in May to date that I ran the search multiple times to make sure I wasn’t making a mistake….

The Davis residential market ground to a halt. Current listing volume is still low but homes in contract is starting to recover (35 in contract in May 2020 to date compared to 41 in 2019). Prices have held up surprisingly well on a year-over-year, price per square foot basis as shown below.

Davis prices have held up surprisingly well

After a period of decline in the fall, prices shifted to stable to slightly increasing. Early days and less reliable than typical because of the sales volume decline.

Woodland, in contrast, was poised for a strong 2020 before the pandemic. Sales volume was up 38% in January and 25% in February from 2019. March sales this year slowed to the 2019 rate and declined steeply in April and May to date.

Woodland sales stated 2020 strong but declined as the pandemic hit

Sales volume declined sooner in Woodland but not as steeply as Davis. Once again, prices in Woodland have been relatively stable overall. Homes currently in contract are low and point to continued sales volume decline in at least the short run.

Woodland prices have been relatively stable in 2020

We’ve seen a significant slowdown in activity that has yet to affect prices significantly. Inventory is slightly higher but not yet affecting prices. Historically low interest rates have certainly helped prop up the market. Buyers and agents have adjusted their protocols to stay healthy while shopping for homes.

I’m concerned at the trickle of sales in Davis. Davis is a really hard place to value properties because of differences in location and the high degree of seasonality from the university. Reduce sales volume significantly and sales comparison is going to be difficult. Here’s hoping my Davis Realtor friends have a busy quarter…..

Add Histograms to Your Appraisal Tool Kit

It was my pleasure to speak at the Sacramento chapter of the Real Estate Appraisers Association last night at the Story of Value class with my good friend Ryan Lundquist. I discussed ways to explain markets in residential real estate appraisals and focused on using graphs and was surprised to see that maybe half of the crowd didn’t include graphs in their reports. This post is the first to offer advice and instructions on how to create meaningful graphs for residential appraisers.

Gross Living Area Histogram

Four appraisers out of 50 in the room reported using histograms. The histogram is a great tool for analyzing residential real estate markets that all appraisers should use.

What is a histogram?

For our needs, a histogram is a graph that shows the distribution of one continuous variable. The histogram splits the variable into equal-sized bins and counts the number of occurrences. It works well for important residential real estate variables like gross living area, lot size, age, and sale price.

Bin size is key to creating a useful histogram. Bins too wide loses meaning as your data is clumped together. Bins too narrow spreads the histogram out too much.

The graph above shows the sales of homes in a market area with homes of certain sizes. There is one sale less than 1000 sf and one more than 3500 sf. The most frequent size of home sold recently is around 2000 sf with the bulk of the homes in the 1400 sf to 2000 sf range.

Every report prepared for a lender asks the question “Is the subject conforming?” At a glance, any home sold in this neighborhood with between 1300 sf and 2600 sf is reasonably conforming in size. There are no sales in the 4000 sf to 5000 sf range so any homes in the neighborhood of that size are likely non-conforming. The two extreme sales, at 800 sf and 5000 sf, are unusual and likely non-conforming.

Sale Price Histogram

This next histogram examines the frequency of sale price in the market area. The most frequent sale price is in the $240,000 to $280,000 range with $360,000 to $400,000 the second most frequent range. A home in contract at $375,000 is fairly typical. A contract price of $700,000 is very unusual and is indicative of a non-conforming home.

The first two graphs were generated using ggplot2 in RStudio. Here’s an example from Excel 365 showing the year built for homes of sales in a market:

Year Built Histogram

Most homes sold in this market area (Placerville, CA small residential acreage) were built in 1970s and 1980s. A couple of homes were built in the 1800s and there are a couple of newly built homes.

The lender forms used by appraisers ask for similar information in a table format:

Table or Histogram, which is better?

Which describes the market better, the two histograms or the table?

Make A Histogram in Excel

Here’s how to use the latest version of Excel to make a histogram. This page has instructions for the latest version and older versions.

Start with your data in an Excel workbook with the top row field names and rows below sales data.

Starting with the field name, select the data to generate the histogram (ctl + shift + down arrow will select all consecutive data down)

Press F11 to insert a graph. Then chose Change Chart Type

Select Histogram then press Ok

You’ve made your first histogram!

First Histogram

However, it’s really ugly. Standard formatting for histograms is to have no space between the bins (columns). To fix that, double left click on one of the bins to activate the Data Series editor. Select the bars to active Series Option

Change the Gap Width to 0%. Notice how the columns come together. If you like having gaps between the columns, set the Gap Width to 6%.

First Not Ugly Histogram

To change the bin width, double left click on the x-axis labels (GLA in this case). Using the Format Axis Axis options, select the Bin Width control and type in what you want. Play with it until you’re happy with the shape of your histogram.

Default Bin Width

Excel defaulted to a bin width of 370 sf. Below is what the histogram looks like with bin width equal to 100 sf:

100 sf Bin Width

Here’s bin width equal to 500:

500 sf Bin Width

Here’s bin width equal to 200:

200 sf Bin Width

Which one appears most useful to you?

Context

I use histograms to understand some aspect of a market. How big are the homes? When were the homes built? How big are the lots in the neighborhood? What do homes sell for in the market area?

Then consider where the subject fits in the market. Is it bigger than typical? If so, you have support for concluding market value is higher than typical. Is it smaller? Well, now you can show a reason why the price is lower.

The Subject’s Position in a Market Area

Let’s consider the histogram above. The subject is one of the larger homes in the neighborhood but still relatively common in size. I would expect, without knowing anything else, that the subject’s market value is on the higher side for the neighborhood but with a reasonable number of homes larger than the subject. Take a look at the graph below.

The Subject is one of the bigger homes….

What if the subject was one of the largest homes in the neighborhood? The subject’s market value is likely on the upper end of the neighborhood range. Also, there are fewer directly competitive sales, implying market value may be less reliable in this market area than for a smaller home. Now let’s look at an extreme case.

Charge big bucks if you get this subject

I pity the appraiser asked to appraise a 6500 sf house in this market. However, you do have sales either smaller or larger. Here’s the time to really open your eyes to what is a competitive sale. Throw this graph in your report and your client will immediately see your data difficulty.

After you arrive at market value and as part of your reconciliation, consider using a histogram to support your market value.

Support for your market value conclusion

“The subject is newer than typical, above average quality custom home on a larger than typical lot. As shown above, the subject’s market value is on the higher side for the greater market area, as expected based on its superior characteristics.”

I hope you agree that histograms can be a powerful tool for appraisers.

Ways to use histograms:

  • Exploratory analysis to understand characteristics of a market area
  • Assist in determining reasonable search criteria for sales comparison
  • Visual representation of the subject’s position in a market area
  • Support for market value conclusions

I learned about histograms from George Dell. Thanks George. Get smart by taking his Stats, Graphs, and Data Science classes or at the very least, sign up for his blog. More info on George’s website.

Postscript: I am working towards moving away from Excel to using R, the data analysis package. I’ll link to the R code used for the two graphs as a separate post/update soon.

What’s A Comp And Why Should You Care?

Sample Comparable Search in Woodland, CA

Two recent posts from my friend Jamie Owen at the Cleveland Appraisal Blog plus a planned realtor office visit inspired me to write this. Jamie did a great job blowing up the myth that comparable sales need to be within one mile of the subject in this post. He also tackled geographical competency, or the need to have boots on the ground knowledge about a market in order to credibly value properties in a second post.

Both posts touch on the subject of what is a comparable sale and why should anyone in real estate, or even the general public, care? The quick answer is that “comps” are the basis for how we, both those in the real estate industry and the man on the street, value residential real estate.

Per the Dictionary of Real Estate Appraisal, 3rd Edition, comparables are:

…similar property sales, rentals, or operating expenses used for comparison in the valuation process; also called comps.

Comps are used in the Sales Comparison Approach to Value, especially in residential real estate appraisal. All of us, appraisers, real estate agents, and folks considering buying a home, use the theory of substitution to determine the value of a home. What would the typical buyer shopping in that neighborhood buy instead of the subject?

A comparable sale is a sale of a home that the typical buyer of the subject would buy instead of the subject.

Subconsciously, everyone who owns a home compares it to homes in their neighborhood. We learn about a recent sale on our block and place a price on ours based on whether we think it’s better than ours, relatively similar, or inferior.  The formal version of this is the sales comparison approach used by appraisers.

We appraisers find the most similar sales, adjust the comparables for differences from the subject, leaving each adjusted comparable sale an indicator of value for the subject. The vast majority of single family residential appraisals in the US rely upon this methodology.

In the context of the sales comparison approach to value, the key is to identify the comps for the subject.

The easiest way to get the value of a single family residence wrong is to get the comps wrong!

As my mentor George Dell says, “What does similar mean?
(Now go subscribe to his blog. He’s really smart. Then take his classes)

Residential real estate, such as a house, a condominium, a home on a small acre lot outside of town, etc., have characteristics (“dimensions”) that serve as descriptions of a specific sale for a specific property. The more similarities between a sale and the subject under consideration, the better a comp. We can go into a deep dive, like George does in his classes; instead, I want to talk about what I do specifically for simple single family residential work in conforming neighborhoods.

Some examples of dimensions and characteristics important to valuing homes include transaction terms (financing, credits, etc.), motivations, location, views, quality, design, condition/age, floor area, and amenities.

Some dimensions/characteristics are more important than others and can vary dramatically in importance depending upon the location. For example, pools are valuable in the Sacramento region but have less value in the Pacific Northwest where the weather is cooler. Basements are common in the Midwest and East Coast but not so here. In the Whisper Creek Subdivision in Arbuckle, CA, a tract of large homes on half acre lots, RV parking is a significant factor unlike other nearby markets. This is why the geographical competency that Jamie discusses is so important. Appraisers with geographical competency understand what characteristics define a true comparable and get the subject’s value right.

Time usually matters except when it doesn’t. If a market is rapidly changing, using the most recent sales can reduce the impact of market change. When a market is relatively stable, time is less important and so using older comparables is reasonable. I downplay time frequently because time is usually the easiest and most reliable adjustment to make.

For a typical tract home in my area, the most important factors are motivations for the purchase or sale, time, location/proximity, and size/floor area. I start with a map search using my neighborhood boundaries and go back 12 months prior to the date of value for closed sales. I exclude from consideration REO sales, short sales, and other transactions where motivations likely had an impact on sale price.

I search for homes a little smaller than the subject because most buyers can make do with a slightly smaller home. Because the typical buyer can accept a larger home than the subject, I set the upper boundary on my floor area range wider than the lower bound. For example, if the subject has 2300 sf of living space, I will search for comparables with 2000 sf to 2800 sf of living space (300 sf smaller to 500 sf larger).

After I set my criteria in the MLS search, I run the search and review the results.

Metrolist Search Results
Search Results

I mentally draw a box around the subject’s important characteristics so I can place it in the competitive market. This is known as bracketing. Reasonably, would the typical buyer consider the sales found suitable substitutes for the subject? Are the sales similar in quality and design? Are there differences in lot size or age? Do I have larger and smaller homes? Do I have homes in similar condition, or inferior and superior? I try to account for every significant characteristic of the subject so I can show, by comparison, the value of the subject by using these comparables.

If I’m comfortable with the sales found, I can start my adjustments analysis. If not, I revise my search criteria and run the search again until I am happy that the sales found reasonably describe the subject.

Once I have my initial candidate comparable sales identified, I dig in and look for most representative comparables of the subject and decide on which sales to research further (view the exterior, contact agents involved in the transaction, etc.). I review outliers, sales outside the normal range, and try to determine why the sales deviate from the norm. I either adjust for the issue or remove the outlier from consideration. The remaining comps, after adjustment, are my indicators of value for the subject.

Comps are usually easy to find in conforming neighborhoods as long as the subject is similar to the rest of the neighborhood. When the subject is unusual, or when there are few sales available and they are all different (“non-conforming”), comparable selection is difficult. The appraisal becomes complex and beyond the scope of this article. I do have tips in my article about appraising complex residential properties.

How do you search for comparables? What are some tips for a real estate agent or new appraiser you can share?

Multiple Listing Service Reporting of Private Sales from an Appraisal Perspective

 

Today I was asked to comment on the issue of reporting private sales to my local multiple listing service (MLS) by a friend who works for Metrolist, the MLS for the Sacramento region. Today, Metrolist and most other local MLS systems do not allow for sales not sold through the listing service to be included in the sale databases maintained by these organizations. There’s a push within the residential real estate community to include this data. Here’s my response for why, from an appraiser’s point of view, I think it’s a bad idea:

As appraisers, data is our lives. We want available as much data as possible to help us value properties. By rule, we’re required to consider all competitive sales when valuing a property. The vast majority of assignments are for some version of market value. Here’s FNMA’s definition of market value:

“Market value is the most probable price that a property should bring in a competitive and open market under all conditions requisite to a fair sale, the buyer and seller, each acting prudently, knowledgeably and assuming the price is not affected by undue stimulus. Implicit in this definition is the consummation of a sale as of a specified date and the passing of title from seller to buyer under conditions whereby:

  • buyer and seller are typically motivated;
  • both parties are well informed or well advised, and each acting in what he or she considers his/her own best interest;
  • a reasonable time is allowed for exposure in the open market;
  • payment is made in terms of cash in U.S. dollars or in terms of financial arrangements comparable thereto; and
  • the price represents the normal consideration for the property sold unaffected by special or creative financing or sales concessions granted by anyone associated with the sale.” (FNMA Selling Guide, Section B4-1.1-01)

This definition requires us appraisers to confirm some information regarding every sale used as a comparable in our reports. We must analyze each comparable we use in the sales comparison approach, the primary method for determining the market value of single family residential homes in the US. We must understand that both buyers and sellers do not have unusual motivations and that the comparable sale was properly exposed to the market so that all interested parties could bid on the comparable sale. The most widely used marketplaces in most of California are the various multiple listing services. Exposure on the local multiple listing service gives the widest viewing to potential buyers and allows for market mechanisms to arrive at the market value for any given home. Without this exposure, there is significant uncertainty whether the agreed-to price is market value or something else.

In addition to the value of having a central marketplace with mechanisms to arrive at a market value, the multiple listing services serve as a central repository of data. Most of the time, we can look at one central database and see all relevant property characteristics and data. Additionally, we have record of listing agents and buyer representatives who we are required to contact as part of due diligence. Some of the markets we cover have a significant percentage of sales not reported to the local multiple listing service. In general, we do not use these transactions in our appraisals because of the uncertainty of whether they sold at market value or not. For example, the for sale by owner that puts a sign up on his lawn may attract offers from people driving by but most likely he missed all potential buyers and may have sold his home too low. The “pocket listing” of one agent only marketed to agents in his office misses a huge pool of potential buyers. As appraisers, we can’t rely on these sales as primary data-we just don’t know if the sale price was market-derived.

I have worked extensively in Solano County over the past 15+ years. BAREIS, the multiple listing service for this area, has accepted sales data not sold through the MLS and reported it as “Sold Off MLS.” In the handful of years since this data has been offered, I have used it once in approximately 300 appraisals in Solano County. The sale used was included as secondary evidence for a very difficult assignment because this sale was not clearly a market value transaction. In more than 95% of assignments, I do not bother to check the “Sold Off MLS” sales. Even when similar sales are very difficult to find, the “Sold Off MLS” sales are not very helpful.

Does your local MLS system allow for agents to enter non-MLS sales into the database? Is this good or bad in your opinion? Why or why not?