Machine Learning & Big Data Blog

Tableau for Finance: How To Join Tables, Write Calculations, and Analyze Finances

Banner
5 minute read
Walker Rowe

This is the fourth post in our series on Tableau. For the next few blog posts, I’m going to illustrate certain Tableau concepts. For the data, I’m using stock price, earnings, and dividend information from Disney, Starbucks, and Johnson & Johnson.

In this article, I will illustrate how to join tables, use right and left joins, reverse mistakes, and use statistical functions to find correlation between variables. We’ll use these functions and analytics to answer these questions:

  • To what extent do stock prices reflect cash flow and earnings?
  • Are dividends related to cash flow or earnings?

(This article is part of our Tableau Online Guide. Use the right-hand menu to navigate.)

Setting up Tableau

If you’re new to Tableau, you can see our previous articles that help you get started with basic datasets and examples. Previous articles include:

Download the data

To start, you’ll need data. To get the data I use, simply do what I did: download from Stockpup and Yahoo Finance and clean it up a little. The earnings data for all three companies is from 1999. Stock prices and dividends are from 2010 to 2018.

Download the data here. Next, store the companies’ earnings, dividends, and stock data in any database you like, as long as it is supported by PostgreSQL. (We use PostgreSQL.)

Creating tables for Tableau

Here are the steps for creating your tables of data:

  • Use the below commands to create the tables.
  • Upload your tables to PostgreSQL.
  • Connect those data sources to Tableau using the procedure we previously outlined.
create table earnings (
Symbol VARCHAR(50),
Quarterend date,
Shares bigint,
Sharessplitadjusted bigint,
Splitfactor bigint,
Assets bigint,
CurrentAssets bigint,
Liabilities bigint,
CurrentLiabilities bigint,
Shareholdersequity bigint,
Noncontrollinginterest bigint,
Preferredequity bigint,
Goodwillintangibles bigint,
Longtermdebt bigint,
Revenue bigint,
Earnings bigint,
Earningsavailableforcommonstockholders bigint,
EPSbasic float,
EPSdiluted float,
Dividendpershare float,
Cashfromoperatingactivities bigint,
Cashfrominvestingactivities bigint,
Cashfromfinancingactivities bigint,
Cashchangeduringperiod bigint,
Cashatendofperiod bigint,
Capitalexpenditures bigint,
Price float,
Pricehigh float,
Pricelow float,
ROE float,
ROA float,
Bookvalueofequitypershare float,
PBratio float,
PEratio float,
Cumulativedividendspershare float,
Dividendpayoutratio float,
Longtermdebttoequityratio float,
Equitytoassetsratio float,
Netmargin float,
Assetturnover float,
Freecashflowpershare float,
Currentratio float
);
create table dividends (Date date,
Dividends float,
Symbol VARCHAR(50));
 
create table prices ( Date date,Open float,High float,Low float ,Close float,AdjClose float ,Volume bigint ,symbol varchar(50));

Load the data into PostgreSQL using the steps below. You must be the admin user to copy these files to these tables.

COPY earnings FROM '/home/ubuntu/Documents/tableau/dis.dividends.csv' DELIMITER ',' CSV HEADER;
COPY earnings FROM '/home/ubuntu/Documents/tableau/sbux.dividends.csv' DELIMITER ',' CSV HEADER;
COPY earnings FROM '/home/ubuntu/Documents/tableau/jns.dividends.csv' DELIMITER ',' CSV HEADER;
COPY dividends FROM '/home/ubuntu/Documents/tableau/sbux.dividends.csv' DELIMITER ',' CSV HEADER;
 
COPY dividends FROM '/home/ubuntu/Documents/tableau/jnj.dividends.csv' DELIMITER ',' CSV HEADER; 
 
COPY dividends FROM '/home/ubuntu/Documents/tableau/dis.dividends.csv' DELIMITER ',' CSV HEADER;
COPY prices FROM '/home/ubuntu/Documents/tableau/sbux.prices.csv' DELIMITER ',' CSV HEADER;
COPY prices FROM '/home/ubuntu/Documents/tableau/jns.prices.csv' DELIMITER ',' CSV HEADER;
COPY prices FROM '/home/ubuntu/Documents/tableau/dis.prices.csv' DELIMITER ',' CSV HEADER;

How to join tables (right and left joins)

The common element among all these tables in the stock symbol. Tableau will automatically try to join the three tables—dividends, earnings, and prices—by the stock symbol. But we can’t allow this auto-join because the tables have different numbers of records. This is because Disney did not pay dividends in all 40 quarters, though Johnson & Johnson and Starbucks did. (Doing this, an inner join, in this case would drop some rows of data.)

So, you must do right or left joins, depending on what data you’re investigating. For our purpose, looking at earnings and dividends, we need to do a right join because there are more earnings than dividends.

To change the join type, double-click the double circles in Tableau Online.

This worksheet shows that Disney paid dividends only 15 out of 40 quarters. This is a bar chart of the stock symbol and cnt(Dividends), meaning dividend count.

As you work with worksheets, type over the name at the bottom and give them a descriptive name.

In the above worksheet, the plus (+) sign indicates that the two tables are joined. Notice that it shows the 3 tables as well as the joins you have defined in the data sources. Be careful to pick the correct table or relation when you make a chart.

Below, when you select dividends+ it lists fields from both tables on the left. These are the fields you can use in the worksheet:

How to reverse errors

If you mess up, you can simply reverse: click the left-hand arrow at the top to go back.

Earnings analysis

Let’s look at earnings. On the left, select the earnings table.

If you’ve read financial statements before, you know that they are shown in the thousands. So, create a calculated field earnings1000 to divide this large number by 1,000 to make it easier to see. We make this a calculated field.

Analysis: Disney earnings by quarter

We put the quarter filing date in the rows and the value we want to list in Marks section. This sums earnings per quarter, which is the same as listing the single value of earnings, since there is only one record per filing period.

Then, filter on the stock simple. We also filter on the Quarterend to pick only dates from 2010 to 2018, since that is the dates for which we have dividends and stock prices.

Now, using Disney, Starbucks, J&J, or your own company’s data, you can begin your analysis.

Learn ML with our free downloadable guide

This e-book teaches machine learning in the simplest way possible. This book is for managers, programmers, directors – and anyone else who wants to learn machine learning. We start with very basic stats and algebra and build upon that.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Brings the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
Learn more about BMC ›

About the author

Walker Rowe

Walker Rowe is an American freelancer tech writer and programmer living in Cyprus. He writes tutorials on analytics and big data and specializes in documenting SDKs and APIs. He is the founder of the Hypatia Academy Cyprus, an online school to teach secondary school children programming. You can find Walker here and here.