Welcome back to Part IV of our week-long series of Gathering, Extracting, Harvesting and Implementing Data Scraped from the Web. Today, in Episode #1686, Bill introduces the 'real' intent behind this series: the release of the next version, and various uses, of Project code Name "GeoFlow" Preview for Microsoft Excel 2013. And this is a Must-See. With numerous comprehensive improvements, GeoFlow has already dramatically evolved. Using the Data that we've collected and manipulated this week, see how using GeoFlow effectually explodes the dynamic of your Harvested Data. This is really amazing! If nothing else, watch the podcast for the varying flexibility of GeoFlow and how it could benefit you at home, in your hobbies or in your career.
This mini-series is a really cool overview of some of our earlier Podcasts, a more advanced 'how to' for integrating a variety of Formulas and Functions as well as an introduction to the power behind and uses of the latest release of GeoFlow to evaluate Real Estate.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. http://www.amazon.com/Excel-2013-Dept
This mini-series is a really cool overview of some of our earlier Podcasts, a more advanced 'how to' for integrating a variety of Formulas and Functions as well as an introduction to the power behind and uses of the latest release of GeoFlow to evaluate Real Estate.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! http://www.mrexcel.com/learn2010/LE20
And for more information on Excel 2013, check out "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. http://www.amazon.com/Excel-2013-Dept
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast episodes 1686.
Project Codename GeoFlow.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today April 11th is the release of the next version of Project Codename Geoflow preview for Excel.
All week I've been talking about this real estate data and I use the tricks from Monday, Tuesday and Wednesday to actually get data 50 years worth of sales data for over 2000 homes in the Merritt Island.
So, we're gonna Map, Launch GeoFlow.
Now, lots of nice improvements to GeoFlow here including some things that you would never really find and I want to show this to you I've had a chance to use GeoFlow for a couple of days here.
First, you want to choose your geographic fields.
So, I have four of those I have address, city, state and zip.
My address is just street address, like 123 Main Street and they want to map that to this new field called address.
Never choose address, always choose street.
Address is supposed to have the street, the city, state and zip and if you choose address everything is gonna get mapped incorrectly not everything, but a lot of things will get mapped incorrectly.
Let's see chart types they now, offer a column bubble and heat map, the heat map is really cool, but I'm not gonna show you that today I'm gonna choose bubble for the height I want to show the sales amount the category is going to be allotment and then the sale date is going to be time.
All right! Now, while I'm waiting for all this to load let's take a look at some of the new things we can do this little date decoration up here.
I'm going to right click that and say, Edit and they offer different ways to show the date I really don't need to show the sale date down to the time I don't even have that of course, so I just show that as a month and year click Accept.
I can make it a little bit smaller if I want, but make sure to leave enough room there for September or whatever the long months are here we have our different allotments and I can resize this, get all of the allotments in like that.
So, nice improvements there, okay let's also we'll zoom in here remember it's [ 2:18.4 ] before control wheel mouse to zoom in control wheel mouse, control wheel mouse, but I figured out something really interesting if you want to tip the data, now this should be an average.
In case, there are two sales in the same month, there we go.
Okay if you want to rotate the data I used to talk about holding down Alt and doing an arc it's not the arc it's to the left and right.
So, if I want to rotate this data just hold down the Alt key and drag directly to the left or directly to the right.
Now, hand off of Alt and we can bring the area back into view if I need to tip, Alt and straight up or straight down will tip.
So, I get a different view of the data.
All right, now, some more things I'd say we have our layer one here and we have the little cog next to layer one they've now given us control this might have been there before I'm not sure of the thickness and see each one of these columns ends up being about one city block but since I have every house on the street I might have ten blocks per city and they're all on top of each other.
So, we want to make these really really small like five percent is actually a good way to go that way even if i zoom all the way in, I can see the individual houses.
All right, so, five percent that got smaller also we have a cog here on the timeline and before it was always going way too fast for me.
So, we can slow that down a little bit.
All right, so, here's some things that I was able to discover with this data.
Our target area is this section Merritt Island right here from 520 to 528 one thing I've learned though it's Merritt Island is looking for a newer house and frankly this whole area started to really go haywire back in the 60's when the Apollo program because Kennedy Space Center is just right up here when the Apollo program started that's when we started to see the first homes pop-up.
So, back in the 60's and a lot of this area was built up.
Now, let's say you know by the end of the Apollo program in the 1970's.
All right, now, I'm going to pause this I can drag the scrubber here anywhere along the timeline and so, right here we're at April 1986.
I'll zoom in a little bit oh, hey, here's another great thing this theme, the second theme actually replaces the map with a satellite image.
So, I can you know, actually see the streets and everything like that.
So, right here look at these homes that are now there were not built in 1986.
So, if Merritt Island and is looking for a newer house, hey, let's go check out these streets what streets are those.
Okay Map Labels and we can zoom right in on that allotment.
So Leeward Lane, Sykes Point Lane, Sykes Creek Drive, those might be some interesting places and I can see when they started to build up right there, right there around 1987, but I can hover and see 200, 256000, but there's some smaller houses there as well.
All right! So, we'll let this continue to go and we can see as the area gets build up.
So, those are the newer homes although I'll have to go check later and see you know how the prices are, the other thing that was really fascinating to me here is you know Florida real estate went crazy in the, in the 2000's and we can really see that as I drag the scrubber.
So, let's get right up to, so there we are in 2000.
Now, watch how home prices just soared over the next several years in some cases almost doubling or tripling.
Now, not every home was sold during that period.
So, you know, they're not all sitting there practically underwater, but a lot of them are there's a lot of inventory that was sold during that period and you know, we'll have to see what happens with those.
So, this is the new Project Codename GeoFlow certainly doesn't give me all the answers, but I can hover over any individual house see the date of the sale see how much was sold for you know, I can see some of these really high spiky homes here.
You know, the palaces that I'm never gonna move in I just need a home back here on the canal where I can throw my kayak in.
So, great way to visualize data set again 2,000 homes that we have here with 50 years where the sales history all that was scraped from the Brevard county property tax appraiser site, using the Macros from Tuesday and Wednesday of this week.
If you have Excel 2013 Pro Plus, check out GeoFlow again you can download it from the URL on the screen there.
All right, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast episodes 1686.
Project Codename GeoFlow.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today April 11th is the release of the next version of Project Codename Geoflow preview for Excel.
All week I've been talking about this real estate data and I use the tricks from Monday, Tuesday and Wednesday to actually get data 50 years worth of sales data for over 2000 homes in the Merritt Island.
So, we're gonna Map, Launch GeoFlow.
Now, lots of nice improvements to GeoFlow here including some things that you would never really find and I want to show this to you I've had a chance to use GeoFlow for a couple of days here.
First, you want to choose your geographic fields.
So, I have four of those I have address, city, state and zip.
My address is just street address, like 123 Main Street and they want to map that to this new field called address.
Never choose address, always choose street.
Address is supposed to have the street, the city, state and zip and if you choose address everything is gonna get mapped incorrectly not everything, but a lot of things will get mapped incorrectly.
Let's see chart types they now, offer a column bubble and heat map, the heat map is really cool, but I'm not gonna show you that today I'm gonna choose bubble for the height I want to show the sales amount the category is going to be allotment and then the sale date is going to be time.
All right! Now, while I'm waiting for all this to load let's take a look at some of the new things we can do this little date decoration up here.
I'm going to right click that and say, Edit and they offer different ways to show the date I really don't need to show the sale date down to the time I don't even have that of course, so I just show that as a month and year click Accept.
I can make it a little bit smaller if I want, but make sure to leave enough room there for September or whatever the long months are here we have our different allotments and I can resize this, get all of the allotments in like that.
So, nice improvements there, okay let's also we'll zoom in here remember it's [ 2:18.4 ] before control wheel mouse to zoom in control wheel mouse, control wheel mouse, but I figured out something really interesting if you want to tip the data, now this should be an average.
In case, there are two sales in the same month, there we go.
Okay if you want to rotate the data I used to talk about holding down Alt and doing an arc it's not the arc it's to the left and right.
So, if I want to rotate this data just hold down the Alt key and drag directly to the left or directly to the right.
Now, hand off of Alt and we can bring the area back into view if I need to tip, Alt and straight up or straight down will tip.
So, I get a different view of the data.
All right, now, some more things I'd say we have our layer one here and we have the little cog next to layer one they've now given us control this might have been there before I'm not sure of the thickness and see each one of these columns ends up being about one city block but since I have every house on the street I might have ten blocks per city and they're all on top of each other.
So, we want to make these really really small like five percent is actually a good way to go that way even if i zoom all the way in, I can see the individual houses.
All right, so, five percent that got smaller also we have a cog here on the timeline and before it was always going way too fast for me.
So, we can slow that down a little bit.
All right, so, here's some things that I was able to discover with this data.
Our target area is this section Merritt Island right here from 520 to 528 one thing I've learned though it's Merritt Island is looking for a newer house and frankly this whole area started to really go haywire back in the 60's when the Apollo program because Kennedy Space Center is just right up here when the Apollo program started that's when we started to see the first homes pop-up.
So, back in the 60's and a lot of this area was built up.
Now, let's say you know by the end of the Apollo program in the 1970's.
All right, now, I'm going to pause this I can drag the scrubber here anywhere along the timeline and so, right here we're at April 1986.
I'll zoom in a little bit oh, hey, here's another great thing this theme, the second theme actually replaces the map with a satellite image.
So, I can you know, actually see the streets and everything like that.
So, right here look at these homes that are now there were not built in 1986.
So, if Merritt Island and is looking for a newer house, hey, let's go check out these streets what streets are those.
Okay Map Labels and we can zoom right in on that allotment.
So Leeward Lane, Sykes Point Lane, Sykes Creek Drive, those might be some interesting places and I can see when they started to build up right there, right there around 1987, but I can hover and see 200, 256000, but there's some smaller houses there as well.
All right! So, we'll let this continue to go and we can see as the area gets build up.
So, those are the newer homes although I'll have to go check later and see you know how the prices are, the other thing that was really fascinating to me here is you know Florida real estate went crazy in the, in the 2000's and we can really see that as I drag the scrubber.
So, let's get right up to, so there we are in 2000.
Now, watch how home prices just soared over the next several years in some cases almost doubling or tripling.
Now, not every home was sold during that period.
So, you know, they're not all sitting there practically underwater, but a lot of them are there's a lot of inventory that was sold during that period and you know, we'll have to see what happens with those.
So, this is the new Project Codename GeoFlow certainly doesn't give me all the answers, but I can hover over any individual house see the date of the sale see how much was sold for you know, I can see some of these really high spiky homes here.
You know, the palaces that I'm never gonna move in I just need a home back here on the canal where I can throw my kayak in.
So, great way to visualize data set again 2,000 homes that we have here with 50 years where the sales history all that was scraped from the Brevard county property tax appraiser site, using the Macros from Tuesday and Wednesday of this week.
If you have Excel 2013 Pro Plus, check out GeoFlow again you can download it from the URL on the screen there.
All right, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
