Appraisers' Free Forum

**Jim Plante** · Posted: Tue Dec 04, 2007 9:12 am Post subject:

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

**Steve Owen** · Posted: Tue Dec 04, 2007 9:41 am Post subject:

**Jim Plante** · Posted: Tue Dec 04, 2007 9:52 am Post subject:

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.
_________________
Jim Plante

**Rhonda Brown** · Posted: Tue Dec 04, 2007 10:32 am Post subject:

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

But I am happy that it does. I spent hours on that datedif stuff and thought I was going to lose my mind. Surprised

_________________
"Do not dwell on the past, do not dream of the future, concentrate the mind on the present moment" Buddha

**Steve Owen** · Posted: Tue Dec 04, 2007 1:48 pm Post subject:

**Rhonda Brown** · Posted: Sun Dec 23, 2007 10:15 pm Post subject: Quarters

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

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.

whip

_________________
"Do not dwell on the past, do not dream of the future, concentrate the mind on the present moment" Buddha

**Jim Plante** · Posted: Sun Dec 23, 2007 11:04 pm Post subject:

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.
_________________
Jim Plante

**Rhonda Brown** · Posted: Mon Dec 24, 2007 12:39 am Post subject:

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

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.

whip

_________________
"Do not dwell on the past, do not dream of the future, concentrate the mind on the present moment" Buddha

**Jim Plante** · Posted: Mon Dec 24, 2007 9:04 am Post subject:

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.
_________________
Jim Plante

**Otis** · Posted: Wed Jan 02, 2008 10:49 pm Post subject:

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

**Rhonda Brown** · Posted: Wed Jan 02, 2008 11:22 pm Post subject:

**Otis** · Posted: Wed Jan 02, 2008 11:48 pm Post subject:

**Rhonda Brown** · Posted: Thu Jan 03, 2008 10:00 am Post subject: