US National Financial Conditions Using Excel Panel Charts

Using data from the Chicago Fed's National Financial Conditions Index, here's how to create an Excel panel chart showing Risk, Leverage, and Credit conditions.

109

This figure uses the Chicago Fed's National Financial Conditions Index to illustrate how to create an Excel panel chart.I’ll explain the meaning of this chart figure shortly. But first, let’s look at it from an Excel perspective.

(Note: I’ve begun to use economic data to illustrate Excel topics, for three reasons: Actual economic data is more interesting than artificial data, which is my other option. I can download economic data more easily than I can create artificial data. And the more we all know about the economy, the more clearly we can understand the reasons for our company’s performance.)

This figure uses the Chicago Fed's National Financial Conditions Index to illustrate how to create an Excel panel chart.

The Excel Perspective

The delta (“Δ”) symbol represents “change.” It’s generated by the formula:
=UNICHAR( HEX2DEC(“0394”) )

(Converting from Hex to decimal is needed here because the UNICHAR function requires a decimal value, not Hex.)

I found the “U+0394” hexadecimal Unicode value by searching for white unicode delta. The “U+” indicates hexadecimal Unicode, and the “0394” is the Unicode value.

As always, this figure uses my Excel Productivity methods, which allow me to update the figure with one command.

The figure shows FIVE charts, not four. In the left one, the Plot Area is hidden, leaving only a column of index numbers for the Y axis. The other four charts—which have the same values in their Y axes—display no Y-axis labels at all.

This way to display Y axes is possible only because all five charts always display the same Y axes values. There are two ways to set this up.

The way that requires frequent maintenance is to set the maximum and minimum settings for the Y axis of each chart—manually. Using this method means that you would need to manually adjust all five settings as the data moves beyond the values in the Y axis.

The way that works automatically is to plot TWO lines in each of the charts. The second line is always hidden, and it plots only two values: the maximum and minimum values of all data that the four line charts display.

The Y-axis chart contains only the plot of the two values for the maximum and minimum.

The labels with Tighter, Looser, and so on within the plot areas are merely text boxes.

The Management Perspective

As companies around the world struggle with the fallout of the Covid-19 pandemic, the health of the world economy could come down to liquidity. That is, how easy will it be for companies to raise the cash necessary to give them enough time to recover?

The charts illustrate how quickly US financial conditions tightened in the first three month of 2020, suggesting that companies were finding it ever-more difficult to raise the cash they needed. But since about the first of April, financial conditions have loosened up considerably.

With regard to the design of the chart figure, managers typically want figures that are easy to understand at a glance. Line charts typically provide that benefit. And panel charts, like the ones shown here, allow managers to compare the trends in related measures quickly.

The Economic Perspective

The Chicago Fed’s National Financial Conditions Index (NFCI) provides a comprehensive weekly update on U.S. financial conditions in money markets, debt and equity markets, and the traditional and “shadow” banking systems.

The NFCI is a weighted average of 105 measures of financial activity (download the PDF), each expressed relative to their sample averages and scaled by their sample standard deviations.

  1. The Risk subindex captures volatility and funding risk in the financial sector.
  2. The Credit subindex is composed of measures of credit conditions;
  3. The Leverage subindex consists of debt and equity measures.

As the Chicago Fed explains, increasing risk, tighter credit conditions, and declining leverage are consistent with tightening financial conditions. Thus, a positive value for an individual subindex indicates that the corresponding aspect of financial conditions is tighter than on average, while negative values indicate the opposite.

For additional details about the index, see Chicago Fed Letter #386, Introducing the Chicago Fed’s New Adjusted National Financial Conditions Index.