Market Stats - Any Experts?

[Gregory Beck]

Hello,

I'm sure this topic is already on the forum. I've searched under spreadsheet and stats, but I'm looking for the "Market Stats - Analysis & Reporting for Dummies" version... plain and simple (if possible) pointers / advice on how to better report what is going on in the market and import the data into the URAR (or other form report). The most time consuming stage of completing each assignment (for me) is usually the market analysis, looking for trends and providing data to support my analysis. Is there a way to utilize Excel or some other program to report the data and import it into the appraisal? As far as I know, I would use Excel (still have much to learn on that), print as a pdf or "Snag-it" and paste it into an exhibit page in the appraisal report. I've assisted a commercial appraiser with a few reports recently and he does a bang-up job with tables, charts, graphs, etc. in his narrative commercial reports. I would like to improve my residential work in that area.

Any comments, advice, etc. would be greatly appreciated. For all you pros on stats, I would appreciate any resources for improving in this area.

Thanks,

Hank O.

This is the place you want to go.

http://appraisalnewsonline.typepad.com/appraisal_news_for_real_e/2007/08/market-trends--.html

This linear regression is shown by Kat Bryce a certified appraiser in Fort Myers. She's really quite good and she's kind to show us all how to do a linear regression in Excel.. So hats off to Kat:new_multi:

 

[Tom Woolford]

I, too, find it to be very time-consuming. So much so that I do a formal market study only when it real serves an important purpose. It's one thing to look at MLS closed sales, listings, sales date from other sources and get a feel for what's going on. I do that, routinely. It's quite another to import data and produce tables, charts and graphs worthy of including in an appraisal report. Rarely is that feasible in a residential report.

I have to disagree. I do a through analytsis for all my major markets of Average sales price, median sales price, days on the market as well as # of sales and New listings. I update the data on a monthly basis, and only takes a couple of min once they are set up. I follow at least 20 specific sub markets just in Citrus County, as well as countywide trends for Conventional and manufactured. Sample is attached

 

[Scott Lanz]

Hal,

I have a very similar spreadsheet that I built. Actually, it sounds almost identical. I built ones for commercial, land (commercial, acreage and lakefront) and residential housing.

 

[VolcanoLvr]

The main thing in doing residential reports is to graph only the relevant data pertaining to that specific type of property. Don't mix condos in with SFR's, duplexes with 4plexes, 1,000 s/f with 4,000 s/f, etc. NAR, Case-Shiller, OFHEO info is fine for a regional look at all properties, but don't use those for a specific assignment.

I also include an Excel 5 year median price Historical Value Trend Chart keyed to the specific property & it's neighborhood location, with a second Demand/Supply (absorption rate) chart, on one page, that is inserted into reports. These are self-calculating, with an overlaid red trend line on the median price chart, and a self-calculating % increase or decrease in value from one year to the next. The demand/supply chart calculates absorption rates over a year period.

This value trend chart can also be modified to be a 5 quarter chart, just by changing the reporting dates, and plugging in that type of info rather than year-by-year.

Info data is obtained from the MLS or a private data source.

If you'd like a PDF sample, send a msg to: dtowne (at) fidalgo [dot] net ...... I'd attach it here but don't know the trick to do that! I'll also provide info on how you can obtain the original so you don't have to spend time re-creating it.

 

[Hall McClenahan]

Hall:

I would love to see a copy of that spreadsheet.

I've been trying to get excel to take a raw set of set data, and organize it in months Jan '08 and from that organization take median price, median $/SF, median DOm for each month, and best of yet a count. Problem is I can do that if I organize the data manually by sorting and filtering, however I would like to set up a blank excel template that I just import raw data into and it does all the leg work...any thoughts?

I've tried pivot tables, but have not tried macros yet. I'm sure there is an easy answer, but I have been so busy the last year that I lack the time to even tinker.

Hi Brian, when I get home on Tuesday I will try and send you the template. What you want is so easy you will freak when you find out how. You can set up your own template in about 4 minutes to do that.

On your imported data sheet format your dates column to Mar/08 format.
On a separate sheet select a cell and type: =median( . Select the data sheet and left click on the column you want the median value of and hit enter. label each cell to identify which value you are working on. Clear the data out and save as a template. Your done! You can also do the average and mode the same way.

Anthony A. Young CREA actually produces a DVD that will change the way you look at Excel. I spent the last six months (unfortunately I have been slow) trying to learn the things he teaches in that DVD. I wish I knew about it before I started because it would have saved me months of frustration. Now, if he would just produce a DVD for what I am trying to learn now. :laugh:

For appraisers I would not recommend any other book or video. I think you can find it here:

http://www.graphthetrend.net/Home_Page.html

 

[Kali the Boston Terrier]

On the seperate worksheet how does it know which one is Mar/08...Feb/08 and how to just give me the median for March and Feb....

 

[Artemis Fowl]

Hall:

I would love to see a copy of that spreadsheet.

I've been trying to get excel to take a raw set of set data, and organize it in months Jan '08 and from that organization take median price, median $/SF, median DOm for each month, and best of yet a count. Problem is I can do that if I organize the data manually by sorting and filtering, however I would ike to set up a blank excel template that I just import raw data into and it does all the leg work...any thoughts?

I've tried pivot tables, but have not tried macros yet. I'm sure there is an easy answer, but I have been so busy the last year that I lack the time to even tinker.

Brian. We need to get together. I have something to show you. Formulas are the answer.

 

[Kali the Boston Terrier]

I figured as much...name a time and place

 

[Hall McClenahan]

On the separate worksheet how does it know which one is Mar/08...Feb/08 and how to just give me the median for March and Feb....

use subtotals under Data, select "average" for the function, if you want monthly averages. screen #2 is the information you seek.

 

[George W Dodd]

Anthony A. Young CREA actually produces a DVD that will change the way you look at Excel. I spent the last six months (unfortunately I have been slow) trying to learn the things he teaches in that DVD. I wish I knew about it before I started because it would have saved me months of frustration. Now, if he would just produce a DVD for what I am trying to learn now. :laugh:

For appraisers I would not recommend any other book or video. I think you can find it here:

http://www.graphthetrend.net/Home_Page.html

Anthony's DVD is great "The nuts & Bolts of Spreadsheets.." is an excellent learning tool and resource. I'd highly recommend it to both novice and expert.