Unlocking Financial Data

Chapter 1. Introduction

In a nutshell, the role of any financial analyst is to develop a view on how much a security—such as a bond, loan, equity, or even a securitized portfolio of commercial real-estate loans—is worth. In other words, an analyst must determine the price at which he would buy or sell a security. There isn’t a uniform method, model, or formula that analysts use to determine worth, because every investment is different. However, the valuation process generally consists of two important steps:

• An analyst needs to know each potential investment inside and out, identifying the different nuanced ways and probabilities of losing or making money.

• The resulting risk-adjusted return is measured against other potential investments to determine if the given investment is cheap, rich, or fair valued.

Valuations aren’t simply performed once per security; investments must be continuously valued. In today’s global economy, the risks to an investment are constantly changing, affected by changing commodity prices, government regulations, natural disasters, consumer sentiment, and countless other variables. Likewise, even if these variables don’t affect a security directly, they can make an investment look cheap or rich relative to other securities that were affected.

The process for valuing a security is complicated, time consuming, and generally requires analyzing a lot of financial data. Much of the financial data is provided by the issuing company or a bank that is marketing a deal, and doesn’t always paint the full, unbiased, picture. More important, most firms have access to a tremendous amount of financial data that would aid the valuation process, but few outside of technology groups know how to access it, or even know it exists.

This book will show you how to unlock the real potential of Bloomberg data, beyond simply using the Bloomberg Terminal. Using only Excel, without any programming experience, this book demonstrates how to access the data behind the Bloomberg screens, organize it the way you want, and connect it with your views and insights to make investment decisions. This book also discusses how to access data from IHS Markit, one of the best sources for corporate bond and loan data, especially pricing.  Many banks, asset managers, and hedge funds already subscribe to Markit, but most of the data is used to support internal systems and rarely makes its way to the hands of analysts.

In addition to demonstrating how to access financial information, the second section of this book covers data analysis. This section describes how to use financial data to determine relative value, measure the risk of a portfolio of securities, calculate correlation between securities, and measure market trends by analyzing loan price movements, issuance, and refinancings.

Finally, we’ll put all the pieces together by combining the data and analysis with your views to create custom reports laid out exactly the way you need. Instead of relying on canned reports, these reports will compare a company’s performance using peer groups that you decide are appropriate, a corresponding benchmark index that makes sense to you, and financial fields and calculations that you find important.

Although these sections (data access, analysis, and reporting) are designed to be easily implemented, maintained, and updated automatically without an ounce of programming, this book also covers the same topics using C# (a programming language) for programmers or potential programmers. Data and information are valued in any industry, but none more so than finance, where a developer’s ability to write code and query databases can make her more valuable as an analyst. This book will show examples of how to access financial information, perform different types of easy financial analysis on large datasets, and create financial reports using SQL Server Reporting Services (SSRS) without the need for a SQL server. If you are interested in an intermediate step between Excel and C#, we cover the same material using a Microsoft Access database.

This chapter dives deeper into each section of the book, highlighting key concepts and goals. It also breaks down how the book will benefit you depending on the asset class (equities, bonds, and loans) in which you’re invested.

Overview

This book is broken down into three sections: Accessing Financial Data, Financial Data Analysis, and Creating Financial Reports. Each of these sections is meant to convey a practical set of goals detailed in this subsection.

Section I: Accessing Financial Data

Section I, comprising Chapters 2 through 4, covers how to access and store financial data on equities, indices, bonds, and bank loans using Bloomberg and Markit. By extracting this information from these systems, you will be able to do the following:

• Access additional data that isn’t visible on Bloomberg screens

• Create tables containing corporate data that makes it possible to compare multiple companies, bonds, or loans, side by side

• Explore daily prices and facility information for most of the tradable corporate bond and loan markets

• Display only the fields that matter, arranged by your preference

• Override wrong or missing information using other sources

• Categorize companies to establish appropriate peer groups based on your views

• Combine Bloomberg and Markit data with your custom calculations and insights

At the end of the section, you will be able to slice and dice financial data that has been enhanced with your input, with far more flexibility than you could using a Bloomberg Terminal. Furthermore, the techniques in Chapter 3 will ensure that you store the data in a way that is easy to maintain and combine with other sets of data.

Section II: Financial Data Analysis

Section II, which includes Chapters 5 through 8, helps make sense of the financial data collected in Section I by putting it into context using financial analysis. Section II begins by comparing an individual security to its peers and expands to examining risk at a portfolio level and then, finally, incorporating broader market trends. By using the financial techniques in this section, you will be able to do the following:

• Determine the relationship between two securities (or indices) using correlation and regression

• Compare each security’s performance to a cohort made up of securities with similar risk and return characteristics 

• Rank securities by their relative performance using a weighted z-score

• Measure portfolio risk-adjusted return by calculating variance, standard deviation, and Sharpe ratio

• “Bucket” a portfolio into different groups to highlight unseen concentrations and trends

• Establish portfolio thresholds by using different metrics to highlight risks

• Use Markit data to identify meaningful trends in prices, new issue spreads, and refinancings

At the end of this section, you will be able to put the different datasets collected in Section I into perspective, highlight risks, and identify trends. Additionally, the methods described in Chapter 6 will show you how to maintain a history and identify any issues in the data from Section I.

Section III: Creating Financial Reports

Section III (Chapters 9 and 10) incorporates the lessons from Sections I and II and demonstrates how to build analytic reports for individual companies and portfolios. In this section, the data and analysis from earlier sections will be displayed and charted exactly the way you prefer. This section will demonstrate how to do the following:

• Build two-page analytic (“Tear Sheet”) reports for individual companies that incorporate important historical financials, custom notes, relative value comparison of the company to its peers, and price trends with research analyst targets

• Calculate time-weighted (geometric) portfolio returns, annualized portfolio returns, annualized portfolio standard deviation, and Sharpe ratio using historical returns for a portfolio or an index

• Build a two-page portfolio summary report that contains a high-level view of the portfolio’s performance, growth, risk-adjusted return, and composition

At the end of this section, you will be able to create a custom professional company report by simply choosing the company’s name from a drop-down list without any programming. Additionally, you will be able to chart the portfolio’s performance and compare risk and return to benchmarks.

Financial Markets

This book focuses on three main financial markets: equities, corporate bonds, and corporate loans. Nevertheless, you can apply many of the lessons in this book directly to other markets like structured products, municipal bonds, and so on.

Equities

This book has a lot of great content for analysts focused on the equities markets. The first section of the book demonstrates how to create a table of fundamental and technical data (converted into a single currency) from Bloomberg for a list of companies or constituents of an index. Because it is in Excel, you can easily supplement the Bloomberg data with your own input. For instance, by adding your own Category column, you can correctly group similar companies together instead of relying on industry classifications, which are typically misleading. Moreover, Bloomberg has an overwhelming amount of financial corporate data. This book shows you how to find the exact information you’re looking for instead of paging through dozens of screens for every company. Following are just some of the fields:

• Sector, Industry, Sub-Industry

• Short Interest Percent of Equity Float

• S&P and Moody’s Ratings

• Market Cap, Enterprise Value, EPS

• Total Debt, Net Debt, Total Debt/EBITDA

• Dividend Gross Yield, Twelve-Month Total Return

• Three-Month and Year-to-Date Price Change (%)

• Interest Coverage, Free Cash Flow (FCF), FCF/Total Debt

• CDS Spread

• Buy, Sell, Hold Recommendations

• Gross Profit, TTM EBITDA

• Historical Financials

The second section of the book demonstrates how to calculate the relationship (correlation and Beta) between two companies or a company and a benchmark index. It also adds median performance data to the Excel company table for each custom category. You can use this median performance data to compare each company’s performance against its peer group.

Finally, the third section shows you how to design your own company report instead of relying on third-party reports. By customizing your own report, you can include the fields that you want and your own notes and calculations.

Corporate Loans (Bank Debt, Leveraged Loans)

Like equities, the first section of this book pulls a series of useful fields from Bloomberg into Excel (or Access), including these:

• Margin, Floor, Index

• Maturity

• Moody’s and S&P Facility Rating

• YTD and Three-Month Price Change

• Discount Margin, Yield

• Next Call Date, Call Price

However, unlike equities, the first section of this book shows how to access loan data from IHS Markit, which has a treasure trove of loan information. Moreover, the Markit data is for most of the tradable loan universe instead of a subset. Daily loan prices on almost every loan combined with facility information on each loan makes it possible for you to identify different trends such as loans selling off in a sector. Here is some of the Markit loan facility data:

• Issuer, Industry, Facility Type

• Sponsor, Lead Agent, Admin Agent

• Size, Spread, Floor, OID

• CUSIP

• Lien Type (1st lien, 2nd lien, etc.)

• Cov-Lite Flag

• Moody’s and S&P Ratings

• Launch Date, Close Date, Maturity Date

In the second section, loans are categorized by their parent company’s category as well as their risk and return characteristics, such as “Short CCC” for shorter CCC rated loans. Loans are then lined up against the median values for their peer groups to put their metrics into context. In addition to facility-level information and prices, Markit provides information on refinancings that we use in the second section of this book to identify trends such as spread tightening or widening by sector.

Corporate Bonds

Like equities, there is a lot of corporate bond data in Bloomberg that you can extract into an Excel (or Access) table. Some of these fields include the following:

• Coupon

• Maturity

• Moody’s and S&P’s Facility Ratings

• YAS Spread and YAS Yield (Spread and Yield from the Bloomberg YAS Screen)

• YTD and Three-Month Price Change

• Callable Flag, Next Call Date, Next Call Price

Like loans, bonds will also be categorized in the second section. The resulting median values for each category are then used as a benchmark for each bond in that category.

The Three Paths

The concepts in this book are implemented three different ways (paths), ranging in technical difficulty from simplest to complex: Excel, Microsoft Access, and C#. As the concepts in the chapters build on one another, so do the implementations. Hence, the “paths” nomenclature was used because you will use the same implementation as you traverse the book.

Because most people have familiarity with Excel, most chapters use Excel as the primary method of covering concepts. The Microsoft Access path builds on the Excel path by linking the database to the Excel workbooks and then demonstrates how to query the data instead of using complicated Excel formulas. The C# path uses code to extract data from third-party systems (Bloomberg and Markit), stores the data and analysis in Microsoft Access, and uses SSRS to generate reports.

There are two reasons why each chapter is broken down into the three paths. First, this book targets different audiences. These concepts can be very useful to a financial analyst who has years of Excel experience but has never used a database or written a line of code. Alternatively, for a developer, there are more straightforward ways to access, manipulate, and store data than using Excel. The second reason is to encourage those who have never used a database or written a line of code to try it. Although this book does not teach you how to query databases or write programs, you can combine what you learn herein with books that do, such as Head First C#, by Jennifer Greene and Andrew Stellman (O’Reilly). Moreover, first-time developers can use the Excel examples to better understand the C# code. The subsections that follow outline the benefits and drawbacks of each path.

Path 1: Microsoft Excel

It doesn’t matter whether you’re an analyst, programmer, scientist, accountant, or practically any other professional, if you work with numbers, you probably use Excel. Excel is one of the most powerful, flexible, and well-loved analytic programs on the market. In addition to its impressive collection of useful functions and charting tools, Excel has an intuitive interface that anyone can use. However, it is worth noting a few of its drawbacks relative to Access and C#:

• Although Excel is great at doing computation on a couple thousand rows, it is still very inefficient at processing very large sets of data. Large numbers of rows or columns can result in poor performance or even crashes.

• Excel formulas can quickly become long, complicated, and very difficult to read. Performing anything but the most basic computation can result in multiple inner-functions and array functions.

• Even though Excel formulas are very dynamic, they can be difficult to maintain. Simply inserting a column could introduce errors into formulas that easily can go unnoticed.

• Dynamic and long formulas can be difficult to debug when issues arise.

Path 2: Microsoft Access

Although Microsoft Access is nothing more than a very watered-down version of Microsoft’s enterprise database software, SQL Server, almost everyone can use Access without having to answer a lot of questions from your IT department.  Access is a great no-frills database application that makes it simple to link Excel data, import comma-separated vales (CSV) files, or create your own tables that can contain millions of rows (up to around 2 GB). If you have never used a database before, it is a powerful skill that isn’t more difficult than using Excel. Like Excel, data is stored in rows and columns in database tables that are very similar to Excel worksheets except the columns are well defined (dates, text, numbers, etc.). However, instead of long Excel formulas, Access slices and dices data using a very easy language called  Structured Query Language (SQL) that is easy to use and read.  You can use SQL to connect data from multiple tables, and it contains many of the same aggregation functions as Excel (average, max, min, standard deviation, etc.). A simple query to get the number of books published in 2017 might look like this:

SELECT COUNT(*) FROM Books WHERE Year(PublicationDate) = 2017

In this book, those following Path 2 will first get the data from Bloomberg using Excel and then link the Excel worksheets to Access for querying. It is the ability to simply query data that makes Access so useful. However, Access also has its drawbacks:

• Its built-in reporting functionality is inferior to Excel’s in many ways. In addition to being nonintuitive, it lacks a lot of Excel’s charting features.

• Access is less flexible than Excel. You cannot simply start typing a formula in a cell or drop a chart next to a table.

• Accessing a value from the previous row in a query can be more complicated than it should be.

• Query results aren’t as easy to format as Excel.

• Although Access can easily connect to Excel sheets for Bloomberg data, it cannot connect directly to Bloomberg.

• Although they are readable, SQL queries can also become complicated and long.

Path 3: C#

C# (pronounced “C sharp”) is a programming language that utilizes the Microsoft .NET Framework. Despite being very powerful, C# is simple and easy to use. Moreover, Microsoft’s Visual Studio integrated development environment (IDE)—a tool for writing code—contains one of the best designers for creating beautiful applications. Unlike Access and Excel, C# is not constrained by tables, rows, and columns. It can pull data from Excel, Access, a website, an email, Bloomberg, a text file, or pretty much anything else. C# can also tap into an endless number of math, scientific, and financial functions. Data and calculations can be passed to SSRS to generate beautiful reports that can be converted into PDFs or Excel files with a tiny amount of code. Lastly, because C# generates applications, it can easily be scheduled to update data, generate reports, or send emails. Nevertheless, C# comes with its drawbacks:

• C# requires a lot more upfront work than Excel. It takes dozens of lines of code to pull data from Bloomberg instead of a simple Excel formula.

• SSRS, which you use as the reporting tool in conjunction with C#, is powerful and full of features; however, it still isn’t as easy or dynamic as Excel.

• Not everyone can install C# (.NET) on their computer at work without involving the IT department and getting special permission.

• Even though C# is one of the easier programming languages to understand, there is a learning curve. Like any programming language, C# has its own grammar and nuances.

Although I encourage you to explore new technologies, the concepts are more important than the implementation; use what works best for you and your situation.

Online Files

It’s likely that you will download all the completed Excel workbooks, Access databases, and C# solutions from O’Reilly’s website and modify them to suit your needs instead of reproducing each step. However, it is still important that you understand the purpose of each step so that you don’t introduce mistakes. Furthermore, I cannot distribute all the data (especially from Markit) because of licensing issues, so some data will be replaced with made-up information.

You can download the files from http://bit.ly/unlockFD_examples.

Summary

When making an investment decision, ultimately it is the analyst’s conviction that matters most. Conviction cannot be taught; it comes from years of experience making (and sometimes losing) money. It is experience that informs you whether the numbers don’t make sense or if the CFO didn’t quite answer the questions on the earnings call. All the data and analysis in the world doesn’t make up for conviction, but the goal of this book is to give you an edge by putting more information at your fingertips to help develop conviction and find new investment ideas.