Case Study: Automatically Updating Interactive Time Series Dashboard
This is the third in a series of blog posts designed as a DIY training course for using Displayr. This post illustrates how to pull in finance and google trends time series data into a document so that they automatically update, turn them into cool interactive charts, and then lay everything out so it looks nice. The final document can be viewed here. You can also view a video of this dashboard being created here.
Step 1: Create the document
- Create a new Displayr document and call it Microsoft KPIs.
- We want this document to be a bit wider than the defaults. In the Ribbon, Home > Layout (Page Layout) > Custom and set Width 14 inches an Height to 7.8.
Step 2: Getting the time series data
We are now going to extract stock price and google trends data.
- First, let’s create a new page that will store the data.
- In the Ribbon, select Insert > New Page > Title Only
- Then give it a title, such as Data
- We’re going to create data table for stock prices (using a ready-to-go R package that pulls in daily time series stock price data using Yahoo's Finance's API):
- In the Ribbon, go to Insert > More (Analysis) > Data > Stock prices.
- This creates up a blank output on the page.
- Next, we’ll edit our stock price object so it knows what data to fetch. We’ll also give it our preferences. We do this on the right-hand side in the Object Inspector:
- Fields that are circled in red (such as stock symbol) are mandatory, and the R object won’t compute with information in this box.
- In the Object Inspector (as shown in the image below), set:
- Stock symbol(s): MSFT
- Period: Last year
- Data fields: Low, Close, High
- Below this, toggle on Automatic updating, and change Frequency to 7. This will make the data updated every day automatically.
- Toggle on Republish – this makes the completed dashboard re-published with fresh data so that viewers can see its (automatically) revised results.
- Back on the page, drag the newly computed output, to the left-hand side of the page.
- Next, we’ll insert some Google trends data on the same page
- In the Ribbon, go to Insert > More (Analysis) > Data > Google Trends
- The in the Object Inspector, set:
- Topic(s): Microsoft
- Period: Last 12 months
- Turn on Automatic updating, and within that toggle on Republish and set Frequency to 7.
- Drag it to the bottom right side of the page.
- Finally, we’re going to hide this page, because it is a reference page of data, not something we necessarily want our viewers to see. Its purpose is to help us build visualizations on other pages.
- Click on the page called Data in the Pages Tree (top-left of the screen), and then in the Ribbon select Appearance > Hide (Layout).
- You will see some grey stripes appear on the page. There will also be a strikethrough on the page in the Pages Tree. This means that you can see the page when you edit the document (as we are), but the page will not be visible when viewed as a dashboard (see View vs. Edit mode for more info).
If my instructions are good, your screen should now look like this (although your data will be more recent):
Step 3: Creating the charts
In this section, we will create some cool interactive time series charts. These use an open source charting technology called dygraphs.
- Again, create a new blank page:
- In the Ribbon, go Insert > New Page > Blank
- Create the visualization:
- In the Ribbon, open up a blank visualization with Insert > Visualization > Time Series
- In the Object Inspector,
- Click into Inputs > DATA SOURCE > Output in 'Pages' and choose the output we already created, which should be called: stock.prices
- Go to the Chart tab of the Object Inspector, and select:
- APPEARANCE > Show range bars: Checked
- APPEARANCE > Window start (days from data end): 90
- We will now set the color to a Microsoft blue. Of course, you can save a bit of time and choose a pre-defined color if you wish. DATA SERIES > Color color and press Custom color > More colors and click on R and set it to 2, B: 97, and G 172 and press OK.
- Draft the chart to the left of the page.
- Next we'll create a Google trends chart by copying and pasting and then tweaking.
- Select the stock prices chart on the page, and then in the Ribbon press Home > Duplicate.
- Drag the new copy to the right side of the page.
- Then in the Object Inspector,
- On the Inputs tab under DATA change Tables from stock.prices to google.trends.
- On the Chart tab, DATA SERIES > Custom color > More colors and click on R and set it to 217, B: 59, and G 4, and then press OK.
- On the Chart tab, APPEARANCE > Show range bars: Uncheck.
You should now have two charts looking something like this (but with more up-to-date data of course):
Step 4: Formatting
Now we are going to make it look pretty. The goal is to make it look like what you can see to the right.
- We will add a grey border to the page:
- In the Ribbon, Insert > Shapes > Rectangle, drawing it to cover the whole screen.
- In the Object Inspector, Properties set:
- LAYOUT > (for Left, Top, Width, and Height respectively): 5, 5, 1335, 745
- APPEARANCE > Line width: 10
- APPEARANCE > Fill color: set to Transparent.
- APPEARANCE > Line color: Set it to R 204, B: 204, and G 204
- Then send the grey border to the back by going to the Ribbon and select Appearance > Send to Back (Arrange)
- Add another rectangle following the procedure above, but with these settings:
- LAYOUT > Left: 4, Top 97, Width: 669, Height: 53
- APPEARANCE > Line Width: 0
- APPEARANCE > Fill Color and select the blue at the very end of the custom colors (which is the blue we created before when setting the color of the chart).
- Select Home > Duplicate to copy the rectangle you just created, and modify its settings as follows:
- Left: 672
- Top: 97
- Fill Color: the red we created before.
- In the Ribbon, select Home > Duplicate to create another box. Modify this as follows:
- Left: 672
- Top: 153
- Height: 593
- Fill Color: R 244, B: 244, and G 244
- Select the three boxes we have just created (using the SHIFT key) and send them to the back (Ribbon: Appearance > Send to Back).
- Create a text box in the moddle of the blue box, using Insert > Text with text of type S T O C K P R I C E . Format it in 14 point white.
- Copy and paste this, placing it on the red box with text of G O O G L E T R E N D S.
- Download a Microsoft logo: to your computer and insert it using Ribbon: Insert > Image, placing it above the blue box.
Step 5 (Optional): Automatically updating text boxes
So far we have created text boxes by typing. We can also create them programmatically. I will illustrate a couple of examples. Do not be worried about the details of how I worked out the code; that is a topic for another post. In this post, I am just illustrating the basic process.
- In the Ribbon, choose Insert > R Output. This creates a box where we can write custom code using the R programming language.
- Over in the Object Inspector on the right, under Properties > R CODE paste in the code you see below.
close = round(stock.prices[nrow(stock.prices), 2],2) paste0("Closing stock price: $", close)
- Create a second textbox, placed at the top-right, with the following code:
updated = rev(rownames(google.trends)) paste0("Last updated: ", updated)
- Finally, amend both these boxes to be blue with white text, using the various settings in the Object Inspector > Properties > LAYOUT and APPEARANCE.
Step 6: Exporting as a Web Page
Click the Export tab in the Ribbon, press on the very bottom of the Web Page (Document) button, and check the option which says Hide navigation pane, and then press Export > Web Page > Anyone with the link > Publish.(Click here and follow the links for a bit more information about this step.)
Try it yourself
The two data sources we have used here are being pulled in via different APIs. If we were to write some code we could customize things a bit more, and pull in pretty much any data source that has a public API.
Click here to sign into Displayr and edit the document that I created when writing this post.
To learn a bit more about Displayr, please read Case Study: Advanced Analysis of Experimental Data (MaxDiff), which introduces you to the basic way that more advanced analyses are performed in Displayr. Or, if you are sure that now is not the time for you to think about advanced analyses, skip ahead to Case Study: Visualizations.