Finding a new home: workflow—Analytics

Workflow using ArcGIS Insights

This workflow guides you through the process of downloading Zillow data for your current home (or for a hypothetical home) and determining an appropriate selling price. It then steps through the process of identifying candidate neighborhoods where you might want to buy a new home.

Selling your home

Determine an appropriate selling price

Download home sales time series data from Zillow at www.zillow.com/research/data.
Mark and Lisa have a 3-bedroom home, so they will select the ZHVI 3-Bedroom time-series ($) data set at the ZIP Code level. Select the file that best reflects your own home. If you don't own a home, pretend you do, or use Mark and Lisa's scenario to complete this workflow.
Prepare the Excel data as follows:
1. Using Excel, open the .csv file you downloaded.
  Notice that the RegionName field has ZIP Codes as numbers (if you sort the RegionName field you will notice the ZIP Codes for Massachusetts, for example, don't have leading zeros; 01001 is 1001). Also, notice the median home value columns are named using the year and month. The first data available is for April 1996 (1996-04). When this workflow was created, the most current data provided was 2017-06. The data you download will likely be more current.
2. Copy all the column headings and the one record with data for your ZIP Code to a new Excel sheet.
  
  Tip:
  Apply a filter to the RegionName field and select your ZIP Code. Mark and Lisa live in Crestline, California, so they apply a filter for the 92325 ZIP Code.
3. Select (highlight) fields starting with the month and year when you bought your home and continuing to the last month and year column in the Excel table. So, for example, since Mark and Lisa bought their home in December 2005, they highlight the the two rows from column 2005-12 to column 2017-06 (your data will likely be more current; be sure to include all the most current data for your ZIP Code).
4. Copy (press Ctrl+C) the selected data and paste it, along with the column headings, to a new Excel sheet using Paste Transposed (right-click in the first cell of the new sheet to see the paste options; select Paste Transposed).
  This gives you two columns of data.
5. The first column has date values but only includes the year and month. In column C, create a proper date field.
  - Right-click column C and format the cells to be category Date.
  - In the first cell of column C, enter the following formula: = DATEVALUE(CONCATENATE(A1, "-01"))
  - Drag the Autofill handle down to the last data cell in the column.
6. Insert a top row and type the column headings: YYYYMM, Value, and Date.
7. Rename the Excel sheet (probably called Sheet2 at present) something like <MyCity>AveSellingPrice and delete the other sheets (the first sheet contains a large amount of data that you won't be using further in the workflow).
  Mark and Lisa named their price Excel sheet CrestlineAveSellingPrice.
8. Save this new sheet as an Excel workbook.
  Mark and Lisa named their Excel file Crestline3BdrmAveSellingPrice.xlsx.
Sign in to your ArcGIS Enterprise portal. To find Insights, click the Apps button . Alternatively locate Insights by typing Insights for ArcGIS in the Search window. Open the Insights application.
Click the New Workbook button.
Add your Excel data and create a time-series map as follows:
1. The Add To Page dialog box is displayed. Click Excel or CSV and drag the Excel file you created above onto the data drop zone. Alternatively browse to the Excel file using Browse my computer. Click Add.
2. Expand the average selling price layer and select the Value and Date fields.
3. Drag these fields onto the Create Chart drop zone and notice Insights creates a time series graph showing you how average home prices have changed since you bought your home. Resize the card if necessary.
4. Click the Chart Statistics button and check the Mean check box.
  This displays the mean selling price for all average home values across all years.
5. Click somewhere off the card. Then click the card title and give the time series graph a meaningful name such as Average Selling Price for <#>BD Homes in <your ZIP Code or city>, <dates>.
  Mark and Lisa, for example, named their card Average Selling Price for 3BD Homes in Crestline, 12/2005 to 3/2017.
Determine an appropriate selling price based on home sales trends as follows:
1. Hover over the ends of the time-series line to determine the current average selling price and the average selling price when you bought your home. (In order to select the very first month/year data value, you may need to stretch the chart wide). Divide the current average price by the beginning average price to see how much homes in your ZIP Code have appreciated or depreciated. For example, in June 2017, 3-bedroom homes in Crestline were selling, on average, for $243,900. When Mark and Lisa bought their home in December of 2005, 3-bedroom homes were selling for $316,000. They divide 243,900 by 316,000 and get 0.77. It seems that homes in Crestline are only worth 77 percent of what they were at the end of 2005.
2. You can get a rough estimate of what your home is worth by summing what you paid for your home plus what you invested in it, and multiplying that sum by the ratio computed above. Mark and Lisa, for example, paid $355,000 in 2005 and invested $100,000 in solid improvements (new kitchen, major landscaping, hardwood flooring, and so on). Multiplying ($355,000 + $100,000) by 0.77 gives them a rough suggested selling price of $350,350.
Name your workbook by clicking the Untitled Workbook text at the top of the Insights page, and save your work.
Mark and Lisa named their workbook Finding a Great New Home.

Get additional information about the local real estate market

If your home is part of a seller's market, you are more likely to get your asking price.

Download the Buyer-Seller Index data at the ZIP Code level from www.zillow.com/research/data. (You will find it toward the bottom of the list of data products, near the end of the Other Metrics data sets).
Open the .csv file using Excel. Zillow reports ZIP Codes as numbers, while Insights references them as strings. You will need to pad the ZIP Code numbers with leading zeros so the Zillow data will link to the Insights ZIP Code geometry. Follow these steps:
1. Sort the RegionName column from smallest to largest so you will be able to see how the formula below works.
2. Name a new column in the Excel table ZIPString.
3. In the first cell of the new column, enter the formula to pad each RegionName value with leading zeros, keeping the rightmost five characters: =RIGHT(CONCAT("00000",B2),5)
4. Drag the Autofill handle down to the last data cell in the column.
  Note:
  You can create the ZIPString field in Insights as well, using the same formula.
5. Save the .csv file as a new Excel workbook and close Excel.
  Mark and Lisa named their Excel file BuyerSellerIndex.xlsx.
In Insights, click Add Data and click Excel or CSV. Drag and drop the buyer-seller index file onto the Insights data drop zone or browse to the Excel file. Click Add.
Expand the BuyerSellerIndex_Zip layer in the data pane to see all of the variables. Select all the variables except DaysOnMarket, BuyerSellerIndex, and ZIPString.
Click Dataset Options and select Hide Selected Fields.
Open the data table and explore the data values as follows:
1. Click the Dataset Options button and select View Data Table.
2. Sort on the DaysOnMarket field and notice the range. For the data Mark and Lisa downloaded, the range is 33 to 331 days. Sort on the BuyerSellerIndex field and notice the range of values. ZIP Codes with index values near 0 are part of a strong seller's market; ZIP Codes with index values near 10 are part of a strong buyer's market.
3. Close the data table by clicking the close button in the upper-right corner.
  Note:
  The first time you click the Sort button , the data is sorted smallest to largest. The second time you click it, the sort order is largest to smallest. If you click a third time, all sorting is cleared and the data appears in table order.
Create a table showing the DaysOnMarket and BuyerSellerIndex values for your ZIP Code as follows:
1. Select the DaysOnMarket, BuyerSellerIndex, and ZIPString fields and drag them onto the Show Table drop zone.
  The resultant table has data for all ZIP Codes. You will filter the table to only show your home's ZIP Code.
2. Click the Card filter button at the top of the table card and select the ZIPString field.
3. Uncheck Select All and type your ZIP Code in the search window. Check the box next to your ZIP Code and click Apply.
4. Close the Card Filters dialog box. .
  Notice the average number of days you can expect it will take for your home to sell. Determine if your home is part of a buyer's or seller's market. Mark and Lisa learn that their home is part of a buyer's market (6.7), and they can expect their home to be on the market approximately 116 days before it sells.
5. Click somewhere off the table card. Then click the card name to change it to something more appropriate. Mark and Lisa named the card Crestline Days on Market and Buyer-Seller Index.
Link the Excel data to ZIP Code geometry so it can be mapped.
1. Define the geography to associate with your data by clicking Add Data, selecting Boundaries, and choosing USA_Boundaries_2015.
  Note:
  If you do not see the boundary layers, ask your system administrator to configure Insights to use them.
2. All the United States boundaries are listed. Select only the USA_ZIP_Code layer and click Add.
3. Click Dataset Options next to BuyerSellerIndex in the data pane and select Enable Location. Set the Enable Location parameters as follows:
  - Location type : Geography
  - Location fields : ZIPString
  - Matching geography level : USA_ZIP_Code
Create a map of the BuyerSellerIndex field using the following steps:
1. Change the BuyerSellerIndex field role to Rate/Ratio. The Rate/Ratio role is meant for proportions and percentages but is also appropriate for indices.
2. Since low numbers are associated with a seller's market and high numbers with a buyer's market, renaming this field SellerBuyerIndex will better match the legend. The Rename Field button allows you to do this.
3. Drag the SellerBuyerIndex field onto the Create Map drop zone and zoom the map to focus on your ZIP Code.
  Tip:
  Use the zoom tools, or press the Shift while drawing a box to quickly zoom to an area of interest.
4. Refine the map symbology as follows:
  1. Expand the map legend to open Layer Options.
  2. Click the options tab, , and change the Classification type from Natural Breaks to Equal Interval.
  3. Click the style tab, , and select a divergent Color pallet such as green to purple. The green areas are seller's markets where the seller has more bargaining power than the buyer. Similarly, the purple areas are buyer's markets where the buyer has more bargaining power.
  4. Apply a 35 percent Transparency so that you can see the city names behind the ZIP Code features. Experiment with different transparency settings to find one that appeals to you.
  5. Change the basemap if desired.
5. Give the card a more appropriate title such as Buyer Seller Index by ZIP Code.
Create a map of the DaysOnMarket field as follows:
1. Drag the DaysOnMarket field to the Create Map drop zone.
2. Enabling Sync Extents on the Buyer Seller Index map will set the extent on your new map.
3. Hover over your ZIP Code to see the average number of days it takes for homes to sell in your neighborhood.
4. Give the map a more appropriate name such as Average Number of Days Homes for Sale are on the Market. Resize the maps as appropriate.
Rename this page of your workbook something appropriate like Selling Our Home. Save the Insights workbook.

House hunting

Find all ZIP Codes within a specified drive time of important places

Create an Excel table with columns for Street, City, State, and Zip. Add addresses for the locations you want to access from your new home. Mark and Lisa's table below has their current job addresses. They named their Excel file ImportantPlaces.xlsx and the Excel sheet WorkLocations.
Add a new page to your Insights workbook and drag the Excel file onto the data drop zone. Call the new page something like Buying a New Home.
Set the geography for your addresses (click the Dataset Options button and select Enable Location). Use the following parameters:
- Location type : Address
- Geocoding service : Esri World Batch Geocoder
- Country : United Sates
- Address: Street
- City : City
- Region : State
- Postal : Zip
Drop the layer onto the Create Map drop zone. Change the basemap if desired. You may also want to change the color and size of the points by expanding the legend and modifying the values on the style tab .
Click somewhere on the card to activate the Action button . Click the Action button to open the Analytics pane. Select the Create Buffer/Drive Times tool.
For Set distance and units, select Driving Time and enter the maximum time you are willing to spend commuting from a new home to your important places. Select the Dissolve buffer style to merge overlapping polygons.
Resize the map and rename the card something like 45-Minute Drive Time Buffer from Key Locations. Rename the result layer 45-Minute Buffer by clicking Dataset Options and selecting Rename Dataset. Turn on Sync Extents . Save your work.

Map market health, home values, and projected appreciation

Download and prepare the Excel Market Health data as follows:
1. From www.zillow.com/reserach/data, download ZIP Code level Market Health Index data.
2. Open the .csv file using Excel and add the ZIPString column as a text field. Compute the values using =RIGHT(CONCAT("00000",B2),5). Drag the Autofill handle down to the last cell in the column to create text formatted ZIP Code values with leading zeros.
  
  Tip:
  You did this above for the Buyer Seller Index data. Alternatively, you can create the ZIPString field in Insights using the same formula.
3. Save the file as an Excel workbook. Close Excel.
  Mark and Lisa named their file MarketHealth.xlsx.
Drag the market health Excel file into Insights. Hide all but the City, MarketHealthIndex, ZHVI, ForecastYoYPctChange, and ZIPString fields.
Tip:
You have experience hiding fields, above, with the Buyer Seller Index data.
Click the Dataset Options button and click View Data Table. Sort the table on the ZIPString field so you can locate your ZIP Code. Make a note of the values for MarketHealthIndex, ZHVI, and ForecastYoYPctChange. In Crestline, for example, the market health index is fair: 5.2 on a scale that ranges from 0 to 10. The median home value for all homes (not just 3-bedroom homes) is $214,100. Homes are expected to appreciate 4.8 percent. Close the table.
Set the geography (Enable Location) on the MarketHealthIndex layer using ZIP Codes (matching the ZIPString field to the ZIP_Code field).
Tip:
You did this above for the Buyer Seller Index data. You must first add USA_ZIP_Code Boundaries to the data pane. You can then use Enable Location and select Geography as the Location type.
Change the field role for MarketHealthIndex, ZHVI, and ForecastYoYChange to Rate/Ratio.
Tip:
You did this above for the BuyerSellerIndex field.
Drag the entire MarketHealthIndex_ZIP.Table layer onto the Create Map drop zone.
Drag the WorkLocations legend box from the drive time buffer map onto the new map, positioning it over Filter by Selected Feature (in the lower-right corner). Run the Spatial Filter tool with the default parameters.
Note:
You may have named the Excel sheet for your important places something other than WorkLocations; you are looking for the legend box on the 45-minute drive-time buffer map to drag over to the new map.
Create a Market Health map as follows:
1. Click the options tab in Layer Options and select Style By for the MarketHealthIndex field.
2. Change the classification type from Natural Breaks to Quantile.
3. Click the Info button to see the back of the card. Notice how many ZIP Codes intersect your drive time buffer. Flip the card back to the map and give the card an appropriate name such as Market Health Index.
  The larger the index, the darker the color, and the healthier the housing market is. You will want to buy your new home in an area with a healthy housing market (rather than a location where there are vacancies, homes that aren't selling, and numerous foreclosures).
Rename the Market Health Index result (the orange layer in the data pane called either Card 1 or Market Health Index) Market Health Data within Buffer.
Tip:
Click Dataset Options and select Rename dataset.
This result has all the variables you are interested in mapping, narrowed down to the ZIP Codes that are within an acceptable drive time of your important places. Create your remaining maps from this result so that all the maps will be linked.
Expand the Market Health Data within Buffer result and drop the ZHVI field (Zillow Home Value Index) onto the Create Map drop zone. Click the legend and click options . Change the classification type from Natural Breaks to Quantile. Name the card Zillow Home Value Index.
The dark ZIP Codes have the most expensive average home value estimates.
Similarly, from the Market Health Data within Buffer result, drop the ForecastYoYPctChange field onto the Create Map drop zone. Click the Options tab in Layer options. Change the classification type from Natural Breaks to Quantile. Name the card Home Value Appreciation.
The darkest ZIP Codes are expected to have the largest increase in home values over the next year.

Find the best cities to begin house hunting

Narrow your search to ZIP Codes with home values in your price range as follows:
1. Expand the Market Health Data within Buffer result in the data pane and click the Dataset filter button associated with the ZHVI field.
2. On the Filter dialog box, set the beginning and ending ZHVI values to reflect the range of median home prices you are interested in.
  Mark and Lisa, for example, will only sell their home for a loss if they can purchase a home valued the same as theirs with better investment potential. They will look for homes that cost about $350,000 in neighborhoods with median home values between $300,000 and $600,000. Being surrounded by homes that are valued higher than yours is much better than being the most expensive home on the block. That's why Mark and Lisa extended their search to ZIP Codes with median home values as high as $600,000. You probably don't want to set the median home value vastly higher than your target home price, though, unless you are open to extreme fixer-uppers.
  Notice when the filter is applied, the map changes. ZIP Codes that don't meet the filter criteria are removed from the map and the colors change to reflect the recomputed Quantile classification.
You also want to narrow your search to areas belonging to healthy housing markets with expected home value appreciation. Mark and Lisa, for example, exclude ZIP Codes in unhealthy housing markets by filtering for MarketHealthIndex > 8. They also focus on ZIP Codes with expected home appreciation higher than Crestline by filtering for ForecastYoYPctChange > 0.060. Set filters to reflect your own criteria for the MarketHealthIndex and ForecastYoYPctChange fields.
Create a ZIP Code/City look-up table by selecting City, MarketHealthIndex, ZHVI, ForecastYoYPctChange, and ZIPString on the Market Health Data within Buffer layer. Drag these fields to the Show Table drop zone.
Resize the table as desired and change the summary statistic from SUM to AVG.
Give the card an appropriate name such as House Hunting Candidate Locations.

The results show possible cities and ZIP Codes where you can explore homes for sale. Use real estate websites such as Zillow.com or Realtor.com to see if you can find a home in your price range with the characteristics and qualities you are looking for.