1 / 63

Creating an Integrated Appraisal SW System in Excel

Creating an Integrated Appraisal SW System in Excel. Alan Gertner Tejas Appraisal And Software. Alan Gertner Bio. Graduated from Colorado St University, BS and MS in Computer Science

cyrah
Download Presentation

Creating an Integrated Appraisal SW System in Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

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

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

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

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

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

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

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

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

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

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

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

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

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

  15. Appraisal Report Contents • See Example

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

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

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

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

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

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

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

  23. Tejas Appraisal SW Folder Hierarchy • See Examples

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

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

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

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

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

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

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

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

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

  33. Tejas Appraisal Cost Approach • See examples

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

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

  36. MLS Import Issue • See examples

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

  38. XML Creation • See examples

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

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

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

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

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

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

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

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

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

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

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

More Related