Asset Valuation Alert System for Real Estate & Securities Investments

One of the most frequent requests I get as a data modeler is to integrate external unstructured "Big Data" with internal asset management databases. A billionaire bank CEO once stated in a meeting of IT professionals, senior IT management, and the CIO, "I need to know what the hell is happening to increase or decrease the value of our assets BEFORE it happens!" He was looking directly at me, the data modeler. In my mind, I thought, "Predictive analysis," right after I thought, "Oh.. Sh**."

Clearly, if an IT professional lands in a meeting with the CEO of a major bank, something has already gone wrong; big time. After that meeting, people were shaking my hand saying, "It was nice meeting you," with a sad watery look in their eyes as if I was on my way out, three days after being hired. Since both the CIO and CEO knew me by name before they had actually met me, I assumed that I was indeed in trouble. I wondered if I had been hired to be the scapegoat fall-guy for whatever had happened.

Fortunately, that bank had one of the best business analysts I have ever seen. She had painstakingly collected and archive every requirement for every project the bank had ever had, including the projects that had executed before she was hired. She was known for corning people and asking very detailed questions. Co-workers would often react to her as if they had seen a snake on the floor. She and I had been moved directly under the CIO with two scary titles, SVP of Business Analytics and SVP of Data Governance, to prevent anyone from derailing the data governance project.

Our analysis and design meetings were notorious for being tedious, so we limited each meeting to one hour to address specific collections of specific requirement in specific areas, one area at a time. We prepared questions to ask before each meeting and left each meeting with a list of assigned action items. Most co-workers seemed to tend to withhold and compartmentalize data. The Business analyst had a way of staring people down in silence until they "spit out" whatever they were hiding. It was always something critical to the way the bank operated. I found that there was a mountain of hidden requirements locked in the minds of employees. Each requirement had drastic effects on the structure of the data model.

I also later found out that someone had calculated that the bank was losing over three billion dollars per year due to bad data issues and what was termed "data inefficiencies." In the words of the CEO, "I needed that god damned data a month ago and I just got it five minutes ago!" The bank also had issues with court ordered data that they could not find and they received millions in fines just because they could not find data a court ordered them to produce.

Very fortunately, the business analyst was very good at extracting information and organizing it into business requirements documents. Unfortunately, some of the older projects were documented only on paper, which filled about half my office by the time she finished loading me up with requirements. From there, we created an OCR scanning process to digitize the paper and create one big master copy of requirements for the entire bank.

Once I had a big picture of the requirements, I exhaled and smiled. The business analyst asked, "What are you smiling about?" "This is simple," I said. I explained to her that we didn't have to reinvent the entire bank system to get asset valuation alerts working. All of the assets were already well documented, I just had to create a multi-dimensional data model, use it as a data mart, and import the data from the old relational master data system to the new system using ETL, extract transform and load. Then, I explained, that we simply had to create a star in the system of extended star schema for each area, type of big data, that affects asset valuation and connect specific asset types to the specific types of big data. "That doesn't sound simple to me," she replied. "Good luck. Call me if you have any questions," my door clicked shut as she left.

I knew I only had about twenty working days to show some results. So, I created a multi-dimensional asset management data model, then created a section for tracking permits and permit applications, one of the major areas that unexpectedly affects real estate assets. If your asset management system tracks permits and finds that someone is building a paper mill, oil refinery, or heaven forbid an apartment building next door to your multi-million dollar luxury investment property, the market value will go down. I also created sections for legal zoning change logs, historical sales by location, and subsidiary sales, a situation in which a bank sells a property to itself or a subsidiary, usually to artificially raise the price and valuation of a property. I also included other areas that affect valuation, such as materials used in construction, historical sales, etc.

However, the subsidiary sales section proved most useful for calculating actual versus artificially inflated sale prices and profit margins. Once I had the data model complete, imported data, ran a web spider to collect data from the Internet, and government sources for permits and zoning, the atomic data was easily used to predict rises and falls in valuations BEFORE they happened; predictive analysis.

However, the bank CEO used the software in ways I had not expected nor intended. He used the software to track zoning and permits primarily, then used his influence to either promote or stop zoning changes and permits to protect the value of bank owned property or to deflate and later inflate the value of property the bank wanted to acquire. He also tracked what other banks did with internal subsidiary sales to get the real value of property before buying property form another bank or approving loans based on sale history. Soon, senior loan officers had to determine the real value of the property, but in many cases pass the inflated part of the value to the buyers in order to make higher profits from interest.

Unfortunately, in many cases, buyers buying homes with inflated values received slightly lower interest rates to "sweeten the deal," which made them spend less time dong research before deciding to sign the mortgage agreement. The CEO was also able to set the system to ignore subsidiary sales and focus on external sales to get the real value of commercial property as well. He could then calculate the real time on market figures. Some banks sell properties to themselves to hide long time on market figures which decreases the marketable value of property.

The best feature of the software was the data driven alerts that automatically fired emails to anyone subscribed to a particular list when new data arrived suggesting an effect on asset valuation. The alerts were also configured to show on an IBM Cognos dashboard upon login.

Once everyone was satisfied with my first success, I received handshakes welcoming me to the company. I was then told to do the same thing with other types of assets with the aid of their business analyst and financial analysts. I felt a little guilty, but in the back of my mind I thought, "What if everyone had access to that kind of data before purchasing real estate and securities?" I wondered if the thought was a foreshadowing of a future startup.

Thank you for reading my article and may your data always have integrity.

Hanabal Khaing

Enterprise Data Modeler, Data Governance VP