US Market Analysis Tool

Consumer Credit and Stock Market Trade Ratio Data

My name is Erel Amit, I am currently entering my Sophomore year at Duke University where I study math and economics. This summer, I joined DataChant as a guest author and decided to work on a Power BI dashboard with the eventual goal of publication. My passion for the markets drove me towards two datasets, the US consumer credit report, and the stock market’s trade ratio decile data. These are both highly rich and underexplored sets within the worlds of economics and finance and my recent completion of an econometrics course motivated me to tackle the statistical challenges they would bring.

I intend to continue with a series of technical blog posts explaining the specific challenges I faced and how I used Power Query, M, and the Power BI interface to solve them. Moreover, I encourage interested users to reach out with any questions, comments, and suggestions so that I can continue to update the dashboard and help it meet the needs of its users. You can reach me through email or LinkedIn. I constantly seek to improve and would love to hear about additional use cases or datasets that could prove useful.

Dashboard Overview:

The Power BI dashboard is divided into two main sections: “US Consumer Credit Analysis” and “US Stock Market Decile Analysis.” Each section contains multiple pages that together aim to present a holistic view of the data.

Methodology and Definitions:

The report primarily uses two publicly accessible datasets: The Federal Reserve’s Consumer Credit – G.19 report (released monthly) and the SEC’s Summary Metrics report (released quarterly). Details about both data sets can be accessed on their respective government webpages. Some relevant information needed to effectively utilize the report is included below:

Consumer Credit Data

All credit data, unless stated otherwise, is measured in millions of dollars (USD). The “(Weighted) YoY % Change” measures the change in value from the same period, last year. This allows us to eliminate the effects of seasonality when examining data on a more atomized time scale.

Parent Attribute:

• Parent Attribute refers to 5 main categorizations I made throughout the process:
  • Auto Loans
  • Student Loans
  • Revolving Consumer Credit
  • Nonrevolving Consumer Credit
  • Total Consumer Credit

Each of these types of credit can be further divided by the Institution that holds the debt and its form of Securitization.

Note: because of the differing sampling frequencies for the data, some averages will appear “wrong” at first glance when calculated on certain time frames.

Institution:

Data can be further analyzed by which institution holds the debt on its balance sheet. These are:

• Depository Institutions
• Finance Companies
• Credit Unions
• Federal Government
• Nonprofit/Educational Institutions
• Nonfinancial Business
• Commercial Banks
• Savings Institutions
• N/A

Where “N/A” refers to totals/elements not characterized by a specific institution (i.e. Parent Attribute: Nonrevolving Consumer Credit with Institution: N/A – will refer to the sum total of all Nonrevolving Consumer Credit without regard for holding institution):

Securitization:

These securitization categories are in line with the Federal Reserve’s classification system. Due to changes in reporting over time, there is a specific designation for “Securitized” (but not owned) debt. The “Securitized” attribute should generally not be used, except by advanced users. The categories are:

• Owned and Securitized
• Securitized
• Owned
• N/A

A securitization designation of “N/A” generally refers to data that are not forms of held/balance book debt. Some examples include finance rates and average auto-loan maturities.

Stock Market Decile Data

The data sample includes every order from 9:35 AM to 4:00 PM on Amex, Arca, Nasdaq, and the NYSE exchanges with some filtering for intra-day cross and IPO halts. Specific stocks, prices, etc., are sourced from the CRSP database.

This dataset can be significantly more difficult to conceptualize and work with for those unused to the SEC’s reporting style. The US Stock Market is divided into Deciles, 10% ‘groupings’ based on a certain Metric. For example, Decile: “10” with Metric: “Market Cap” – refers to the top 10% of US-listed companies by market capitalization. Each of these deciles has data associated with it on a daily basis. We have data on 6 relevant ratios:

1. Cancel to Trade Ratio: # of cancels ÷ # of trades
2. Trade to Order Volume: trade volume ÷ order volume
3. Hidden Rate: # of hidden trades ÷ # of trades
   • Hidden Trades are those which occur on an exchange as undisplayed, hidden, or resting orders. These trades are usually not visible on the exchange’s feed but can be found on the SIP (as of January 2014).
4. Hidden Volume: hidden trade volume ÷ total trade volume
5. Odd Lot Rate: # of odd lot trades ÷ # of trades
   • Odd lots are trades of less than 100 units of stock with the following exceptions: BH has a round lot of 10, BRK.A and SEB have a round lot of 1.
6. Odd Lot Volume: odd lot volume ÷ total trade volume

Meanwhile, the deciles are created/divided using 4 metrics:

1. Market Cap: Price * shares outstanding.
2. Price: Closing stock price for the day. When there is no closing trade, a quote midpoint is used.
3. Turnover: # of shares traded ÷ # of shares outstanding
4. Volatility: is the daily standard deviation of 1-minute quote-midpoint values

Conclusion

I hope you find this dashboard insightful. As before, I encourage you to reach out with any questions you may have, suggestions of how I can improve the product or interesting use cases and insights you may have discovered. Stay tuned for the next article where I will discuss how I built the foundations for the slicers (filters) I built using lookup tables and relationships.