Appraisers' Free Forum

Okay, lurkers, here's some news. Rhonda sent me her spreadsheet in which she was trying to work out DOM from the MLS data, which provided only original list date and closing date. That's what all the DAYS360 stuff was all about earlier.

In tinkering with that spreadsheet in OpenOffice Calc, I discovered that simply subtracting one date from the other produced the desired results. With list date in column A, and closing date in column B, the DOM was found by using the formula =B1-A1. In Calc, the result was some weird date--until I formatted it as a simple integer number (one without a fraction). Then it gave the correct number of days between two dates.

I don't know why that worked. According to what I've read about Calc, it should have showed the number of seconds between the two dates. But this illustrates a good point to remember for spreadsheet usage: If you think something will work, try it on test data and see. Make sure your test data crosses tricky boundaries, such as months with 30, 31, 28, and 29 days. Does it work when there's more than a year between the dates?

So in Excel or Calc, you don't necessarily need DAYS360 or DAYS to calculate the number of days between two dates. Just subtract'em.

Jim Plante wrote:I don't know why that worked.

If open office works the same way as Quattro Pro for displaying dates, then the reason it worked is because each date is simply a number. It's been years since I read about this, so my numbers may be a bit off, but basically, any date is a function of a number of days added to some previous date. I believe the starting date they use is 1900. so, for example, the number of the date October 26, 2007 is 39381. So, October 27, 2001 minus October 26, 2001 equals one day; calculated as 39382 - 39381 = 1. As far as getting such a thing to display right, well, I've never had an occasion to need it... but, Jim's method sounds right to me. In Quattro Pro (as in WordPerfect) you can actually see that number, or what is going on behind the scenes. Just one reason I believe these programs are superior to Microsoft's offerings.

Actually, Steve, the number isn't supposed to be the number of days. It's supposed to be the number of seconds since some system-specified starting date. That's what caused my confusion.

Now, you could take the number of seconds between two dates, divide by 60 seconds/minute; 60 min/hr; 24 hr/day and come up with the number of days.

In Excel (at least my version) when you subtract list date from closed date you get a negative number. When I subtract closed date from list date it gives me a positive number. They're the same number .... only one negative and one positive.

So I use A1-B1 with A1 being the closed date and B1 being the list date.

You'll have to ask Jim why this works cause I have no idea. But I am happy that it does. I spent hours on that datedif stuff and thought I was going to lose my mind.

Jim Plante wrote:Actually, Steve, the number isn't supposed to be the number of days. It's supposed to be the number of seconds since some system-specified starting date. That's what caused my confusion.

Now, you could take the number of seconds between two dates, divide by 60 seconds/minute; 60 min/hr; 24 hr/day and come up with the number of days.

Maybe open office works differently. In QP the number actually appears in a box above the line and it is a whole number. It is the number of days since some specific date (around 1900) and the number I gave in my example is the actual number for that date.

I can't understand why any software system would confuse the issue of "date" by introducing "time."

OK, I finally figured it out. Whew, it was awful for a simple mind like mine.

Now can anyone explain how I would incorporate different areas into the same pivot table? This is only one of the counties in my area. There are 3 major counties and I would like to put them on the same graph to compare.

Jim, is that possible?

My MLS is weird and will only let me download 1,500 sales at a time - it took forever combining all the data. I'm sure there is an easier way, but I have not figured it out yet.

Here is my latest creation.

Yes, it's possible. Just append the other counties to that data set, and drag "county" into that pivot table. You should get one bars for each county for each quarter.

BTW, whattinhell happened in Q2/Q3 of 2007 there? Big sales spike. What caused it?

As far as combining text files is concerned, that's best done at the DOS level. Look up "concatenating text files" in the help or on google. I know in Unix it's something like "cat file1 >> Masterfile", and what you'd do is remove the header line from file1, then give that command. Both files are then combined. You'd remove the header line from file3, then type "cat file3>>Masterfile", and all three would be combined. Then you import the one file into Excel, and do your analysis. The ">>" operator, IIRC, appends the output of the "cat" command to the second file. Be careful, though, because a single ">" will replace the contents of the second file with the first. In DOS, I think there's an APPEND command or something like that.

A huge spike in foreclosures and banks/investors dumping properties at lower than 'average' prices as quickly as they can. Lots of duplexes and properties around 100k and below. That is a very low price for Madison County.

Same spike in Hinds County (where I live) only the prices are so low they're almost giving away some parts of town. (the part where you'll get shot sitting in your house) I saw a house that sold for $500. I called the realtor thinking it was a mistake - it was not a mistake. There are places in Jackson where you would literally have to pay someone to take a house now. They're selling for less than the assessed lot values, if they sell at all.

Each sub-market area is quite different. Some areas are actually increasing in value and sales are up. Locations close to the medical center/university are stable and increasing. Go a couple of miles away and they're dumping houses for pennies on the dollar.

Thank you Jim. I'll work on combining the counties next and see what I can come up with.

Okay, I looked it up for you. Here's a link:
http://www.computing.net/dos/wwwboard/forum/15784.html

In your case, you won't need the "ECHO" command. Since you'll eliminate the header line from File2.txt anyway, just leave it's carriage return in place. (IOW, leave a blank line as the first line of File2.txt.)

At the risk of getting *too* simple, change to the directory in which the text files are stored (CD \textfiles\); open File2.txt in NotePad; select the header line; and hit backspace. Then save. Do the same for File3.txt.

Then, type "type file1.txt >> Masterfile.txt"; "type file2.txt >> Masterfile.txt"; "type file3.txt >> Masterfile.txt".

Finally, import Masterfile.txt into Excel, and do your stuff.

I must admit, I'm impressed with the amount of information and help that is openly exchanged here. A rather nice fresh view of our peeps as compared to other locations I've seen (no names stated).

Otis wrote:I must admit, I'm impressed with the amount of information and help that is openly exchanged here. A rather nice fresh view of our peeps as compared to other locations I've seen (no names stated).

JP inspired me.

Of course you inspire me too Otis Dear.

Rhonda Brown wrote:

Otis wrote:I must admit, I'm impressed with the amount of information and help that is openly exchanged here. A rather nice fresh view of our peeps as compared to other locations I've seen (no names stated).

JP inspired me.

Of course you inspire me too Otis Dear.

Shhhhhhhhhhh - you're not supposed to tell. As Schultz said "I know nuthen - nuthen!"

Otis wrote:

Rhonda Brown wrote:

Otis wrote:I must admit, I'm impressed with the amount of information and help that is openly exchanged here. A rather nice fresh view of our peeps as compared to other locations I've seen (no names stated).

JP inspired me.

Of course you inspire me too Otis Dear.

Shhhhhhhhhhh - you're not supposed to tell. As Schultz said "I know nuthen - nuthen!"

So you don't kiss and tell ???