Creating a dashboard can be an effective and efficient way to pool in many data points. In this post, I’ll show you how to create a dashboard that factors in several economic indicators, including inflation, interest rates, housing starts, GDP, unemployment, and the performance of the stock market. It will utilize power query and allow you to easily refresh the data.
Creating and collecting the data points
To make the data that I’m dynamic, I will also use a variable for the current date, so that the data will automatically update. In this example, it will be called todaysdate which is equal to the following formula:
=TEXT(TODAY(),"YYY-MM-DD")Below are the sources for the data that I will use in creating this dashboard along with the Power Query links I will use (along with the variable for the date). I’ll also set up the Power Query links as named ranges in the Excel spreadsheet, making it easy to reference them within the queries.
Unemployment:
Named Range: unemployment
Source: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm
Power Query: https://www.bls.gov/charts/employment-situation/civilian-unemployment-rate.htm
GDP:
Named Range: gdp
Source: https://fred.stlouisfed.org/series/A191RL1Q225SBEA
Interest Rate:
Named Range: interest
Source: https://fred.stlouisfed.org/series/DFEDTARU
Inflation:
Named Range: inflation
Source: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years
Power Query: https://data.bls.gov/timeseries/CUUR0000SA0?years_option=all_years
Housing Starts:
Named Range: housing
Source: https://fred.stlouisfed.org/series/HOUST
Stock Market:
Named Range: stockmarket
Source: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC
Power Query: https://finance.yahoo.com/quote/%5EGSPC/history?p=%5EGSPC
Loading the data into Power Query
Note on named ranges
Using the links above, I’ll create the connections in Power Query and make adjustments where necessary. To reference a named range in Power Query, you can use the following code as an example:
NamedRange = Excel.CurrentWorkbook(){[Name="namedrange"]}[Content]{0}[Column1],The name is case-sensitive so if you use a named range that is all in lowercase as I have done, then those references also need to be in lowercase in Power Query. However, for the purposes of this example, you don’t need to use named ranges and it is an optional step.
Creating the Power Query connections
To create a Power Query connection, I’m going to start by going into the Data tab and selecting From Web under the Get & Transform Data section. For the unemployment rate data, I’ll use the link for that:
After click on OK, I’ll select the table that I want to use, which is the first one on the list:
I’ll click on the Transform Data button before loading it. What I will do is split the Month column so that I have both a Month and Year field. To do this, I’ll select the column, right-click and select the option to Split by Delimiter and use a space. I’ll also use this opportunity to put in my named range for the data link. In the Power Query window, under the Home tab, there’s an option to click on the Advanced Editor. Here, I’ll enter my NamedRange variable and use that when referencing the Source:
When you’re running a query for the first time, you may see a warning asking you about Privacy Levels. Set these to Public and select Save.
Now it’s time to repeat the steps for the other data sources.
Transforming the data in Power Query
There will be some adjustments that need to be made along the way when loading the data. For example, for the data that comes from the FRED website, there are some rows at the top that need to be removed:
In this case, I’ll need to click the Remove Rows button at the top, and specify that I want to Remove Top Rows and enter a value of 11, to remove the first 11.
For the housing and inflation data, I need to make additional adjustments since the data is raw and doesn’t show the percent change, which is what I want. Here are the steps I’m going to take for those queries:
- Unpivoting the data. This is important for the sake of making sure that months are not going across and are instead going vertically. Refer to this post on how to flip and unpivot data in Power Query.
- Generating previous and current period data. I’ll create a calculated column to calculate the current period and the previous period. After the current period column is created (by simply joining the month and year together), I’ll duplicate the query so that there is an additional table for the inflation data. As for the previous period, this involves subtracting 1 from the year to get the previous year’s values. Then, the year and month are concatenated:
- Doing a lookup of the prior-year period. I’ll now merge the query with the one I copied earlier (the other inflation period). This involves doing a lookup of the previous period on the other table’s current period. The goal here is to get the prior-year period’s value. Here’s an overview of how to merge queries in Power Query.
- Calculating the percent change. Once the prior-year period’s value is loaded and on the same row, I can create a custom column to calculate the year-over-year change, which is just the new value / old value -1.
- Removing unneeded values. The final steps involve removing any blank values from the inflation rate and removing and periods that contain the word “HALF” indicating half-year values. Lastly, I’ll split the columns back out so I again have the year and month broken out, this time, along with the inflation rate %:
These steps will be similar for the housing data, except I won’t need to unpivot the data since it isn’t broken out by month and year.
Creating the pivot tables and linking to the data
Now that the data is loaded, the next step is to link to it or create pivot tables, to populate the dashboard. For the unemployment data, I will summarize the average by year:
For the GDP tab, I’ll pull in just the four most recent quarters. To do this, I can use the INDEX function and the COUNTA function to grab the furthest values. For the most recent period, I can use the following formula:
=INDEX(A:A,COUNTA(A:A),1)For more recent periods, I’ll deduct 1, 2, and 3 from the COUNTA value:
The interest rates I will leave as is as that data can chart smoothly given that there normally aren’t many interest rate changes.
For the inflation rate, I will again take the average annual rate using a pivot table but only looking at data since 2010:
On the housing tab, I will break out the average housing starts by quarter, again using a pivot table:
Creating the dashboard
Now that the pivot tables are set up, I can start putting together the dashboard.
For starters, I’m going to go for a clear, dark background, setting it to black. I’m going to create headers for each of the different categories: Unemployment, GDP, Interest, Inflation, Housing Starts, and Stock Market. I’ll link to the key data, referencing the key metric that I want from each tab. Each header will take up three columns, with a space between each one:
What I will also do is create some conditional formatting rules for these values so that they can appear green or red based on their values. Refer to this post for an in-depth overview on conditional formatting. Below the values, I will also extract the date of the most recent data and put it within a formula, to show when the data was last updated:
Next, I’ll create the charts for the different pivot tables. This is really down to preference and style, but I’ll use a combination of bar, column, and line charts to display the data. Here’s how the dashboard looks after adding a title:
And with the data all coming from the web and utilizing Power Query, you can simply just refresh the data to pull the latest numbers, making your dashboard dynamic and easily updateable.
If you liked this post on How to Create a Dashboard in Excel to Track Economic Indicators, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.