The Challenge
Every day we are inundated with noise about the health of the economy. Take for instance the following headline, “Consumer Sentiment ticks down 2 points to 93.1”. Without more information, there is no way to know if this is slightly negative, very negative, or simply neutral.
The goal of this exercise was to use the advanced capabilities of Power BI to:
- Visualize how various economic leading indicators are correlated to future moves in the employment rate, stock market, and housing market.
- Show the current status of the economy for each leading indicator.
- Develop a solution that can be automatically refreshed with the latest data from the Federal Reserve.
- Provide simple summary of future expectations based on current conditions.
The Solution
Visual Concept
From a visual analytics perspective studying simple averages is a great place to start. Couple this with another powerful methodology called Decile Analysis (also called segmentation or binning data into equal 10-part N-Tiles) and you are on a path towards an advanced understanding of your data.
This dashboard uses this approach to study 8 leading indicators (grouped into equal 8-part tiles based upon how much the indicator has changed over the prior 3 months) to show the average future change in the employment rate, housing prices, or broad stock market index.
Selecting the Data
The Federal Reserve site FRED hosts a wealth of economic data available at https://fred.stlouisfed.org/. To select data elements, I first studied the Conference Board’s Index of Leading Indicators and searched for those data elements on FRED.
Some issues I encountered included:
- Sample size issues: Lack of historical data for various metrics. Many FRED metrics are not available prior to 1992.
- Timeliness issues: Many FRED metrics are only updated quarterly, as opposed to monthly or daily. Using quarterly data was not an option because the 3-month lag in data would not enable a current view of the economy.
- Granularity issues: Some data is available by day while other data sets are by month. I accounted for this in calculations by using averages in DAX. For example, the calculation for “Prior 3-month change” calculation was implemented as an Average of values between 3 and 4 months prior which ensure smooth average of monthly data.
Technical Solutions
Here are some advanced capabilities of Power BI that I utilized:
- Power BI web data sources using M-code
- To quickly integrate each metric, I used the Advanced Editor which enabled “find and replace” to integrate many metrics quickly.
- Scheduled refreshes
- The data is scheduled to automatically refresh each day from the underlying data source.
- Power BI DAX calculated tables and columns
- Complex DAX was written to perform complex time lag calculations such as Prior 3-month change for each leading indicator, and 10-12 month change for each dependent variable.
- Custom visuals
- Finally, to give more clarity to a complex story I used the custom visual called Enlighten Data Story visual. I highly recommend this visual.
Interesting Findings
A great dashboard leads to interesting discoveries within the data. While many of these statements are things I have heard before it was great to prove them to myself.
- Stability is very good for growth. Note how Tile 4 and 5 contain the most positive values.
- Falling Industrial production and consumer confidence is quite negative.
- Home Prices seem to be more stable than the huge decline in 2008 would lead one to believe.
- The Stock Market is more volatile, and the correlations are weaker, but average returns have been greater.
What interesting discoveries can you make?
Evan Schmidt
Decisive Data
