Category Archives: Multiple Listing Service

Easy Fix for Rappatoni Data Export Issue

4 Replies

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.

Multiple Listing Service Reporting of Private Sales from an Appraisal Perspective

Leave a reply

 

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?