Linkedin    Twitter   Facebook

Get Started
Log In

Linkedin

Articles Tagged with: Data Management
Home / Blog Archives

The newest, fastest and easiest way to access and analyze Agency MBS data

TL;DR Summary of Benefits

  • Data normalization and enhancement: RiskSpan’s MBS data on Snowflake normalizes Fannie, Freddie, and Ginnie loan-level data, consolidating everything into one set of field names. It also offers enhanced loan level-data fields, including current coupon, spec pool category, and mark-to-market LTV, which are not available in the raw data from the agencies. The data also includes pool-level factors like pool prefix and pool age, as well as full loan histories not available from the GSEs directly.
  • Data access and querying: Users access the data in Snowflake using SQL or Python connectors. Snowflake functions essentially as a cloud SQL server that allows for instantaneous data sharing across entities. In just a few clicks, users can start analyzing MBS data using their preferred coding language—no data, ETL, or IT Teams required.
  • Data merging and analytics: Users can merge the data in Snowflake with other available loan level or macroeconomic data, including interest rates, home prices, and unemployment, for advanced analytics. Users can also project performance, monitor portfolios, and create spec pools, among other features.
CHECK OUT RISKSPAN’S OFFERING ON SNOWFLAKE MARKETPLACE

The Problem

Even though Fannie, Freddie and Ginnie have been making MBS performance data publicly available for years, working with the raw data can be challenging for traders and back-office analysts.

Traders and analysts already have many of the tools they need to write powerful queries that can reveal hidden patterns and insights across different markets – patterns that can reveal lucrative trading opportunities based on prepayment analysis. But one big obstacle often stands in the way of getting the most out of these tools: the data from the agencies is large and unwieldy and is not formatted in a consistent way, making it hard to compare and combine.

What’s more, the Agencies do not maintain full history of published data on the websites for download. Only recent history is available.

The Solution: RiskSpan’s new MBS loan-level historical offering on Snowflake Marketplace

Using RiskSpan’s new MBS Loan-Level Historical Data Offering, MBS traders and analysts can now leverage the power of Snowflake, the leading cloud data platform, to perform complex queries and merge data from multiple sources like never before.

This comprehensive data offering provides a fully normalized view of the entire history of loan-level performance data across Agencies – allowing users to interact with the full $9T Agency MBS market in unprecedented ways.

A list of normalized Fannie and Freddie fields can be found at the end of this post.

In addition to being able to easily compare different segments of the market using a single set of standardized data fields, MBS traders and analysts also benefit from derived and enhanced data, such as current coupon, refinance incentive, current loan-to-value ratio, original specified pool designation, and normalized seller and servicer names.

The use cases are practically limitless.

MBS traders and analystscan track historical prepayment speeds, find trading opportunities that offer relative value, and build, improve, or calibrate prepayment models. They can see how prepayment rates vary by loan size, credit score, geographic location, or other factors. They can also identify pools that have faster or slower prepayments than expected and exploit the differences in price.

Loan originators can see how their loans perform compared to similar loans issued by other originators, servicers, or agencies, allowing them to showcase their ability to originate high-quality loans that command premium pricing.

Enhanced fields provide users with more comprehensive insights and analysis capabilities. They include a range of derived and enhanced data attributes beyond the standard dataset: derived fields useful for calculations, additional macroeconomic data, and normalized field names and enumerations. These fields give users the flexibility to customize their analyses by incorporating additional data elements tailored to their specific needs or research objectives.

Enhanced loan-level fields include:

  • Refi Incentive: The extent to which a borrower’s interest rate exceeds current prevailing market rates
  • Spread at Origination (SATO): a representation of the total opportunities for refinancing within a mortgage servicing portfolio. SATO encompasses all potential refinance candidates based on prevailing market conditions, borrower eligibility, and loan characteristics
  • Servicer Normalization: A standardization of servicer names to ensure consistency and accuracy in reporting and analysis
  • Scheduled Balance: A helper field necessary to easily calculate CPR and other performance metrics
  • Spec Pool Type: A designation of the type of spec story on the loan’s pool at origination
  • Current LTV: a walked forward LTV based on FHFA’s HPI and the current balance of the loan

Not available in the raw data from the agencies, these fields allow MBS traders and analysts to seamlessly project loan and pool performance, monitor portfolios, create and evaluate spec pools, and more.

Access the Data on Your Terms

Traders and analysts can access the data in Snowflake using SQL or Python connectors. Alternatively, they can also access the data through the Edge UI, our well-established product for ad hoc querying and visualization. RiskSpan’s Snowflake listing provides sample queries and a data dictionary for reference. Data can be merged with macroeconomic data from other sources – rates, HPI data, unemployment – for deeper insights and analytics.

The listing is available for a 15-day free trial and can be purchased on a monthly or annual basis. Users don’t need to have a Snowflake account to try it out. Learn more and get started at the Snowflake Marketplace or contact us to schedule a demo or discussion.

CHECK OUT RISKSPAN’S OFFERING ON SNOWFLAKE MARKETPLACE

Fannie/Freddie Normalized Fields

NAMETYPEDESCRIPTION
AGENumberLoan Age in Months
AGENCYVarcharFN [Fannie Mae], FH [Freddie Mac]
ALTDQRESOLUTIONVarcharPayment deferral type: CovidPaymentDeferral,DisasterPaymentDeferral,PaymentDeferral,Other/NA
BORROWERASSISTPLANVarcharType of Assistance: Forbearance, Repayment, TrialPeriod, OtherWorkOut, NoWorkOut, NotApplicable, NotAvailable
BUSINESSDAYSNumberBusiness Day in Factor Period
COMBINEDLTVFloatOriginal Combined LTV
CONTRIBUTIONFloatContribution of Loan to the Pool, to be used to correctly attribution Freddie Mirror Pools
COUPONFloatNet Coupon or NWAC in %
CURRBALANCEFloatCurrent Balance Amount
CURRENTCOUPONFloatPrimary rate in the market (PMMS)
CURRENTLTVFloatCurrent Loan to Value Ratio based on rolled-forward home value calculated by RiskSpan based on FHFA All-Transaction data
CURTAILAMOUNTFloatDollar amount curtailed in the period
DEFERRALAMOUNTFloatDollar amount deferred
DQSTRINGVarcharDelinquency History String, left most field in the current period
DTIFloatDebt to Income Ratio %
FACTORDATEDatePerformance Period
FICONumberBorrower FICO Score [300,850]
FIRSTTIMEBUYERVarcharFirst time home buyer flag Y,N,NA
ISSUEDATEDateLoan Origination Date
LOANPURPOSEVarcharLoan Purpose: REFI,PURCHASE,NA
LTVFloatOriginal Loan to Value Ratio in %
MATURITYDATEDateLoan Maturity Date
MICOVERAGEFloatMortgage Insurance Coverage %
MOSDELINQVarcharDelinquency Status: Current, DQ_30_Day, DQ_60_Day, DQ_90_Day, DQ_120_Day, DQ_150_Day, DQ_180_Day, DQ_210_Day, DQ_240_Day, DQ_270_Day, DQ_300_Day, DQ_330_Day, DQ_360_Day, DQ_390_Day, DQ_420_Day, DQ_450_Day, DQ_480_Day, DQ_510_Day, DQ_540_Day, DQ_570_Day, DQ_600_Day, DQ_630_Day, DQ_660_Day, DQ_690_Day, DQ_720pls_Day
MSAVarcharMetropolitian Statistical Area
NUMBEROFBORROWERSNumberNumber of Borrowers
NUMBEROFUNITSVarcharNumber of Units
OCCUPANCYTYPEVarcharOccupancy Type: NA,INVESTOR,OWNER,SECOND
ORIGBALANCEFloatOriginal Loan Balance
ORIGSPECPOOLTYPEVarcharSpec Story of the pool that the loan is a part of. Please see Spec Pool Logic in our linked documentation
PERCENTDEFERRALFloatPercentage of the loan balance that is deferred
PIWVarcharProperty Inspection Waiver Type: Appraisal,Waiver,OnsiteDataCollection, GSETargetedRefi, Other,NotAvailable
POOLAGENumberAge of the Pool
POOLIDVarcharPool ID

Transforming Loan Data Management Using Snowflake Secure Data Sharing

Presenters

Paul Gross

Senior Quantitative Analyst, Rithm Capital

Michael Cowley

Principal, Data Cloud Products, Snowflake

Bernadette Kogler

CEO, RiskSpan

Suhrud Dagli

CTO, RiskSpan

Wednesday, May 29th, 2024

1:00 ET

Hear from a distinguished panel including RiskSpan and Snowflake customers as they describe how Data Share has transformed their approach to mortgage investment. Specific topics to include:

  • High-speed data processing using Snowflake for easy delivery of risk analytics and diligence data
  • How Snowflake’s Data Sharing facilitates data access across and between organizations while maximizing computational performance and flexibility 
  • How Snowflake protects client data
  • The unique value of a central hub for all mortgage industry data and never having to FTP a file again

watch recording

Snowflake and the Future of Data Sharing Across Financial Institutions

The digitization of the financial services industry has opened countless doors to streamlining operations, building customer bases, and more accurately modeling risk. Capitalizing on these opportunities, however, requires financial institutions to address the immense data storage and sharing requirements that digitization requires.  

Recognizing this need, Snowflake has emerged as an industry-leading provider of cloud-computing services for the financial industry. According to estimates, some 57 percent of financial service companies in the Fortune 500 have partnered with Snowflake to address their data needs.1 In this article, we highlight some of Snowflake’s revolutionary data sharing capabilities that have contributed to this trend and RiskSpan’s decision to become a Snowflake partner.     

Financial institutions contemplating migration to the cloud are beset by some common concerns. Chief among these are data sharing capabilities and storage costs. Fortunately, Snowflake is well equipped to address both. 

Data Sharing Between Snowflake Customers

Ordinarily, sharing information across institutions inflates storage costs and imposes security and data integrity concerns.  

Snowflake’s Secure Data Sharing eliminates these concerns because no physical data transfer occurs between accounts. When one Snowflake customer desires to share data with another Snowflake customer, a services layer and metadata store facilitate all sharing activities. As a result, shared data does not occupy any storage in the institution consuming the data, nor does it impact that institution’s monthly data storage expenses. Data consumers are only charged for the compute resources, such as virtual warehouses, they use to query the shared data.  

The setup for Secure Data Sharing is streamlined and straightforward for data providers, while consuming institutions can access shared data almost instantaneously.   

Organizations can easily: 

  • Establish a share from a database within their account, granting access to specified objects within that database.  
  • Share data across multiple databases, provided all databases are under the same account.  
  • Add, remove, and edit access for all users. 

Data Sharing with Non-Snowflake Customers

For institutions desiring to share data with non-Snowflake customers, Snowflake offers an alternative secure data sharing method, known as a “reader account.” Reader accounts offer an efficient and cost-effective solution for data sharing without requiring consumers to register for Snowflake. They are associated exclusively with the provider’s account that established them. Data providers share databases with reader accounts, but each reader account can only access data from its originating provider account. Individuals using a reader account can perform queries on shared data but are restricted from carrying out DML operations, such as data loading, insertions, updates, and other data manipulations. These accounts serve as cost-effective solutions for organizations seeking to limit the number of more expensive user profiles. 

Secure Sharing with Data Clean Rooms

Clean room managed accounts are another way for Snowflake customers to share data with non-Snowflake customers. Data clean rooms are created by data providers to avoid privacy concerns when sharing their data. This is accomplished by allowing data consumers to compile aggregated results and analysis without permitting access to query the original raw data. Data providers can granularly control how their data is accessed and the types of analysis that can be run using their data. The data is encrypted and uses differential privacy techniques for further protection.   

How Can RiskSpan Help?

Knowing that you want to be on Snowflake isn’t always enough. Getting there can be the hardest part, and many organizations face challenges migrating from legacy systems and lack the expertise to fully utilize new technology after implementation. RiskSpan has partnered with numerous companies to help guide them towards a sustainable framework that holistically addresses all their data needs. No matter where the organization is within their data journey, RiskSpan has the expertise to help overcome the challenges associated with the new technology.    

RiskSpan is equipped to help institutions with the following as they embark on their Snowflake migration journey: 

  • End-to-end migration services, including architecture design, setting up the Snowflake environment, and properly validating the new platform.   
  • Adaptive project management. 
  • Data governance including the creation of a data catalog, tracing data lineage, and compliance and security requirements. 
  • Establishing data warehouses and data pipelines to facilitate collaboration and analysis. 
  • Creating security protocols including role-based access controls, disaster recovery solutions, and ensuring the utmost protection of personally identifiable information.   
  • Optimizing extract, transform and load solutions   

Snowflake’s data sharing capabilities offer an innovative solution for businesses looking to leverage real-time data without the hassle of traditional data transfer methods. These features not only enhance operational efficiency but also provide the scalability and security necessary for handling extensive datasets in a cloud environment.

Contact us with any questions or to discuss how Snowflake can be tailored to your specific needs.

CONTACT US

Connect with us at SFVegas 2024

Click Here to book a time to connect

RiskSpan is delighted to be sponsoring SFVegas 2024!

Connect with our team there to learn how we can help you move off your legacy systems, streamline workflows and transform your data.

SFA-Attendees
Click Here to book a time to connect

Don’t miss these RiskSpan presenters at SFVegas 2024

Bernadette Kogler

Housing Policy:
What’s Ahead
Mon, Feb 26th, 1:00 PM

Tom Pappalardo

Future of Fintech
Wed, Feb 28th, 9:15 AM

Divas Sanwal Photo (3)

Divas Sanwal

Big Data & Machine Learning: Impacts on Origination
Wed, Feb 28th, 11:05 AM

Can’t make the panels?

Click here to make an appointment to connect. Or just stop by Booth 13 in the exhibit hall!

Snowflake Tutorial Series: Episode 3

Using External Tables Inside Snowflake to work with Freddie Mac public data (13 million loans across 116 fields)

Using Freddie Mac public loan data as an example, this five-minute tutorial succinctly demonstrates how to:

  1. Create a storage integration
  2. Create an external stage
  3. Grant access to stage to other roles in Snowflake
  4. List objects in a stage
  5. Create a format file
  6. Read/Query data from external stage without having to create a table
  7. Create and use an external table in Snowflake

This is the third in a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.

Episode 1, Setting Up a Database and Uploading 28 Million Mortgage Loans, is available here.

Episode 2, Using Python User-Defined Functions in Snowflake SQL, is available here.

Future topics will include:

  • OLAP vs OLTP and hybrid tables in Snowflake
  • Time Travel functionality, clone and data replication
  • Normalizing data and creating a single materialized view
  • Dynamic tables data concepts in Snowflake
  • Data share
  • Data masking
  • Snowpark: Data analysis (pandas) functionality in Snowflake
TALK TO AN EXPERT

RiskSpan’s Snowflake Tutorial Series: Ep. 2

Learn how to use Python User-Defined Functions in Snowflake SQL

Using CPR computation for a pool of mortgage loans as an example, this six-minute tutorial succinctly demonstrates how to:

  1. Query Snowflake data using SQL
  2. Write and execute Python user-defined functions inside Snowflake
  3. Compute CDR using Python UDF inside Snowflake SQL

This is this second in a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.

Episode 1, Setting Up a Database and Uploading 28 Million Mortgage Loans, is available here.

Future topics will include:

  • External Tables (accessing data without a database)
  • OLAP vs OLTP and hybrid tables in Snowflake
  • Time Travel functionality, clone and data replication
  • Normalizing data and creating a single materialized view
  • Dynamic tables data concepts in Snowflake
  • Data share
  • Data masking
  • Snowpark: Data analysis (pandas) functionality in Snowflake
TALK TO AN EXPERT

RiskSpan Adds CRE, C&I Loan Analytics to Edge Platform

ARLINGTON, Va., March 23, 2023 – RiskSpan, a leading technology company and the most comprehensive source for data management and analytics for mortgage and structured products, has announced the addition of commercial real estate (CRE) and commercial and industrial (C&I) loan data intake, valuation, and risk analytics to its award-winning Edge Platform. This enhancement complements RiskSpan’s existing residential mortgage toolbox and provides clients with a comprehensive toolbox for constructing and managing diverse credit portfolios.

Now more than ever, banks and credit portfolio managers need tools to construct well diversified credit portfolios resilient to rate moves and to know the fair market values of their diverse credit assets.

The new support for CRE and C&I loans on the Edge Platform further cements RiskSpan’s position as a single-source provider for loan pricing and risk management analytics across multiple asset classes. The Edge Platform’s AI-driven Smart Mapping (tape cracking) tool lets clients easily work with CRE and C&I loan data from any format. Its forecasting tools let clients flexibly segment loan datasets and apply performance and pricing assumptions by segment to generate cash flows, pricing and risk analytics.

CRE and C&I loans have long been supported by the Edge Platform’s credit loss accounting module, where users provided such loans in the Edge standard data format. The new Smart Mapping support simplifies data intake, and the new support for valuation and risk (including market risk) analytics for these assets makes Edge a complete toolbox for constructing and managing diverse portfolios that include CRE and C&I loans. These tools include cash flow projections with loan-level precision and stress testing capabilities. They empower traders and asset managers to visualize the risks associated with their portfolios like never before and make more informed decisions about their investments.

Comprehensive details of this and other new capabilities are available by requesting a no-obligation demo at riskspan.com.

### 

About RiskSpan, Inc. 

RiskSpan offers cloud-native SaaS analytics for on-demand market risk, credit risk, pricing and trading. With our data science experts and technologists, we are the leader in data as a service and end-to-end solutions for loan-level data management and analytics.

Our mission is to be the most trusted and comprehensive source of data and analytics for loans and structured finance investments. Learn more at www.riskspan.com.

RiskSpan Incorporates Flexible Loan Segmentation into Edge Platform

ARLINGTON, Va., March 3, 2023 — RiskSpan, a leading technology company and the most comprehensive source for data management and analytics for residential mortgage and structured products, has announced the incorporation of Flexible Loan Segmentation functionality into its award-winning Edge Platform.

The new functionality makes Edge the only analytical platform offering users the option of alternating between the speed and convenience of rep-line-level analysis and the unmatched precision of loan-level analytics, depending on the purpose of their analysis.

For years, the cloud-native Edge Platform has stood alone in its ability to offer the computational scale necessary to perform loan-level analyses and fully consider each loan’s individual contribution to a mortgage or MSR portfolio’s cash flows. This level of granularity is of paramount importance when pricing new portfolios, taking property-level considerations into account, and managing tail risks from a credit/servicing cost perspective.

Not every analytical use case justifies the computational cost of a full loan-level analysis, however. For situations where speed requirements dictate the use of rep lines (such as for daily or intra-day hedging needs), the Edge Platform’s new Flexible Loan Segmentation affords users the option to perform valuation and risk analysis at the rep line level.

Analysts, traders and investors take advantage of Edge’s flexible calculation specification to run various rate and HPI scenarios, key rate durations, and other calculation-intensive metrics in an efficient and timely manner. Segment-level results run at both loan and rep line level can be easily compared to assess the impacts of each approach. Individual rep lines are easily rolled up to quickly view results on portfolio subcomponents and on the portfolio as a whole.

Comprehensive details of this and other new capabilities are available by requesting a no-obligation demo at riskspan.com.

This new functionality is the latest in a series of enhancements that further the Edge Platform’s objective of providing frictionless insight to Agency MBS traders and investors, knocking down barriers to efficient, clear and data-driven valuation and risk assessment.

###

About RiskSpan, Inc. 

RiskSpan offers cloud-native SaaS analytics for on-demand market risk, credit risk, pricing and trading. With our data science experts and technologists, we are the leader in data as a service and end-to-end solutions for loan-level data management and analytics. Our mission is to be the most trusted and comprehensive source of data and analytics for loans and structured finance investments. Learn more at www.riskspan.com.

RiskSpan’s Snowflake Tutorial Series: Ep. 1

Learn how to create a new Snowflake database and upload large loan-level datasets

The first episode of RiskSpan’s Snowflake Tutorial Series has dropped!

This six-minute tutorial succinctly demonstrates how to:

  1. Set up a new Snowflake #database
  2. Use SnowSQL to load large datasets (28 million #mortgage loans in this example)
  3. Use internal staging (without a #cloud provider)

This is this first in what is expected to be a 10-part tutorial series demonstrating how RiskSpan’s Snowflake integration makes mortgage and structured finance analytics easier than ever before.

Future topics will include:

  • Executing complex queries using python functions in Snowflake’s SQL
  • External Tables (accessing data without a database)
  • OLAP vs OLTP and hybrid tables in Snowflake
  • Time Travel functionality, clone and data replication
  • Normalizing data and creating a single materialized view
  • Dynamic tables data concepts in Snowflake
  • Data share
  • Data masking
  • Snowpark: Data analysis (pandas) functionality in Snowflake
TALK TO AN EXPERT

5 foundational steps for investors to move towards loan-level analyses

Are you curious about how your organization can uplevel the accuracy of your MSR cost forecasting? The answer lies in leveraging the full spectrum of your data and running analyses at the loan level rather than cohorting. But what does it take to make the switch to loan-level analytics? Our team has put together a short set of recommendations and considerations for how to tackle an otherwise daunting project…

It begins with having the data. Most investors have access to loan-level data, but it’s not always clean. This is especially true of origination data. If you’re acquiring a pool – be it a seasoned pool or a pool right after origination – you don’t have the best origination data to drive your model. You also need a data store, like Snowflake, that can generate loan-loan level output to drive your analytics and models.  

The second factor is having models that work at the loan level – models that have been calibrated using loan-level performance and that are capable of generating loan-level output. One of the constraints of several existing modeling frameworks developed by vendors is they were created to run at a rep line level and don’t necessarily work very well for loan-level projections.

The third requirement is a compute farm. It is virtually impossible to run loan-level analytics if you’re not on the cloud because you need to distribute the computational load. And your computational distribution requirements will change from portfolio to portfolio based on the type of analytics that you are running, based on the types of scenarios that you are running, and based on the models you are using. The cloud is needed not just for CPU power but also for storage. This is because once you go to the loan level, every loan’s data must be made available to every processor that’s performing the calculation. This is where having the kind of shared databases, which are native to a cloud infrastructure, becomes vital. You simply can’t replicate it using an on-premise setup of computers in your office or in your own data center. Adding to this, it’s imperative for mortgage investors to remember the significance of integration and fluidity. When dealing with loan-level analytics, your systems—the data, the models, the compute power—should be interlinked to ensure seamless data flow. This will minimize errors, improve efficiency, and enable faster decision-making.

Fourth—and an often-underestimated component—is having intuitive user interfaces and visualization tools. Analyzing loan-level data is complex, and being able to visualize this data in a comprehensible manner can make all the difference. Dashboards that present loan performance, risk metrics, and other key indicators in an easily digestible format are invaluable. These tools help in quickly identifying patterns, making predictions, and determining the next strategic steps.

Fifth and finally, constant monitoring and optimization are crucial. The mortgage market, like any other financial market, evolves continually. Borrower behaviors change, regulatory environments shift, and economic factors fluctuate. It’s essential to keep your models and analytics tools updated and in sync with these changes. Regular back-testing of your models using historical data will ensure that they remain accurate and predictive. Only by staying ahead of these variables can you ensure that your loan-level analysis remains robust and actionable in the ever-changing landscape of mortgage investment.

Company

Products

Security & Compliance

Amazon Web Services
Microsoft

Linkedin

SOC2 Type II Certified

Get Started
Log in

Linkedin   

risktech2024