1-4 Family Mortgage Pricing Engine
I conceptualized and developed the bank’s pricing engine during a vacation break because I could envision how awesome this pricing tool would be for the pricing desk, each loan officer, and for loan compliance review documentation purposes. I later added the capability to compare products from 5 separate investors in a single click highlighting the best priced provider for each loan proposal or scenario.
The pricing engine enabled the financial institution to provide consistent, accurate and uniform rate quotes to all borrowers, review details of each loan quote made by each lender, calculate 1-4 family real estate rates at each price change for corporate websites and daily rate sheets, refine or change pricing rapidly to take advantage of the opportunities and the competition, print and post pricing optimized daily (periodic) rate sheets, generate input files for website pricing and save rate sheets to web and to internal networks.
The pricing engine took basic mortgage information (loan amount LTV, CLTV, type of transaction, etcetera) from the loan officer that they enter during a conversation with a prospective borrower, and quickly provided an array of comparative pricing information for up to 5 approved investors. The program highlights (in color) the best-priced provider overall for that loan product, and the best rate options from each investor for that specific product.
Security provisions were put into place that allowed the Price Desk Manager to quickly announce upcoming price change, lock-out the ability to make loan commitments until new investor data is processed, which is very important when the market is changing rapidly. The application was locked-down and its use was restricted to authorized users, and if a network user was not on a preapproved list, the application would display a notification message and close. It would not to run if it was not hosted on the Hills Bank network, the VBA code was locked from view or modification.
Residential Real Estate Market Share Web Site Scraping and Data Analysis
I browsed the Recorder’s web site and found a database of recently recorded mortgages consisting of many pages. I manually scraped the data from the website while recording a macro and populated it into an Excel spreadsheet, parsed and cleaned it up. I later programmed an Excel VBA application that automated the gathering of the data from the 2 county Recorder offices and summarized the dollar and unit share information and created ordered and sorted lists of “all” lender activity in 2 seperate counties.
“Sales” Closed Loan Income & Earnings Tracking System
I reviewed the competition’s compensation programs. Developed an Excel model of alternatives and performed Sensitivity Analysis to demonstrate the impact given extreme variations in the marketplace. Then, using VBA for Excel, programmed a Loan Officer Income and Incentive Tracking Reporting System which automated the process of monthly income reporting. It ensured accuracy, privacy and security for compensation and earnings by posting to secured network server directories and to individual loan officer Microsoft SharePoint sites.
This sample below does not do justice to the sophistication of the application which provided many reports and features unable to be shared here. Each time lender compensation was calculated, data was extracted from the LOS and various detailed reports were provided to each loan officer, operations, HR department, upper management and auditors by writing time stamped reports directly to MS SharePoint intranet sites. Loan officers and other parties were automatically emailed links to their secure sites. The reports and data were all secured documents that could not be opened when an authorized user was not logged into the company network.
Open House Worksheet (Traditional and 100% financing)
I programmed the Side-by-Side “Open House Worksheet” (Traditional and 100% financing) a proposed mortgage financing application. It contained 102 pages of Sheet and Module coding, and used worksheet cell values and VBA Data Arrays; not for the faint of heart. The APR, Good Faith Estimate and Truth-in lending documents and calculations were verified as accurate by internal, state and federal examiners.
QuanTech Statistical Analysis Applications
In the early years of My career, after 2 years in the PhD program, I started a technology-based business that focused on providing quantitative applications for businesses using various computer technologies. Hence, QuanTech was founded. I produced DOS-based financial software applications for the banking industry using compiled automated Lotus 1-2-3 executables. Later, I wrote PC-based quality assessment software for the automatic identification industry using Basic and later, Visual Basic. QuanTech also performed bar code quality assessment services for major global corporations; Gillette, Miller Brewing, Honda of America and others. I learned to develop in Excel VBA and then translated my Visual Basic banking applications and code into Excel VBA applications.
Automobile Dealership Operations Application
I co-founded a local car dealership and served as C.E.O. and registered agent for the company. I developed automobile dealership software using Excel VBA to automate the onboarding, managing, online marketing, sales process and the documentation of vehicles… allowing him to focus on financial management aspects including cash management, accounts receivables, payables, licensing and payroll. The company sold over 250 vehicles per year, and had two full-time mechanics employed among others people. I turned the day-to-day operations over to my partner and assumed a silent partner role when hired as a Vice President at a community bank.
Loan Origination Software (LOS) Reports and Reporting
The mortgage LOS software had limitations that made it difficult to adapt to new rules and the changing lending environment. LOS and reports did not accurately reflect real-life activity and statuses. “Loans in process” (LIP) reports, statuses and upcoming closing lists were inaccurate. Shortly thereafter, RESPA (MDIAII) regulations changed requiring mortgage lenders to send mortgage re-disclosures to clients when the APR on their loans changed by an eighth (.125%) after application for the loan.
I utilized SAP Crystal Reports to clean-up the LOS database, identify loan progress anomalies as they occurred and cleaned-up, filtered, and corrected problem data. Once the data integrity improved, I wrote Excel VBA applications and scheduled nightly data reports be delivered to specified users and department email addresses. I programmed multiple Excel VBA applications to calculate and monitor changes in APR rates for loans in the pipeline, report loan activity, calculate “Close-By” using holiday schedules and day of week, and create alerts when RESPA MDIAII parameters were exceeded and re-disclosure was required.
Internet of Things
On Thanksgiving in 2019, I decided to learn how to program Internet of Things (IoT) smart phone applications. I conceptualized and prepared a long list of functions I thought would be useful for monitoring and controlling animal habitats. With this goal in mind, I purchased sensors, relays, motors and LCD displays panels and learned how to wire and write commands to read sensors and control replays and stepper motors.
I tested, refined and completed the task by February. The animal habitat IoT system performed the following functions: It monitored exterior door, screen door, feed door and outdoor run gate positions. It monitored water-level and food-level statuses, interior & exterior temperature, humidity and brightness with the smart phone or manual controls. Users could control and operate the fan, heater, interior lights, exterior lights and an LED night light, and open, close and stop the animal run door.