Constructing a KPI chart

This example will show how to construct a simple KPI chart showing stock prices of the Standard and Poor's 500 list. For more information on the S&P 500, please review this Wikipedia page:

https://en.wikipedia.org/wiki/S%26P_500_Index

Alternatively, use this shortened link:

http://bit.ly/2ODeEvV

For the example, we will be downloading an example dataset from Kaggle and joining it to another dataset that's been downloaded from GitHub that will allow us to categorize the companies in the S&P 500 into their industry sectors.

Please download the S&P 500 data from this link (you will need to log in to Kaggle):

https://www.kaggle.com/camnugent/sandp500

Extract the downloaded .zip file, and then download the list of S&P companies and their categories from GitHub:

https://github.com/datasets/s-and-p-500-companies/blob/master/data/constituents.csv

Alternatively, use this shortened link:

http://bit.ly/2U6akeG

(Credit to Rufus Pollock and the Open Knowledge Foundation).

Reminder—to download the file from GitHub, you will need to follow these steps:

1. Navigate to one of the URLs that was provided previously.
2. Right-click on the Raw button and click Save link as... and save the file somewhere on your computer where you can import it into Spotfire.

Now, let's load the data into Spotfire. The process of loading the data is exactly the same, whether you are using the web or Analyst clients:

1. Import the all_stocks_5yr.csv file into Spotfire, accepting the default column types.

1. Add the constituents.csv file as columns to the all_stocks_5yr data table. First of all, drop in the options by clicking the import bar—you can see where I clicked:

Choose Add as columns to:

1. Click the cog wheel to open the Match columns dialog:

1. By default, Spotfire matches on column name and type, but the recommended match isn't what's needed in this case, so the preview doesn't show any matches in the data. Remove the suggested match and add a new match between Name (String) and Symbol (String):
   
2. Use the default settings—2 of 2 columns, left outer join. Notice how the preview shows that the match is now working correctly:

1. Click OK to close the dialog and OK again to add the data to the analysis.
2. If you're interested, take a look at the data canvas by clicking on the button on the bottom-left of the Spotfire window (highlighted). You should see something like this:

1. If you did open the data canvas, you'll need to close it again by clicking on the data canvas button once more. Now, add a KPI chart from the visualizations flyout:

A default KPI chart will be added to your Spotfire analysis. Now, we can begin by configuring the KPI chart using the following steps:

1. Right-click on the box that's showing figures and a sparkline (mini-line graph) and click KPI Settings:

1. Let's start by configuring the values of the KPI chart—the properties dialog will open to the correct place for us to do this. Set the following properties:
   1. Value (y-axis): Avg(close)
   2. Time (x-axis): Date(Year >> Month >> Day of Month):

1. 1. Show time in tile: (checked).
   2. Tile by: Sector.
   3. Comparative value: Avg(volume). Here's the completed configuration for the Values part of the KPI chart:

1. Select the Formatting property page and set the format for the value axis to show Currency. Check the checkbox to use the short number format and choose USD:

1. Set the formatting for the comparative axis in the same way, but this time, choose number formatting.
2. Click close on the properties dialog. You should see something like this:

The KPI chart is looking good so far, so let's pause to discuss some important aspects of the chart before configuring it further:

• The KPI chart is divided into "tiles". Each tile represents a category in the data. In our case, we joined the constituent data so that the industry sectors could be represented in an easy-to-view format. Showing all the individual symbols in a single KPI chart is too much information to digest at a glance.
• KPI charts show a trend over time with the sparkline. A sparkline is a small line chart with the purpose of showing a general trend, without the full details. A sparkline is very efficient in the amount of screen space it uses. In this case, we have chosen to keep the y-axis of the sparklines the same for all categories—this allows for a direct comparison between the categories, but we could choose to show individual scales, which would give a greater indication of trend in each category, but not allow direct comparison between the categories.
• There are two values shown in each tile the KPI chart:
  • Value: This is the "large" value shown on the top-left of each tile
  • Comparative value: This is the smaller value shown at the bottom-left of each tile
• The value (top-left) is always based on the data at the latest time point. In my case, the value shows the average closing price on February 7, 2018—the date is displayed in the top-right of each tile. The KPI chart needs special care if the data isn't complete and the latest dates are different across the categories, but we will discuss this later on in this chapter.

• The comparative value is designed to show any other value—in our case, we have selected Avg(volume). It's important to note that this is the average volume (per day, as that is how the data is stratified) for all stocks in each sector since "the beginning of time."
• The (Empty) tile is just present because some of the stock symbols are not listed in the constituent data, so they have not been allocated an industry sector. This is not a problem for our example, but you may wish to fix issues like this in the real world.