Chapter 4. IHS Markit: Big Corporate Data

This chapter covers how to retrieve financial information from IHS Markit, one of the best sources for financial data covering the universe of corporate bonds, loans, and a range of different products. Unlike Bloomberg, Markit offers its clients access to retrieve pricing and reference data on almost the entire spectrum of tradable bonds and loans, without restrictions. Working with very large sets of financial data opens the door to different types of useful analysis, such as looking for trends in daily price changes for thousands of syndicated loans based on spread, rating, or other characteristics. Many banks, asset managers, and hedge funds already subscribe to Markit data; however, most of the data is used to support internal systems and rarely makes its way to the hands of analysts.

Chapter 8 discusses the different types of analysis on large datasets, whereas this chapter focuses on the different types of data available, how to retrieve the data, and techniques for storing the data with Excel and Microsoft Access based on the path you are following.

This chapter does not cover all of the products IHS Markit offers. To learn more about Markit and its complete range of products, visit https://www.markit.com. Most of the field descriptions used in this chapter are from Markit’s documentation.

Corporate Loans

Corporate loans (also referred to as bank loans, leveraged loans, or syndicated loans), are loans issued by corporations that are typically rated noninvestment grade. Markit provides reference data (facility information, ratings, identifiers, etc.) and performance information (pricing, financials, analytics, etc.) on most of the investable global market.

Data Request

Fortunately, Markit makes it very simple to request data using their Markit Loans Automated Data Exchange. Each type of data Markit provides is referred to as a “channel,” which you can access in either comma-separated value (CSV) or Extensible Markup Language (XML) format using a customized web request. The web request is simply a URL (website) containing parameters, such as firm, username, password, and other options. For instance, accessing loan prices (LoanXMarks channel) in CSV is as simple as visiting the following URL in a web browser:

https://loans.markit.com/loanx/LoanXMarks.csv?LEGALENTITY=firmname
  &USERNAME=user1&PASSWORD=password1

This URL downloads a CSV file that you can open in Microsoft Excel. Switching the .csv extension to .xml in the URL will return an XML document that is both human-readable and machine-readable (designed to be read by applications). If the parameters, like your username, contain special characters, you must encode them to avoid problems; for example, instead of “/”, you need to use “%2F”. A quick search on the internet for “URL encoder” will return multiple websites that can assist you with the encoding.

Note

You can download most of the information discussed in this section manually from Markit’s website at http://www.markit.com.

Facility Information

The Facility Update channel will return reference data on every loan facility that was updated since your last request; contact support@markit.com to receive the initial file of facility data. As such, if you sent two consecutive requests with no interval between them, the second request would return an empty result. Use the following URL syntax to access facility data in CSV format:

https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY=
mylegalentity&USERNAME=user1&PASSWORD=mypassword

The file returned will have columns defined in Table 4-1. Because Markit has a partnership with S&P, some of the columns (denoted by an asterisk [*]) are accessible only to clients that also have an agreement with S&P. In addition, some columns (denoted by **) must be specifically requested from Markit (you can contact support@markit.com) before they will appear in the results. Columns denoted with *** require both an agreement with S&P and a specific request to Markit.

Table 4-1. Facility update columns
Column	Description
LoanX ID	Unique Identifier for each loan.
PMD ID	Unique identifier associated with a particular issuer/tranche combination. This can be a positive or negative number.
PMD Trans ID*	Unique ID by which PMD/LCD identifies a Transaction, or loan package.
Issuer Name	Name of borrower or issuer.
Issuer ID*	A generated unique identifier associated with a particular issuer.
Deal Name	Name of borrower. This is usually the same as preceding cell but can include date and type of deal.
Facility Type	Specific loan type; TLB, bridge loan, etc.
LoanX Facility Type	Markit consolidates the PMD Facility Type into one of currently 16 standardized values.
Facility Status*	Specific instrument type: bridge, 364-day, subord, Term Loan Amortizing.
LoanX Facility Type Code*	Code representation of LoanX Facility Type + LoanX Facility Category.
LoanX Facility Category*	Markit simplifies the PMD Facility type to one of the following: Institutional, RC, TLA, Other.
Industry	Industry classification based on SIC code
Initial Amount	Facility amount in MM.
Initial Spread	Original LIBOR spread.
Maturity Date	Final maturity date.
Ticker***	The issuer’s ticker symbol.
Currency***	Currency of the loan.
LoanX Currency Code*	Standardized currency abbreviation.
SP Org ID***	S&P’s assigned Org ID.
Commitment Fee*	Commitment Fee.
Sponsor*	Sponsor of the loan.
LoanX Sponsor Code*	Sponsor name as a numeric code.
Launch Date*	Launch date of the loan.
Close Date*	Close date of the loan.
State*	State of issuer.
Country*	Country of issuer.
LoanX Country Code*	Standardized Country abbreviation.
Pro Rata Assignment*	Pro Rata Assignment Minimum.
Institutional Assignment*	Institutional Assignment Minimum.
Pro Rata Fee*	Pro Rata Fee.
Institutional Fee*	Institutional Fee.
Facility Fee*	Annual fee paid on the full amount of a facility.
Consent*	Agent, company, both.
Security*	Assets securing the loan.
LoanX Security Code*	Standardized Security abbreviation.
Lead Agent*	Lead agent.
LoanX Lead Agent Code*	A generated unique identifier associated with an agent name.
Admin Agent*	Administrative agent.
LoanX Admin Agent Code*	A generated unique identifier associated with an agent name.
Document Agent*	Documentation agent.
LoanX Doc Agent Code*	A generated unique identifier associated with an agent name.
Syndicate Agent*	Syndication agent.
LoanX Synd Agent Code*	A generated unique identifier associated with an agent name.
Initial SP Rating*	Initial S&P rating.
Industry Code*	Industry code.
SIC Code*	SIC code.
SIC Description*	SIC description.
Industry Segment ID*	Industry segment ID.
Industry Segment Description*	Industry segment description.
Status Code*	Code for internal status.
Status	Description of internal status.
Cancelled*	Flag to indicate the deal was cancelled.
Created Time	Date a facility record was created.
Modified Time	Date a facility record was modified.
Term*	Term of the loan, in years.
RC Term*	Term of the RC loan, in years.
TLA Term*	Term of the TLA loan, in years
TLB Term*	Term of the TLB loan, in years.
TLD Term*	Term of the TLD loan, in years.
OID*	Original offering price of the loan at issuance.
Libor Floor*	The minimum base rate paid in the event Libor is below the specified floor level.
Lien Type***	Seniority of the debt within the levels of borrower’s capital structure.
Cov-Lite***	Flag to indicate the tranche is cov-lite.

Table 4-1 shows that the Facility Update data contains a lot of useful information, especially for S&P clients. You can use this data in many interesting ways; for instance, you could use it to determine global issuance trends (spreads and original issue discounts [OIDs] by rating or industry) or total upcoming maturities by industry. The LoanX ID column is the Primary Key and corresponds to exactly one loan.

The Recommended Updates channel provides information on refinancing and other reasons a loan might become inactive. The Recommended Updates channel, similar to the Facility Update channel, will return new records only since your last request. You can retrieve the Recommended Updates by using the following URL syntax:

https://loans.markit.com/loanx/LoanXRecUpdates.csv?LEGALENTITY=myfirm&USERNAME=
user1&PASSWORD=mypassword

Table 4-2 lists and describes the columns returned by the Recommended Updates request.

Table 4-2. Recommended Update columns
Column	Description
LoanX ID	Unique Identifier for each loan.
LCD ID	Identifier for S&P LCD Data.
Issuer Name	Name of borrower or issuer.
Dealname	Name of borrower. This is usually the same as preceding cell but can include date and type of deal.
Facility Type	Specific loan type; TLB, bridge loan, etc.
Industry	Industry classification based on SIC code
Initial Amount	Facility Amount in MM.
Final Maturity	Final maturity date.
Initial Spread	Original LIBOR spread.
Facility Status	Active/ inactive status (A or I).
Inactive Date	Date of status change, always in the past.
Inactive Reason	Reason for status change.
Replacement LoanX ID	The LoanX ID for the replacement loan.
Replacement PMD ID	Replacement Unique identifier associated with a particular issuer/tranche combination. This can be a positive or negative number.
Replacement Issuer Name	Replacement Name of borrower or issuer.
Replacement Deal Name	Replacement Name of borrower; usually same as preceding cell, but can include date and type of deal.
Replacement Facility Type	Replacement Specific loan type; TLB, bridge loan, etc.
Replacement Industry	Replacement Industry classification based on SIC code
Replacement Initial Amount	Replacement Facility Amount in MM.
Replacement Final Maturity	Replacement Final maturity date.
Replacement Initial Spread	Replacement Original LIBOR spread.
Replacement Status	Replacement Facility Status.

The first set of columns in the Recommended Update dataset (LoanX ID through Facility Status) contain details on the original loan before it became inactive. The Inactive Date and Inactive Reason indicate the date and cause for the original loan to become inactive. The remaining columns (prefaced with “Replacement”) are details on the loan that replaced the original loan, if applicable. For instance, if a loan with the LoanX ID of LX123456 was refinanced with the proceeds of another loan, LoanX ID LX98765, there would be one row with LX123456 in the LoanX ID column, and LX98765 in the Replacement LoanX ID column. If the replacement columns are blank, there isn’t a corresponding new loan. Both the original LoanX ID and Replacement LoanX ID will correspond to rows in the Facility Update data. It is also possible that the replacement loan was subsequently refinanced and might appear in another row in the Recommend Update data as a LoanX ID.

Even though the purpose of the table is to update a portfolio with the latest information in the event of a refinancing or termination, this data has a lot of other uses. For instance, you can use this data to track refinancing trends such as the percentage and average spread tightening of loans originally rated B+ in the past three months, by industry. You can also use it to determine the average number of months before final maturity a typical loan will refinance.

The Daily Ratings channel provides the most recent rating information from Moody’s and S&P for each actively priced loan. Use the following URL syntax to request the latest Daily Ratings data (CSV only):

https://loans.markit.com/loanx/LoansDailyRatings.csv?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=pw239876

Alternatively, provide an optional DATE argument to specify the date of ratings to send using the format MM-DD-YY. Historical ratings data is available for the most recent 10 business days and the most recent three month-ends. For example, to request daily ratings for May 14, 2016, use the following:

https://loans.markit.com/loanx/LoansDailyRatings.csv?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=pw239876&DATE=05-14-16

Table 4-3 contains the columns returned from a Daily Ratings request.

Table 4-3. Daily Rating columns
Column	Description
As of Date	Date the file was created
LoanX ID	Unique Identifier for each loan
Price Date	Date of the Bid and Offer provided
Moody’s Rating	Rating provided by Moody’s
Moody’s Rating Date	Date the rating was last updated by Moody’s
Moody’s Watch	Watch list description from Moody’s
Moody’s Watch Date	Date the watch list was last updated
Moody’s Outlook	Outlook provided by Moody’s
Moody’s Outlook Date	Date the outlook was last updated by Moody’s
S&P Rating	Rating provided by S&P
S&P Rating Date	Date the rating was last updated by S&P
S&P Watch	Watch list description from S&P
S&P Watch Date	Date the watch list was last updated
S&P Outlook	S&P’s outlook value
S&P Outlook Date	Date the outlook was last updated by S&P

Because Markit provides the dates of the rating updates, you can use this data to construct a history for an individual loan or, thinking bigger, to track rating changes across an industry, a set of companies, or determine the correlation between falling loan prices and downgrades.

The LoanID Updates channel provides a mapping from Markit’s LoanX IDs to CUSIPs or other identifiers. The LoanID Updates channel, similar to the Facility Update channel, will return new records only since your last request. Use the following URL syntax to request LoanID Updates:

https://loans.markit.com/loanx/LoanIDUpdates.csv?LEGALENTITY=mylegalentity
&USERNAME=user1&PASSWORD=mypassword

Table 4-4 contains the columns returned from the LoanID Updates request.

Table 4-4. LoanID Updates columns
Column	Description
Identifier	An industry-standard unique identifier associated with a particular issuer/tranche combination (typically stores the CUSIP)
Identifier Type	Specifies the source of the Identifier (e.g., “CUSIP”)
LoanX ID	Unique identifier for each loan
Valid From	Date the Identifier was mapped to the LoanX ID
Valid To	Date the Identifier was unmapped from the LoanX ID
Modified Time	Date the Identifier Mapping was edited

The Identifier column in the returned dataset will contain a CUSIP when the Identifier Type column contains the word “CUSIP.” The mapping between LoanX ID and CUSIP is important because LoanX ID is unique to Markit, whereas CUSIP is used by multiple systems (including Bloomberg). As a reminder, CUSIP is a licensed product from CUSIP Global Services, and it might require you to obtain a license before storing and using CUSIPs in a database.

Loan Pricing, Financials, and Analytics

You use the Marks channel to get daily loan prices for thousands of US and European loans. Here’s the basic URL syntax:

https://loans.markit.com/loanx/LoanXMarks.xml?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=password

The URL can also handle two additional parameters. You can include the RELATIVEVERSION parameter to request data up to 10 business days in the past. For instance, providing a RELATIVEVERSION of -1 returns marks as of two business days prior to today (“0” is the default and will return marks as of previous business day):

https://loans.markit.com/loanx/LoanXMarks.xml?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=password&RELATIVEVERSION=-1

North American buy-side clients can also set the EOD parameter to Y after 4 PM EST to request today’s closing prices:

https://loans.markit.com/loanx/LoanXMarks.xml?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=password&EOD=Y

European clients requesting end of day pricing for European securities can use a different URL after 4 PM GMT:

https://loans.markit.com/loanx/LoanXMarksUK.csv?LEGALENTITY=firmname
&USERNAME=eurouser&PASSWORD=password&EOD=Y

Table 4-5 contains the descriptions of the columns returned by the Marks channel.

Table 4-5. Marks column descriptions
Column	Description
LoanXID	Unique identifier for each loan.
Mark Date	Date of price.
Evaluated Price	Midpoint of close bid/close offer.
Bid	Average bid for facility on mark date subject to change through the trading day.
Offer	Average offer for facility on mark date subject to change through the trading day.
Depth	Depth is generally the count of the contributing dealers.
Close Bid	Closing bid captured at 4 PM Eastern time. This will not change.
Close Offer	Closing offer captured at 4 PM Eastern time. This will not change.
Close Date	Date of Close bid and offer.
Contributed	Returns “Yes” if your company contributed to the average mark.

Many firms across Wall Street use Markit loan prices to mark their positions nightly but there is a lot more for which you can use this data. You can use it to answer questions such as these:

How many loans currently trading above $90 have ever traded below $70?
Are there any loans we sold that have since fallen in price by more than 10%?
Are there any industries or rating groups that are under/over performing the market?
Which loans were the biggest movers this day, week, month, quarter, year?
Is there a strong correlation between a ratings downgrade and a change in price?
Which loans are trading above par and are currently callable?

Taking it even further, Markit provides a plethora of daily analytics on each loan using its Loan Performance channel. Use the following URL syntax to request loan performance (CSV only):

https://loans.markit.com/loanx/LoanPricingAndAnalytics.csv?LEGALENTITY=firmname
&USERNAME=user1&PASSWORD=password

The returned loan performance dataset has more than 200 columns containing most of the useful columns from other channels (facility, pricing, ratings) as well as the following:

Daily, monthly, and yearly returns
Spread; duration; modified duration; PV01; remaining weighted average life; yield to maturity; and yield to a one-, two-, three-, four-, and five-year call
Current yield
Thirty-, 60-, and 90-day average bid
Daily, monthly, and yearly price change in percentage and point terms

Finally, Markit also provides access to S&P’s Capital IQ financial data for Capital IQ clients using its Financial Statement channel. Use the following URL syntax to request all financial statement data since January 2007 (CSV only):

https://loans.markit.com/loanx/FinancialStatement.csv?LEGALENTITY=firmname
&USERNAME=user&PASSWORD=password

The URL syntax also accepts an optional parameter, TIMEFRAME, which you can set to LatestAvailable to return only the latest available financial statement data:

https://loans.markit.com/loanx/FinancialStatement.csv?LEGALENTITY=firmname
&USERNAME=user&PASSWORD=password&TIMEFRAME=LatestAvailable

Table 4-6 contains the column descriptions for the Financial Statement data.

Table 4-6. Financial Statement columns
Column	Description
SP_COMPANY_ID	S&P Capital IQ identifier
Currency	Currency of the financial data
Year	Year of the financial statement
Quarter	Quarter of the financial statement
Is_Annual	Indicator of annual data
Is_Latest	Indicator of the most recent information available
Total_Sr_Secured_EBITDA	Total senior secured debt/EBITDA
Sr_Debt_EBITDA	Senior debt/EBITDA
Sr_Sub_Debt_EBITDA	Senior subordinated debt/EBITDA
Jr_Sub_Debt_EBITDA	Junior subordinated debt/EBITDA
Sub_Debt_EBITDA	Subordinated debt/EBITDA
Total_Debt_EBITDA	Total debt/EBITDA
Net_Debt_EBITDA	(Total debt – cash and ST investments)/EBITDA
Total_Assets	Total assets
Revenue	Revenue
EBITDA	Earnings before interest, tax depreciation, and amortization
Retained_Earnings	Retained earnings
EBITDA_INT	EBITDA/interest expense
Quick_Ratio	(Total cash and short-term investments + accounts receivables)/total current liabilities
Current_Ratio	Total current assets/total current liabilities
Total_Debt_Capital	Total debt/total capital
Total_Debt_Equity	Total debt/total equity

Although Chapter 3 discussed how to pull a lot of this information from Bloomberg, this feature in Markit can pull the complete history for a large set of companies. Unfortunately, to connect this information to a LoanX ID, you need to make a separate request to the Financial Statement Map channel using the following syntax (CSV only):

https://loans.markit.com/loanx/FinancialStatementMap.csv?LEGALENTITY=firmname
&USERNAME=user&PASSWORD=xxxxx

This URL request will return data containing an LXID (LoanX ID) column as well as an SP_COMPANY_ID column that you can use to connect other Markit datasets with the S&P Capital IQ financial statements data.

Corporate and Sovereign Bonds

In addition to providing pricing for corporate loans, Markit provides a daily pricing and analytics CSV file for corporate and sovereign bonds. Unlike loan pricing, bond prices are delivered over SSH File Transfer Protocol (SFTP). The server and credentials for the SFTP server is provided by Markit. To price thousands of bonds, Markit “builds an issuer curve using data that is cleaned and corroborated with multiple observable sources.” Bonds without observable sources are priced by using interpolation or limited extrapolation of observable data.

Markit’s bond pricing data contains more than 90 columns, including the following:

Static bond reference data (CUSIP, Maturity, Currency, etc.)
Market data sources for pricing the bond
Clean and dirty bid, ask, and mid prices
Yield-to-worst (YTW) using bid, ask, and mid prices
Asset Swap Spread using bid, ask, and mid prices
Z-spread based on bid, ask, and mid prices
G-spread based on bid, ask, and mid prices
PV01
Effective, modified, and Macaulay duration based on bid, ask, and mid prices
Convexity based on bid, ask, and mid prices
Option Adjusted Spread (OAS) based on bid, ask, and mid prices.
Depth and liquidity statistics

Path 1: Storing Markit Information in Excel

Fortunately, Markit delivers all of its content in CSV files that Excel can open. Unfortunately, Excel was not designed to handle very large datasets, although it has become better in newer versions. Furthermore, Excel requires a few extra steps to ensure that duplicate rows are removed. Nevertheless, this section walks through recommended techniques for storing Markit data from CSV files in Excel. Even though you can keep the Markit data in the same workbook as the Bloomberg data, I recommend that that you store Markit data in a separate workbook because large datasets are slow to deal with in Excel.

Begin by creating a new worksheet for each desired dataset (channel). For the Facility Update and Loan ID Updates worksheet, contact support@markit.com and request the full CSV file that contains all the facility information for each loan and the entire mapping from LoanX ID to CUSIP. Optionally, request historical information on the other channels, as well. For the other tables, use the URL syntax provided earlier in this chapter to download a CSV file for each channel.

Next, copy and paste the contents of the CSV files into their corresponding worksheet (e.g., Marks CSV data into a Marks worksheet). Add a column to the end in row 1 with the header labeled “Duplicate Check.” As described in Chapter 2, convert each range in each worksheet to an Excel table by selecting all the cells (including the “Duplicate Check” header), and then, on the ribbon, on the Home tab, click the “Format as Table” button (or press Ctrl-T), and select any table style you prefer. When prompted, check the box marked “My table has headers.” Choose a name for each Excel table, such as “LoanFacilities,” “LoanUpdates,” and “LoanPrices.”

Some tables (Facility Update, Recommended Update, and LoanID Updates) can use LoanX ID as their Primary Key because only the latest data is important and it would not make sense to keep a history of these tables. If a row is added to these tables with the same LoanX ID, the existing row should be removed. To accomplish this, add this formula in the Duplicate Check column that counts the number of rows with the same LoanX ID (or Identifier for LoanID Updates) and a newer Modified Time (or Inactive Date for RecUpdates):

=SUMPRODUCT(N([LoanX ID]=[@[LoanX ID]]),N([Modified Time]>[@[Modified Time]]))

This formula demonstrates how to use a combination of the Excel SUMPRODUCT and N functions to return a count based on multiple columns. The N function returns 1 when the conditional statement argument is true (there is a LoanX ID in the LoanX ID column that matches the current row’s LoanX ID and that same row’s Modified Time is greater than the current row’s Modified Time), otherwise it returns zero. SUMPRODUCT returns the sum of the products of the N functions. The formula works because when both N statements are true, it will add 1 to the result (1 multiplied by 1 equals 1), but if either are false, it will add zero to the result (1 multiplied by 0 equals 0). You can safely remove any row for which the resulting formula is greater than zero because there is another row with the same LoanX ID and a more recent Modified Time.

Going forward, to add data to these sheets after using the URLs to request an updated CSV file from Markit, copy all of the rows and columns from the CSV (exclude the header) and, in the destination worksheet, right-click any row header (the box to the very left containing the row numbers) and select “Insert Copied Cells” to insert the rows from the CSV file into the worksheet. Then, ensure that the formula in the Duplicate Check column is copied into these new rows. Remove any duplicate entries.

For tables that could contain a history, such as Marks, Daily Ratings, and Financial Statements, either keep only the latest data by clearing the entire sheet every time you retrieve data from Markit (or, using the same formula as the other tables to ensure only one LoanX ID exists) or use the following formula in the Duplicate Check column that returns the count of rows with the same LoanX ID and Mark Date (or As of Date):

=SUMPRODUCT(N([@[LoanX ID]]=[LoanX ID]),N([@[Mark Date]]=[Mark Date]))

Path 2: Importing Markit Data into Microsoft Access

This section covers how to import Markit CSV files into Access and maintain the data going forward. First, contact support@markit.com and request the full Facility Update and LoanID CSV files that contain all of the facility information for each loan and the entire mapping from LoanX ID to CUSIP. Then, perform the following steps:

In Access, on the ribbon, click External Data, and then select Text File.
Click the browse button and select one of the CSV files retrieved from Markit, such as LoanXFacilityUpdates.csv.
Select the option “Import the source data into a new table in the current database.”

This creates a new table using the data from the CSV file.
Click Okay. Choose the Delimited option, and then click Next.
On the next screen, choose “Comma” delimiter, set the Text Qualifier drop-down box to the double quote symbol (“), and then check the box labeled First Row Contains Field Names.
On the next screen, simply click Next.

The data types will be changed later.
On the next screen, select “No primary key.”

The Primary Keys will be set later.
Click the Finish button to complete the import.
Repeat steps 1 through 8 to import all of the CSV files for each channel.
Right-click one of the tables, and then select Design View.
Select the columns indicated in Table 4-7, and then click the Primary Key button.

This step sets the appropriate columns as Primary Keys for the table.
Adjust the Data Type for columns containing dates to Date/Time, and then set the Data Type for numbers to Double.

For a complete list of data types for each table, read the Markit documentation.

Table 4-7. Primary Keys for Markit tables
Table name	Primary Key(s)
LoanIDUpdates	Identifier, Modified Time
LoanPricingAndAnalytics	PricingAsOf, LoanX ID
LoansDailyRatings	Date, LoanX ID
LoanXFacilityUpdates	LoanX ID, Modified Time
LoanXMarks	LoanX ID, Mark Date
LoanXRecUpdates	LoanX ID, Inactivation Date
FinancialStatement	SP_COMPANY_ID, Year, Quarter
FinancialStatementMap	LXID

The date columns were included in the Primary Key list in Table 4-7 to prevent Access from excluding updated rows from future imports. To delete rows that have the same LoanX ID but an earlier Modified Date, use the following query (adjust table and column names as necessary):

Delete
FROM LoanXFacilityUpdates r
where r.[Modified Time] < (select max(r2.[Modified Time]) 
from LoanXFacilityUpdates r2 where r2.[LoanX ID]=r.[LoanX ID])

Going forward, to update these tables with new CSV files, use the same Text File button on the External Data tab on the ribbon, but select “Append a copy of the records to the table” option and choose the appropriate table for the file you selected. Follow the same directions used on the first import. On the final screen, select “Save import steps” to create a quick way to repeat these steps. To rerun the import, choose the name of the saved import after selecting Saved Imports on the External Data menu, and then click Run.

Path 3: Importing Markit Data Using C#

This section describes how to import data from Markit into Microsoft Access using C#. First, follow the steps presented in Path 2 to create the initial tables by importing them from the CSV files and setting the appropriate Primary Keys and data types. Next, in Visual Studio, create a new console application project. Add a reference to Micorosft.VisualBasic by right-clicking on References and selecting the Add Reference check box adjacent to Mirosoft.VisualBasic under Assemblies/Framework.

In Program.cs, begin by adding the following using directives:

using System.Data;
using System.Data.OleDb;
using System.Net;
using Microsoft.VisualBasic.FileIO;

Within the body of the Program class, add the following properties, making changes to the database location, and Markit credentials:

private string ConnStr = 
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\Path 3.accdb";
private string FIRM = "MyCompanyName";
private string USERNAME = "MyMarkitUserName";
private string PASS = "MyMarkitPassword";

As discussed earlier, install and use the appropriate OleDB driver for the appropriate version of Microsoft Access. Next, in the Main method, create an instance of the Program class and call the Run method that will be created next:

static void Main(string[] args)
{
    Program p = new Program();
    p.Run();
}

In the Run method, call a ProcessURL method (created later) with the URL syntax of the Markit request, the name of the table in the Access database, and the name of the Primary Key for tables that should be searched and updated instead of appended. For tables that build a history, pass null for the primary key argument:

public void Run()
{
                       
    ProcessURL(
        "https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoanXFacilityUpdates",
        "LoanX ID");

    ProcessURL(
        "https://loans.markit.com/loanx/LoanXRecUpdates.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoanXRecUpdates",
        "LoanX ID");

    ProcessURL(
        "https://loans.markit.com/loanx/LoanIDUpdates.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoanIDUpdates",
        "Identifier");

    ProcessURL(
        "https://loans.markit.com/loanx/LoanXMarks.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoanXMarks",
        null);
    ProcessURL(
        "https://loans.markit.com/loanx/LoansDailyRatings.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoansDailyRatings",
        null);

    ProcessURL(
        "https://loans.markit.com/loanx/LoanPricingAndAnalytics.csv?LEGALENTITY=" 
        + FIRM + "&USERNAME=" + USERNAME + "&PASSWORD=" + PASS,
        "LoanPricingAndAnalytics",
        null);
}

The ProcessURL method uses WebClient to download the CSV file from Markit using the url parameter. Then, as it loops through the CSV file, it checks to see if it should update or insert a new row based on the Primary Key. The data is updated or inserted where the column names match the column headers in the CSV file.

The ProcessURL method starts by populating a dataset with the current rows from the database (using the FillDataSet method defined later). Next, it uses the WebClient class to download the CSV file from Markit into a temporary file location.

Next, the method instantiates a TextFieldParser class and sets the properties for parsing a CSV file and starts a while loop until the end of file is reached. On the first line, it populates the header List instance with the column array from the parser. This will contain the column names from the CSV file. On the following lines, it populates the col string array with the column array from the parser; this will contain a row of data to populate the database.

The method then checks whether the primaryKey argument was passed; if so, it finds the associated row in the database. If the primaryKey argument was not passed or the corresponding row in the database does not exist, a new DataRow is created.

Next, the columns of the database table are populated if the CSV file contains a column with the same name and the data can be converted to the column’s datatype.

If the DataRow instance was created instead of found, it needs to be added to the DataTable. Then, the DataSet is updated (using the UpdateDataSet method defined later).

Here’s what the code looks like:

private void ProcessURL(string url, string table, string primaryKey)
{
    DataSet ds = FillDataSet(table);
    WebClient client = new WebClient();

    string tmpfile = 
    System.Environment.GetFolderPath(Environment.SpecialFolder.InternetCache) + 
    "\\" + Guid.NewGuid().ToString() + ".csv";
    client.DownloadFile(url, tmpfile);

    using (TextFieldParser parser = new TextFieldParser(tmpfile))
    {
        parser.Delimiters = new string[] { "," };
        parser.TextFieldType = FieldType.Delimited;
        parser.SetDelimiters(",");
        parser.TrimWhiteSpace = true;

        List<string> header = null;
        while (!parser.EndOfData)
        {
            if (header == null)
            {
                header = new List<string>(parser.ReadFields());
                continue;
            }
            string[] col = parser.ReadFields();

            DataRow row = null;
            if (primaryKey != null)
            {
                var rows = ds.Tables[table].Select("[" + primaryKey + "]='" 
                + col[header.IndexOf(primaryKey)] + "'");
                if (rows.Count() > 0)
                {
                    row = rows[0];
                }
            }
            if (row == null)
            {
                row = ds.Tables[table].NewRow();
            }
            foreach (DataColumn dc in ds.Tables[table].Columns)
            {
                if (header.Contains(dc.ColumnName))
                {
                    string val = col[header.IndexOf(dc.ColumnName)];
                    if (dc.DataType == typeof(string))
                    {
                        row[dc] = val;
                    }
                    else if (dc.DataType == typeof(DateTime))
                    {
                        DateTime foo;
                        if (DateTime.TryParse(val, out foo))
                            row[dc] = foo;
                    }
                    else if (dc.DataType == typeof(double))
                    {
                        double foo;
                        if (Double.TryParse(val, out foo))
                            row[dc] = foo;
                    }
                    else if (dc.DataType == typeof(Int32))
                    {
                        int foo;
                        if (Int32.TryParse(val, out foo))
                            row[dc] = foo;
                    }
                    else
                        Console.WriteLine("Unhandled Column Type");
                }
            }
            if (row.RowState == DataRowState.Detached)
                ds.Tables[table].Rows.Add(row);

        }
        parser.Close();
    }
    int rowc = UpdateDataSet(ds, table);
    Console.WriteLine("Updated " + rowc + " in table " + table);
}

The only remaining methods to create are the FillDataSet method that populates a DataSet instance with contents from the Access database file, and the UpdateDataSet method that updates the Access database file:

private DataSet FillDataSet(string table)
{
    DataSet ds = new DataSet();
    using (OleDbConnection conn = new OleDbConnection(ConnStr))
    {
        string cmdStr = "SELECT * FROM " + table;

        OleDbCommand cmd = new OleDbCommand(cmdStr, conn);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        conn.Open();
        da.Fill(ds, table);
        conn.Close();
    }
    return ds;
}
private int UpdateDataSet(DataSet ds, string table)
{
    int rowc = 0;
    using (OleDbConnection conn = new OleDbConnection(ConnStr))
    {
        string cmdStr = "SELECT * FROM " + table;

        OleDbCommand cmd = new OleDbCommand(cmdStr, conn);
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.SelectCommand = cmd;
        OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
        cb.QuotePrefix = "[";
        cb.QuoteSuffix = "]";
        conn.Open();
        da.UpdateCommand = cb.GetUpdateCommand();
        da.InsertCommand = cb.GetInsertCommand();
        rowc = da.Update(ds, table);

        conn.Close();
    }
    return rowc;
}

Summary

Almost every firm that participates in the corporate markets as either investors or dealers will require access to pricing services such as those offered by Markit. However, this data is often captured and used by back-office systems for accounting or risk purposes and, all too often, never makes its way into the hands of those who could also benefit: the analysts. Although Markit is one of many providers of reference and pricing data, its simple API that can retrieve a wealth of information as demonstrated in this chapter makes the company ideal for analysts with limited technology resources. Of course, simply accessing this information is useful, but there is a lot of value in the different types of analysis that can be done only on large sets of financial data such as this. The topic of analysis on large financial datasets is covered in Chapter 8.

Get Unlocking Financial Data now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Unlocking Financial Data by Justin Pauley