Quick Guide to Analyzing a Stock with Tableau
Quick Guide to Analyzing a Stock with Tableau

Learn a little about the stock market and Tableau at the same time!

Photo by Markus Spiske on Unsplash

Are you learning how to Tableau? Tired of playing with the default super store data and want to try something a bit more practical? Try this quick guide to analyzing a stock, and see where it takes you!

Gather your Data

Go to Yahoo Finance and look up a stock. Really, any stock will do. Even better if it’s a stock you happen to own! Once on the stock’s summary page, go to the tab labeled historical data. Change the view to the desired time period (one year is the default). Click on the ‘Download Data’ link to download a CSV file.

Note that for the remainder of the example, I will be working with a CSV showing data for this stock during the following time period: January 20, 2019 — January 20, 2020.

Yahoo Finance Historical Data for ADIL

Prep your Data

Data prep is always the first step of bringing raw data into Tableau. In this case, our data is relatively clean and straightforward already. But just in case, we need to check.

To prep your data, open it in Excel or Google Sheets. Check it over for formatting. If you wish, change the title of any of the columns from the default. In these cases, we probably won’t change anything.

Raw data from our stock in Excel

Visualize your Data in Tableau

If needed, save any changes to the CSV file in Excel, and close it. Then, let’s get started with Tableau!

Open the CSV File in Tableau

Open Tableau Desktop. Under ‘Connect,’ look for ‘To a File’ and then select ‘More…’ Browse to the location of the CSV file and open it. Now we see the Tableau Data Source view. Click on the Sheet 1 tab to open the workbook view. Now we’re ready to start visualizing!

Notice the Data pane on the left side. Here, we have Dimensions and Measures. As this data contains mostly measurable numbers, such as open, close and adjusted close prices as well as volume, there are far more items under Measures.

Build the Workbook — Visualize the Daily Closing Price

Double-click the ‘Date’ dimension. This will automatically bring it to the Columns area of the workbook. Click and drag the ‘Adj Close’ measure to the Rows area of the workbook. Note that we’re using the Adjusted Close price rather than Closing price, because this is more accurate. Read more here if you want to learn why.

Now we have a basic line graph, showing us… what exactly? Definitely not what we’re looking for here. So, we need to break this down a bit more.

Notice the pill in the columns area labeled YEAR(Date)? It appeared when we double-clicked the Date dimension. This shows that we are currently only viewing the years in our data — 2019 and the first few weeks of 2020. Our visualization shows only that our stock’s price went down between 2019 and 2020. It’s also adding up ALL of the closing prices for the year, which again, is definitely not what we want.

This is because our date is currently discrete, meaning it is adding every adjusted close price for the entire respective year. Since we have about 49 weeks in 2019 and three weeks in 2020 in our example, of course the price is higher in 2019. Hover over the MONTH(Date) pill until you see a down-facing triangle on the right. Click the triangle to bring up a menu.

Notice that the menu shows two date options that appear identical, and the Year option is selected on the top one. Also notice that Discrete is selected. Side note, we can also tell that this dimension is discrete because the pill is blue. If it were green, it would be continuous. In the menu, select ‘Day’ from the second set of date options. Notice the pill is now green and the visualization looks like what we need! We’re now viewing a line graph showing the adjusted closing price by day for the entire date period on the CSV file.

Our first stock price visualization!

Format the Workbook

Let’s clean up a few things to make this thing look a bit more professional.

Format the Title

Double-click on the title of the workbook (currently ‘Sheet 1’). Change the name to something descriptive, such as “ADIL Closing Price” with a sub-title below with the date range. Play with the font sizes to give the title a bit more clout than the sub-title.

Formatting the Title of our Workbook

Format the Y-Axis

Next to the Y-axis (the vertical one), double-click the title ‘Adj Close.’ In the dialogue box, under Axis Titles, let’s go ahead and clear the title. Given that we’ve named the chart ADIL Closing Price, it should be intuitive that the Y-axis indicates the closing price. Close the dialogue box when done. Right-click (or control-click on Mac) anywhere on the Y-axis numbers and select ‘Format.’ Under ‘Scale,’ select the arrows next to ‘Numbers’ and select ‘Currency (Standard).’ Now our viewer knows for sure that this is the range of closing prices. While we’re in here, let’s go ahead and change ‘Ticks’ to ‘None.’

X-Axis Formatting

For X-axis (the horizontal one), we really don’t need a title because we know those are dates.

Double-click ‘Day of Date,’ and under ‘Axis Titles,’ simply delete the title.

Looking better already! But there’s one more thing we can do to make this look more professional — remove those grid lines! Go to Format > Workbook. Under the Format Workbook options that now appear on the left side, go to Lines and select the down-triangle next to Grid Lines. Change it from Automatic to Off.

Now we’re looking good!

But wait, there’s more we can add! Remember how our CSV file had a column showing volume? This is the number of shares traded during a given day. You can read more about trade volume here, but adding this makes this visualization look especially professional. We have two options here.

Add a Second Measure as a Color

This one is straight-forward. We want to change the color of the line in the graph to indicate the areas where trade volume was higher. Simply drag ‘Volume’ from the Measures area and drop it onto the ‘Color’ box under ‘Marks.’

Notice the line changed colors in some areas — the darker areas are where the stock saw higher trade volume. Can you correlate these to any major rises or fall in the stock price?

Add a Second Measure as a Dual-Axis

If you want to go a little more advanced and professional looking (of course you do), try this!

If needed, remove the measure color from the previous section by dragging the pill from Marks to the empty area below. (Dragging any pill to this area removes it from the workbook.) Drag ‘Volume’ from the Measures area all the way to the far-right of the workbook until it shows a vertical rectangle. Release it here.

Now we have a Y-axis on the left showing our closing stock price range, and a Y-axis on the right showing our volume range. Now, let’s format it a bit. Under ‘Marks’ you should now see three collapsable sections — All, SUM(Adj Close) and SUM(Volume). Expand the last one, SUM(Volume). Using the drop-down menu, change it from Line to Bar. Also under Marks > SUM(Volume), click the box labeled ‘Size’ and change it from ‘Fixed’ to ‘Manual.’ Let’s go ahead and change the title of the workbook so we can finish formatting the new Y-axis. Double-click the title, and change it to ADIL Closing Price and Volume. Click OK to exit the dialogue. Double-click on the Y-axis on the right side; delete the title. In the same dialogue, select the ‘Tick Marks’ tab. Change the ‘Major Tick Marks’ from ‘Automatic’ to ‘Fixed.’ Close the dialogue. One final bit of housekeeping — let’s get rid of the rest of the lines. Right-click (or command-click) on the Y-axis on the left and select ‘Format.’ At the top of the Format pane on the left, select the borders option (looks like a square window-pane). Under Row Divider > Pane, select none. Under Column Divider > Pane, select none. Close the format pane.

Hey, you’re almost a pro!

We’re not done yet! Want to tackle a basic intro to stock analyzation and research? Of course you do!

Research the Findings

Look at your workbook. See anything that stands out?

Annotate It!

Let’s start with two obvious points — the 52 week high, and the 52 week low. These should be easy to find in your workbook. You can even hold your cursor over the point and Tableau will tell you the date and price. Let’s annotate them as such.

Right-click (or command-click) on the high point and select Annotate > Point. Clear the dialogue of its defaults. Set the justification to left, and enter something to help us understand what happened here, like “52 Week High: $6.88, Feb. 1.”

Annotation Dialogue

Repeat these steps for the 52 week low. Then, do something similar for any major outliers in trade volume. In our example, we can see a major outlier on February 21st. Let’s also mark the other outlier later in the year on December 16th. Call these something like “Major Trade Volume: 5.5m., February 21.” Use the same format as the 52 week high and low.

Research It!

Now comes the really fun part — seeing if we can figure out why these events happened. Can you answer the following questions by looking up the investor relations page of the company? Hint: all publicly traded companies have this section on their website.

Why did the stock experience an all time high on February 1st?

Why did the stock experience an all time low on December 5th?

Why did the stock experience such a high trade volume on February 21st?

Why was there a random instance of high volume on December 16th?

When were the quarterly results announced? (annotate these dates when you find them)

On the company’s investor relations page, start with press releases. Your basic answers are usually here. In the case of the stock we’re using, we can find out that:

The CEO appeared on two news programs on February 1st, one of which was in the Bay Area…also known as an area full of venture capital and investors. Notice this correlates with high trade volume (almost 1.9m) on the same day.

On February 21st, the company released an $8m public offering in an effort to raise more money. This correlates with a massive jump in trade volume, but also sort of lines up with a downward spiral in the stock price, as this likely diluted the value.

Quarterly results were released on May 13th, August 13th and November 14th. None of these are correlated to any outstanding activity on the graph. However, such results often do correlate with a jump in volume and a rise or fall in price depending on how good or bad the news is.

Finally, on December 16th, the company announced the allowance of an additional patent, correlating to a larger trade volume and significant increase in closing price.

Conclusion

After annotating the important things you’ve found, check out what you might have! A pretty infographic, with a basic analyzation of the stock.

What you learned:

How to look up a stock on Yahoo Finance and download its one year historical data.

Check the data so you can ensure it is prepped for Tableau.

Import a CSV into Tableau.

Create a workbook with a line graph, and a dual axis line graph and bar chart.

Research and analyze the basic historical activity of a stock.

Final note: this is on Tableau Public if you want to download and play with it. Best of luck in your Tableau and stock market journey!

RELATED: Tableau Order of Operations and the Top N Filter
['Alex Ashton']
['Tableau', 'Data Visualization', 'Stock Market', 'Analysis']