Financial Analytics in Tableau: Viz Finance with Dorian

I am starting 2020 with a new project and a challenge for myself: Financial DataViz!

It’s not a New Year resolution; it’s something I wanted to do for a long time: takeover a specific topic or industry and work to fathom it out.

The way I want to do is by finding specific topics, visualizations, datasets within the finance industry, and using Tableau to understand them.

Each week I’ll create a new visualization in Tableau, and I’ll show you how to create it. From gathering the data, setting it up in Tableau, to creating charts and dashboards.

Why Finance?

What I see in my work and in that of others is lots of tables.

Using a tool like Tableau to create crosstabs is like giving up. I am not a fan of surrendering, and that is the main reason why I am starting this weekly project.

I hope that the stuff I will bring to your attention will help some of you see the value of visualizing financial data.

And also, I’m a huge fan of financial charts and dashboards! They are so sexy and packed with information. They are meaningful, beautiful, and, most important, useful!

In the end, I want to have a collection of financial visualizations as a go-to resource for myself and anyone interested.

I hope you’ll find them valuable! It is a work of love, and I have faith it will transpire beyond the limits of your screen.

Before we start

There are a lot of resources out there! Here you will read about my perspective on things. Please don’t agree with it, as I love a good debate!

Feedback is of extreme importance to me. If you have any thoughts, please share them loud! Constructive criticism can only lead to progress.

If you want to viz along with me, you are more than welcome to do it. The only way to learn is by doing!

So let’s get started!
First up, stocks!
In this section, I will reproduce some of my favorite stock and trading chart types. I will try to explain why they work well and expose some of their weaknesses.

Of course, you can use them beyond financial data once you understand what works well about them.

Week 1: S&P 500 stock market overview

(click on the image to interact with the visualization on Tableau Public ⇡)

What I love about this dashboard

  • it offers at a glance performance overview of the whole market
  • displaying complex hierarchies is an excellent use case for treemaps; I dug up some old articles on how treemaps should be used, and I feel that this article from Jeff Shaffer sums it up nicely
  • it adds additional context in tooltips (showing up when we hold our mouse over a specific stock)

Weaknesses

  • the red-green color palette which seems widely accepted in the financial data world is making this dashboard unusable for colorblind people (I recommend using red-blue if you care about this)
  • it’s difficult to make accurate comparisons based on color and irregular box sizes (I’m okay with it as long as the purpose of the dashboard is to show the market overview)

The Data

Collected from Yahoo! Finance using Python. There is no need for API keys or paid subscriptions.

You can find the code in this Github folder (the main script is in the getSandP.py file).

How to create this dashboard in Tableau

Update Shortly after publishing the visualization, it was featured as Viz of the Day on Tableau Public.

I am taking this acknowledgment as an obligation to do more and better work!

So let’s move on to the next challenge!

Week 2: Historical evolution of the stock market

This week we are looking at how the stock market has evolved since 1928.

Now, why would we do that?

First of all, for fun! Also, those who don’t know their history are doomed to repeat it.
So I set some time aside to try and understand some facts.

Below is the dashboard I ended up with:

(click on the image to interact with the visualization on Tableau Public ⇡)

The chart shows the daily values of the S&P 500 Index since 1928 with different colors for Bear and Bull markets.

My inspiration for this chart comes from an article I read in The Economist a while back. It’s pretty much a reproduction of that in Tableau.

I am in love with The Economist graphics. They are informative, their look is super clean, and they complement the written article perfectly!

My only problem is that most of them are static. My friends often surprise me with a ruler or cardboard pieces trying to make sense of the charts.

I know, I’m weird!

For this chart, I added two interactive elements I felt the need for.

The first is a threshold controller for when a market is considered Bull or Bear.

The article mentions a brief Bear market in 1990 that some argue it didn’t reach the technical threshold of 20%. The market dropped by 19.9% from the previous high, which technically isn’t Bear.

Using a parameter, we can now move that threshold to see what would happen at 20%, 19.9%, 19%, and so on.

I also wanted is a switcher between logarithmic and classic linear axes.

Many people don’t get log scales. But from what I am seeing, they seem to be widely accepted in the trading world.

I would guess that the reason behind it is their usefulness for comparing rates of change over time.

For example, the distances between $10 – $20 and $20 – $40 are the same on a log scale. Both scenarios represent 100% growth. Hence their slopes are equal as you can see below:

A linear scale would give us a better indication of the magnitude of evolution.

Let’s take January 23, 2020, as an example. The S&P 500 index surged past 3300 for the first time in history!

Not very easy to notice in the log-scaled chart.

Using a linear scale would give us a better feeling of how the market has exploded in the past decade:

With the linear scale, we can’t see how the market was changing in the early days. Offering our audience an easy way to switch back and forth would fix both problems.

This is also something Viz Animations (the new to be released feature of Tableau) would help with understanding. Sliding from one state to another gives us a better feeling of what is going on.

The Data

The data comes from Yahoo! Finance, which you can find at this address.

How to video tutorial

It might seem easy to develop at first sight, but it was not a walk in the park.

I did everything in Tableau to practice more with Table Calculations, and to show what the software can really do.

I believe that these techniques will be useful in other cases as well. It’s a great way to look at dense time-series data!

Hopefully, this video will save you some time if you’re trying to do something similar!

Week 3: Financial results by business segment in a Small Multiples Dashboard

I’m taking a break from stocks this week to have a look at Sony’s financial results.

First, let’s have a look at how the company is showing the data. You guessed it. It’s a table with numbers.

While I can certainly see the value it might have for finance professionals. I want to try a different way of visualizing the data.

Below is what I came up with:

(click on the image to see the dashboard on Tableau Public)

The dashboard tells a few exciting stories that would be difficult to distinguish from looking at the table, such as:

  • the Game & Network Services segment is growing big time (probably powered by PlayStation sales)
  • Financial Services shows an interesting pattern with a high in Q1; additional question: why is Q4 of 2018 so much lower than 2017?
  • Home Entertainment is winding down; additional question: could the decreasing interest in Sony TVs cause this?
  • mobile communications is down 30%; apparently Sony’s mobile phones are not catching on

What I love about this kind of visualization is its potential to spark conversations around data. By using Tableau, we can answer a lot of questions on the spot. And here is where real progress can be achieved!

Should I company that’s making thousands of billions invest a few yen in helping their users understand the data at a glance?

One can only hope!

Finance people love their tables. But what if we display the dashboard alongside the crosstabs? Maybe we can convince a few of them to give data visualization a chance.

What we can do is keep trying. And that is what I’m going to do!

A few thoughts on the chart type

I arranged all the business units in a small multiple layout to be able to see everything on one screen. You might also call it by the name of “panel chart” or “trellis”.

What you might not notice is that the height of the rows is not the same.

By default, Tableau creates equally sized cells. Here’s how my dashboard would look by default:

Some might argue that this is a waste of space.

I somewhat agree, especially for dashboards with lots of segments to display.

But they obviously haven’t heard about Klaus Schulte’s method of getting rid of the grid. I am adapting his solution to size each row differently.

See the video below for more details (including color trick I used to shade between the lines)!

Video with implementation details

And now if I may take a small dig at the PowerBI users complaining about this. Anyone can download this workbook for free from my Tableau Public profile and use it in their work.

But I also want to thank them for inspiring me to work on this!

The Data

The data comes from SONY’s website, which you can find in this PDF doc on page 3.

Week 4: Improving the way we interact with time series

Welcome to the 4th week of my journey to visualize financial data in Tableau!

Today we’re looking at the Tesla stock evolution. The company’s share price has surged in the past two-three weeks, and I wanted to see for myself how that happened.

There’s no chart is better than a line chart to see evolution across time. And this is precisely what I used!

Here’s the interactive version of the dashboard:

(open the dashboard on Tableau public)

Simplicity was essential for me here. Even though the chart type is super simple, the complexity comes with the interactivity I built into it.

Now if you’ll take a second to play around with it, you’ll notice that:

  • you can filter to predefined periods ranging from one day to the maximum available
  • the date level changes according to the length of the period (hourly data for one day, daily for five days to one year, and weekly for 5+ years)
  • you can hover your mouse anywhere across the chart area and a reference line will show up with more details about that date

The interactivity is possible thanks to parameter actions, available in Tableau since 2019.2

After exploring the dashboard a bit, we can start to get some real insights.

  1. for example, we can see that there was a vast increase starting with Monday (Feb 3rd), reaching an all-time high of $887 the next day
  2. we can also filter back to one year and see the downward trend ending in June of 2019
  3. we can look at hourly data from yesterday, which doesn’t say much to me, but it might to the passionate traders out there
  4. and we can have the whole picture from IPO to yesterday, which shows the magnitude of growth that happened recently; huge!

Walkthrough video with technical details

The Data

Collected using the Alpha Vantage API via R. It’s the only resource I found that offers intraday data for free. You can register here to get one too. The R code I used is on my Github profile.

If you have difficulties in getting the data, reach out to me, and I’ll try to help!

Week 5: Encoding change in bar charts

This week I am rethinking one of NIKE’s revenue reports for the fiscal year of 2019. Below is a screenshot of the table I found on the SEC website:

The report looks at the revenue for the last 3 years and breaks it down in a few different ways.

Without further ado, here’s how I visualized the data in Tableau:

(https://public.tableau.com/profile/dorian.barosan#!/vizhome/NIKERevenue/Dashboard)

(click on the image to see the interactive version on Tableau Public)

A tad more insightful, I’m sure you would agree!

My goal was to create a dashboard that anyone can use to understand the data at a glance.

The numbers are there for the taking, and I added the bars for visual context.

There’s also a selector at the top we can use to compare different years.

On the bar charts, I wanted to have something the shows the direction and magnitude of change.

I noticed that this type of arrows is often used in the financial world, and I wanted to give it a try. And I am quite pleased with the result. Here is how I would usually go about it:

It’s not bad at all, but I think the arrows direct my attention faster to the significant changes.

To see how I created the arrows, you can go ahead and download the workbook from Tableau Public and reverse engineer or see the video tutorial below.

Instructions on to build such a thing

The Data

The data comes from the SEC website, which you can find at this link.

Update I want to share a not-so-slightly changed version of the NIKE Revenue dashboard.

Shortly after publishing my version, Rosario Gauna (Tableau Zen Master) reached out to show me a way to integrate the section titles in the totals.

(see it on Tableau Public)

It involves some additional work, but the end result is something that makes much more sense for accounting/finance people.

There is a blog post with details about the technique by Rosario and Klaus Schulte. They called it “internal data densification”.

Don’t get frightened by the name, the principle is not that scary!

Klaus recently wrote a blog post on custom sub-totals offering not one, not two, not three, but four methods for implementation.

I want to thank both of them for the work they’re putting in for the community!

View Comments (39)

  • Dorian, I'm with a mid size Canadian financial institution and we will be following this closely. What a great subject matter.

  • Dorian, Congrats on the viz of the day. beautiful job showing a lot of data in a small space. One small critique, I couldn't find a scale for the stock price spark lines.

  • Thanks for acknowledging the problems with the red-green palette. I would suggest, however, that your seemingly throwaway parenthetical comment about "if you care about this" basically says to people who are color-blind "if you care about color-blind people." color-blindness affects mostly men, from 5-10% of the population of people of Northern European descent (according to Wikipedia: https://en.wikipedia.org/wiki/Color_blindness). I urge everyone to be more careful with their language, and what a lack of time spent accommodating such populations (especially when you recognize you have failed to make it accessible!) says without words.

  • This is a very good visualization. I love having the marketing cap size paired with the change in stock price. Have you thought about adding time lapse to it to see how sectors are expanding or collapsing over time?
    Also, take a look at Simply Wall Street for other ideas...I see symmetry.

    • I have thought about a time lapse, but I didn't get a chance to look for data on S&P changes or market cap evolution.
      It's an interesting idea! Thanks for the tips!

  • Dorian, this is great! One question - do you happen to know the cadence of data refreshes (hourly, daily, etc.)? Thanks and congrats on the Viz of the Day!

    • No data refresh on Tableau Public unfortunately, only extracts that have to be updated manually. Is that the question?

  • Dorian, I love the Viz! As I become more acclimated to Tableau I'd love to use this template for a personal Viz of my portfolio (i.e. only include my holdings). Do you know how I could easily do that? Thanks for sharing!

    • Of course, that would be an awesome use case for it!
      You just need to replace the shares from the Python script with the ones from your portfolio. Once you get the data, you can plug it into Tableau and replace the current data source

      • Sorry for the follow up-not being well versed in Python, I think I see how to add my own Ticker Symbols, but not how to make it dynamic (i.e. is there a way to make Tableau use the code to go search for the up to date ticker price; either on refresh,save, etc.?). Do you know if that's possible? Even if it were each time I save/refresh my extract.

        • I believe it’s possible with a Python integration either in Tableau Desktop or Prep. I haven’t tried it, but it sounds like an interesting idea

        • I believe it’s possible with a Python integration either in Tableau Desktop or Prep. I haven’t tried it, but it sounds like an interesting idea

  • Hi Dorian, great viz! I have a question, did you manually input all the stock tickers or is there an efficient way to get all the tickers within a index like S&P,DOW,DJIA?

      • Thank you! Again, great viz. You can recreate it with other indices. Also, can you do event driven treemaps? Like how DJIA might change after a trade agreement between China and USA.

  • Great Viz! Congrats!!
    Could you please share the colors palettes that you are using in Tableau Custom Colors code? it would be very nice! :)

    • Mainly to avoid the "cannot mix aggregate and non-aggregate" error. I had to aggregate the dimensions because of the table calculations involved

    • Hi Irina, if you’re using area charts you need to unstack the marks: Analysis > Stack marks > Off
      Glad to see you’re following along!

  • Hi Dorian, would love it if you could make a Youtube tutorial on your Week 5 Viz. I am trying to create something similar but could not achieve a similar result.

  • Hi Dorian. I am a beginner tableau user working in Finance, and these are exceptional visualisations. Kudos on that. I am trying to recreate the NIKE one it but am having trouble understanding the "AGG(MIN(IF.....then 1/2/3/4 END" function as well as how the polygons marks card helps you get the arrows at the end. Would you be kind enough to give me some insight if convenient?

  • Following up on my previous comment, is it possible to get a video tutorial on the NIKE dashboard?

  • Hello Dorian your work is amazing and I am really interested in the Week 5: Encoding change in bar charts, could you provide a "how to" video tutorial?

Related Post

This website uses cookies.