Appraisers' Free Forum

Edd Gillespie · by **Edd Gillespie** on Sun Nov 25, 2007 11:14 am

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.

Jim Plante · by **Jim Plante** on Sun Nov 25, 2007 12:47 pm

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.

Edd Gillespie · by **Edd Gillespie** on Sun Nov 25, 2007 2:49 pm

OK,

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

Thanks,

Edd

Edd Gillespie · by **Edd Gillespie** on Sun Nov 25, 2007 9:09 pm

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 Gillespie · by **Edd Gillespie** on Mon Nov 26, 2007 8:06 am

Jim Plante · by **Jim Plante** on Mon Nov 26, 2007 8:47 am

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

Edd Gillespie · by **Edd Gillespie** on Mon Nov 26, 2007 11:43 am

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 Gillespie · by **Edd Gillespie** on Mon Nov 26, 2007 11:46 am

Why double every quarter? What did you do? What is that X axis?

Jim Plante · by **Jim Plante** on Mon Nov 26, 2007 1:32 pm

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.

Edd Gillespie · by **Edd Gillespie** on Mon Nov 26, 2007 5:31 pm

Thanks Jim,

I'll do that. Get back to you soon.

hallmac · by **hallmac** on Wed Dec 05, 2007 8:47 pm

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 · by **Jim Plante** on Wed Dec 05, 2007 11:11 pm

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).

hallmac · by **hallmac** on Thu Dec 06, 2007 12:56 am

Thanks Jim!

Appraisers' Free Forum

Date Functions in Spreadsheets

Who is online