1. Welcome to AppraisersForum.com, the premiere online community for the discussion of real estate appraisal. Register a free account to be able to post and unlock additional forums and features.

1004MC + Worksheet in Excel (Freebie)

Discussion in 'Fannie Mae, Freddie Mac, USPAP' started by George Hatch, Jan 20, 2009.

Thread Status:
Not open for further replies.
  1. George Hatch

    George Hatch Elite Member

    124
    Jan 15, 2002
    Professional Status:
    Certified General Appraiser
    State:
    California
    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.
     
  2. George Hatch

    George Hatch Elite Member

    124
    Jan 15, 2002
    Professional Status:
    Certified General Appraiser
    State:
    California
    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. Randolph Kinney

    Randolph Kinney Elite Member
    Gold Supporting Member

    83
    Apr 7, 2005
    Professional Status:
    Certified Residential Appraiser
    State:
    California
    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. PropertyEconomics

    PropertyEconomics Elite Member

    0
    Jun 19, 2007
    Professional Status:
    Certified General Appraiser
    State:
    New Mexico

    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.
     
  5. DaveH

    DaveH Junior Member

    0
    May 5, 2007
    Professional Status:
    Certified Residential Appraiser
    State:
    Ohio
    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. PropertyEconomics

    PropertyEconomics Elite Member

    0
    Jun 19, 2007
    Professional Status:
    Certified General Appraiser
    State:
    New Mexico

    In my example Dave the lower end of the market was the subject. That is where this form will be a bit interesting.
     
  7. VegasWayne

    VegasWayne Senior Member

    12
    Nov 15, 2007
    Professional Status:
    Licensed Appraiser
    State:
    Nevada
    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. Randolph Kinney

    Randolph Kinney Elite Member
    Gold Supporting Member

    83
    Apr 7, 2005
    Professional Status:
    Certified Residential Appraiser
    State:
    California
    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. VegasWayne

    VegasWayne Senior Member

    12
    Nov 15, 2007
    Professional Status:
    Licensed Appraiser
    State:
    Nevada
  10. Randolph Kinney

    Randolph Kinney Elite Member
    Gold Supporting Member

    83
    Apr 7, 2005
    Professional Status:
    Certified Residential Appraiser
    State:
    California
Thread Status:
Not open for further replies.

Share This Page