Category Archives: Excel

Quiet, too quiet, and upcoming speaking engagements

I had a problem earlier this week with this website reverting to a 2016 version but was able to quickly fix with the help of the fine folks at my host, SiteGround. When reviewing the issue, I noticed that my last post was last summer. Yikes! Here’s the first effort to be more timely with my writing.

The Northern California Residential Appraiser Conference

Friday, May 3 (tomorrow), I am speaking at the Northern California Residential Appraisal Conference in Fairfield, California. My topic is a brief introduction to short-term rental (STR) properties with a focus on the issue of what to do when a lender asks for an appraiser to report “rental income” for a STR on the 1007 form.

Denis DeSaix, Penny Woods and I came together to produce the first joint conference between the Real Estate Appraisers Association (REAA) and the Northern California Chapter of the Appraisal Institute (NorCal AI) in 2018. Our goals were to bring the excellent teachers from NorCal AI to REAA residential appraisers and to build connections between the two organizations. Tomorrow is the fifth annual conference (Covid canceled the 2020 event) and we have a great group of speakers.

More information here

Other upcoming classes

The California Probate Referees Association has invited me to speak at their conference on Monday, May 20. I plan to discuss market change adjustments and contrasting, two quick and easy tools every valuation professional should use. This event is not open to the public but if you’re a probate referee in California, I hope to see you there.

Tuesday, June 4 I am teaching a time adjustments/market change adjustments class via Zoom for REAA. I will discuss time adjustments in detail here in a series of posts over the next several weeks.

Click here for more information or to register

What I’ve been doing instead of updating my website

I immensely enjoyed my trip to Palm Springs for the Community of Asset Analysts meeting in January. It was great to see in person many of my valuation friends. Below are some photos from the trip.

From upper left clockwise: George Dell speaking at the Community of Asset Analysts meeting; my fellow asset analysts; Brad Bassi in real life!; first time in Palm Springs; Joshua Tree NP was amazing; Yosemite Valley in the snow on the way home

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.