Unlocking Financial Data

Book description

Investors recognize that technology is a powerful tool for obtaining and interpreting financial data that could give them the one thing everyone on Wall Street wants: an edge. Yet, many don’t realize that you don’t need to be a programmer to access behind-the-scenes financial information from Bloomberg, IHS Markit, or other systems found at most banks and investment firms.

This practical guide teaches analysts a useful subset of Excel skills that will enable them to access and interpret financial information—without any prior programming experience. This book will show analysts, step-by-step, how to quickly produce professional reports that combine their views with Bloomberg or Markit data including historical financials, comparative analysis, and relative value. For portfolio managers, this book demonstrates how to create professional summary reports that contain a high-level view of a portfolio’s performance, growth, risk-adjusted return, and composition. If you are a programmer, this book also contains a parallel path that covers the same topics using C#.

Topics include:

  • 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
  • Build one-page analytic (“Tear Sheet”) reports for individual companies that incorporates important financials, custom notes, relative value comparison of the company to its peers, and price trends with research analyst targets
  • Build two-page portfolio summary report that contains a high-level view of the portfolio’s performance, growth, risk-adjusted return, and composition
  • Explore daily prices and facility information for most of the tradable corporate bond and loan market
  • Determine the relationship between two securities (or index) using correlation and regression
  • Compare each security’s performance to a cohort made of up of securities with similar risk and return characteristics
  • Measure portfolio risk-adjusted return by calculating variance, standard deviation, and Sharpe ratio
  • Use Markit data to identify meaningful trends in prices, new issue spreads, and refinancings

Publisher resources

View/Submit Errata

Table of contents

  1. Preface
    1. Conventions Used in This Book
    2. Using Code Examples
    3. O’Reilly Safari
    4. How to Contact Us
    5. Acknowledgments
  2. 1. Introduction
    1. Overview
      1. Section I: Accessing Financial Data
      2. Section II: Financial Data Analysis
      3. Section III: Creating Financial Reports
    2. Financial Markets
      1. Equities
      2. Corporate Loans (Bank Debt, Leveraged Loans)
      3. Corporate Bonds
    3. The Three Paths
      1. Path 1: Microsoft Excel
      2. Path 2: Microsoft Access
      3. Path 3: C#
    4. Online Files
    5. Summary
  3. 2. Organizing Financial Data
    1. Path 1: Excel
      1. Excel Range Versus Excel Table
      2. Adding Reference Columns
      3. Data Validation
    2. Paths 2 and 3: Tables in Access
      1. Connecting the Data with Queries
    3. Summary
  4. 3. Bloomberg
    1. Identifying the Fields
      1. The Mouse-Over
      2. The FLDS Screen
      3. Bloomberg Function Builder and Finding Fields in Excel
      4. If All Else Fails...
    2. Excel Examples
      1. Pulling a Single Field (BDP)
      2. Pulling Bulk Data (BDS)
      3. Pulling Historical Data (BDH)
    3. Comparable Securities
      1. Indices
      2. Peers
      3. Related Securities
    4. Paths 1 and 2: Excel and Access
      1. Corporate Bonds, Loans, and Indices
      2. Company Worksheet
      3. References and Overrides
    5. Path 3: Bloomberg C# API
      1. Setting Up Microsoft Access for Use with C#
      2. Bloomberg C# API
      3. Basic Reference Example
      4. Basic Historical Example
      5. Populating Access Database
    6. Summary
  5. 4. IHS Markit: Big Corporate Data
    1. Corporate Loans
      1. Data Request
      2. Facility Information
      3. Loan Pricing, Financials, and Analytics
    2. Corporate and Sovereign Bonds
    3. Path 1: Storing Markit Information in Excel
    4. Path 2: Importing Markit Data into Microsoft Access
    5. Path 3: Importing Markit Data Using C#
    6. Summary
  6. 5. Financial Data Analysis
    1. Data Integrity
      1. Checking the Data
      2. Sample Size
      3. Outliers
    2. Portfolio
      1. Portfolio Worksheet
      2. Portfolio Database Table
    3. Linking Excel Worksheets to Microsoft Access
    4. Keeping a History
      1. Path 1: Excel
      2. Path 2: Microsoft Access
      3. Path 3: C#
    5. Summary
  7. 6. Relative-Value Analysis
    1. Path 1: Excel
      1. Correlation and Regression in Excel
      2. Peer Groups
      3. Ratings
      4. Stats Worksheets
      5. Side by Side
      6. Indices
      7. Weighted Z-Score
    2. Path 2: Access
      1. Correlation and Regression in Access
      2. Median in Access
    3. Path 3: C#
      1. Correlation and Regression
      2. Peer Groups
      3. Ratings
      4. Stats Tables
      5. Side by Side
      6. Weighted Z-Score
    4. Summary
  8. 7. Portfolio Risk Analysis
    1. Path 1: Excel
      1. Variance, Volatility, and Standard Deviation
      2. Sharpe Ratio with Historical or Forecasted Returns
      3. Portfolio Breakdown
      4. Warning Signs
    2. Path 2: Access
      1. Portfolio Breakdown
      2. Warning Signs
    3. Path 3: C#
      1. Sharpe Ratio with Historical or Forecasted Returns
      2. Portfolio Breakdown and Warning Signs
    4. Summary
  9. 8. Market Analysis
    1. Path 1: Excel
      1. New Issue Loan Analysis
      2. Refinancings
      3. Price History
    2. Paths 2 and 3: Access and C#
      1. New Issue Loan Analysis
      2. Refinancings
      3. Price History
      4. A Step Further
    3. Summary
  10. 9. Creating Reports
    1. Path 1: Excel
    2. Path 2: Microsoft Access
    3. Path 3: C# and SSRS
    4. Summary
  11. 10. Portfolio Reports
    1. Monitoring Performance and Risk
    2. Path 1: Microsoft Excel
      1. Calculating Returns
      2. Portfolio Report
    3. Path 3: C# and SSRS
    4. Summary
  12. 11. Conclusion
  13. A. Table Reference
  14. Index

Product information

  • Title: Unlocking Financial Data
  • Author(s): Justin Pauley
  • Release date: October 2017
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781491973257