Real Estate Appraisal Forum

appraisersforum.com logo
The Premiere Online Community for Real Estate Appraisers!
 Fastest Way to Find a Real Estate Appraiser Enter Zip Code:
 
 
Go Back   Appraisers Forum > Real Estate Appraisal Forums > Fannie Mae, Freddie Mac, USPAP
Register Help Our Rules Calendar Archives Mark Forums Read


Closed Thread
 
Thread Tools
  #1  
Old 01-20-2009, 10:03 AM
George Hatch's Avatar
George Hatch George Hatch is offline
 
Join Date: Jan 2002
Location: Carlsbad, California
State: California
Professional Status: Certified General Appraiser
Posts: 15,758
Default 1004MC + Worksheet in Excel (Freebie)

Okay gang, here's another variant of the 1004mc file in Excel. I built a worksheet that will work off of 2 MLS downloads and analyze, auto-calc and transfer the results directly into the grid on the 1004mc form.

Here is the 1004mc + worksheet version:

http://www.box.net/shared/vk6h8fms0e

------------------------------------------

Here is the Holloway version that uses 6 MLS downloads:

http://www.box.net/shared/6gdh2140ak

------------------------------------------

Here is the original version that is limited to just the form:

http://www.box.net/shared/3nx558czgn

-------------------------------------------

I haven't put together instructional PDFs for these yet but I'll try to do so before the end of the week. I reckon some users will probably be able to figure it out for themselves.

These are freebie downloads, intended primarily as a stop-gap measure to hold you guys over until something better comes along. Use at your own risk. Check it out to make sure it works for you before using it in a live assignment; better you identify any problems than to have some reader identify it for you.
Sponsored Links

  #2  
Old 01-20-2009, 10:11 AM
George Hatch's Avatar
George Hatch George Hatch is offline
 
Join Date: Jan 2002
Location: Carlsbad, California
State: California
Professional Status: Certified General Appraiser
Posts: 15,758
Default Comments

There are a couple reasons I broke the worksheet into 2 pages and why it requires 2 data downloads. One of the data downloads will include all listings except the closed sales, the other will consist of only the closed sales. The closed sale download will get loaded into both pages.


One reason I did this is because the listing data analyzed consists of properties listed in the last 12 months, so that requires a specific listing date. OTOH, closed sales going back 12 months will include some listings that were originally listed prior to 12 months ago.

There are obviously some limitations involved, one of them being that the total number of available listings during a time period is not limited to the number that were initiated during that time frame. Another is that by counting all the actives, pendings, expireds and cancelled listings there will inevitably be some properties that get counted more than once. These are holes that we'll (probably) eventually be able to plug, although some users may not be able to use these versions if their MLS downloads have limitations we can't work around.


Nonetheless, it's important for users to understand the limitations of this worksheet or any other analytical tool prior to using it. Personally, I'd recommend that users disclose these limitations in their reports so that they don't get tagged by an overly anal reviewer or underwriter.

------------------------

In order to use the worksheet, you'll need to convert your MLS downloads into an Excel format. For those MLS systems that don't already download in a spreadsheet format there are apparently a number of ways to do it. For example, my MLS system doesn't do a spreadsheet download, per se, but it does do a "tab delimited" format that I can convert to an xls file simply by doing a "save as" and substituting in the xls file extension.

After the download is formatted for Excel, you can use the copy/paste function for each of the columns that are on the worksheet (there are 12). Alternately, if your MLS system allows you to format your own downloads as mine does, you can download it in the same order that the worksheet uses. Another easy fix is to move the columns in the worksheet around to fit the order your MLS system downloads use. Whatever works for you. If you're using multiple MLS systems you might end up using separate versions of this form file for each.

After you paste or merge the data in, the worksheet will do the rest. I've set the worksheet up so it will print both the data and the workings in a landscape oriented legal-sized paper or pdf. That way, you have something you can send out should anyone question your methods. There are some instructions that go along with this, displayed at the top of the worksheet.

For those of you who don't need the form itself, you can use the worksheet anyway, and then key in the results into your form on your appraisalware suite. I reckon a download-spreadsheet analysis process will save most users massive amounts of time when compared to trying to compile/analyze the data using only the tools provided by the MLS systems.

I will be writing up an instructional guide, probably with screenshot pics, to guide you through the process. I reckon some users won't need it because beyond the quickie instructions already on the worksheet the process is pretty straightforward.

I anticipate these worksheets will be refined and augmented to perform additional functions as time goes on, so this version will probably end up being an interim step for many users. But for now it should work okay for most users until something better comes along. As always, anyone who wants to contribute fixes or refinements to the effort is highly encouraged to do so - I am no programmer nor am I even a proficient Excel user. I'm just trying to hack my way through this. I appreciate the input of those who are better at this than I.
  #3  
Old 01-20-2009, 10:34 AM
Randolph Kinney Randolph Kinney is offline
 
Join Date: Apr 2005
Location: SoCal
State: California
Professional Status: Certified Residential Appraiser
Posts: 19,152
Default

I like the idea of disclosure of how the numbers were arrived at and the meaning of that.

Double counting and not counting; a single time cut off can't work and meet the form's conditions.

One other thing to consider, you are to characterize the overall trend as declining, stable or increasing given 7-12 months ago median, 4-6 months ago median and 0-3 months ago median. Personally, since it is asking for the overall trend, any time the 0-3 months median is lower than the 7-12 months median then the overall trend is declining. There would never be a stable overall trend unless the 0-3 months median is equal to the 7-12 months median.

And now you may want to consider what sort of sample size are you basing this judgment. The form does not say. The form does say, "The appraiser must explain any anomalies in the data, such as seasonal markets, new construction, foreclosures, etc."

I wonder what the unintended consequences will be of this form?
  #4  
Old 01-20-2009, 10:47 AM
PropertyEconomics's Avatar
PropertyEconomics PropertyEconomics is online now
 
Join Date: Jun 2007
Location: Central New Mexico
State: New Mexico
Professional Status: Certified General Appraiser
Posts: 18,755
Default

Quote:
Originally Posted by Randolph Kinney View Post
I like the idea of disclosure of how the numbers were arrived at and the meaning of that.

Double counting and not counting; a single time cut off can't work and meet the form's conditions.

One other thing to consider, you are to characterize the overall trend as declining, stable or increasing given 7-12 months ago median, 4-6 months ago median and 0-3 months ago median. Personally, since it is asking for the overall trend, any time the 0-3 months median is lower than the 7-12 months median then the overall trend is declining. There would never be a stable overall trend unless the 0-3 months median is equal to the 7-12 months median.

And now you may want to consider what sort of sample size are you basing this judgment. The form does not say. The form does say, "The appraiser must explain any anomalies in the data, such as seasonal markets, new construction, foreclosures, etc."

I wonder what the unintended consequences will be of this form?

Randolph .. I think the unintended consequences are tremendous and I certainly appreciate your thoughts.
We have an area, a neighborhood if you will, where the neighborhood as a total is in decline, homes typically new to less than 15 years old, so overall the market is declining. The particular rate of decline is pronounced at the upper end of this neighborhood (say in the $300k range), however, the lower end of the market (say in the $165K range) is as stable as stable can be. Values among model matches have held their value steady between $162 and $168 for the past three years. The sample size of this lower end of the market, sales in the last year, are less than 8 and listings are fewer than that.
Thus if you use the overall neighborhood indicators you will see a decline, yet if you use truly comparable sales you see stable, and the form will be contradictory. While this can be explained, the question is will the form be used agasint an appraiser that merely reflects the data they have which is in strong contradiction ... the overall market / neighborhood sample size much larger than that of the specific subject comparables.
__________________
Foxtrot, Uniform, Charlie, Kilo Cancer ... In memory of our friend Bill Waite!
  #5  
Old 01-20-2009, 01:44 PM
DaveH's Avatar
DaveH DaveH is offline
 
Join Date: May 2007
Location: Fairfield, OH
State: Ohio
Professional Status: Certified Residential Appraiser
Posts: 439
Default

Property,
I agree with you about the overall market, but shouldn't these be actual comparables to the subject. So if the high end is at $300k and the lower end is at $168k, but the subject is say $200k roughly with GLA, updates and amenities, then wouldn't that market for the $200k average be in decline. I realize that the lower end is stable, most likely due to consumers buying foreclosed properties at a great deal, but the middle will still be in a decline, or close to it. Don't we have to look at the comparables for the subject, "the best fit", to achieve what these forms are trying to show.

I know the overall market for that area could be up or down and there are pockets in the market that sometimes are up with the entire market down. So now we have to explain the numbers that we are transmitting to the client for this report. I have run across this many times here in Cincinnati, where the upper areas of the market are declining and the lower areas are actually increasing, but to help illustrate what needs to be conveyed, I have switched to a narrative format for most of the reports.

Just what I have run into, not withstanding anyone else's areas.

Dave
  #6  
Old 01-20-2009, 02:01 PM
PropertyEconomics's Avatar
PropertyEconomics PropertyEconomics is online now
 
Join Date: Jun 2007
Location: Central New Mexico
State: New Mexico
Professional Status: Certified General Appraiser
Posts: 18,755
Default

Quote:
Originally Posted by DaveH View Post
Property,
I agree with you about the overall market, but shouldn't these be actual comparables to the subject. So if the high end is at $300k and the lower end is at $168k, but the subject is say $200k roughly with GLA, updates and amenities, then wouldn't that market for the $200k average be in decline. I realize that the lower end is stable, most likely due to consumers buying foreclosed properties at a great deal, but the middle will still be in a decline, or close to it. Don't we have to look at the comparables for the subject, "the best fit", to achieve what these forms are trying to show.

I know the overall market for that area could be up or down and there are pockets in the market that sometimes are up with the entire market down. So now we have to explain the numbers that we are transmitting to the client for this report. I have run across this many times here in Cincinnati, where the upper areas of the market are declining and the lower areas are actually increasing, but to help illustrate what needs to be conveyed, I have switched to a narrative format for most of the reports.

Just what I have run into, not withstanding anyone else's areas.

Dave

In my example Dave the lower end of the market was the subject. That is where this form will be a bit interesting.
__________________
Foxtrot, Uniform, Charlie, Kilo Cancer ... In memory of our friend Bill Waite!
  #7  
Old 01-20-2009, 07:38 PM
VegasWayne's Avatar
VegasWayne VegasWayne is online now
 
Join Date: Nov 2007
Location: Area 24
State: Nevada
Professional Status: Licensed Appraiser
Posts: 1,982
Default

I tried to do this as an Excel worksheet back when this form was announced. Excel lacks the ability to find duplicate listings and getting it to properly sort the listings by period was a pain. The use of a database program such as Access or the open office version can make this much easier. Access queries can be imported into Excel for further analysis. The solution I came up with has Access import the same tab delimited file you start with and create the tables for each period for sales and listings. I would then use Excel to import the tables and do the needed calculations.

I kept upgrading what the Access database could do and eventually I could do the whole report in Access. I found a VBA program that does the median function that was lacking in Access. This function allowed me to drop the need to import the tables into Excel. The version I have now will sort the listings into the proper periods and when a duplicate listing is found the newer one is used. This allows for the inclusion of expired and withdrawn listings. The latest version also divides the first time period in two so that trends can be more easily determined by having all periods be of equal time. I can also analyze concession and REO trends from the same data. My latest improvement is to add additional past periods to determine if a seasonal trend can be determined. The file is too large to post here but I will give a copy to anyone that wants it. Just PM me with your email address.
  #8  
Old 01-20-2009, 08:31 PM
Randolph Kinney Randolph Kinney is offline
 
Join Date: Apr 2005
Location: SoCal
State: California
Professional Status: Certified Residential Appraiser
Posts: 19,152
Default

VegasWayne,

I have Access however, my life is not something that can make use of it. The 1004 MC form may be the only use I would have for it as you describe it.

But, if you have a program that you would like to share, why not post it?
  #9  
Old 01-20-2009, 09:04 PM
VegasWayne's Avatar
VegasWayne VegasWayne is online now
 
Join Date: Nov 2007
Location: Area 24
State: Nevada
Professional Status: Licensed Appraiser
Posts: 1,982
Default Here it is

Ok, I tried the same file sharing site. http://www.box.net/shared/neulmoujxs
  #10  
Old 01-20-2009, 09:41 PM
Randolph Kinney Randolph Kinney is offline
 
Join Date: Apr 2005
Location: SoCal
State: California
Professional Status: Certified Residential Appraiser
Posts: 19,152
Default

Quote:
Originally Posted by VegasWayne View Post
Ok, I tried the same file sharing site. http://www.box.net/shared/neulmoujxs
Thanks. I will look at it (play with it) tomorrow.
Sponsored Links

Closed Thread


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump




Copyright © 2000-, AppraisersForum.com, All Rights Reserved
     Terms of Use  Privacy Policy
AppraisersForum.com is proudly hosted by the folks at AppraiserSites.com

Fastest Way to Find a Real Estate Appraiser Enter Zip Code:
Partner Sites:
AppraiserUSA.com - National Appraiser Directory AllDomainsUSA.com - Domain Name Registration
DeadbeatListings.com - Deadbeat ListingsAppraiserSites.com - Web Hosting for the Professional Real Estate Appraiser
Find FHA Appraisers - FHA Appraiser Search Commercial Appraisers - Commercial Appraiser Search
Relocation Appraisal - Find Relocation Appraisers Domain Reseller - Business Opportunity
Home Security Buzz - Home Security Info Radon Testing - Radon Gas Info
My Medicare Forum - Medicare Info Stop Smoking Help - Help Quitting Smoking
CordlessPhoneStore.com - Great Cordless Phones AndroidTabletCity.com - Android Tablet Computers

Follow AppraisersForum.com:          Find us on Facebook            Follow us on Twitter


All times are GMT -5. The time now is 10:10 AM.

SiteMap: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93