Creating an Integrated Appraisal SW System in Excel

2. Creating an Integrated Appraisal SW System in Excel Alan Gertner Tejas Appraisal And Software

3. Alan Gertner Bio • Graduated from Colorado St University,BS and MS in Computer Science • In the high tech industry for 20 years, last 5 years working on a public data standard and data exchange between vendors • Appraiser in Austin, Tx for 12 years

4. What is an Integrated SW System • SW within the same tool or set of tools, work well together • Same HI and usage wherever possible • Prefer using industry standard SW, primarily Microsoft • Some separate tools may be needed • Tight integration is preferred over loose integration

5. Open System Philosophy • Plug and Play user selected tools • Allow user to select best in class tools (sketching, mapping, cost estimate, data source, etc.)

6. SW Development Issue • SW Developer should attempt to limit the creation of new SW code • The SW maintenance cost is much higher than the development cost • Leverage off existing functionality and only develop new SW when needed

7. Path to Becoming an Appraiser • Typical Appraiser path involves Trainee and Mentor • Another path is a Provisional License, no Trainee / Mentor • Provisional License possible but is a challenge and not recommended (TALCB discourages this option and is phasing it out) • Pursued Provisional License, started with nothing • Treated as an Engineering Problem, i.e. identify and evaluate the appraisal process and the required tools (software, hardware, data sources, field inspection equipment, camera, etc.)

8. Appraisal Software Analysis • Appraisal Report Creation process appeared straight forward • Found a small number of Appraisal Software companies • Quick investigation into the companies to determine viable options • Requested demo copies from several companies

9. First Appraisal SW Demo • Started with low end Appraisal SW • Created a 15 page Appraisal Report • Numerous Usability and HI issues (Subject address change requires editing each page in report, cut/paste/import photos were mangled, HI inconsistent with my other SW • After evaluation, not a viable option

10. Second Appraisal SW Demo • Popular Appraisal SW, high end • Appeared to be a heavyweight installation and usage application (required lot of resources and system mods / requirements) • Overwhelmed by the HI and the duplication with other generic SW packages on my pc • Might work, but not a desirable option

11. Third Appraisal SW Demo • Mid range Appraisal SW package • During the evaluation, I discovered the company was struggling and may cease to exist • Stopped evaluation • Not a viable option

12. Appraisal SW Evaluation Results • Numerous issues with the available Appraisal SW packages • Human Interface • Complexity • Lack of needed functionality • Heavyweight installation / usage requirements • Did not fit my requirements, only reason to consider writing an Appraisal SW System

13. Appraisal SW Requirements • Support the Appraiser workflow • Tightly integrated system which leverages off my existing software • Similar HI and usage as my other SW tools • Simple functionality with extended functionality available if needed

14. Appraisal SW Tasks • Admin, business records, bookkeeping • Manage related parties (Appraisers, Clients, etc.) • Manage Assignments (everything related to an assignment) • Create Appraisal Report (Std Forms, Forms Library, Sketch, Maps, Photos, Import Data, customizable) • Deliver Report • Archival

15. Professional Appraisal Report Contents • Cover page • Table of Contents • Cover Letter • Std Form (1004, 1004C, 2000, etc) • 1004MC • Appraiser Comments • Map • Sketch • MLS Listing History • Photos (Subject, Comp, Field Inspection) • Misc (Plat, Survey, Flood Map, Aerial View, etc)

16. Appraisal Report Contents • See Example

17. Appraisal SW Candidate • Combination of MS Office Tools • Access / Word/ Excel • Already have on pc • May be a viable option • How tightly integrated are they? • May be too complicated and overkill for my usage

18. Appraisal SW Candidate • MS Word • Should work well for report creation • Not sure about other requirements • May be able to use with another product to obtain required features • Not sure if best option

19. Appraisal SW MS Word Issues • While attempting to use MS Word for Appraisal SW, I quickly realized MS Word is text centric and an Appraisal Report is data centric • Text centric, primarily text • Data centric, primarily data points • MS Word does not work well for an Appraisal Report with many data points

20. Appraisal SW Candidate • MS Excel • Excel is data centric rather than text centric • Text processing is available but has limitations • Can be used as a simple db • Has drawing capabilities but no 2d sketch • May be a viable option

21. MS Excel vs Appraisal SW Requirements • Administration Yes • Manage Parties Yes • Manage Assignment Yes (folder) • Create Report ??? • Deliver Report ??? • Archive Yes (backup)

22. Excel Appraisal SW Design Overview Data Source (MLS) Std Forms Customized Forms VBA Appraiser Report Creation Admin Manager Assignment Instance Assignment Report Delivery Client Misc. CAD, Map, Aerial, etc. Install Init Cost Data Sketch

23. Tejas Appraisal SW Folder Hierarchy C:/appraisal_1_0_1 main folder appraisal_manager.xlsm appraisal_install_option.xlsm addin Excel VBA addins db data files use by appraisal sw excel_forms_dbstd and customized forms new_assignment_init customized report templates pending_assignment misc. notes on possible assignments active active assignments archive completed assignments

24. Tejas Appraisal SW Folder Hierarchy • See Examples

25. Excel Appraisal Report Creation • Significant challenge • This is a “must have” requirement for an Appraisal SW System • This challenge had to be overcome for Excel to be a viable option

26. Excel Appraisal Report Creation Requirements • Create Report templates • Create library of items to add to a report • Able to insert library items into a report • Library item formulas must be updated after insertion into report • Allow user customizations • Create report to be delivered to the client

27. Tejas Appraisal Report Creation • All the Tejas Appraisal Reports are considered narrative reports. • Reports are created from scratch. • Reports may include GSE standard forms such as 1004, 1004C, 2000, etc.

28. Forms Library • Standard Forms1004, 1004C, 2000, etc.Need Fannie Mae approval, submit samplesFreddie Mac approval is not requiredERC (Employee Relocation Council) form requires license and fee to use • User must be able to created customized forms and load into reports later

29. Excel Forms Library Implementation Notes • VBA forms were considered for forms library Increase complexity and costs • VBA forms were not needed for many itemsEasier and more efficient to use worksheet Use cells, name references and formulas • Leverage off existing functionality

30. Tejas Appraisal Sketch Package • Initial implementation used an external popular 2d Sketch package • Several problems with the external 2d Sketch package: HI was different Lot of functionality, but difficult to use for the infrequent user Sketch was a large image file and fuzzy

31. Tejas Appraisal Native Sketch • Second implementation was a native sketch package built on top of Excel drawing functions • Excel drawing objects may be customized • Simple with advanced features available if needed partition and label sketch build sketch from smaller blocks, then combine eraser temp measuring stick available cloak of invisibility

32. Tejas Appraisal SW Sketch • See Examples • Most complex sketch drawn was a circular house in the shape of an Archimedean spiral

33. Tejas Appraisal Cost Approach • Cost data from any source may be used • Select cost options and perform table lookups • Uses Excel cells, names and lookup functions • Improved Cost Estimate Form 1007 • Craftsman National Building Cost ManualApprox $30 / yr for pdf version 2-3 hours / yr to update the Excel cost data

34. Tejas Appraisal Cost Approach • See examples

35. Local MLS Issues • MLS data is Appraiser’s primary data source • Desire to import MLS data into Appraisal SW • Local MLS staff are not knowledgeable • MLS changes are not disclosed • Appraiser must work around MLS issues • Appraiser must understand MLS and Appraisal SW • Considered using RETS (R.E. Transaction Std) MLS Staff hostile to this approach Worried stealing MLS data

36. MLS Import Issues • MLS Data must be normalized to Appraisal SW data model • MLS field names may change without notice Current Days on Market -> CDOM • MLS field values may change without notice “T” becomes “True” • MLS field default values and missing fields blank vs space, required field is missing

37. MLS Import Issue • See examples

38. Appraisal Report XML Creation • GSE UCDP Requirement • XML is a text file with Header info, Subject info and Comp info • Tejas SW uses two worksheets and VBA to create XML Header and Subject info Comp info, use loop and indirect references • Small VBA function to read the worksheets and write to xml file

39. XML Creation • See examples

40. Excel 2010 Notes • Excel 2010 not as stable as Excel 2003 • Excel 2010 may be slower than Excel 2003 Many more rows x columns 65k x 256 to 1 mil x 16k • Many Excel Names may cause slow performance, primarily deleting or redefining names, workaround it to turn off Excel AutoCalc during large operations

41. Excel 2010 Notes • File size may become huge if many unused rows/columns are included in worksheet 1 mb -> 40 mb with 65k rowsUse <ctl> End to find the last used cell in WS • Worksheet display scaled < 100% may impact other WS items such as cell drop down menu button (shrinks) and VBA AddPicture(picture loaded incorrectly, workaround is Select WS in VBA first)

42. Excel 2010 Notes • Formula window is 1 line and does not auto expand, have to manually expand and shifts the worksheet down, Excel 2003 automatically adjusted the formula window based on the info typed • PropertyDB VBA update hangs (also in Excel 2003) Either a bug in Excel VBA or in my codeWorkaround is set VBA dbugger breakpoint and continue

43. Excel Useful Features • Cell Data Validation Define list of values Specify the list as the valid values for a cell Creates a drop down menu for the cell • Conditional Formatting Highlight cells that meet certain condition(s) • Highlight items of interest in report • INDIRECT, MATCH and INDEX functions table lookup, useful with cost data and XML creation

44. Excel 2010 Excel Addins and Utilities • Data Form Editor (better than Excel built in) • JKP Name Manager for Excel cell names • See Examples

45. Useful Misc Tools • Custom UI Editor for MS Office Create ribbon XML commands • XML Notepad View XML file Use MISMO .xsd to validate field values UCDP validation tool may identify other issues • Diffpdf, compare pdf files • See examples

46. MS Excel Resources • Online forums, search web • Search web for specfic Excel issues

47. GSE UCDP Issues • Many Appraisal Reports must be submitted to UCDP and pass the GSE UCDP validation tool • GSE and UCDP staff limit their support to their selected vendors • If you contact the UCDP staff, they do not want to talk to you if you are not on their selected vendor list

48. GSE UCDP Issues • GSE’s claim Lenders will assist Appraiseers and Appraisal SW Companies to test with UCDP and resolve issues. • GSE’s cannot identify any Lender who will do this. • GSE’s continue to make this claim even thought they know it is not accurate.

49. GSE UCDP Issue • GSE’s and the GSE selected Appraisal SW Vendors claim the UCDP / UAD / MISMO specification documents are sufficient for Appraisal SW Developers to comply with the GSE UCDP requirements • The GSE selected Appraisal SW vendors are unable to comply with the GSE UCDP requirements using the documents • Require GSE UCDP testing and support

50. GSE UCDP Issues • GSE’s have not provided any publicly available mechanism for the Appraisal User Community to determine if an Appraisal Report XML file will pass the UCDP validation test. • At the same time, the GSE’s will begin blacklisting Appraisers for non – compliance • GSE’s have created a similar publicly available test web site for the ULDD https://www.fanniemae.com/singlefamily/loan-delivery-test-environment