Appraisers' Free Forum

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

One for year, one for quarter and one for appreciation over/under previous quarter. I want to add a fourth; appreciation over/under same quarter previous year and then I want to chart the hole mess. There was a chart function on the OFHEO site, but somebody took it away or I lost it and can't find it.
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Jim Plante** · Posted: Sun Nov 25, 2007 11:47 am Post subject:

Edd,
Send me an excerpt of the data, including the header row. A dozen or so rows will do. (Or you can attach it here. Either way will work.)

Your time period would go on the X-axis; try setting the scale. The appreciation would go on the Y-axis.

The time period should be a combination of years and quarters, i.e., 1978 Q1, 1978 Q2, etc. If the year and the quarter are divided in the table, you'll have to add another column to combine them.
_________________
Jim Plante

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

OK,

I'll send in Excel. Please post here for everybody to see, if you can.

Thanks,

Edd
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

This was supposed to be an attachment. Supposed to be 4 columns: Year; Quarter; Index; Std Error.

Year Quarter Index (1Q 1995=1) STD Error
1979 1 58.38 -1.3
1979 2 57.56 -1.27
1979 3 60.95 -1.4
1979 4 61.2 -1.74
1980 1 60.31 -2.07
1980 2 66.39 -3.19
1980 3 61.92 -2.33
1980 4 67 -2.84
1981 1 61.86 -3.26
1981 2 64.72 -1.72
1981 3 66.58 -2.06
1981 4 55.7 -1.76
1982 1 66.41 -2.45
1982 2 60.91 -2.03
1982 3 58.91 -2.51
1982 4 64.04 -2.82
1983 1 65.06 -2.04
1983 2 64.65 -3.27
1983 3 68.56 -3.45
1983 4 65.28 -3.45
1984 1 63.46 -2.79
1984 2 61.65 -2.63
1984 3 - -
1984 4 65.14 -2.51
1985 1 70.97 -2.65
1985 2 62.67 -2.17
1985 3 68.7 -1.72
1985 4 67.93 -1.69
1986 1 70.45 -1.88
1986 2 71.59 -1.26
1986 3 70.3 -1.35
1986 4 72.44 -1.61
1987 1 72.58 -1.45
1987 2 74.32 -1.64
1987 3 71.53 -2.56
1987 4 72.74 -2.09
1988 1 74.81 -2.74
1988 2 74.87 -1.64
1988 3 70.85 -1.51
1988 4 69.42 -1.64
1989 1 72.19 -1.91
1989 2 74.51 -1.92
1989 3 74.97 -1.44
1989 4 75.1 -1.66
1990 1 74 -1.61
1990 2 75.96 -1.42
1990 3 77.13 -1.64
1990 4 76.35 -1.65
1991 1 74.49 -1.33
1991 2 76.36 -1.19
1991 3 78.06 -1.25
1991 4 78.25 -1.14
1992 1 78.87 -1.01
1992 2 79.78 -1.09
1992 3 82.97 -1.1
1992 4 83.95 -1.09
1993 1 83.01 -1.18
1993 2 83.86 -1.05
1993 3 86.18 -1.07
1993 4 86.17 -1.01
1994 1 89.15 -1.18
1994 2 93.33 -1.23
1994 3 93.94 -1.4
1994 4 95.69 -1.35
1995 1 100 0
1995 2 99.61 -1.4
1995 3 104.25 -1.24
1995 4 106.06 -1.35
1996 1 108.98 -1.21
1996 2 110.81 -1.34
1996 3 110.72 -1.34
1996 4 114.15 -1.38
1997 1 112.83 -1.4
1997 2 115.85 -1.39
1997 3 117.04 -1.38
1997 4 118.84 -1.39
1998 1 122.69 -1.33
1998 2 123.47 -1.35
1998 3 124.2 -1.36
1998 4 126.05 -1.33
1999 1 128.53 -1.43
1999 2 129.67 -1.44
1999 3 128.36 -1.48
1999 4 128.75 -1.53
2000 1 131.65 -1.53
2000 2 130.63 -1.49
2000 3 133.23 -1.49
2000 4 135.88 -1.52
2001 1 139.73 -1.48
2001 2 141.76 -1.49
2001 3 141.97 -1.51
2001 4 144.59 -1.5
2002 1 144.97 -1.54
2002 2 145.97 -1.56
2002 3 148.33 -1.55
2002 4 150.42 -1.56
2003 1 151.44 -1.58
2003 2 152.79 -1.58
2003 3 153.45 -1.6
2003 4 153.64 -1.69
2004 1 156.45 -1.74
2004 2 157.94 -1.78
2004 3 159.92 -1.84
2004 4 161.25 -1.87
2005 1 161.02 -2
2005 2 166.41 -1.99
2005 3 165.96 -1.95
2005 4 166.91 -2.12
2006 1 169.4 -2.15
2006 2 170.63 -2.23
2006 3 174.7 -2.19
2006 4 176.38 -2.22
2007 1 176.77 -2.29
2007 2 178.06 -2.37
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

bump
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Jim Plante** · Posted: Mon Nov 26, 2007 7:47 am Post subject:

Is this something like what you're trying to do?

_________________
Jim Plante

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

Yeah,

Now how about a chart that shows the rate of appreciation from quarter to quarter or year to year? How did you get that chart?
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

Why double every quarter? What did you do? What is that X axis?
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

**Jim Plante** · Posted: Mon Nov 26, 2007 12:32 pm Post subject:

Sorry, Edd,
The X-axis is indicating each quarter. The legend needs to be one more line deep, so that it indicates 79 Q1, and so forth, stacked vertically. The data points are much closer together, so all of them are shown, but only half of them are indexed on the X-axis.

To make the chart, I copied the data you posted and pasted it into a text file. Used a regular expression to find and replace all the spaces with tabs, and saved it back to another text file. Finally, I imported that second text file into the spreadsheet as a CSV file.

I then made another column at C, labeled it Y&Q, and combined the last two digits of the year with the quarter, using the formula =RIGHT(A1, 2) & " Q" & B1. That gave me the year-and-quarter in one column. Select Column C and D (the Index), insert Chart, Select the XY plot with the lines option, set the axis titles, and let'er rip.

The rate of appreciation is the slope of the line. So to get that, I'd eliminate everything below 91 (copy from 91 forward onto another sheet in the workbook) and re-plot the graph. This time, insert a linear regression line.

But you need to know the slope and the intercept of that regression line. There's a single function that'll get'em both at once, but it's more complicated than you need to deal with right now. So use the SLOPE() and INTERCEPT() functions to get those values. You want to run those functions on the Y&Q and Index columns.
_________________
Jim Plante

**Edd Gillespie** · Certified General Joined: 13 Aug 2007 Posts: 2282

Thanks Jim,

I'll do that. Get back to you soon.
_________________
Edd “In the real estate economy, there are no guarantees that reason will prevail in a market where emotions run high and the amount of misinformation runs deep.” Jonathan Miller in The Matrix. So what’s an appraiser to do?

hallmac · Member Joined: 11 Aug 2007 Posts: 81 Location: Raymore

Jim I am using Excel 2000. It can not convert the date to year and quarters using the format tool so I had to do some reading. After combining two commands I came up with this : =YEAR(J2)&",Q" &INT((MONTH(J2)+2)/3) in this equation "J" is the Column the date is recorded in.

that returns the date in this format: 2000 Q1

When I run a pivot chart I get the same output you did.

Is there a place I can save this equation so I don't have to type it in each time I want to build a new table or is my only option to build a skeleton and hope I don't lose it?

**Jim Plante** · Posted: Wed Dec 05, 2007 10:11 pm Post subject:

Hall,
Look in the help file for Excel templates. You should be able to save that sheet as a template.

You'll want to remove the downloaded mls data.

I would record a macro to add the extra columns you'll need, like the Y&Q column (maybe others, like DOM if your MLS is like Christine's). Anyway, the idea is to open the template, import the MLS data, then run the macro to add the extra columns and to write the formulas into them, and then to run the pivot table and add the chart(s).
_________________
Jim Plante

hallmac · Member Joined: 11 Aug 2007 Posts: 81 Location: Raymore

Thanks Jim!